If you work with enterprise databases, you already know: stored procedures are everywhere. They hold critical business logic, enforce data transformations, and move data between tables in ways that no view or SELECT statement can capture. PL\/SQL packages in Oracle, T-SQL procedures in SQL Server, PL\/pgSQL functions in PostgreSQL — these are the workhorses of data processing in large organizations.<\/p>\n\n\n\n
And yet, when you fire up a SQL lineage tool and point it at your database, those stored procedures are often quietly ignored. The lineage diagram shows tables and views connected by SELECT and INSERT statements, but the procedural code that actually drives data flow? It is either missing entirely or represented as a black box with no column-level detail.<\/p>\n\n\n\n
This is not a minor gap. In regulated industries, auditors want to know exactly how data moves from source to target. In migration projects, you need to understand what a stored procedure does before you can rewrite it. And for impact analysis, a schema change that breaks a stored procedure can cascade into production failures that no lineage tool warned you about.<\/p>\n\n\n\n
Let us look at why this happens, what the evidence looks like in practice, and what a proper solution requires.<\/p>\n\n\n\n
The core challenge is architectural. Most SQL parsers are built to handle declarative SQL<\/strong> — SELECT, INSERT, UPDATE, DELETE, CREATE VIEW. These statements describe what<\/em> data to retrieve or modify. Stored procedures, on the other hand, contain procedural logic<\/strong> — IF\/ELSE branches, loops, exception handlers, cursor operations, dynamic SQL construction, and vendor-specific control flow.<\/p>\n\n\n\n There are three main reasons tools struggle:<\/p>\n\n\n\n Many lineage tools use a preprocessing approach: they extract the SQL statements embedded inside a procedure body and feed them into a standard SQL parser one at a time. This loses context. A variable assigned in one statement and used three lines later in an INSERT becomes invisible. A cursor that iterates over a result set and inserts rows conditionally cannot be resolved without understanding the control flow.<\/p>\n\n\n\n Native parsing means the parser understands the procedural language itself — DECLARE blocks, BEGIN\/END scoping, cursor declarations, loop constructs, exception handlers — as first-class syntax. This is significantly harder to build, which is why most tools take the shortcut.<\/p>\n\n\n\n A stored procedure is not a single data flow — it is a program with branches. Consider a PL\/SQL procedure that uses Each database vendor has its own procedural language with unique syntax. Oracle’s PL\/SQL has packages, nested procedures, autonomous transactions, and pipelined table functions. SQL Server’s T-SQL has TRY\/CATCH, table variables, the GO batch separator, and MERGE with OUTPUT. PostgreSQL’s PL\/pgSQL has PERFORM (execute and discard), RAISE (logging), RETURN QUERY, and dollar-quoted strings. Supporting all of these at production quality means maintaining separate, deep grammars for each dialect.<\/p>\n\n\n\n The pattern is consistent across the ecosystem. Without naming and shaming, here is what we see:<\/p>\n\n\n\n Open-source metadata platforms<\/strong> have acknowledged procedural SQL gaps. One major platform has had open issues for over five months related to stored procedure lineage extraction, with community members requesting support for PL\/SQL and T-SQL procedure parsing that the platform’s SQL parser cannot handle.<\/p>\n\n\n\n Python-based SQL transpilers<\/strong> that are popular in the dbt ecosystem work well for SELECT statement analysis but struggle with procedural constructs. T-SQL’s GO batch delimiter, complex PL\/SQL blocks with nested BEGIN\/END, and vendor-specific syntax like Oracle’s Large-scale migration tools at Fortune 500 companies<\/strong> have encountered this directly. One major technology company’s open-source migration utility (issue #2098) documented how their SQL parser could not propagate column-level lineage tokens through procedural logic, and the team discussed replacing their parser entirely due to dialect limitations in handling stored procedures.<\/p>\n\n\n\n Database migration tools<\/strong> — both commercial (cloud provider schema conversion tools) and open-source (Oracle-to-PostgreSQL converters) — frequently fail on specific PL\/SQL patterns. Data catalog platforms<\/strong> have added stored procedure support in their UI — you can view and edit procedure code — but the actual parsing<\/em> for lineage extraction is not there. Showing the code is not the same as understanding the data flow within it.<\/p>\n\n\n\n To extract accurate column-level lineage from stored procedures, a parser needs to handle the full procedural language, not just the SQL statements embedded in it. Here are three real-world examples that illustrate the complexity.<\/p>\n\n\n\n The lineage here flows from General SQL Parser<\/a> handles this by parsing the full PL\/SQL package body, resolving variable types through This procedure creates a temp table via Here, The key distinction is whether a tool extracts lineage through<\/em> stored procedures or just around<\/em> them. Working around them means treating the procedure as a black box — you know it reads from table A and writes to table B, but you do not know which specific columns are involved or how they are transformed. Working through them means the parser understands the procedural logic well enough to trace individual columns from source to target, including through variables, cursors, collections, temp tables, and control flow branches.<\/p>\n\n\n\n General SQL Parser<\/a> takes the “through” approach. It parses the full procedural language for each supported dialect, builds an internal representation of variable declarations, assignments, and data flow, and resolves column-level lineage across procedural boundaries. When you feed it a PL\/SQL package or a T-SQL stored procedure, the output is not just “this procedure touches these tables” — it is a complete column-to-column lineage map that shows exactly how data flows from source to target.<\/p>\n\n\n\n You can see this in action with SQLFlow<\/a>, which visualizes the lineage extracted by General SQL Parser. Upload a stored procedure and you will see the column-level connections rendered as a data flow diagram, with each transformation step visible.<\/p>\n\n\n\n If stored procedure lineage is a gap in your current tooling, here is what you can do:<\/p>\n\n\n\n Stored procedures are not going away. If anything, the push toward database-side logic for performance and security is growing. A lineage solution that skips them is a lineage solution with blind spots — and in an era of data governance and regulatory compliance, blind spots are not acceptable.<\/p>\n","protected":false},"excerpt":{"rendered":" Most SQL lineage tools silently skip stored procedures. Learn why PL\/SQL, T-SQL, and PL\/pgSQL procedural code breaks typical parsers, and how General SQL Parser extracts column-level lineage through stored procedures — not just around them.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[14,25,8],"tags":[125,26,122,124,121,123],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\n1. Preprocessing vs. Native Parsing<\/h3>\n\n\n\n
2. Control Flow Complexity<\/h3>\n\n\n\n
BULK COLLECT<\/code> to fetch 10,000 rows into a collection, then uses FORALL<\/code> to insert them into a target table with an exception handler that logs failures to an error table. The lineage from source columns to target columns passes through collection variables, loop indices, and conditional branches. Tracking column-level lineage through this requires something closer to program analysis than SQL parsing.<\/p>\n\n\n\n3. Vendor-Specific Syntax<\/h3>\n\n\n\n
Evidence From the Field<\/h2>\n\n\n\n
EXECUTE IMMEDIATE<\/code> are known limitations. The transpiler approach works for the 80% case (queries and views) but hits a wall with procedural code.<\/p>\n\n\n\nBULK COLLECT<\/code> combined with FORALL<\/code>, which is one of the most common performance optimization patterns in Oracle, is a known pain point. The migration tool either errors out or produces incorrect target code because it does not fully parse the procedural context.<\/p>\n\n\n\nWhat Good Stored Procedure Parsing Looks Like<\/h2>\n\n\n\n
PL\/SQL: Package With Cursor Loops and BULK COLLECT<\/h3>\n\n\n\n
CREATE OR REPLACE PACKAGE BODY etl_pkg AS\n PROCEDURE load_customer_summary IS\n TYPE t_cust_rec IS RECORD (\n customer_id customers.customer_id%TYPE,\n total_amount NUMBER,\n order_count NUMBER\n );\n TYPE t_cust_tab IS TABLE OF t_cust_rec;\n l_batch t_cust_tab;\n\n CURSOR c_active IS\n SELECT c.customer_id,\n SUM(o.amount) AS total_amount,\n COUNT(o.order_id) AS order_count\n FROM customers c\n JOIN orders o ON o.customer_id = c.customer_id\n WHERE c.status = 'ACTIVE'\n GROUP BY c.customer_id;\n BEGIN\n OPEN c_active;\n LOOP\n FETCH c_active BULK COLLECT INTO l_batch LIMIT 5000;\n EXIT WHEN l_batch.COUNT = 0;\n\n FORALL i IN 1..l_batch.COUNT\n MERGE INTO customer_summary cs\n USING (SELECT l_batch(i).customer_id AS cid,\n l_batch(i).total_amount AS amt,\n l_batch(i).order_count AS cnt FROM dual) src\n ON (cs.customer_id = src.cid)\n WHEN MATCHED THEN\n UPDATE SET cs.total_amount = src.amt,\n cs.order_count = src.cnt,\n cs.updated_at = SYSDATE\n WHEN NOT MATCHED THEN\n INSERT (customer_id, total_amount, order_count, updated_at)\n VALUES (src.cid, src.amt, src.cnt, SYSDATE);\n\n COMMIT;\n END LOOP;\n CLOSE c_active;\n EXCEPTION\n WHEN OTHERS THEN\n ROLLBACK;\n INSERT INTO etl_error_log (procedure_name, error_msg, error_time)\n VALUES ('load_customer_summary', SQLERRM, SYSDATE);\n COMMIT;\n RAISE;\n END load_customer_summary;\nEND etl_pkg;<\/code><\/pre>\n\n\n\ncustomers.customer_id<\/code>, orders.amount<\/code>, and orders.order_id<\/code> through a cursor, into a PL\/SQL collection variable, through a FORALL<\/code> + MERGE<\/code> statement, and finally into customer_summary.customer_id<\/code>, customer_summary.total_amount<\/code>, and customer_summary.order_count<\/code>. The error path also creates lineage into etl_error_log<\/code>. A parser that only sees the MERGE statement without understanding the cursor and collection context will miss the source columns entirely.<\/p>\n\n\n\n%TYPE<\/code> and record declarations, tracking data flow through BULK COLLECT<\/code> into collection variables, and connecting those variables through the FORALL<\/code> loop to the final MERGE target.<\/p>\n\n\n\nT-SQL: Procedure With Temp Tables, Dynamic SQL, and TRY\/CATCH<\/h3>\n\n\n\n
CREATE PROCEDURE dbo.usp_refresh_product_metrics\n @CategoryID INT,\n @StartDate DATE\nAS\nBEGIN\n SET NOCOUNT ON;\n BEGIN TRY\n -- Stage data in temp table\n SELECT p.product_id,\n p.product_name,\n p.category_id,\n SUM(s.quantity) AS total_qty,\n SUM(s.quantity * s.unit_price) AS total_revenue\n INTO #product_staging\n FROM products p\n INNER JOIN sales s ON s.product_id = p.product_id\n WHERE p.category_id = @CategoryID\n AND s.sale_date >= @StartDate\n GROUP BY p.product_id, p.product_name, p.category_id;\n\n -- Apply business rules\n UPDATE #product_staging\n SET total_revenue = total_revenue * 0.9\n WHERE total_qty > 1000;\n\n -- Merge into target\n MERGE product_metrics AS tgt\n USING #product_staging AS src\n ON tgt.product_id = src.product_id\n WHEN MATCHED THEN\n UPDATE SET tgt.product_name = src.product_name,\n tgt.total_qty = src.total_qty,\n tgt.total_revenue = src.total_revenue,\n tgt.last_updated = GETDATE()\n WHEN NOT MATCHED THEN\n INSERT (product_id, product_name, category_id,\n total_qty, total_revenue, last_updated)\n VALUES (src.product_id, src.product_name, src.category_id,\n src.total_qty, src.total_revenue, GETDATE());\n\n DROP TABLE #product_staging;\n END TRY\n BEGIN CATCH\n IF OBJECT_ID('tempdb..#product_staging') IS NOT NULL\n DROP TABLE #product_staging;\n\n INSERT INTO dbo.error_log (proc_name, error_message, error_time)\n VALUES ('usp_refresh_product_metrics', ERROR_MESSAGE(), GETDATE());\n\n THROW;\n END CATCH\nEND\nGO<\/code><\/pre>\n\n\n\nSELECT INTO<\/code>, modifies it with an UPDATE, then merges into the final target. The column-level lineage must trace products.product_name<\/code> and sales.quantity<\/code> through #product_staging<\/code> and into product_metrics<\/code>. The GO<\/code> batch delimiter, TRY\/CATCH<\/code> error handling, and temp table lifecycle all need to be understood. General SQL Parser resolves the temp table schema from the SELECT INTO<\/code> statement, tracks the UPDATE transformation, and maps columns through the MERGE into the final target — including the error path to error_log<\/code>.<\/p>\n\n\n\nPL\/pgSQL: Function With PERFORM, RAISE, and RETURN QUERY<\/h3>\n\n\n\n
CREATE OR REPLACE FUNCTION analytics.refresh_user_engagement(\n p_days_back INTEGER DEFAULT 30\n)\nRETURNS TABLE (\n user_id BIGINT,\n engagement_score NUMERIC,\n segment TEXT\n)\nLANGUAGE plpgsql\nAS $$\nDECLARE\n v_threshold NUMERIC;\n v_count INTEGER;\nBEGIN\n -- Cleanup old data\n PERFORM analytics.archive_old_engagement(p_days_back * 2);\n\n SELECT AVG(login_count)::NUMERIC INTO v_threshold\n FROM analytics.user_activity\n WHERE activity_date >= CURRENT_DATE - p_days_back;\n\n INSERT INTO analytics.engagement_daily (\n user_id, engagement_score, segment, calc_date\n )\n SELECT ua.user_id,\n (ua.login_count * 0.4 + ua.page_views * 0.3\n + ua.actions_taken * 0.3)::NUMERIC AS engagement_score,\n CASE\n WHEN (ua.login_count * 0.4 + ua.page_views * 0.3\n + ua.actions_taken * 0.3) > v_threshold THEN 'high'\n ELSE 'standard'\n END AS segment,\n CURRENT_DATE\n FROM analytics.user_activity ua\n WHERE ua.activity_date >= CURRENT_DATE - p_days_back\n ON CONFLICT (user_id, calc_date) DO UPDATE\n SET engagement_score = EXCLUDED.engagement_score,\n segment = EXCLUDED.segment;\n\n GET DIAGNOSTICS v_count = ROW_COUNT;\n RAISE NOTICE 'Processed % user engagement records', v_count;\n\n RETURN QUERY\n SELECT ed.user_id, ed.engagement_score, ed.segment\n FROM analytics.engagement_daily ed\n WHERE ed.calc_date = CURRENT_DATE;\nEND;\n$$;<\/code><\/pre>\n\n\n\nPERFORM<\/code> calls another function (with side effects), RAISE NOTICE<\/code> is PostgreSQL-specific logging, and RETURN QUERY<\/code> makes this a table-returning function whose output can feed into other queries. The lineage traces user_activity.login_count<\/code>, user_activity.page_views<\/code>, and user_activity.actions_taken<\/code> through computed expressions into engagement_daily.engagement_score<\/code> and engagement_daily.segment<\/code>, and also through the function’s return type. General SQL Parser understands all of these PL\/pgSQL constructs natively.<\/p>\n\n\n\nColumn-Level Lineage Through Stored Procedures, Not Around Them<\/h2>\n\n\n\n
Try It Yourself<\/h2>\n\n\n\n
\n