PL/SQL lineage: finding the side effects hidden in triggers and packages

The database is part of the application

In many Oracle and SQL Server estates built in the 1990s and 2000s, a large share of business logic lives in the database: packages of stored procedures, triggers on core tables, views with embedded calculations and scheduled jobs. Modernization teams that treat the database as storage and plan to replace only the application tier discover, late, that half the behavior was never in the application tier.

The hard part is not reading a procedure. It is knowing everything that happens when it runs. That requires lineage across procedure calls, trigger firings, package state and dynamic SQL, at column level.

An ordinary-looking procedure

Here is the visible part. The procedure applies a payment to the oldest unpaid installments, row by row, then reduces the principal balance.

1CREATE OR REPLACE PACKAGE BODY pkg_loan AS
2  PROCEDURE post_payment(p_loan_id IN NUMBER, p_amount IN NUMBER) IS
3    v_left  NUMBER := p_amount;
4    v_prin  NUMBER := 0;
5    CURSOR c_due IS
6      SELECT inst_no, prin_due, int_due
7        FROM loan_sched
8       WHERE loan_id = p_loan_id AND paid_flag = 'N'
9       ORDER BY due_date
10         FOR UPDATE;
11  BEGIN
12    FOR r IN c_due LOOP
13      EXIT WHEN v_left <= 0;
14      v_left := v_left - r.int_due;
15      v_prin := v_prin + LEAST(r.prin_due, GREATEST(v_left, 0));
16      v_left := v_left - r.prin_due;
17      UPDATE loan_sched SET paid_flag = CASE WHEN v_left >= 0 THEN 'Y' ELSE 'P' END
18       WHERE CURRENT OF c_due;
19    END LOOP;
20    UPDATE loan_bal SET principal = principal - v_prin WHERE loan_id = p_loan_id;
21  END post_payment;
22END pkg_loan;

The trigger nobody mentioned

The UPDATE loan_bal fires a row-level trigger. When a payment brings principal to zero, the trigger writes a general ledger entry and calls a notification package that queues a payoff letter. The payoff rule, arguably the most customer-visible behavior in the flow, is not in the procedure at all.

PYD ENTERPRISES' PL/SQL front end resolves triggers by table, column and timing, and inserts them into the control-flow graph at the DML statement that fires them. Lineage then flows through the trigger body as if it were called inline, which, for row-level triggers, is effectively what happens.

1CREATE OR REPLACE TRIGGER trg_bal_payoff
2  AFTER UPDATE OF principal ON loan_bal
3  FOR EACH ROW
4BEGIN
5  IF :NEW.principal <= 0 AND :OLD.principal > 0 THEN
6    INSERT INTO gl_entry (loan_id, entry_type, amount, posted_on)
7    VALUES (:NEW.loan_id, 'PAYOFF', :OLD.principal, TRUNC(SYSDATE));
8    pkg_notify.queue_letter(:NEW.loan_id, 'PAYOFF');
9  END IF;
10END;

Four places side effects hide

Triggers are the best known. Three others cause just as many surprises.

• Package state. Variables declared in a package body persist for the database session. A procedure that reads pkg_ctx.g_branch_id depends on whatever code set it earlier in the session, often a login procedure in the client application. The engine models package variables as session-scoped globals and reports every reader and writer.
• Autonomous transactions. A procedure with PRAGMA AUTONOMOUS_TRANSACTION commits independently of its caller. An audit row written that way survives a rollback of the business transaction. That is usually intended for logging and occasionally load-bearing for a business rule, and the lineage graph marks the edge so reviewers can tell which.
• Dynamic SQL. EXECUTE IMMEDIATE with a statement built from constants and bind variables is resolved statically and parsed like any other SQL. When the table or column name comes from a parameter or a configuration table, the engine records the candidate set it can prove and flags the statement for review.
• Views with logic. A view that computes a derived status with a CASE expression is a business rule. Anything that selects from it inherits the rule, and the lineage shows that dependency explicitly.

Column-level lineage as the output

The result for post_payment is a graph that starts at the two parameters and ends at every column the call can change: loan_sched.paid_flag, loan_bal.principal, gl_entry (four columns), and the notification queue table written inside pkg_notify. Each edge carries the statement and line range that creates it and the condition under which it fires.

Two business rules come out of the flow for analyst review. The allocation order, interest before principal on the oldest installment first, becomes a documented decision. The payoff behavior, a ledger entry plus a customer letter when principal reaches zero, becomes a second rule attributed to the trigger, with a note that the condition <= 0 also fires on overpayment, which may or may not be intended.

Neither rule changes the procedure's signature, so neither would appear in an interface inventory built from package specifications alone. That is the general lesson: in database-heavy estates, interface specs describe what callers send, and lineage describes what actually changes. A modernization plan needs both before it can draw a service boundary around loan_bal.

A note on row-by-row processing

The cursor loop above is a classic row-by-row pattern. It is slow at volume, but the reason to care during modernization is semantic: the order of processing is a business rule (oldest due date first), and the partial-payment state 'P' depends on the running remainder. A target service that rewrites this as a set-based update must preserve both, and the row-by-row cursor entry in the catalog shows equivalent set-based and service-layer forms. For the full workflow, see the PL/SQL to microservices guide and the database logic parser.