Oracle Stored Procedures Improvement

[nqvuong1998]

Is your feature request related to a problem? Please describe.

1. Oracle Stored Procedures Code (displayed on OpenMetadata UI): Oracle metadata ingestion cannot scan enough SP code. This is because:

• DBA_SOURCE: Accessible only to users with DBA privileges or those granted specific access.
• ALL_SOURCE: Accessible to any user, showing only the objects they have privileges to view. https://github.com/open-metadata/OpenMetadata/blob/1.4.0-release/ingestion/src/metadata/ingestion/source/database/oracle/queries.py#L78-L90 => Why did you change DBA_SOURCE to ALL_SOURCE?

2. Oracle Stored Procedures Lineage (displayed on OpenMetadata UI): Oracle metadata ingestion cannot add SP Edge Information between tables. This is because:

• OpenMetadata uses gv$sql query historical table to parse SP Edge Information but this table has retention time (eg: 30 days) and there are many methods to execute SP not only CALL, BEGIN...END but also EXECUTE, EXEC. https://github.com/open-metadata/OpenMetadata/blob/1.4.0-release/ingestion/src/metadata/ingestion/source/database/oracle/queries.py#L92-L141 => Why do you need to use gv$sql to parse SP Edge Information?

Describe the solution you'd like

1. Oracle Stored Procedures Code:

• DBA_SOURCE: Useful when a DBA needs to perform a comprehensive audit or needs to review source code across the entire database.
• ALL_SOURCE: Useful for developers who are working on specific applications and need to view or modify the source code they have access to. => Change to use DBA_SOURCE

2. Oracle Stored Procedures Lineage: => You can use SP Code (SP Definition) from DBA_SOURCE to parse SP as alternative solution.

Additional context

• OpenMetadata version: 1.4.0
• OpenMetadata Ingestion package version: openmetadata-ingestion[oracle]==1.4.0.1

[nqvuong1998]

cc @ayush-shah @NiharDoshi99

[KsM00000]

Hi @nqvuong1998 I see you are working with Oracle in OpenMetadata. So am I. I was wondering if you can share your experience so I could see if some our problems with integrating OM and Oracle are tied to bugs and not to our really complicated DB structure.

1. Were any lineages created after SP ingestion? Despite having both sources and targets tables in OM and INSERT/MERGE/etc queries, after SP ingestion no lineages were created, even partialy.
2. At 1.3.1 we had a bug where some procedures would have swapped lines of code. It was really random procedures and only lines 1 and 4. Have you noticed something like this? I am upgrading to 1.4.0 right now and will see if bug would be reproduced after we re-ingest all SP.

[baohan1999]

Hello @ayush-shah, I have the same issue with @nqvuong1998, for table all_source, it is only possible to view the procedures of the current user or the procedures that current users were granted execute permission on. And it seems not reasonable to grant this permission every time a new procedure is created. So please let us know any reason behind this change and if it can be changed back. Thanks!

[arubincs]

Our organization is having the same issues with Oracle stored procedures not being ingested into OMD. As @baohan1999 said, it is not reasonable to grant execute privileges for every procedure to the OMD user schema. This is precisely what the DBA_SOURCE and DBA_PROCEDURES dictionaries should be used for. Please revert back to using DBA-level dictionaries, or give users the choice between DBA and ALL (as DataHub does, see data_dictionary_mode).

[baohan1999]

Hello @ayush-shah, as @arubincs also has the same issue, which we had described in our previous comment, can you please take a look and consider our suggestion? Thanks in advance!

[ayush-shah]

Hello @baohan1999, yes we are considering the above issue as many users have faced the same, we will target to have a toggle that either uses ALL_ or DBA_ which can cater to different different user requirements