ACL Digital

Join The Team
Home / Blogs / The Transpilation Deep Dive: PL/SQL to IRIS Native Python
The Transpilation Deep Dive PLSQL to IRIS Native Python
Picture of Shubham Rathod

Shubham Rathod

April 13, 2026

5 Minutes read

The Transpilation Deep Dive: PL/SQL to IRIS Native Python

Most companies sitting on years of Oracle PL/SQL know the pain. The code works. It has been running for a decade. However, every time modernization is attempted, database procedures are the one component nobody wants to touch. They are complex, largely undocumented, and deeply intertwined with business logic that is often no longer fully understood.

The real problem isn’t moving data; It is moving logic. Oracle PL/SQL environments often contain thousands of lines of stored procedures, cursors, exception handlers, and package variables that function in a highly Oracle-specific way. Copy-pasting them into Python is not a viable solution. They must be translated correctly, or the result may run without errors but silently produce incorrect outputs, a major risk in AI-driven Database Migration and Legacy Modernization services within the Information Technology and Enterprise Software industry.

This article explores how we built an automated pipeline to translate Oracle PL/SQL into InterSystems IRIS Native Python, highlighting key transformation patterns, common pitfalls, and the safeguards required in Generative AI and Intelligent Automation solutions.

1. Anatomy of a Transformation

One of the most common anti-patterns in PL/SQL migration is translating code line-by-line, mapping each SQL keyword directly into Python. This results in Python code that still behaves like PL/SQL, is hard to maintain, and inefficient, limiting the effectiveness of Data Engineering and Enterprise AI solutions.

The better approach is to look at what the PL/SQL does and translate the intent, not the syntax.

Take the most common pattern in PL/SQL, an explicit `CURSOR` that loops over rows and processes each one:

What the Oracle PL/SQL looks like

PL SQL

Oracle manages the cursor in memory inside the database engine. You have to manually open it, fetch each row, check if you’ve hit the end, and remember to close it. If you forget to close it, you have a resource leak.

What the IRIS Python equivalent looks like

Equivalent Python loop using iterator-based result set, eliminating manual cursor handling.

In Python, for row in result set abstracts all of this. There is no need for manual open/close operations, no explicit termination checks, and cleanup is automatic, even during exceptions. This leads to more robust and maintainable code, which is critical in AI-powered Application Modernization services.

The Transpilation Deep Dive
Architecture showing Python iterator automatically managing cursor lifecycle efficiently.

At scale, this difference becomes even more significant. Python processes rows incrementally without holding large cursors in memory, reducing database load, an essential optimization in Data Analytics and Enterprise Software systems.

By moving from a state-heavy explicit cursor to a prepared SQL statement returning a generator-like result set, we drastically reduce memory overhead on the application server. The Python logic simply iterates over the result set one row at a time. The error handling is handled natively via try-except, ensuring that dataset resource cleanup is guaranteed (even during anomalous faults) and that the core business logic remains cleanly separated from the data retrieval mechanics.

2. Solving the Package Problem: Managing Stateful Context

This one trips up most teams that try to migrate Oracle procedures on their own.

Oracle has a feature called “Packages” – they let you group related procedures together and, crucially, they can hold variables widely used in Banking, Financial Services, and Healthcare Technology applications, that persist across multiple procedure calls within the same session. Think of it like a shared notebook that all the procedures in a package can read from and write to during a user’s session.

Python doesn’t have a direct equivalent of this. If you just migrate each procedure as a separate standalone function, those shared variables disappear between calls, and suddenly, your migrated code gives wrong results that are really hard to trace.

The fix is to use Python classes. A class instance keeps its own state (via `self.variable`), which maps perfectly to what an Oracle package does. When a new session starts, you create a new instance of the class, and all the methods share the same state for the lifetime of that session.

The Structural Mapping

Mapping Oracle package state to Python class instance variables, maintaining session context.

Python Implementation Skeleton

Python Implementation Skeleton

By wrapping the distinct transpiled functions within a cohesive, unified Python class definition, package-level variables are elegantly translated into class-level attributes (accessed via self.variable_name).

When a new database connection or user session initiates an action, the corresponding package class is instantiated. This stateful instantiation approach exactly mirrors the lifecycle properties of an Oracle session state. It flawlessly preserves the shared context across multiple method calls, ensuring that execute_procedure_A can safely mutate a counter integer that execute_procedure_B relies upon later in the flow, completely bridging the architectural gap between procedural PL/SQL and Object-Oriented Python.

This way, the pipeline detects Oracle packages automatically and generates the corresponding Python class instead of a set of disconnected functions. This means the shared state works exactly as it did before – zero behavior change, and the Python code is more readable in the process.

3. Leveraging the IRIS Native Advantage: Beyond Generic Python

When most teams move to a new database platform, they connect to it the same way they always have, through a standard SQL driver. It works, but it leaves a lot of performance on the table because every query goes through the full SQL parsing and network overhead stack.

InterSystems IRIS has a Native Python API that lets you go much deeper. For specific operations, you can skip SQL entirely and talk to the database engine at a much lower level:

IRIS Globals (`iris.gref`): For things like high-speed counters or key-value lookups that don’t need full SQL, you can read and write directly to IRIS Global storage. This is microsecond-level access, much faster than even a simple SQL `SELECT`.

Python code snippet

Direct Class Method Calls: Instead of building SQL strings to call other stored procedures, you can call IRIS class methods directly from Python (`iris.cls(‘PackageName.ClassName’).MethodName()`). The boundary between Python and IRIS essentially disappears.

Our migrator identifies which operations can benefit from these approaches and uses them where it makes sense, rather than wrapping everything in generic SQL calls.

4. The Trickiest Part: NULL Values

This is the one that catches almost every migration project off guard. The code runs perfectly, no errors, everything looks fine, and then you realize it’s been giving you subtly wrong results all along.

The problem comes down to how Oracle and Python handle the absence of a value:

The string concatenation difference:

In Oracle, if you concatenate a string with `NULL`, you just get the string back. `’A’ || NULL` gives you `’A’`. No error, no fuss.

In Python, `”A” + None` throws a `TypeError` and crashes. They’re fundamentally different behaviors.

The empty string difference:

In Oracle, an empty string `”` is treated the same as `NULL`. In Python, `””` is an empty string and `None` is nothing; they’re different things.

The three-valued logic difference:

Oracle’s `NULL = NULL` evaluates to `UNKNOWN`, not `TRUE`. Python’s `None == None` is `True`. This means `IF` statements that involve `NULL` comparisons can behave completely differently between Oracle and Python.

If you just do a surface-level translation without accounting for these differences, you end up with “silent failures” — the procedure runs and returns a result, but the result is wrong. These are the hardest bugs to find because there’s no error message pointing you to the problem.

To address this, we had to implement a dedicated Validation Agent that checks the generated Python against the original PL/SQL logic. When it finds a place where NULL handling could diverge, it automatically adds the appropriate Python guards — replacing bare string concatenation with safe wrappers and making NULL checks explicit. It also runs the migrated code against the same test inputs as the original Oracle procedure to verify the outputs match.

Conclusion

Moving off PL/SQL is one of the harder parts of any database modernization project, but it doesn’t have to be a gamble. The key is treating it as a semantic translation problem, understanding what the old code was actually doing, rather than just a syntax problem.

By mapping Oracle’s cursor loops to clean Python iterators, converting packages to classes, taking advantage of the IRIS Native API where it helps, and actively catching NULL-handling differences before they cause silent bugs, it’s possible to migrate complex stored procedure logic with full confidence.

At ACL Digital, we’ve built this whole process into an AI-driven pipeline that handles the translation, validates the output, and flags anything that needs a human to review. The goal is simple: your migrated code should behave exactly like your Oracle code did, just running on a faster, more modern platform.

References

Related Insights

Turn Disruption into Opportunity. Catalyze Your Potential and Drive Excellence with ACL Digital.

    I have read and agreed to Privacy Policy and consent to sharing my information. For more information, please review our Cookies Policy and Privacy Statement.




    Scroll to Top