Oracle - Lineage

This article outlines the lineage coverage, configuration requirements, metadata handling, supported scenarios, component behaviors, transformation coverage, and known limitations for lineage extraction in Oracle. The Oracle connector provides automated, production-ready data lineage extraction from Oracle databases, enabling end-to-end visibility into data flow from source tables and PL/SQL logic to downstream transformations. It supports both table-level and column-level lineage for Oracle SQL workloads and transformation processes.

Lineage Configuration Requirements

Accurate lineage extraction depends on lineage processing configuration and metadata availability. These requirements must be properly configured and available to ensure successful lineage generation and source-target resolution.

Configuration Requirements Table

Configuration	Required Detail
Lineage Processing Mode	Lineage execution is controlled using jobOption in ProcessLineageService (UNPROCESSED_LINEAGE_OBJECTS, REPROCESS_ALL_SOURCECODE, SELECTED_LINEAGE_OBJECTS, CONNECTION_LINEAGE)
Shallow Lineage	Enabled using OEConfig.LINEAGE_SHALLOW
Shallow Lineage Connection Scope	Controlled using OEConfig.SHALLOW_LIENAGE_CONNECTION_IDS
Processing Limit	Controlled using OEConfig.LINEAGE_CODES_PROCESS_LIMIT

Incorrect lineage processing configuration or missing source code metadata may result in incomplete lineage generation.

Lineage Components

Component / Feature	Availability
Tables	✅
Views	✅
Stored Procedures	⚠️
Functions	⚠️
Triggers	⚠️
Packages / Package Body	⚠️
Columns	✅
SQL Queries	✅
Aggregations	✅
Analytic Functions	✅
Oracle-specific Syntax (DECODE, JSON_TABLE, @prompt)	⚠️
Dynamic SQL	❌
Index DDL	❌
Materialized View Refresh	❌

The ⚠️ icon indicates partially supported functionality with limited lineage coverage in applicable scenarios.

Column Creation Support

This section outlines lineage support for column creation and transformation scenarios.

Column Creation from Lineage

Transformation Type	Supported
Expressions	✅
Aggregations	✅
Calculated Fields	⚠️
Derived Columns	⚠️
Renamed Columns	✅
Temp Columns	✅
Analytic Columns	✅

The ⚠️ icon indicates partially supported functionality with limited lineage coverage in applicable scenarios.

Supported Use Cases

The connector supports lineage extraction across Oracle SQL objects, PL/SQL code, and transformation workflows. These use cases represent scenarios where lineage extraction functions as expected.

Supported Lineage Scenarios

Supported	Details
Table to Table Lineage	Lineage for SELECT, INSERT, UPDATE, DELETE, and MERGE operations
Column to Column Lineage	Lineage including expressions and transformations
View to Table Lineage	Lineage extraction from views to underlying tables
Procedure to Table Lineage	Lineage extraction through PL/SQL parsing
Function to Table Lineage	Partial lineage extraction from functions
Trigger to Table Lineage	Query extraction-based lineage from triggers
Package to Table Lineage	Lineage based on SQL statements within packages
Aggregate Function Lineage	Lineage for functions such as SUM, COUNT, and ROW_NUMBER
Analytic Function Lineage	Lineage for OVER, PARTITION BY, and ORDER BY operations
Oracle Syntax Normalization	Support for DECODE, JSON_TABLE, @prompt, and collate binary_ci normalization
Query Log Lineage	Lineage through CONNECTION_LINEAGE processing
Derived Column Lineage	Expression-based column lineage
Temporary Column Creation	Temporary columns are created when metadata is unavailable

Lineage extraction is based on Oracle SQL parsing, PL/SQL analysis, metadata availability, and configured lineage processing options.

Partial or Limited Coverage

Certain scenarios provide only partial lineage coverage due to parser limitations, Oracle SQL rewrites, metadata dependencies, or PL/SQL complexity.

Scenarios

Scenario	Limitation Description
Calculated Fields	Oracle rewrites (for example, DECODE to SUM) may reduce lineage accuracy
Derived Columns	Expression parsing is limited for multi-step logic
Dynamic SQL References	Object references generated through EXECUTE IMMEDIATE cannot be fully resolved
PL/SQL Procedures	Nested or complex logic is only partially supported
BEGIN...END Blocks	Wrapper-only statements may not resolve into lineage
Cross-object Dependencies	Procedure-to-procedure lineage is limited
Shallow Lineage	Only table-level lineage is generated
Missing Metadata	May result in temporary or unknown columns
Dataset Lineage Status	Lineage may be incomplete if parsing fails

Complex PL/SQL logic, missing metadata, and Oracle SQL rewrites may reduce lineage accuracy or completeness.

Unsupported Scenarios

The connector does not support lineage extraction for certain administrative operations, dynamic SQL patterns, and non-resolvable execution paths due to limitations in static SQL parsing and metadata availability.

Unsupported Lineage

Not Supported	Description
CREATE / DROP / ALTER INDEX	Index management operations are not included in lineage processing
DBMS_STATS.GATHER_TABLE_STATS	Statistics collection operations are not tracked
OPENQUERY	External query execution is not supported
REFRESH MATERIALIZED VIEW	Materialized view refresh operations are not processed
OPTIMIZE and DESC Statements	Administrative statements are excluded from lineage
EXEC Statements with Unresolved Object References	Object references cannot be resolved during parsing
Dynamic SQL (EXECUTE IMMEDIATE) with Non-resolvable Object Names	Runtime-generated objects cannot be identified
MDX or Non-SQL Queries	Queries outside the supported SQL parsing scope
Wrapper-only BEGIN...END Blocks Without DML/SELECT Statements	No lineage-generating SQL available for analysis
Complex PL/SQL Packages and Procedures with Nested or Multi-step Logic	Logic cannot be statically resolved for lineage generation

Unsupported scenarios will not produce lineage and may appear disconnected in lineage visualization.

Current Functional Status

This section outlines the present state of lineage coverage supported by the Oracle connector based on the available capabilities and limitations.

Status Area	Details
Overall Coverage	Partial coverage across Oracle SQL and PL/SQL workloads
Lineage Depth	Strong table-level and column-level lineage support
Supported Inputs	Tables, views, SQL queries, PL/SQL procedures, triggers, packages, and query logs
Functional Scope	Lineage extraction for Oracle SQL parsing, aggregations, analytic functions, and transformation workflows
Limitation Areas	Dynamic SQL, complex PL/SQL workflows, cross-object dependencies, missing metadata, and unsupported administrative statements
Resulting Output	Reliable lineage for most Oracle SQL workloads with partial coverage for advanced PL/SQL and dynamic execution scenarios

Coverage remains partial because dynamic SQL, complex PL/SQL workflows, unsupported administrative statements, and non-resolvable execution paths are not fully supported. The connector provides strong lineage coverage for Oracle SQL parsing, column-level lineage, aggregations, and analytic functions, but advanced procedural logic may require manual validation.

---

Copyright © 2026, OvalEdge LLC, Peachtree Corners, GA, USA.