This is Part 3 of a series on madsql.
Part 1 covered split-statements. Part 2 covered infer-schema. This post covers convert — the main migration command.
By the time you reach convert in a migration workflow, you’ve already
done two things: split your scripts to understand what you’re working
with, and inferred a starter schema to test against. Now you convert.
convert is the most flag-rich command in madsql because batch SQL
conversion has the most variables: single files or directory trees,
stdout or named output, split output or merged, schema artifacts or not,
pretty or compact, fail fast or continue. This post works through each
of those decisions with Oracle-targeted examples, and closes with a
complete CI pipeline pattern.
When to Use convert
Reach for convert when:
- You need translated SQL in a target dialect as the primary output
- You’re processing a directory of SQL scripts and need the output
structure preserved - You want converted SQL and a schema side artifact in a single pass
- You’re building a repeatable, automated migration pipeline where
deterministic output is required - You need structured error reports and run logs that can be attached
to a migration ticket or CI artifact
The minimum required flags are --source and --target. Everything
else is optional depending on your workflow.
Quickest Possible Start: stdin to stdout
The fastest way to test a conversion is to pipe a statement in:
echo "SELECT TOP 3 [name] FROM dbo.employees;" | madsql convert --source tsql --target oracle
Output:
SELECT "name" FROM dbo.employees FETCH FIRST 3 ROWS ONLY
Two translations in one statement: TOP 3 becomes
FETCH FIRST 3 ROWS ONLY and T-SQL bracket quoting becomes Oracle
double-quote quoting. Useful for spot-checking individual statements
before committing to a batch run.
More examples from common T-SQL to Oracle translation patterns:
# ISNULL -> NVLecho "SELECT ISNULL(salary, 0) FROM employees;" | madsql convert --source tsql --target oracle
SELECT NVL(salary, 0) FROM employees
# String concatenationecho "SELECT first_name + ' ' + last_name AS full_name FROM employees;" | madsql convert --source tsql --target oracle
SELECT first_name || ' ' || last_name AS full_name FROM employees
# GETDATE() -> SYSDATEecho "SELECT GETDATE() AS current_time;" | madsql convert --source tsql --target oracle
SELECT SYSDATE AS current_time FROM DUAL
Converting a Single File
Convert a file and write output to a directory:
madsql convert --source tsql --target oracle --in ./examples/input/mssql/example_queries.sql --out ./converted
The output file uses a deterministic name based on the input stem and
target dialect: example_queries.oracle.sql. The relative path of the
input is preserved under --out:
./converted/examples/input/mssql/example_queries.oracle.sql
Add --pretty for multiline formatted output, which is easier to read
during review:
madsql convert --source tsql --target oracle --in ./examples/input/mssql/example_queries.sql --out ./converted --pretty
Batch Converting a Directory Tree
This is where convert delivers the most value. Point it at a directory
and every SQL file is converted, with the relative structure preserved
under --out:
madsql convert --source tsql --target oracle --in ./examples/input/mssql --out ./converted
Output layout mirrors the input layout:
./converted/└── examples/ └── input/ └── mssql/ ├── example_queries.oracle.sql ├── noGOSchema.oracle.sql └── sql-server-sakila-schema.oracle.sql
To target specific files across a tree, use a glob. Quote it to ensure
madsql expands it rather than your shell:
madsql convert --source tsql --target oracle "./examples/input/mssql/**/*.sql" --out ./converted
Converting and Splitting Into One File Per Statement
Add --split-statements to produce individually-numbered converted
files instead of one merged output per input file. Each statement is
translated and written separately:
madsql convert --source tsql --target oracle --in ./examples/input/mssql/example_queries.sql --out ./converted --split-statements
Output:
./converted/examples/input/mssql/example_queries/├── 0001_stmt.oracle.sql├── 0002_stmt.oracle.sql├── 0003_stmt.oracle.sql...└── 0009_stmt.oracle.sql
This is useful when you want to replay individual converted statements
against Oracle rather than running the whole script at once — which is
often the right approach during early validation on a migration project.
Converting and Inferring Schema in a Single Pass
Add --infer-schema to write a schema side artifact alongside the
converted SQL. This avoids running two separate commands when you want
both outputs from the same input:
madsql convert --source tsql --target oracle --in ./examples/input/mssql --out ./converted --infer-schema --pretty
The schema artifact lands at the base of --out with a deterministic
name:
./converted/inferred_schema-tsql-to-oracle.sql
To get Oracle user and grant boilerplate in that artifact:
madsql convert --source tsql --target oracle --in ./examples/input/mssql --out ./converted --infer-schema --infer-schema-create-user --infer-schema-create-user-password 'ChangeMe123' --pretty
Note the flag naming: schema side artifact options use the
--infer-schema-* prefix in the convert workflow, which distinguishes
them from the standalone infer-schema command flags.
Output Naming and the –suffix Flag
By default, converted files are named <stem>.<target>.sql. To change
the suffix:
madsql convert --source tsql --target oracle --in ./sql --out ./converted --suffix .migrated.sql
This produces files like example_queries.migrated.sql instead of
example_queries.oracle.sql. Useful when the output is going into a
repository where the target dialect name in the filename would be
redundant or confusing.
Error Handling in Batch Runs
The default behavior is to continue through failures and report them at
the end. If a statement can’t be converted, it’s recorded with its
statement index and the run continues with the next statement. Exit code
is 1 if any failures were recorded.
To make continuation explicit in scripts (useful for readability in
automation):
madsql convert --source tsql --target oracle --in ./sql --out ./converted --continue
To stop on the first failure:
madsql convert --source tsql --target oracle --in ./sql --out ./converted --fail-fast
To write structured error details to a JSON file:
madsql convert --source tsql --target oracle --in ./sql --out ./converted --errors errors.json
The errors JSON includes version_info at the top level and an errors
array. Each error entry contains the input path, statement index, error
type, parser message, and the SQL text of the failing statement. That’s
enough to identify and fix most failures without re-running the full
batch interactively.
--continue and --fail-fast are mutually exclusive.
Complete Run With All Artifacts
For a migration batch you’re handing off — to a review, a ticket, or
another team — pull all the observability features together:
madsql convert --source tsql --target oracle --in ./examples/input/mssql --out ./converted --infer-schema --infer-schema-create-user --infer-schema-create-user-password 'ChangeMe123' --pretty --continue --errors errors.json --log 1 --report
What lands in ./converted:
- All converted
.oracle.sqlfiles with the input directory structure
preserved inferred_schema-tsql-to-oracle.sql— Oracle DDL with user
creation boilerplateerrors.json— structured error report with version info,
statement indexes, and SQL text for any failuresYYYYMMDD-HHMMSS-madsql-convert.log— detailed run log including
full command line, dialect settings, and per-statement resultsYYYYMMDD-HHMMSS-madsql-convert-report.md— Markdown summary with
statement counts, success rate, dialect info, and version details
The Markdown report is the most useful artifact to attach to a migration
ticket. It gives reviewers everything they need to understand what ran,
what succeeded, what failed, and what version of the tool produced the
output — without reading through logs.
Practical Debugging Workflow
A batch run comes back with exit code 1. Here’s the sequence that
gets you to the root cause fastest.
Step 1: Re-run the failing file by itself with an errors report.
madsql convert --source tsql --target oracle --in ./examples/input/mssql/noGOSchema.sql --out ./debug --errors debug-errors.json
Step 2: Split the file to find the failing statement index.
madsql split-statements --source tsql --in ./examples/input/mssql/noGOSchema.sql --out ./debug/split
The numbered output files tell you which statement indexes succeeded and
which are absent. Open the failing statement file directly — it’s
often a dialect construct that SQLGlot doesn’t fully support for that
source/target combination, or a non-standard syntax that needs a manual
rewrite.
Step 3: Read the error detail from debug-errors.json.
The errors file includes the statement index, error type, parser
message, and the SQL text of the failing statement. Combined with the
split output, this is almost always enough to diagnose and fix the
problem.
Step 4: Once resolved, scale back out to the full batch.
madsql convert --source tsql --target oracle --in ./examples/input/mssql --out ./converted --continue --errors errors.json --overwrite
Common Pitfalls
Missing –out for multi-input runs. When more than one input file
is resolved — from a directory, glob, or multiple positional arguments
— --out is required. madsql exits with code 2 (CLI misuse) if
it’s missing.
Not quoting glob patterns. If your shell expands the glob before
madsql sees it, the tool receives a flat list of files rather than a
pattern to expand itself, which can change the output layout in
unexpected ways. Quote glob patterns: "./sql/**/*.sql".
Overwrite refusal on repeated runs. madsql won’t replace existing
output files without --overwrite. In a development workflow where
you’re iterating on the same input, always add --overwrite or clean
the output directory between runs.
Expecting all SQL constructs to convert cleanly. SQLGlot has broad
dialect coverage but it isn’t exhaustive. Complex stored procedures,
vendor-specific extensions, and highly non-standard syntax may produce
partial or incorrect conversions. Treat the output as a strong starting
point, not a finished product — especially for DDL-heavy scripts with
Oracle-specific constructs on the target side.
Using –infer-schema flags without –out. The schema side artifact
needs somewhere to land. --infer-schema requires --out.
Mixing –continue and –fail-fast. They’re mutually exclusive.
madsql exits with code 2 if both are present.
CI Integration Notes
madsql’s deterministic output and predictable exit codes make it
straightforward to embed in a CI pipeline. Here’s a complete pattern
for an Oracle migration CI job:
madsql convert --source tsql --target oracle --in ./sql --out ./converted --infer-schema --infer-schema-create-user --infer-schema-create-user-password "${ORACLE_SCHEMA_PASSWORD}" --errors errors.json --log 1 --report --continue
Useful patterns on top of this:
Diff the schema artifact. The deterministic output name
(inferred_schema-tsql-to-oracle.sql) means you can commit a baseline
to source control and diff on each run. A schema change in CI means the
underlying query workload changed in a structurally meaningful way —
worth flagging for review.
Publish the Markdown report as a CI artifact. Most CI systems
support artifact attachment. The Markdown report is a clean
human-readable summary that reviewers can read directly in the CI UI or
download without needing to interpret log output.
Use –ignore-errors to keep the build green during early migration
phases. When you’re still iterating on source SQL quality,
--ignore-errors lets you capture all diagnostics without failing the
build on every unsupported construct:
madsql convert --source tsql --target oracle --in ./sql --out ./converted --errors errors.json --ignore-errors
Switch to the default exit-code behavior (or --fail-fast) once the
workload is stable enough that you want CI to enforce clean conversion.
Version-pin madsql in CI. The determinism guarantee is tied to a
specific SQLGlot version. Pin the madsql release in your CI environment
and treat version upgrades as a deliberate step that may produce changed
output — not an automatic dependency update.
Quick Reference: Flag Combinations
Goal Flags to add
Readable output for review --pretty
Replace existing output files --overwrite
One file per converted statement --split-statements
Schema DDL alongside converted SQL --infer-schema
Oracle user + grants in schema artifact --infer-schema-create-user --infer-schema-create-user-password <pw>
Custom output file suffix --suffix .migrated.sql
Stop on first failure --fail-fast
Structured error details --errors errors.json
Full run artifacts for handoff --errors errors.json --log 1 --report
CI green build with diagnostics captured --ignore-errors --errors errors.json
That wraps the madsql series. The
repository has the full README,
built-in help for each command, and binary releases for macOS, Linux,
and Windows. Issues and pull requests are welcome.