PL/SQL parsing

In this document, you'll learn how Dataedo uses an SQL parser with the PL/SQL dialect and explore its capabilities.

Supported SQL engines

The following SQL engines use this dialect:

• Oracle
• Oracle E-Business Suite
• IBM Db2

Automatic column-level lineage from SELECT||CREATE VIEW statements

Dataedo will create column-level data lineage for views and SQL queries based on the script. Just leave the Automatic lineage option selected during import.

If you deactivate this feature during import, you can still create column-level lineage automatically using the Parse Script to Lineage functionality in the Script tab.

This function also applies to manually added views created with a script, as well as SQL query objects.

Supported syntax overview

Supported SQL Syntax

Dataedo supports automatic lineage creation for the following PL/SQL syntax:

• SELECT and CREATE VIEW statements
• CASEs and other logic-related expressions
• UNIONs
• JOINs
• Aliases
• Functions and operators
• Subqueries and nested queries
• Non-recursive Common Table Expressions (CTEs)

Unsupported SQL Syntax

Dataedo does not support automatic lineage creation for the following PL/SQL syntax:

• Statements that access nested data such as JSON, XML, or objects as columns
• PIVOT and UNPIVOT relational operators
• Recursive Common Table Expressions (Recursive CTE)
• Dynamic queries
• Window functions

Automatic column-level lineage from imported Stored Procedures and User Defined Functions

Dataedo generates column-level data lineage for stored procedures and user-defined functions based on their scripts. To use this feature, make sure you selected the Automatic Lineage option during the initial import or when modifying import settings.

Dataedo divides procedures into steps, representing each step as a process and creating data lineage for supported steps. Unsupported steps are labeled using the first word of the process script, followed by ellipses (...).

In the Script tab, each process includes a fragment of the procedure script it corresponds to.

Supported syntax overview

Supported SQL Syntax

Dataedo supports automatic lineage creation for the following PL/SQL syntax:

• INSERT
• UPDATE
• RETURNING INTO statements
• Non-recursive Common Table Expressions (CTEs, WITH statements)
• CREATE TABLE AS SELECT

When temporary tables are created in a procedure (i.e., tables that do not normally exist in the documented database), Dataedo will attempt to merge the data lineage entering and exiting these tables. Instead of representing the lineage as SOURCE_TABLE -> #TEMP_TABLE -> DESTINATION_TABLE, it will simplify this to SOURCE_TABLE -> DESTINATION_TABLE.

However, due to the limitations of ANTLR, the presence of unsupported elements in a procedure may prevent it from being parsed entirely. At this stage, any procedure containing elements outside those explicitly supported is considered unsupported. Nevertheless, we strive to ensure that unsupported elements do not interfere with the processing of supported steps.

Unsupported SQL Syntax

Dataedo does not support automatic lineage creation for the following PL/SQL syntax:

• DELETE statements
• MERGE statements
• INSERT ... WHEN ... statements
• Syntax mentioned as unsupported in the section above

Extract data from PL/SQL packages

Dataedo uses SQL parser to break PL/SQL packages into individual objects – functions and stored procedures with parameters and scripts.

Online SQL Parser

Check out Dataedo's online SQL parsing and analysis tool to test SQL parsing capabilities in real time.