Oracle Data Lineage: PL/SQL Stored Procedure and Package Analysis

Oracle data lineage is the column-level map of how data moves through an Oracle estate: which source tables and columns feed each target table, view, or materialized view, and which PL/SQL packages, procedures, functions, and triggers do the moving. Because in most Oracle shops the transformation logic lives in PL/SQL rather than in standalone queries, accurate lineage requires a tool that parses PL/SQL as a procedural language, not just Oracle SQL. Gudu SQLFlow ships a dedicated PL/SQL parser that does exactly that, including dynamic SQL built with EXECUTE IMMEDIATE.

Why Oracle data lineage is harder than parsing SELECT statements

Oracle databases are old in the best sense: many production estates carry twenty or more years of accumulated business logic, and the bulk of it is PL/SQL. Nightly loads, mart refreshes, reconciliation jobs, and audit trails are implemented as packages calling procedures calling functions, with triggers firing along the way. A lineage tool that only understands SELECT, INSERT, and CREATE VIEW sees the thin surface of such an estate and misses most of the logic underneath.

The technical reason is that PL/SQL is not SQL. It is a block-structured procedural language with its own grammar: declarations, control flow, cursors, exception handlers, package specs and bodies. SQL statements are embedded inside that structure, and their inputs and outputs flow through variables, parameters, and cursor loops. Extracting lineage from PL/SQL means parsing the procedural layer, tracking values through it, and connecting the embedded SQL statements to each other. A SQL-only grammar cannot do this, which is why generic lineage extractors typically skip procedure bodies entirely or fail on the first BEGIN block.

A dedicated PL/SQL parser, not a SQL parser with extensions

SQLFlow is built on the General SQL Parser, a commercial SQL compiler front-end developed since the mid-2000s and validated against roughly 13,600 per-dialect test fixtures. For Oracle it provides two things: an Oracle SQL parser for queries, DDL, and views, and a separate procedural parser for PL/SQL. The PL/SQL parser handles the object types where Oracle transformation logic actually lives:

• Packages: analyzed in full, covering every procedure and function the package contains, with a call graph across package boundaries.
• Procedures and functions: lineage traced through procedure parameters and temp tables, so a value that enters as an argument and lands in a table is connected end to end.
• Triggers: row-level logic that copies or transforms data on INSERT, UPDATE, or DELETE appears in the lineage graph rather than remaining invisible side effects.
• Dynamic SQL: statements assembled at runtime and executed with EXECUTE IMMEDIATE are resolved and analyzed instead of skipped.

On top of the per-object analysis, SQLFlow renders an interactive call graph: which procedures invoke which, across package boundaries. In a codebase where etl_driver.run_nightly fans out into a dozen package procedures, the call graph is how you find the one that actually writes the table you care about.

Example: tracing lineage through a package procedure

Here is a simplified but representative pattern: a package procedure that loads a daily revenue mart from staging.

CREATE OR REPLACE PACKAGE BODY sales_etl AS

  PROCEDURE load_revenue_mart(p_load_date IN DATE) IS
  BEGIN
    DELETE FROM mart.revenue_daily
     WHERE day_id = TO_CHAR(p_load_date, 'YYYYMMDD');

    INSERT INTO mart.revenue_daily (day_id, region_name, total_amount)
    SELECT TO_CHAR(o.order_date, 'YYYYMMDD'),
           r.region_name,
           SUM(o.amount)
      FROM staging.orders o
      JOIN dim.region r ON r.region_id = o.region_id
     WHERE o.status = 'SHIPPED'
       AND TRUNC(o.order_date) = TRUNC(p_load_date)
     GROUP BY TO_CHAR(o.order_date, 'YYYYMMDD'), r.region_name;
  END load_revenue_mart;

END sales_etl;

SQLFlow reads the package body and reports, per output column: mart.revenue_daily.total_amount is fed by staging.orders.amount through SUM; region_name comes from dim.region.region_name via the join on region_id; day_id is derived from staging.orders.order_date through TO_CHAR. It also records that o.status, o.order_date, and the parameter p_load_date shape the result through the WHERE clause, and that the procedure sits inside the sales_etl package in the call graph. Multiply this by the hundreds of procedures in a real estate and you have the dependency map that manual documentation never keeps current.

Dynamic SQL: the EXECUTE IMMEDIATE blind spot

Oracle teams reach for dynamic SQL constantly: loading partition-named tables, applying DDL from procedures, building statements whose target depends on a configuration row. Code like this is where most lineage extraction gives up:

v_sql := 'INSERT INTO mart.revenue_archive (day_id, region_name, total_amount)
          SELECT day_id, region_name, total_amount
            FROM mart.revenue_daily
           WHERE day_id = :d';
EXECUTE IMMEDIATE v_sql USING v_day_id;

SQLFlow resolves the dynamic SQL inside the procedure and analyzes the assembled statement, so the flow from mart.revenue_daily into mart.revenue_archive is captured rather than silently dropped. If your estate uses EXECUTE IMMEDIATE heavily, this is the single capability to verify before trusting any lineage tool: run one of your own dynamic-SQL procedures through it and check whether the target table appears in the graph at all.

Direct and indirect lineage in Oracle code

SQLFlow distinguishes direct lineage (a source column’s value lands in a target column) from indirect lineage (a column steers the result through a WHERE predicate, JOIN condition, GROUP BY, or aggregate without appearing in the output). In the example above, orders.status never reaches the mart, yet changing its values would change every number in it. The two relationship types are toggleable separately in the diagram, so impact analysis can include or exclude filter-only dependencies deliberately. Most competing tools do not model this distinction, which makes their impact analysis either too narrow (missing filter columns) or too noisy (everything connects to everything).

Getting your Oracle code into SQLFlow

Input method	When to use it
Paste SQL or PL/SQL	Quick checks on a single package, procedure, or script in the browser
Upload files	Analyzing exported DDL and PL/SQL source in bulk
Live JDBC connection	Pulling schema metadata, view definitions, and stored code straight from the database
Grabit metadata extraction	Extracting metadata with the Grabit companion tool and feeding it to SQLFlow

Whichever route you choose, SQLFlow performs static analysis of the SQL and PL/SQL text plus schema metadata. It never reads the rows in your tables. For banks and other regulated Oracle shops, the On-Premise edition runs as Docker or Kubernetes inside your network, including fully air-gapped, so the source code itself never leaves your infrastructure. At enterprise scale it batch-scans estates of 100+ databases and over a million columns, with incremental scans and export adapters for DataHub, Microsoft Purview, and OpenMetadata.

Oracle migrations and mixed estates

Lineage is most valuable at exactly the moment Oracle estates tend to face: migration. Before moving workloads to Snowflake, BigQuery, or Databricks, the lineage graph tells you which packages actually feed the reports the business uses, which tables are dead, and in what order objects must move. After migration, re-running the analysis on the new platform verifies nothing was orphaned. SQLFlow parses 39 dialects with dialect-specific grammars, so the same tool covers both ends of the move.

Few large estates are Oracle-only. The same procedural-parsing depth is available for SQL Server T-SQL lineage, and warehouse platforms such as Teradata have their own dialect-specific parsers. One lineage repository can hold the whole estate.

Where open-source parsers fit

Open-source projects like sqllineage and sqlglot are genuinely good at parsing individual queries, and for extracting table-level lineage from standalone SELECT and INSERT statements they may be all you need. The gap for Oracle work is the procedural layer: package bodies, parameter flow, triggers, and EXECUTE IMMEDIATE are precisely the constructs general-purpose SQL parsers are not built for, and in an Oracle estate that is where most of the transformation logic sits. The honest evaluation is empirical: take your largest package body, run it through your candidate tools, and count how many of its target tables each one finds.

Frequently asked questions

Can SQLFlow trace lineage through Oracle stored procedures and packages?

Yes. SQLFlow has a dedicated PL/SQL parser, separate from its Oracle SQL parser, that analyzes packages, procedures, functions, and triggers. Lineage is traced through procedure parameters and temp tables, and an interactive call graph shows procedure-to-procedure invocations across packages.

Does SQLFlow handle EXECUTE IMMEDIATE dynamic SQL?

Yes. Dynamic SQL assembled inside PL/SQL procedures is resolved and analyzed rather than skipped, so statements built with EXECUTE IMMEDIATE contribute their source-to-target flows to the lineage graph.

Does SQLFlow need access to my Oracle data?

No. SQLFlow performs static analysis of SQL and PL/SQL code and optionally reads schema metadata over JDBC. It never reads table row data. With the On-Premise edition, even the SQL text stays inside your network.

Is Oracle column-level lineage available in the free version?

Yes. SQLFlow Cloud has a free tier: paste Oracle SQL or PL/SQL in the browser and get column-level lineage diagrams. Premium is $49.99/month; On-Premise is $500/month or $4,800 one-time per selected database type. See pricing for details.

Can I export Oracle lineage to a data catalog?

Yes. Lineage exports as JSON, CSV, or PNG, is queryable over a REST API, and enterprise deployments include export adapters for DataHub, Microsoft Purview, and OpenMetadata, so SQLFlow can serve as the PL/SQL-aware lineage engine behind the catalog you already run.