Skip to content
ITACC
Geometric network of connected nodes

Data Migration

Oracle stored procedures to Databricks: how to migrate PL/SQL and keep the same results

By Ed Svrini, CTO, ITACC · · 6 min read

Key takeaways

  • PL/SQL can land in Databricks as SQL stored procedures (CREATE PROCEDURE, Unity Catalog) or as PySpark code run by Lakeflow Jobs.
  • The hard part is behaviour, not syntax: empty strings, NULL concatenation, NUMBER precision, cursors, exceptions and sequences all need explicit decisions.
  • Inventory first: every procedure, package and scheduler job, with complexity and lineage, so you can migrate in small waves.
  • Prove each wave with a parallel run that compares outputs and traces every difference to a cause.
  • Validation works whichever converter you use, including Databricks' own Lakebridge.

Short answer: you can move Oracle stored procedures to Databricks either as Databricks SQL stored procedures or as PySpark code orchestrated by Lakeflow Jobs. Translating the syntax is the easy part. The real work is keeping Oracle's behaviour (how it treats empty strings, numbers, errors and row-by-row logic) so the new pipelines give the same answers as the old ones, and proving that they do.

This guide is for Canadian small and mid-size teams with a few dozen to a few thousand PL/SQL procedures and packages. It covers where each piece of Oracle code lands, the six behaviours that most often change results, how to phase the work, and how to prove the outputs match before you switch Oracle off.

Where Oracle stored procedures land in Databricks

Databricks now has a procedural SQL layer. SQL scripting (BEGIN ... END blocks with variables, IF, LOOP, WHILE, FOR and error handlers) runs in Databricks SQL and Databricks Runtime 16.3 and above. CREATE PROCEDURE needs Databricks Runtime 17.0 or above and Unity Catalog, and supports IN, OUT and INOUT parameters. That makes a procedure-for-procedure migration possible for SQL-minded teams. PySpark is the other common target, especially for heavy transformation logic.

OracleTypical Databricks targetWatch out for
Stored procedureDatabricks SQL stored procedure, or a PySpark functionThe body is not checked for semantic errors until the procedure is called
PackageA schema of related procedures, or a Python modulePackage-level variables (state) have no direct equivalent
Cursor FOR loopOne set-based INSERT, UPDATE or MERGECursors exist in SQL scripting only from Databricks Runtime 18.1
EXCEPTION WHEN ...DECLARE EXIT or CONTINUE HANDLER, or Python try/exceptCONTINUE handlers need Databricks Runtime 18.1 or above
Sequence NEXTVALIdentity column on a Delta tableValues are unique but not guaranteed to be contiguous
DBMS_SCHEDULER jobLakeflow JobsDependencies, retries and alerts must be set up explicitly

Six Oracle behaviours that change your results

1. Oracle treats an empty string as NULL

Oracle's documentation is explicit: the database treats a character value with a length of zero as null. Databricks treats '' as an ordinary value. A procedure that assigns '' and later tests IS NULL, or new source data arriving with empty strings, will behave differently after migration. Counts, filters and joins on those columns can all shift. Decide per column whether to convert empty strings to NULL on load, and make the converted logic say so.

2. Concatenation with NULL

In Oracle, concatenating a zero-length string with another operand returns the other operand, so 'Name: ' || NULL is 'Name: '. In Databricks, concat is null-intolerant: the NULL semantics reference shows concat('John', null) returning NULL, and || is a synonym for concat. Every concatenation of a nullable column needs a coalesce, or labels, keys and messages quietly turn into NULL.

3. NUMBER precision and scale

Oracle NUMBER allows precision 1 to 38 and scale -84 to 127. Databricks DECIMAL allows precision 1 to 38 and scale 0 up to the precision, and a bare DECIMAL defaults to DECIMAL(10, 0), so a careless cast drops the decimals. Oracle columns declared as plain NUMBER need a deliberate choice based on the real data. Avoid mapping money or quantities to DOUBLE: Databricks notes that DOUBLE loses precision beyond 15 significant digits.

4. Cursors and row-by-row loops

Many PL/SQL procedures open a cursor, loop over rows and update one row at a time. Databricks Runtime 18.1 adds cursors to SQL scripting, but on a distributed engine the same logic usually works better as one set-based MERGE or INSERT ... SELECT. When you rewrite a loop, check whether the old result depended on row order, on a COMMIT inside the loop, or on a counter such as SQL%ROWCOUNT. Those details are where rewrites go wrong.

5. Exceptions used as control flow

PL/SQL code often relies on implicit errors. A SELECT INTO that returns no rows raises NO_DATA_FOUND, and one that returns more than one row raises TOO_MANY_ROWS. Procedures catch these to choose a branch, log an error or stop a batch. In Databricks you have to write those checks yourself, with handlers for SQLEXCEPTION, NOT FOUND or a specific SQLSTATE, and SIGNAL to raise your own error. Handlers are not allowed inside ATOMIC blocks, so plan transaction boundaries and error handling together.

6. Sequences and scheduler jobs

Identity columns are the usual replacement for sequences. Databricks documents that their values are unique but not guaranteed to be contiguous, so any logic that expects gap-free numbers (invoice numbers, for example) needs another design. DBMS_SCHEDULER jobs become Lakeflow Jobs, with schedules, file-arrival triggers, task dependencies and notifications. For most configurations, failed tasks are not retried unless you add a retry policy, so set retries to match what the Oracle job did.

How to plan and phase the migration

  1. Inventory everything. List every procedure, function, package, trigger and scheduler job, with a complexity rating per object and lineage (which tables each one reads and writes).
  2. Pick a first wave. Choose a handful of procedures that matter to the business and have clear outputs you can check.
  3. Choose a target per object. SQL stored procedures suit SQL-heavy logic your team will maintain in SQL; PySpark suits large transformations and code that needs testing frameworks.
  4. Convert and review. Automated conversion, then a human review of every NULL, number, error and loop decision listed above.
  5. Prove the results with a parallel run (next section).
  6. Cut over wave by wave. Keep Oracle available read-only until each wave is signed off.

How to prove the new pipelines give the same answers

Run the legacy procedure and the new Databricks version on the same inputs, then compare the outputs table by table: row counts, keys present on one side only, and column-by-column values. Agree tolerance rules up front (for example, timestamp precision). Then trace every difference to a cause: empty-string handling, rounding, row order, source data timing, or a real conversion bug. A wave is done when every difference is either fixed or explained and accepted by the business owner.

Databricks offers Lakebridge, a free toolkit from Databricks Labs with assessment, conversion and reconcile steps. It is a sensible place to start. The planning and validation steps above apply whichever converter you use.

How ITACC helps with Oracle to Databricks migrations

ITACC is an AI and data engineering partner based in Oakville, Ontario. ITACC's migration tool, now in early access, does three things:

  • Converts Oracle SQL and PL/SQL procedures (and Informatica PowerCenter and IDMC mappings and workflows) into Databricks SQL, PySpark and Databricks jobs, keeping Oracle behaviour such as NULL handling and error behaviour.
  • Assesses your legacy code: an inventory with complexity per object and lineage, so you can plan waves.
  • Proves the results by comparing the new outputs with the legacy outputs and tracing every difference to its cause. This works on output from any converter, including code your team or another tool has already converted.

The tool has been tested on hundreds of real-world PL/SQL files. Learn more about our data and workflow automation work and custom AI development, or browse more Insights. The best first step is small: talk to us about a pilot on a few of your own procedures or mappings.

Frequently asked questions

Can Oracle stored procedures run in Databricks?

Not as-is, but they can be converted. Databricks supports SQL stored procedures (CREATE PROCEDURE, Databricks Runtime 17.0+ with Unity Catalog) and SQL scripting with variables, loops and error handlers. Many teams also convert procedures to PySpark run by Lakeflow Jobs.

What is the biggest risk when migrating PL/SQL to Databricks?

Silent differences in results. Oracle treats empty strings as NULL, concatenation ignores NULLs, and procedures often depend on NO_DATA_FOUND or row-by-row logic. Converted code can run without errors and still give different numbers, so every wave needs output comparison.

Should PL/SQL become Databricks SQL or PySpark?

It depends on the object and your team. SQL stored procedures keep the logic recognisable for SQL developers; PySpark suits large transformations and code that benefits from Python testing. Many migrations use both.

How do we prove the migrated pipelines are correct?

Run old and new side by side on the same inputs, compare row counts, keys and values column by column, and trace each difference to a cause until all are fixed or accepted.

Abstract flowing lines of light

Ready to take AI past the demo?

Tell us what you want to build. We'll reply within one business day, and we're happy to sign an NDA first.