Home » SQL & PL/SQL » SQL & PL/SQL » How create such attached table by travelling view and its dependent objects (Oracle 18c)
 How create such attached table by travelling view and its dependent objects [message #687824] |
Thu, 22 June 2023 08:33  |
mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai
|
Senior Member |
|
|
Hi Experts,
I have a requirement is that a view need to be travelled till its leaf level objects and need to be capture in table as attached.
Is the possible to achieve using query or need to be programmed.
Whether is it possible or not?
I am using the following query to travel through view and its dependent objects.
Level can also increase dynamically as per each views.
select *
from all_dependencies
start with NAME = 'DW_NOBEL_INVOICE_LINES_VTAB'
connect by nocycle name = prior REFERENCED_NAME
Appreciated your expert guidence.
Thanks & regards,
Kanishka
[Updated on: Thu, 22 June 2023 08:34] Report message to a moderator |
|
|
|
|
|
|
|
| Re: How create such attached table by travelling view and its dependent objects [message #687827 is a reply to message #687826] |
Thu, 22 June 2023 11:26  |
mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai
|
Senior Member |
|
|
I have created the following query. is this possible to dynamically extend to any level.
Quote:Select a.referenced_owner,a.referenced_name,a.referenced_type,b.referenced_owner,b.referenced_name,b.referenced_type,c.referenced_owner,c.re ferenced_name,c.referenced_type
from
(Select lvl,name,referenced_owner,referenced_name,referenced_type from (
select level lvl,a.*
from all_dependencies a
start with NAME = 'GW_ORDER_LINES_VTAB'
connect by nocycle name = prior REFERENCED_NAME)
Where lvl=1) a,
( Select lvl,name,referenced_owner,referenced_name,referenced_type from (
select level lvl,a.*
from all_dependencies a
start with NAME = 'GW_ORDER_LINES_VTAB'
connect by nocycle name = prior REFERENCED_NAME)
Where lvl=2) b,
( Select lvl,name,referenced_owner,referenced_name,referenced_type from (
select level lvl,a.*
from all_dependencies a
start with NAME = 'GW_ORDER_LINES_VTAB'
connect by nocycle name = prior REFERENCED_NAME)
Where lvl=3) c
Where a.referenced_name = b.name(+)
and b.referenced_name = c.name(+)
|
|
|
|
| Re: How create such attached table by travelling view and its dependent objects [message #687829 is a reply to message #687827] |
Thu, 22 June 2023 15:23 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can create a procedure that will dynamically extend it to any level. I have provided such a procedure below for you to copy and paste and run. I have output the result set, but you could return it to a ref cursor variable instead or you could modify the code to dynamically create a table from the result set.
CREATE OR REPLACE PROCEDURE get_dependencies
( p_start IN VARCHAR2 )
AS
v_levels NUMBER;
v_sql1 VARCHAR2(32767);
v_sql2 VARCHAR2(32767);
v_sql3 VARCHAR2(32767);
v_sql VARCHAR2(32767);
v_refcur SYS_REFCURSOR;
BEGIN
SELECT MAX (LEVEL)
INTO v_levels
FROM all_dependencies
START WITH name = p_start
CONNECT BY NOCYCLE name = PRIOR referenced_name;
-- DBMS_OUTPUT.PUT_LINE ('levels: ' || v_levels);
v_sql1 := 'SELECT a.referenced_owner,a.referenced_name,a.referenced_type';
v_sql2 := ' FROM (SELECT lvl,name,referenced_owner,referenced_name,referenced_type
FROM (SELECT LEVEL lvl,a.*
FROM all_dependencies a
START WITH name = ''' || p_start || '''
CONNECT BY NOCYCLE name = PRIOR referenced_name)
WHERE lvl=1) a';
v_sql3 := ' WHERE 1 = 1 ';
FOR i IN 2 .. v_levels LOOP
v_sql1 := v_sql1 || CHR(10) || CHR(13)
|| ',' || CHR (i + 96) || '.referenced_owner,'
|| CHR (i + 96) || '.referenced_name,'
|| CHR (i + 96) || '.referenced_type';
v_sql2 := v_sql2 || CHR(10) || CHR(13)
|| ', (SELECT lvl,name,referenced_owner,referenced_name,referenced_type
FROM (SELECT LEVEL lvl,a.*
FROM all_dependencies a
START WITH name = ''' || p_start || '''
CONNECT BY NOCYCLE name = PRIOR referenced_name )
WHERE lvl=' || i || ') ' || CHR (i + 96);
v_sql3 := v_sql3 || CHR(10) || CHR(13)
|| ' AND ' || CHR (i + 95) || '.referenced_name = ' || CHR (i + 96) || '.name(+)';
END LOOP;
v_sql := v_sql1 || v_sql2 || v_sql3;
-- DBMS_OUTPUT.PUT_LINE (v_sql);
OPEN v_refcur FOR v_sql;
DBMS_SQL.RETURN_RESULT (v_refcur);
END get_dependencies;
/
SHOW ERRORS
EXECUTE get_dependencies ('GW_ORDER_LINES_VTAB')
but the code should display however many levels there are in your data.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE get_dependencies
2 ( p_start IN VARCHAR2 )
3 AS
4 v_levels NUMBER;
5 v_sql1 VARCHAR2(32767);
6 v_sql2 VARCHAR2(32767);
7 v_sql3 VARCHAR2(32767);
8 v_sql VARCHAR2(32767);
9 v_refcur SYS_REFCURSOR;
10 BEGIN
11 SELECT MAX (LEVEL)
12 INTO v_levels
13 FROM all_dependencies
14 START WITH name = p_start
15 CONNECT BY NOCYCLE name = PRIOR referenced_name;
16 -- DBMS_OUTPUT.PUT_LINE ('levels: ' || v_levels);
17
18 v_sql1 := 'SELECT a.referenced_owner,a.referenced_name,a.referenced_type';
19 v_sql2 := ' FROM (SELECT lvl,name,referenced_owner,referenced_name,referenced_type
20 FROM (SELECT LEVEL lvl,a.*
21 FROM all_dependencies a
22 START WITH name = ''' || p_start || '''
23 CONNECT BY NOCYCLE name = PRIOR referenced_name)
24 WHERE lvl=1) a';
25 v_sql3 := ' WHERE 1 = 1 ';
26 FOR i IN 2 .. v_levels LOOP
27 v_sql1 := v_sql1 || CHR(10) || CHR(13)
28 || ',' || CHR (i + 96) || '.referenced_owner,'
29 || CHR (i + 96) || '.referenced_name,'
30 || CHR (i + 96) || '.referenced_type';
31 v_sql2 := v_sql2 || CHR(10) || CHR(13)
32 || ', (SELECT lvl,name,referenced_owner,referenced_name,referenced_type
33 FROM (SELECT LEVEL lvl,a.*
34 FROM all_dependencies a
35 START WITH name = ''' || p_start || '''
36 CONNECT BY NOCYCLE name = PRIOR referenced_name )
37 WHERE lvl=' || i || ') ' || CHR (i + 96);
38 v_sql3 := v_sql3 || CHR(10) || CHR(13)
39 || ' AND ' || CHR (i + 95) || '.referenced_name = ' || CHR (i + 96) || '.name(+)';
40 END LOOP;
41 v_sql := v_sql1 || v_sql2 || v_sql3;
42 -- DBMS_OUTPUT.PUT_LINE (v_sql);
43
44 OPEN v_refcur FOR v_sql;
45 DBMS_SQL.RETURN_RESULT (v_refcur);
46 END get_dependencies;
47 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> EXECUTE get_dependencies ('DEPT_EMP_VIEW')
PL/SQL procedure successfully completed.
ResultSet #1
REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE
----------------- ----------------- --------------- ----------------- ----------------- ---------------
SCOTT EMP_SALGRADE_VIEW VIEW SCOTT EMP TABLE
SCOTT EMP_SALGRADE_VIEW VIEW SCOTT SALGRADE TABLE
SCOTT DEPT TABLE
3 rows selected.
[Updated on: Thu, 22 June 2023 15:34] Report message to a moderator |
|
|
|
|
|
| Re: How create such attached table by travelling view and its dependent objects [message #687835 is a reply to message #687830] |
Sat, 24 June 2023 13:19 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I created a synonym dev_syn for the dept_emp_view that I used for my previous demonstration, so that I could show that the same code works for additional levels. If you copy and paste the code that I originally provided for you and run it, it should show however many levels there are on your system for whatever you run it against. I am not sure if you understand that what I provided was not just a work in progress, but a dynamic expansion of the method you were using for all levels, a finished product of what you requested. Did you copy and paste it and run it and did it give you the results you wanted?
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE get_dependencies
2 ( p_start IN VARCHAR2 )
3 AS
4 v_levels NUMBER;
5 v_sql1 VARCHAR2(32767);
6 v_sql2 VARCHAR2(32767);
7 v_sql3 VARCHAR2(32767);
8 v_sql VARCHAR2(32767);
9 v_refcur SYS_REFCURSOR;
10 BEGIN
11 SELECT MAX (LEVEL)
12 INTO v_levels
13 FROM all_dependencies
14 START WITH name = p_start
15 CONNECT BY NOCYCLE name = PRIOR referenced_name;
16 -- DBMS_OUTPUT.PUT_LINE ('levels: ' || v_levels);
17
18 v_sql1 := 'SELECT a.referenced_owner,a.referenced_name,a.referenced_type';
19 v_sql2 := ' FROM (SELECT lvl,name,referenced_owner,referenced_name,referenced_type
20 FROM (SELECT LEVEL lvl,a.*
21 FROM all_dependencies a
22 START WITH name = ''' || p_start || '''
23 CONNECT BY NOCYCLE name = PRIOR referenced_name)
24 WHERE lvl=1) a';
25 v_sql3 := ' WHERE 1 = 1 ';
26 FOR i IN 2 .. v_levels LOOP
27 v_sql1 := v_sql1 || CHR(10) || CHR(13)
28 || ',' || CHR (i + 96) || '.referenced_owner,'
29 || CHR (i + 96) || '.referenced_name,'
30 || CHR (i + 96) || '.referenced_type';
31 v_sql2 := v_sql2 || CHR(10) || CHR(13)
32 || ', (SELECT lvl,name,referenced_owner,referenced_name,referenced_type
33 FROM (SELECT LEVEL lvl,a.*
34 FROM all_dependencies a
35 START WITH name = ''' || p_start || '''
36 CONNECT BY NOCYCLE name = PRIOR referenced_name )
37 WHERE lvl=' || i || ') ' || CHR (i + 96);
38 v_sql3 := v_sql3 || CHR(10) || CHR(13)
39 || ' AND ' || CHR (i + 95) || '.referenced_name = ' || CHR (i + 96) || '.name(+)';
40 END LOOP;
41 v_sql := v_sql1 || v_sql2 || v_sql3;
42 -- DBMS_OUTPUT.PUT_LINE (v_sql);
43
44 OPEN v_refcur FOR v_sql;
45 DBMS_SQL.RETURN_RESULT (v_refcur);
46 END get_dependencies;
47 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> EXECUTE get_dependencies ('DEV_SYN')
PL/SQL procedure successfully completed.
ResultSet #1
REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE
----------------- ----------------- --------------- ----------------- ----------------- --------------- ----------------- ----------------- ---------------
SCOTT DEPT_EMP_VIEW VIEW SCOTT EMP_SALGRADE_VIEW VIEW SCOTT EMP TABLE
SCOTT DEPT_EMP_VIEW VIEW SCOTT EMP_SALGRADE_VIEW VIEW SCOTT SALGRADE TABLE
SCOTT DEPT_EMP_VIEW VIEW SCOTT DEPT TABLE
3 rows selected.
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 12:42:29 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
