I have some views. I would like to find out all the referenced table, functions, views inside those views. In case the views references another view then I would like to know the objects referenced inside the child view and so on. Can someone provide me the SQL for this? Would be nice if the output is nicely formatted.
Also, I want to drill down to further level only in case the referenced object is a view.
Message was edited by: user10566312
Query the data dictionary views USER_DEPENDENCIES, ALL_DEPENEDENCIES or (if you have privileges) DBA_DEPENDENCIES.
A CONNECT BY query will work in any version of Oracle. Starting in Oracle 11.2, you also have the option of using a recursive WITH clause.
Exactly how to do it depends on exactly what you want. If you'd like help, post a complete test case (CREATE statements for tables, views and/or functions that depend on one another) and the results you want to see from that input.
Always say what version of Oracle you're using (e.g. 18.104.22.168.0).
See the forum FAQ: https://forums.oracle.com/message/9362002
I have written the below SQL. However, the SYS_CONNECT_BY_PATH is not showing the leaf node child node in output. I have considered object name as parent and referenced objects as children. For example for the first row the PATH should have been /BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL as per the SYS_CONNECT_BY_PATH functionality.
SELECT level, type||' '||owner||'.'||name ASparent_object, LPAD(' ', 2*(LEVEL - 1)) || referenced_type ||' '||referenced_owner||'.'||referenced_name ASunderlying_object, SYS_CONNECT_BY_PATH( name, '/') ASPath FROMall_dependencies WHERE REFERENCED_OWNER <> 'SYS' START WITH NAME IN ('BK_LOTVSCT_SUM_VW') ANDOWNER = 'ADMIN CONNECT BY nocycle priorreferenced_owner = owner AND prior referenced_name = name AND priorreferenced_type = type ORDER SIBLINGS BY referenced_name;
|1||VIEW admin.BK_LOTVSCT_SUM_VW||VIEW admin.MTD_BK_LOTVSCT_DTL||/BK_LOTVSCT_SUM_VW|
|2||VIEW admin.MTD_BK_LOTVSCT_DTL||VIEW admin.MTD_BK_CTVSCT_VW||/BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL|
|3||VIEW admin.MTD_BK_CTVSCT_VW||VIEW admin.MTD_BK_CT_VW||/BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL/MTD_BK_CTVSCT_VW|
|4||VIEW admin.MTD_BK_CT_VW||VIEW admin.MTD_CT_VW||/BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL/MTD_BK_CTVSCT_VW/MTD_BK_CT_VW|
|5||VIEW admin.MTD_CT_VW||TABLE admin.LOT||/BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL/MTD_BK_CTVSCT_VW/MTD_BK_CT_VW/MTD_CT_VW|
|5||VIEW admin.MTD_CT_VW||TABLE admin.LOTRUN||/BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL/MTD_BK_CTVSCT_VW/MTD_BK_CT_VW/MTD_CT_VW|
|5||VIEW admin.MTD_CT_VW||TABLE admin.LOTRUN_HOLD_RELS||/BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL/MTD_BK_CTVSCT_VW/MTD_BK_CT_VW/MTD_CT_VW|
|5||VIEW admin.MTD_CT_VW||TABLE admin.PROD_CUR_PART_PRCD||/BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL/MTD_BK_CTVSCT_VW/MTD_BK_CT_VW/MTD_CT_VW|
Message was edited by: user10566312
If you are missing the Leaf nodes in the Path, you can use the CONNECT_BY_ISLEAF pseudo column to append it to your path:
drop table test_table;
drop table test_table1;
drop table test_table2;
drop table test_table3;
drop view vw_test;
drop view vw_vw_test;
drop view vw_vw_vw_test;
create table test_table (col number);
create table test_table1 (col number);
create table test_table2 (col number);
create table test_table3 (col number);
create or replace view vw_test as
select sum(a.col) a_sum, sum(b.col) b_sum, sum(c.col) c_sum, sum(d.col) d_sum
from test_table a, test_table1 b, test_table2 c, test_table3 d;
create or replace view vw_vw_test as
select a_sum, b_sum
create or replace view vw_vw_vw_test as
select a_sum + b_sum sm_cols
select name, path || decode(isleaf, 1, '\' || trim(referenced_name), null) path
select name, lpad(' ', 4 * (level - 1), ' ') || referenced_name referenced_name, referenced_type,
sys_connect_by_path(name, '\') path, level, connect_by_isleaf isleaf
where referenced_name != 'STANDARD'
start with name = 'VW_VW_VW_TEST'
connect by name = prior referenced_name
6 rows selected
user10566312 wrote:I have written the below SQL. However, the SYS_CONNECT_BY_PATH is not showing the leaf node in output. I have considered object name as parent and referenced objects as children. For example for the first row the PATH should have been /BK_LOTVSCT_SUM_VW/MTD_BK_LOTVSCT_DTL as per the SYS_CONNECT_BY_PATH functionality.
No, it won't be. Since you are traversing the hierarchy from the Root node to Leaf, a Parent will not know its children, until you access them. If you have only one child, it might be alright, but how do you expect it to work if there are multiple children? Hence, the Child node will get its hierarchy correct. I hope it answers your questions. If you, please let us know.
Also, I have posted a test case, which I believe resembles your situation. I would be great if you could explain your question(s) considering the test case or modifying it to suit your scenario. This allows people to replicate it at their databases and provide correct solutions.
No. I do not see that happening. Please read SYS_CONNECT_BY_PATH for more understanding. Documentation clearly states:- "It returns the path of a column value from root to node, with column values separated by
char for each row returned by
Thus, your requirement to get the child name in the parent row is incorrect. You will have to look for a workaround in this situation.
Also, in the link you provided, I do no see any example displaying Child node information. Would you like to precisely mention which example you were mentioning about?
I would like to give a kind suggestion, it is lot better to explain the scenario with a test case which allows volunteers to understand and visualize situation better. I have been mentioning this to you, but it looks like you choose to ignore it, thus delaying in getting a correct response.
As per the first example in the URL, column parent_key is the parent column and key is the child column. So if you see the output, the value in the KEY_INDENTED (child column key) field is coming appended as part of KEY_PATH.
And as Oracle says it return value from root to node, so the first record in my output should have /MTD_BK_LOTVSCT_DTL appended to the path. And similarly for other records.
Your hierarchy starts with BK_LOTVSCT_SUM_VW; Hence, the first row will have the root node populated i.e. current row. As you traverse down the hierarchy, the nodes will be appended to the Path.
Now, the view MTD_BK_LOTVSCT_DTL, is child of BK_LOTVSCT_SUM_VW, hence while processing BK_LOTVSCT_SUM_VW, Oracle does not have any knowledge of the Child node; Hence, no detail of MTD_BK_LOTVSCT_DTL will be appended to Path while BK_LOTVSCT_SUM_VW is being processed.
However, while traversing the tree, when oracle processes MTD_BK_LOTVSCT_DTL node, it knows the parent node and thus deatils of parent node are appended to the Path.
The term root to node, need not be taken literally. It means, Oracle will append details from Root node to the Current node being processed. but not that, it will append details of Child Nodes or the nodes that are yet to be processed.
Why don't you quit reinventing the wheel and use the utldtree.sql script that Oracle provides with the database?
See my extensive replies in this thread from last year:
This Oracle Database doc reference describes one of the tables used by the script