Overview
plsql is one of the source SQL dialects recognized by the 5-phase conversion pipeline (Discovery → Planning → Conversion → Validation → Auto-Fix) behind 3xcode pyspark convert. It covers Oracle's procedural extension to SQL (anonymous blocks, stored procedures, packages, functions, triggers, cursors, and exception handlers), as opposed to plain declarative SQL statements.
PL/SQL is a full procedural language layered on top of SQL, so PL/SQL files tend to carry more control-flow (loops, conditionals, exception blocks) and more stateful constructs (cursors, packages with persistent state) than a typical T-SQL or ANSI SQL script. The planning phase accounts for this by resolving package and procedure dependencies before generating any code, so a package body that calls another package's function converts against that function's real signature rather than a guess.
Full pipeline coverage
Discovery, planning, conversion, validation, and auto-fix all apply to plsql files exactly as they do for every other supported dialect. Nothing is skipped or degraded for Oracle sources.
Package-aware planning
Packages are treated as dependency units: package specs and bodies are resolved before the procedures that call them, so a package body's functions are always known before the procedures that depend on them convert.
Dialect & Platform Detection
Detection happens in two separate layers during the Discovery phase. First, the conversion agent classifies the file's dialect as plsql based on its syntax and structure (a dialect field-validator normalizes common spellings and labels down to the canonical dialect names, so files described as "Oracle SQL" or "Oracle PL/SQL" are both classified as plsql).
Second, a separate deterministic, regex-only platform-detection pass (no LLM call) scores the file against syntax markers for 10 platforms, including Oracle, and records a confidence label plus the exact markers that fired. This platform fingerprint is written as a one-line header comment in every generated output file, so you can see at a glance what the pipeline detected and how confident it was.
Object inventory
Every PL/SQL object in the file (package, procedure, function, trigger) is catalogued with exact line ranges, parameters, cross-references, cursor/dynamic-SQL/transaction flags, and a complexity score.
Explainable, not black-box
Platform detection never blocks conversion if it fails or is inconclusive: it's wrapped so a detection error is simply skipped rather than failing the whole file.
PL/SQL Constructs Handled
The planning phase runs a construct-detection pass over each file and selectively injects only the relevant knowledge-base sections for the constructs actually present. This is what keeps a simple SELECT+JOIN script cheap to convert while still giving a cursor-and-exception-heavy package the full relevant guidance.
Construct categories recognized
| Category | What it covers |
|---|---|
| Stored procedures | CREATE OR REPLACE PROCEDURE bodies, IN/OUT/IN OUT parameters, nested procedure calls |
| Packages | Package specs and bodies, package-level state, cross-package function calls |
| Cursors | Explicit and implicit cursors, cursor FOR loops, %ROWTYPE/%TYPE anchoring |
| Exception handling | EXCEPTION blocks, named exceptions, WHEN OTHERS handlers, RAISE / RAISE_APPLICATION_ERROR |
| Dynamic SQL | EXECUTE IMMEDIATE and dynamic statement construction |
| Transactions | COMMIT / ROLLBACK / SAVEPOINT usage inside procedural blocks |
| Window functions | Analytic functions (RANK, ROW_NUMBER, LAG/LEAD, etc.) |
| CTEs | WITH clauses and recursive subquery factoring |
| Joins & aggregation | Standard SQL joins and GROUP BY / aggregate logic embedded in PL/SQL blocks |
Packages & Stored Procedures
Package specs and bodies are treated as first-class objects in the dependency graph the planning phase builds. If a package body function is called by a stored procedure elsewhere in the same file (or in another file processed in the same run), the procedure is scheduled to convert only after the package function's signature is known. Dependency resolution runs before conversion begins, the same way it does for any cross-object reference the planning phase discovers.
At the output-file level, the deterministic grouping step (no LLM call) sorts converted objects into utilities (pure functions and simple procs called by 2+ other objects, frequently where package helper functions land), main_pipeline (the highest-complexity procedure plus its exclusive helpers), and standalone (independently schedulable jobs). A signature registry is threaded across these groups so a procedure calling into a package's converted function sees that function's real PySpark signature rather than guessing at its shape.
# Single file: package body or standalone procedure 3xcode pyspark convert orders_pkg.sql # Preview the object inventory and conversion plan first (no code generated) 3xcode pyspark analyze orders_pkg.sql # Bulk-convert every .sql file in a directory of PL/SQL packages 3xcode pyspark convert --dir ./oracle_packages/ --session "oracle-migration" -w 4
Cursors & Row-by-Row Logic
Cursors are one of the constructs Discovery flags explicitly on every object (has_cursor) because they have no direct PySpark equivalent: Spark is a distributed, set-oriented engine, and row-by-row cursor loops don't map onto that model without a rewrite in approach, not just syntax.
Preferred: rewrite as a window function
Most cursor loops that accumulate, rank, or compare rows can be expressed as native window functions (F.lag(), F.lead(), F.rank(), running aggregates), which the pipeline's knowledge base specifically recommends over a UDF loop.
Avoid: Python UDF row loops
A cursor translated literally into a Python UDF that loops row by row runs 50-100x slower than the equivalent native PySpark operation. The pipeline treats this as a correctness/performance issue, not just a style preference.
Cursor-driven objects also lower the pipeline's own confidence score for that file (the confidence calculation applies a deduction whenever the object involves a cursor or dynamic SQL), which is a signal worth watching in the generated audit report: it tells you where to look first during review, not just what compiled.
Exception Handling
PL/SQL EXCEPTION blocks (named exceptions, WHEN OTHERS, RAISE_APPLICATION_ERROR) are recognized during Discovery as part of an object's construct profile and factored into its complexity score. The conversion phase applies the pipeline's exception-handling guidance to translate each handler into idiomatic PySpark logic, and cases where the original Oracle error semantics (a specific Oracle error number or named exception) don't have a direct equivalent are surfaced in the audit report for review rather than silently dropped.
Transactions (COMMIT / ROLLBACK / SAVEPOINT) that appear inside exception-handling logic are flagged the same way transactions are flagged everywhere in the pipeline: they have no direct PySpark equivalent and are classified as requiring manual review rather than an automatic rewrite (see Known Gotchas below).
Known Gotchas
These are the specific correctness pitfalls the conversion and validation phases actively check for. Some are dialect-general (they apply across T-SQL, PL/SQL, and other sources that share the underlying issue); the ones most relevant to Oracle PL/SQL migrations are called out here.
Gotchas the pipeline checks for
| Category | Gotcha | How it is handled |
|---|---|---|
| NULL semantics | NULL ordering in ORDER BY differs between Oracle and Spark | Flagged during validation for review |
| NULL semantics | COUNT(*) vs COUNT(col) behave differently with NULLs | Checked during validation against the source object’s intent |
| String ops | Case-sensitive string comparison: Spark is case-sensitive by default, unlike some Oracle collations | Flagged so comparisons aren’t silently wrong after conversion |
| Cursors | Python UDF row loops run 50-100x slower than native PySpark operations | Pipeline recommends window functions over UDF loops (see Cursors section above) |
| Type casting | Decimal arithmetic precision loss when casting between numeric types | Flagged during validation for review |
| No direct equivalent | Cursors, dynamic SQL / EXECUTE IMMEDIATE, transactions (COMMIT/ROLLBACK/SAVEPOINT), MERGE/upsert, recursive queries | Classified as `requires_manual`, surfaced in the audit with prescriptive fix guidance, never silently auto-converted |
Every issue found during validation is bucketed into one of four developer action items in the final audit: auto-fixed, requires manual review (cursors, dynamic SQL, transaction logic), infrastructure setup (things needing external provisioning, not code changes), or recommended review (lower-severity items). This keeps the distinction between "the pipeline fixed this" and "you need to look at this" explicit rather than buried in a wall of comments.
Converting a PL/SQL File
You don't need to specify the dialect. Discovery detects plsql automatically from the file's syntax. A workspace and a valid login are required before any conversion or analysis, since even a dry-run analysis calls the LLM.
# 1. Log in and set up a workspace once 3xcode login 3xcode workspace init ./oracle-migration --name "oracle-migration" # 2. Dry-run analyze a package to see the object inventory and plan 3xcode pyspark analyze billing_pkg.sql # 3. Convert a single package/procedure file 3xcode pyspark convert billing_pkg.sql # 4. Bulk-convert an entire directory of PL/SQL files, 4 workers in parallel 3xcode pyspark convert --dir ./sql/oracle/ --session "oracle-batch-1" -w 4 # 5. If a bulk run is interrupted, list sessions to find the generated session ID 3xcode session list # 6. Resume using that ID (not the bare --session name you passed in step 4) 3xcode pyspark convert --resume <session-id-from-list>
The --session flag sets a human-readable name, but the resumable session ID the pipeline actually generates is derived from it rather than identical to it. Run 3xcode session list to see the exact ID before passing it to --resume.
Each converted package or procedure produces a .py module, a per-file _audit.md report in human-readable form, and a canonical _audit.json, including a per-file confidence/completion grade (A+ through F) so you know exactly which converted objects deserve a closer look before they ship.
FAQ
Do I need to specify that my file is PL/SQL?
No. Dialect classification and platform fingerprinting both run automatically during the Discovery phase. There is no --dialect flag to set.
A package body converts but calls into an unresolved signature: what happened?
This usually means the package spec/body it depends on wasn't included in the same conversion run. Convert dependent packages together, e.g. with --dir bulk mode against the whole package directory, so the planning phase can resolve cross-package signatures before conversion.
Why does a cursor-heavy procedure get a low confidence grade?
This is expected and intentional: objects with cursors or dynamic SQL get a confidence deduction because they often require a genuine rewrite (e.g. cursor loop → window function), not just a syntax translation. Check the object's entry in the audit report for the specific requires_manual items flagged.
Is my PL/SQL source code sent to the 3XDE backend?
No. Source SQL is never sent to the 3XDE backend. The CLI talks to your configured LLM provider directly for conversion. The backend only handles auth, licensing, and provider-key metadata.