Oracle Stored Procedures Entities are not displayed on the UI Table lineage

[nqvuong1998]

Affected module

• OpenMetadata UI
• Ingestion Framework

Describe the bug

• I try to write some SQL to test oracle procedures:

CREATE TABLE HCVN_HANBUINB.A ( id NUMBER, name VARCHAR2(50), age NUMBER ); INSERT INTO HCVN_HANBUINB.A (id, name, age) VALUES (1, 'John', 25);

CREATE TABLE HCVN_HANBUINB.B AS SELECT * FROM HCVN_HANBUINB.A WHERE 1 = 0; INSERT INTO HCVN_HANBUINB.B (id, name, age) VALUES (3, 'Bob', 22);

CREATE OR REPLACE PROCEDURE CopyDataFromBtoA AS BEGIN INSERT INTO HCVN_HANBUINB.A (id, name, age) SELECT id, name, age FROM HCVN_HANBUINB.B; COMMIT; END CopyDataFromBtoA;

BEGIN CopyDataFromBtoA; END;

• After running metadata ingestion, OM displayed new entities: table HCVN_HANBUINB.A, table HCVN_HANBUINB.B, store procedure COPYDATAFROMBTOA
• After running lineage ingestion, OM displayed column-level lineage between HCVN_HANBUINB.A and HCVN_HANBUINB.B.
• But OM does not show Edge Information as a Stored Procedures entity on lineage between HCVN_HANBUINB.A and HCVN_HANBUINB.B.

To Reproduce

• https://github.com/open-metadata/OpenMetadata/blob/main/ingestion/src/metadata/ingestion/source/database/oracle/queries.py#L92-L139
  • It seems Oracle is not tracking Queries in Stores Procedure so q.query_start_time between sp.procedure_start_time and sp.procedure_end_time and q.query_end_time between sp.procedure_start_time and sp.procedure_end_time condition may not be working.
  • sql_text LIKE 'CALL%%' => Do we should use upper(sql_text)?

Expected behavior

• I expect that OM should support column-level lineage and attach Stored Procedures entities (if exists) on table-level lineage.

Version:

• OpenMetadata version: 1.3.0
• OpenMetadata Ingestion package version: [e.g. openmetadata-ingestion[oracle]==1.3.0]

[nqvuong1998]

cc @ayush-shah @NiharDoshi99

[ayush-shah]

@nqvuong1998 please share if the table already exists in OpenMetadata and share the screenshot of the lineage from that stored procedure itself, Thanks

[nqvuong1998]

Hi @ayush-shah ,

• Lineage between A and B:

• Stored Procedure:

[nqvuong1998]

Hi @ayush-shah ,

Several inquiries regarding Oracle Stored Procedures (SP) include:

1. Why doesn't OM utilize SP Definition in DBA_SOURCE to extract table lineage and attach edge information?
2. Why doesn't OM utilize UPPER(sql_text) when filtering SP data? For instance: sql_text LIKE 'CALL%%'.
3. When dealing with Oracle SP, there are various ways to trigger SP (CALL, BEGIN ... END). Does OM require additional conditions for filtering SP data?
4. In the Oracle gv$sql table, it appears that Oracle is not tracking the entire query history, potentially causing OM to miss some lineage.

[ayush-shah]

@nqvuong1998 we can see some similarities with below ticket, please refer to the below https://github.com/open-metadata/OpenMetadata/issues/15062

[ayush-shah]

@nqvuong1998

1. we use DBA_SOURCE itself to fetch the stored procedure, parse that and create an edge info from that. 2nd, 3rd and 4th we will investigate the same, also, if you want to contribute, do let us know, and we will help you guide around the same 🙏