AI-Powered PL/SQL Analysis: How We Score Package Complexity

“How Bad Is the PL/SQL?”

It is the first question on every Oracle migration, and the usual answer — a line count — is close to useless. Fifty thousand lines of straightforward CRUD procedures is a smaller job than four thousand lines that use autonomous transactions, package state and DBMS_SQL. Effort tracks construct density, not volume.

The Signals That Actually Predict Effort

• Package state. Package-level variables that persist for a session have no direct PostgreSQL equivalent. Each one is a design decision — GUC via set_config, a session temp table, or a rewrite.
• Autonomous transactions. PRAGMA AUTONOMOUS_TRANSACTION needs dblink or a restructure. Always a human decision, never mechanical.
• Dynamic SQL. EXECUTE IMMEDIATE maps cleanly; DBMS_SQL’s verbose cursor API usually collapses to a few lines, but only after someone reads what it was doing.
• Collections and bulk operations. BULK COLLECT, FORALL, and TYPE ... IS TABLE OF have array-based equivalents that change the shape of the code.
• Oracle built-in dependencies. UTL_FILE, UTL_HTTP, DBMS_LOB — each pulls in an extension or an application-layer change.
• Fan-in. A procedure called from thirty places is riskier to change than one called from one, regardless of its own complexity.

Turning Signals Into a Score

The scoring itself is deliberately boring: parse each object, count occurrences of the constructs above, weight them by how much human work each historically costs, and sum. Boring is the point — a score you can’t explain is a score nobody will act on. Every number in our report can be traced back to the specific lines that produced it.

PKG_BILLING              score 84   HIGH
  package state (3 vars)          -> GUC or session table
  autonomous transaction (1)      -> dblink or redesign
  DBMS_SQL (2 call sites)         -> collapse to EXECUTE
  called from 31 objects          -> high blast radius

PKG_LOOKUP               score 6    LOW
  straight SELECT wrappers        -> mechanical

Two packages, similar line counts, entirely different jobs. That distinction is what a complexity score is for — it tells you where the weeks go.

Where the Model Helps — and Where It Doesn’t

Detection is a parsing problem, and we treat it as one. Rules find the constructs, deterministically, every time. A language model that “usually” spots autonomous transactions is strictly worse than a regex that always does.

Where a model genuinely earns its place is explanation: reading a 900-line package nobody has owned since 2011 and summarising what it does, so the person deciding whether to port or rewrite has something to decide with. Detection is mechanical; interpretation is not.

Using the Score

Sort ascending and start at the bottom. The low-score objects convert almost entirely automatically and give you a working target schema early, which is what lets integration testing start. The high-score objects are where you want your most experienced person and your longest estimate — and where you should seriously ask whether a 1,400-line package needs porting at all, or whether the application should own that logic now.