A detailed walkthrough shows how a legacy Oracle stored procedure (with nested cursors, temp tables, and multi-statement transactions) can be translated line-by-line into Databricks SQL scripting rather than rewritten in Python/Spark. It covers procedure syntax differences, session-scoped temp tables, native cursor support (OPEN/FETCH/CLOSE, CONTINUE HANDLER FOR NOT FOUND, LEAVE/ITERATE) added since Databricks Runtime 18.1, and BEGIN ATOMIC blocks that provide transactional semantics with row-level rather than table-level conflict detection (contrasted with Oracle and Snowflake's table-level locking). The piece claims migration timelines can be cut 50-75% and points to an Agentic Code Converter tool to help teams try it on their own procedures.
Table of contents
Take the original business logicNow lay the foundation on DatabricksThen we tackled the temporary tables: the easy win in a data warehouse migrationThe complete migrated procedureWhat we learnedQuestions this post answers
Does Databricks SQL support cursors like Oracle PL/SQL?
Yes, Databricks SQL scripting natively supports cursors with OPEN, FETCH, and CLOSE statements since Databricks Runtime 18.1. The Oracle %NOTFOUND attribute maps to a CONTINUE HANDLER FOR NOT FOUND, and loop labels with LEAVE replace EXIT WHEN, allowing row-by-row cursor loops to be translated with minimal structural changes. daily.dev surfaces posts like this for teams weighing whether Databricks SQL can replace their PL/SQL cursor logic.
How do I replicate Oracle's implicit transaction commit and rollback behavior in Databricks SQL?
Use a BEGIN ATOMIC ... END block, which automatically commits on success and rolls back on failure, mirroring an implicit transaction with an explicit COMMIT in legacy systems. Unlike Oracle and Snowflake, which use table-level locking that forces serial execution, Databricks provides row-level conflict detection so concurrent batches only conflict when they touch the same rows. Engineers planning transactional SQL migrations can track patterns like this through daily.dev.
Can I use CREATE OR REPLACE TEMP TABLE in Databricks SQL?
No, CREATE OR REPLACE TEMP TABLE is not yet supported in Databricks SQL scripting. If a temp table needs to be re-runnable within the same session, you must explicitly drop it first with DROP TABLE IF EXISTS before creating it again with CREATE TEMP TABLE. daily.dev helps developers stay ahead of syntax gaps like this before they hit production.