How to find hidden rules during a PL/SQL migration

The dangerous part of a PL/SQL migration is not converting syntax. It is discovering which database behavior the company depends on before the old database stops providing it. Package bodies, row and statement triggers, scheduled calls, session state, and exception paths can carry rules that no application repository contains.

A credible migration treats the Oracle schema as an executable system, not a bucket of stored procedures. Inventory establishes what exists. Tracing establishes what runs. Characterization tests establish what it means. Only then can a team decide whether a rule belongs in a Go service, a Rust kernel, a TypeScript client, a Postgres constraint, or nowhere at all.

Inventory the schema as an executable system

Start with a repeatable extraction from the production-shaped database, because a source checkout rarely matches what Oracle is executing. Hot fixes get compiled from a workstation, editioned objects hide behind synonyms, and grants determine which definer-rights code can touch which tables. Export DDL, source, status, trigger metadata, jobs, synonyms, grants, and dependencies under a timestamped snapshot.

Oracle's ALL_SOURCE view exposes source text for accessible procedures, functions, packages, package bodies, triggers, types, and type bodies. Use DBA_SOURCE when the assessment account has the necessary privilege and the scope spans schemas. The following query produces a stable source inventory rather than a directory full of anonymous files:

SELECT owner,
       type,
       name,
       COUNT(*) AS source_lines
FROM   dba_source
WHERE  owner IN ('BILLING', 'ORDERS', 'FINANCE')
GROUP  BY owner, type, name
ORDER  BY owner, type, name;

Its output shape is one row per stored object: OWNER, TYPE, NAME, and SOURCE_LINES. A package appears as separate PACKAGE and PACKAGE BODY rows. Do not collapse them. The specification is a public contract; the body contains private routines, initialization code, and most implementation details.

Extract creation DDL as well as text. DBMS_METADATA.GET_DDL preserves object-level details that a concatenation of ALL_SOURCE.TEXT does not. For a package, request PACKAGE_SPEC and PACKAGE_BODY; for a trigger, request TRIGGER. Record the database version, schema, object status, edition, and extraction time beside every file. A migration team must be able to answer which compiled definition produced a captured result.

Include invalid objects and compilation errors instead of filtering them away. An invalid trigger can still block the statement that fires it, and a package invalidated by a changed table can recompile on demand under conditions that the source repository never reproduced. DBA_OBJECTS.STATUS and DBA_ERRORS turn those conditions into evidence. Also inventory materialized views, virtual columns, function-based indexes, fine-grained access policies, and default expressions that call functions. They are not all PL/SQL containers, but each can invoke or depend on stored logic.

Take a second snapshot after the observation period. A diff often exposes deployment tools, nightly jobs, or administrators that replace package bodies outside the nominal release process. Do not begin a rewrite against a moving target without either freezing those changes or feeding them into the extraction and test pipeline.

The count is not the risk score. A six-line trigger that silently changes an accounting period can matter more than a 9,000-line reporting package. Inventory gives you the search space and lets you detect drift; it does not tell you which code carries business meaning.

Find entry points before reading package bodies

Trace who can invoke PL/SQL and what database events invoke it, then read inward from those entry points. Reading packages alphabetically wastes time because private utility routines and dead code look as important as the call paths used for every order.

Build an entry-point table with at least these sources:

• Application calls containing anonymous blocks, CALL, or qualified package procedure names
• Enabled DML, DDL, logon, startup, and instead-of triggers
• Scheduler jobs and legacy job queue entries
• Views and functions called from SQL statements
• External tools, reports, file loaders, and operational scripts

For triggers, capture more than the body. Query DBA_TRIGGERS for TRIGGERING_EVENT, TRIGGER_TYPE, TABLE_OWNER, TABLE_NAME, STATUS, WHEN_CLAUSE, and ACTION_TYPE. Join that inventory to the tables written by each application workflow. A rule attached to ORDERS can run when a support script updates the table even if the main service never calls the trigger by name.

Oracle's PL/SQL trigger guidance makes two points that affect migration design. Triggers run automatically for their defined events regardless of which user or application issues the statement, and code must not depend on the order in which a SQL statement processes rows. If the existing system violates the second point through a package global updated by a row trigger, the behavior may already be nondeterministic. Preserve observed outcomes for parity, but mark the dependency for explicit redesign instead of blessing it as a requirement.

Package initialization is another entry point people miss. Oracle runs a package body's initialization part the first time a session references that package. If it loads configuration, derives a business date, or sets a package variable, the first public procedure call has an invisible prelude. Search the final BEGIN ... END region of every body and record reads, writes, exceptions, and context dependencies there.

Finish this pass with a call graph whose roots are operational events, not merely a graph of object names. Each root should name the initiating actor, transaction, input shape, package or trigger reached, tables read, tables written, external effects, and observed errors. Unknown cells are useful: they tell you exactly where runtime evidence is still missing.

Overloaded package procedures need signatures, not just names. Collect argument position, mode, type, default status, and overload identifier from ALL_ARGUMENTS, then match those signatures against callers. Drivers may bind by position, expose Oracle collection types, or depend on an OUT cursor shape. Replacing ORDER_API.SUBMIT with an HTTP endpoint changes a wire contract even when the business result is identical, so catalog that compatibility work separately from rule recovery.

Static dependencies do not reveal the whole program

Treat ALL_DEPENDENCIES as a useful lower bound, because Oracle cannot record a normal compile-time dependency for an object name assembled inside dynamic SQL. Synonyms, database links, invoker-rights resolution, application contexts, and strings stored in tables widen the gap.

Start with the static graph:

SELECT owner,
       name,
       type,
       referenced_owner,
       referenced_name,
       referenced_type
FROM   dba_dependencies
WHERE  owner IN ('BILLING', 'ORDERS', 'FINANCE')
ORDER  BY owner, name, referenced_owner, referenced_name;

Then search source for behavior that the graph cannot resolve reliably: EXECUTE IMMEDIATE, DBMS_SQL, OPEN ... FOR, database link markers, SYS_CONTEXT, autonomous transaction pragmas, file and queue packages, mail calls, and exception handlers containing writes. Search table data for configured procedure names when the application uses metadata-driven dispatch.

Dynamic SQL needs a string-provenance review. For each statement, identify the template, every substituted identifier, bind values, the schema used for name resolution, and examples captured at runtime. A line such as EXECUTE IMMEDIATE l_sql USING p_id says little until you know whether l_sql updates one known partition or calls a tenant-specific package selected from a configuration table.

Privilege behavior belongs in the same analysis. A package without an explicit AUTHID CURRENT_USER uses definer's rights by default, so its unqualified object references and permissions do not behave like a service query issued under an end-user identity. Record AUTHID, direct grants, roles, synonyms, and application context reads. Moving the routine into an application service can accidentally remove a legitimate authority boundary or grant the service account far more access than the package ever had.

Do not respond by instrumenting every line. Add observation at boundaries: package entry and exit, transaction outcome, trigger firing, dynamic statement shape, and external calls. Use a correlation identifier that survives from the application request into database session metadata. Capture bind values only where policy permits, and redact sensitive fields before storage. The objective is a behavioral map, not a second production database full of secrets.

Static analysis also overstates some dependencies. A package body may contain abandoned routines that reference tables nobody has used for years. Marking every edge as active creates a migration scope that grows faster than the evidence. Keep separate "can call" and "did call" graphs, and retain the observation window and workload beside the latter. Absence from a trace does not prove dead code, but it is a defensible reason to demand an owner or a designed test before rebuilding the path.

Turn discoveries into a behavior ledger

A behavior ledger converts source findings into testable contracts. One row describes one externally meaningful rule, including its inputs, outputs, state changes, failure behavior, and evidence. Without this intermediate artifact, architects tend to assign whole packages to target components and carry accidental boundaries into the replacement.

Consider a package procedure that confirms an invoice. It validates status, derives tax from customer and effective-date tables, inserts ledger lines, changes the invoice state, and enqueues a notification. That is not one rule. The ledger should separate eligibility, tax selection, posting, state transition, and notification intent because each may deserve a different target and a different test oracle.

A useful ledger record contains:

• Rule identifier and a plain-language statement with its source location
• Triggering event plus required session, table, and package state
• Inputs, outputs, writes, messages, files, and commits or rollbacks
• Boundary cases, Oracle errors, custom error codes, and retry behavior
• Evidence from source, traces, production examples, and an approving owner

Give each rule a status such as observed, inferred, disputed, approved, or obsolete. Source code alone supports "inferred" when the branch may be unreachable. A captured execution supports "observed." Finance or operations can approve whether an odd behavior is contractual. This prevents the common meeting in which a surprising result gets called a bug only because the new design did not reproduce it.

Separate business rules from database mechanics. "An invoice cannot post into a closed period" is a rule. "A BEFORE INSERT trigger queries PERIOD_CONTROL and raises -20041" is one implementation. "Set UPDATED_AT from SYSTIMESTAMP" may be a persistence policy. "Increment a package global" may be a workaround. The consequence matters: migrate the rule, test the old mechanism, and keep the mechanism only when its semantics are part of the contract.

Record negative space too. If direct SQL updates bypass an application validation but still hit a trigger, that trigger defines the actual enforcement boundary. If a package commits internally, callers cannot roll back the whole workflow even if their code appears to own the transaction. These awkward facts determine cutover design and should not be softened into architecture prose.

Make disagreements executable where possible. If operations says a backdated cancellation is allowed and finance says it is not, preserve both candidate examples, expected outcomes, and the data conditions behind them. Ask the owner to approve one result in the ledger. A prose requirement such as "handle backdating correctly" will survive every review while giving the implementation team nothing it can compare.

The ledger also controls deletion. When nobody can supply an invoking actor, observed execution, regulatory reason, or approved example for a routine, flag it as a removal candidate. Keep the old source and prove that callers do not reach it; do not spend migration time translating it merely because it compiles.

Test transactions, not isolated functions

Characterization tests should drive the old system through its real public boundaries and compare the full transaction result. Unit testing a private tax function misses trigger side effects, package state, NLS settings, sequence consumption, exception translation, and commit behavior.

Create a disposable Oracle test environment from a masked, referentially consistent dataset. Fix session inputs explicitly: time zone, NLS_DATE_FORMAT, numeric characters, current schema, application context, and business date sources. Reset tables and sequences to a known baseline for each case when exact identifiers matter. Use separate sessions for tests involving package state.

Oracle documents that each session gets its own package instantiation and that stateful package values normally persist for the session. Recompiling an instantiated stateful package can discard that state and cause ORA-04068 on the next invocation. A connection pool therefore turns package globals into hidden per-connection memory. Your suite needs cases for a fresh session, repeated calls on one session, two concurrent sessions, pooled-session reuse, rollback, and package invalidation if production deployments can trigger it.

For every test, capture a normalized observation record:

{
  "case": "closed-period-credit",
  "entry": "billing.invoice_api.post_credit",
  "result": {"status": "error", "oracle_code": -20041},
  "tables": {"invoice": [], "ledger_entry": []},
  "events": [],
  "transaction": "rolled_back"
}

The new implementation should produce the same business observation, not necessarily the same Oracle stack or sequence value. Normalize generated identifiers into stable aliases, compare money at its declared scale, order sets only when order is contractual, and compare timestamps using the precision the old interface exposed. Keep exact custom error codes when callers branch on them; otherwise map them to an explicit domain error and test that mapping at the compatibility boundary.

Test failures and partial work deliberately. Force a duplicate key after an audit insert. Make the notification queue unavailable. Throw an exception from a row trigger after several rows have been processed. Exercise bulk DML, where statement-level and row-level trigger timing changes what survives. A happy-path procedure result will not reveal an autonomous transaction that wrote an audit row even though the business transaction rolled back.

One common failure looks like this: an application begins a transaction, calls a package to reserve credit, inserts an order, and then rolls back when inventory allocation fails. The package updates a global "available credit" cache and an autonomous audit routine commits a reservation record. The table update rolls back, but the package value remains in that pooled session and the audit record remains committed. A replacement that wraps everything in one clean service transaction will differ on the next request. The ledger must decide whether those remnants are required, tolerated defects, or behavior to remove, and the test suite must pin the approved choice.

Trigger ordering requires its own cases when several triggers share a table and timing point. Oracle provides FOLLOWS and, in limited cases, PRECEDES clauses for declared relationships, but unconnected triggers do not gain a dependable total order. Capture the DDL and test final outcomes under multirow statements. Do not write a target test that asserts an incidental firing sequence unless the source explicitly declares it and the business outcome depends on it.

Measure coverage by ledger rules and entry points, not PL/SQL line coverage. A test can execute every line in a tax package without proving which rate wins at an effective-date boundary. Conversely, a compact matrix of dates, jurisdictions, customer classes, and reversal states can characterize the contract while leaving defensive branches unvisited. Keep ordinary code coverage as a diagnostic, not the acceptance criterion.

Production traffic supplies cases, not truth

Recorded production traffic is the strongest source of realistic inputs, but it does not define the complete specification. It overrepresents normal cases, contains historical accidents, and rarely captures the counterfactual inputs that should fail.

Record requests at the boundary where input meaning is still visible. Include the called operation, ordered calls within a transaction, sanitized parameters, relevant session context, result classification, and identifiers needed to collect affected rows. For SQL-driven entry points, record bind values and transaction grouping rather than raw SQL text alone. For batch work, retain file shape and control totals without copying restricted payloads into an uncontrolled test store.

Replay each case against old and new implementations from equivalent starting state. Compare return values, errors, database changes, emitted events, and commit boundaries. When results differ, classify the reason before changing code: missing rule, intentional redesign, nondeterminism, bad fixture, or an old defect that the owner has chosen to retire.

Traffic must be supplemented with designed cases from the ledger. Add boundary dates, nulls, duplicate requests, retries after timeout, maximum supported numeric scale, unauthorized users, closed accounting periods, and concurrent updates to the same entity. Add metamorphic checks where an exact result is hard to enumerate. For example, posting and then reversing an eligible invoice should leave its net ledger effect at zero, subject to the legacy rounding rule.

Sample by behavior as well as volume. A million successful order submissions add little evidence after the input shapes repeat, while one year-end close, one daylight-saving transition, or one manual adjustment may cover a unique branch. Preserve rare cases deliberately and give them stable fixtures. Production frequency should guide performance testing, but business consequence and branch uniqueness should guide parity coverage.

CodeHero uses a parity harness against recorded production traffic when rewriting systems, including PL/SQL estates, and reads the languages around the database code as one codebase. That matters because a stored procedure's contract often exists partly in a Java caller, a scheduler script, and the rows a trigger changes. The harness still needs the ledger's negative and boundary cases; replay volume cannot prove behavior that traffic never exercised.

Do not send captured production inputs into a model or test environment without a data classification decision. Masking must preserve properties the rules use, such as equality groups, date ordering, account prefixes, and referential integrity. Replacing every value with random text may protect identity while destroying the very cases the migration needs to test.

Put each rule at its narrowest reliable boundary

Place a rule where every relevant write must pass and where the team can observe and test it. This usually produces a split architecture rather than a campaign to move all PL/SQL into services or keep all rules in Postgres.

Use database constraints for invariants expressible against the row or relational state: nullability, uniqueness, foreign keys, and checkable value ranges. Constraints cover every writer and give the query planner useful facts. Do not replace a declarative constraint with application code because application code looks easier to version.

Keep a small database function or trigger only when the rule truly belongs to all writers and cannot be expressed declaratively, and when those writers will continue to bypass one service. Make side effects explicit and minimal. A trigger that stamps audit context can be defensible; a trigger that calculates pricing, writes five tables, sends a message, and commits autonomously hides a workflow that needs an owned API.

Put workflow rules in a service when they coordinate aggregates, call external systems, require explicit retries, or need product-level observability. The service should own the transaction or use an outbox for post-commit work. Do not publish a message before the database commit and hope consumers tolerate a rollback. Do not let both the compatibility trigger and the new service emit it.

Put numeric kernels in Rust only when the work benefits from a tight, independently testable computation boundary. Put client presentation rules in TypeScript only when the server or database still enforces the underlying invariant. A disabled button is useful feedback; it is not authorization.

Postgres is not Oracle with different spelling. Package session state has no natural one-for-one home, empty strings and nulls differ, exception and autonomous transaction behavior differ, and trigger ordering deserves an explicit design. Modernize these boundaries instead of transliterating them. An explicit service request, transaction, and outbox record is easier to reason about than recreating a web of hidden callbacks.

A decision record for each ledger rule should name the chosen owner, enforcement point, compatibility plan, test cases, and removal condition for the Oracle implementation. If nobody owns a rule, it has not moved. If two components enforce it, document which one is authoritative and how long the duplication lasts.

Cut over without running rules twice

The greatest cutover risk is duplicate behavior: the new service performs a rule while an old trigger silently performs it again. This creates doubled ledger lines, repeated notifications, conflicting timestamps, or an update that passes one validator and fails the other.

Build a rule-by-rule activation matrix. Rows are behavior-ledger identifiers. Columns are old application, Oracle package, Oracle trigger, new service, Postgres constraint or trigger, and event consumer. For each rollout state, mark one authoritative executor and any observers. Refuse a state with two executors unless the operation is proven idempotent and duplication is intentional.

Shadow execution should not mutate shared production state. Run the new decision logic in observation mode, or replay captured inputs against an isolated target, then compare its proposed outcome with Oracle's committed outcome. For workflows with external effects, substitute a sink that records intent without sending mail, charging an account, or publishing to a live topic.

Dual writes are popular because they appear to make rollback easy. They usually create two failure modes and an ambiguous source of truth. Prefer one writer plus change capture or an outbox, with a measured replication lag and a reconciliation query. If temporary dual writes are unavoidable, assign an idempotency key at the original request boundary and persist the outcome on both sides.

Cut over by coherent entry point, not by arbitrary package file. Move the procedure, the triggers it relies on, its transaction semantics, and its downstream effects as one behavior slice. Block or reroute direct writers that would bypass the new authority. Keep a compatibility facade only when callers need time to switch, and make it call the new owner rather than contain a second implementation.

Rollback must specify data direction, not only deployment direction. State which system remains authoritative, which writes pause, how target-only records return to Oracle if needed, and how emitted effects are reconciled. A container rollback is not a business rollback after money, messages, or files have left the transaction.

Oracle retirement is an acceptance test

A PL/SQL migration finishes when the business can run with the relevant Oracle behavior disabled and the team can prove why the results remain correct. "All package bodies translated" says nothing about triggers, jobs, session globals, operational scripts, or callers that still connect directly.

For each entry point, require a closed chain from initiating actor to an approved ledger rule, a target owner, passing characterization cases, cutover state, and production observation. Re-run the schema inventory and compare it with the starting snapshot. Every remaining enabled trigger, executable grant, scheduler job, synonym, and application connection needs an explicit reason.

Then perform a denial test in a production-shaped environment. Revoke the legacy execute path or disable the migrated trigger, run the full traffic and designed-case suite, and monitor attempted connections. The test should fail if any path still depends on Oracle. A successful run supplies stronger evidence than a spreadsheet in which every component owner marked a row complete.

Retain the source snapshot, behavior ledger, normalized observations, decision records, and parity results as one evidence set. They explain more than how the old code worked. They show which oddities the business accepted, which defects it retired, and where each surviving rule now lives.

CodeHero delivers legacy rewrites in under 30 days, but speed does not excuse guessing about database behavior. The way to move quickly is to inspect the whole system at once, turn discoveries into executable comparisons, and refuse to count a rule as migrated until Oracle can stop enforcing it.