Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Dinakar.

Asked: June 04, 2024 - 1:48 pm UTC

Last updated: June 10, 2024 - 6:56 am UTC

Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Viewed 100+ times

You Asked

We have a UI based application where users come in and setup SQL's to get excel based reports back, there are multiple options to chose on the schedule of the SQL execution and other related parameters. It's a free text box, the expectation is that the users will test their SQL in the database before they setup the SQL in this tool. One of our current requirement is to identify dependency of objects in the SQL.
if this is the SQL:
select
a.col1, b.col2, c.col3
from
table_a a,
view b,
pipe_line_function c
where a.col1 = b.col2
and b.col2 = c.col3;

As a list of dependent objects, the requirement is to get:
TABLEA
VIEW and the objects within the VIEW until we drill down to the base tables or the most granular.
pipe_line_function and the objects within until we drill down to the base tables or the most granular.

Is this possible using any new SQL functions, dependency functions etc., without creating a view of the above SQL setup by the users. We are aware about DBA_DEPENDENCIES. it is not possible to create a view and then grab the dependencies and hence this ticket.

and Connor said...

If you can't create anything (view, procedure, etc), then you're not going to be able to get *everything* because the dependency tree for a cursor/SQL is stored in memory and will be transient.

The closest you could probably get it to determine the execution plan, and then dig into the plan table, eg

SQL> create or replace
  2  view my_view as
  3  select e.*, d.dname
  4  from emp e, dept  d
  5  where e.deptno = d.deptno;

View created.

SQL> explain plan for
  2  select *
  3  from my_view, hr.employees
  4  where my_view.hiredate = employees.hire_date;

Explained.

SQL> select distinct object_owner, object_name
  2  from plan_table;

OBJECT_OWNER                   OBJECT_NAME
------------------------------ ---------------------

MCDONAC                        EMP
HR                             EMPLOYEES
MCDONAC                        DEPT_PK
MCDONAC                        DEPT


If you want more than that, you'll a database object (view etc) so that you can dig into DBA_DEPENDENCIES

Rating

  (1 rating)

Comments

Thank you

Dinakar, June 07, 2024 - 11:38 am UTC

Thank you, very helpful!
If it's a pipelined table function with multiple views and objects underneath, we can only get the function details and not the underlying objects in the Explain Plan. Probably in that case, we could directly go to DBA_DEPENDENCIES. Any thoughts, comments?
Connor McDonald
June 10, 2024 - 6:56 am UTC

Execution plan will tell you its a function, and then dig into xxx_DEPENDENCIES from there

SQL> create or replace function pipedemo return sys.odcinumberlist pipelined is
  2  begin
  3    pipe row ( 1 );
  4    return;
  5  end;
  6  /

Function created.

SQL> select * from pipedemo();

COLUMN_VALUE
------------
           1

SQL> explain plan for
  2  select * from pipedemo();

Explained.

SQL> select * from plan_table
  2  @pr
==============================
STATEMENT_ID                  :
PLAN_ID                       : 1282
TIMESTAMP                     : 10-JUN-24
REMARKS                       :
OPERATION                     : SELECT STATEMENT
OPTIONS                       :
OBJECT_NODE                   :
OBJECT_OWNER                  :
OBJECT_NAME                   :
OBJECT_ALIAS                  :
OBJECT_INSTANCE               :
OBJECT_TYPE                   :
OPTIMIZER                     : ALL_ROWS
SEARCH_COLUMNS                :
ID                            : 0
PARENT_ID                     :
DEPTH                         : 0
POSITION                      : 29
COST                          : 29
CARDINALITY                   : 8168
BYTES                         : 16336
OTHER_TAG                     :
PARTITION_START               :
PARTITION_STOP                :
PARTITION_ID                  :
OTHER                         :
OTHER_XML                     :
DISTRIBUTION                  :
CPU_COST                      : 1937344
IO_COST                       : 29
TEMP_SPACE                    :
ACCESS_PREDICATES             :
FILTER_PREDICATES             :
PROJECTION                    :
TIME                          : 1
QBLOCK_NAME                   :
==============================
STATEMENT_ID                  :
PLAN_ID                       : 1282
TIMESTAMP                     : 10-JUN-24
REMARKS                       :
OPERATION                     : COLLECTION ITERATOR
OPTIONS                       : PICKLER FETCH
OBJECT_NODE                   :
OBJECT_OWNER                  :
OBJECT_NAME                   : PIPEDEMO
OBJECT_ALIAS                  : "KOKBF$0"@"SEL$2"
OBJECT_INSTANCE               :
OBJECT_TYPE                   : PROCEDURE
OPTIMIZER                     :
SEARCH_COLUMNS                :
ID                            : 1
PARENT_ID                     : 0
DEPTH                         : 1
POSITION                      : 1
COST                          : 29
CARDINALITY                   : 8168
BYTES                         : 16336
OTHER_TAG                     :
PARTITION_START               :
PARTITION_STOP                :
PARTITION_ID                  :
OTHER                         :
OTHER_XML                     : <other_xml><info type="has_plsql">yes</info><info
type="db_version">21.0.0.0</info><info type="parse_schema"><![CDATA["MCDONAC"]]></info><info
type="plan_hash_full">1182393574</info><info type="plan_hash">2048677713</info><info
type="plan_hash_2">1182393574</info><stats type="compilation"><stat
name="bg">2</stat></stats><qb_registry><q o="18" f="y"
h="y"><n><![CDATA[SEL$F5BB74E1]]></n><p><![CDATA[SEL$1]]></p><i><o><t>VW</t><v><![CDATA[SEL$2]]></v>
</o></i><f><h><t><![CDATA[KOKBF$0]]></t><s><![CDATA[SEL$2]]></s></h></f></q><q
o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[from$_subquery$_001]]></t><s><![CDATA[SEL$1]]></s></
h></f></q><q
o="2"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[KOKBF$0]]></t><s><![CDATA[SEL$2]]></s></h></f></q></
qb_registry><outline_data><hint><![CDATA[FULL(@"SEL$F5BB74E1"
"KOKBF$0"@"SEL$2")]]></hint><hint><![CDATA[OUTLINE(@"SEL$2")]]></hint><hint><![CDATA[OUTLINE(@"SEL$1
")]]></hint><hint><![CDATA[MERGE(@"SEL$2"
>"SEL$1")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$F5BB74E1")]]></hint><hint><![CDATA[ALL_ROWS]]><
/hint><hint><![CDATA[DB_VERSION('21.1.0')]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('21.1.0'
)]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></other_xml>
DISTRIBUTION                  :
CPU_COST                      : 1937344
IO_COST                       : 29
TEMP_SPACE                    :
ACCESS_PREDICATES             :
FILTER_PREDICATES             :
PROJECTION                    : VALUE(A0)[22]
TIME                          : 1
QBLOCK_NAME                   : SEL$F5BB74E1

PL/SQL procedure successfully completed.

SQL>





More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library