The sample code as below:
---------------------------------------------------------------------------------------------------------------------
create table dba_dependX
(
owner varchar2(50), --parent owner
type varchar2(50), --parent type
name varchar2(50), --parent name
referenced_owner varchar2(50), --child owner
referenced_type varchar2(50), --child type
referenced_name varchar2(50) --child name
);
The data structure as below:
---------------------------------------------------------------------------------------------------------------------
SPK_A(PACKAGE BODY)......SPK_A(PACKAGE)......T_A1(TABLE)
..................................................................T_A2(TABLE)
..................................................................STANDARD(PACKAGE)
.....................................SPK_B(PACKAGE)
.....................................STANDARD(PACKAGE)
SPK_B(PACKAGE BODY)......SPK_B(PACKAGE)......STANDARD(PACKAGE)
.....................................STANDARD(PACKAGE)
.....................................T_B1(TABLE)
.....................................T_B2(TABLE)
Question1: how to get the result as above?
I have tried to write sql as below, but there are some difference
---------------------------------------------------------------------------------------------------------------------
select 'TEST' || ' ' || 'SPK_A' || ' ' || 'PACKAGE BODY' from dual
union all
select lpad(' ',5) || lpad(' ',3*(level-1)) || referenced_owner || ' ' || referenced_name || ' ' || referenced_type
from dba_dependX dd
start with dd.name='SPK_A' and dd.type='PACKAGE BODY'
connect by prior referenced_name=name and referenced_type=type
union all
select 'TEST' || ' ' || 'SPK_A' || ' ' || 'PACKAGE' from dual
union all
select lpad(' ',5) || lpad(' ',3*(level-1)) || referenced_owner || ' ' || referenced_name || ' ' || referenced_type
from dba_dependX dd
start with dd.name='SPK_A' and dd.type='PACKAGE'
connect by prior referenced_name=name and referenced_type=type
then it comes the structure as below, but it is not what i want:
--------------------------------------------
SPK_A(PACKAGE BODY)......SPK_A(PACKAGE)
.....................................SPK_B(PACKAGE)
.....................................STANDARD(PACKAGE)
SPK_A(PACKAGE)...............T_A1(TABLE)
.....................................T_A2(TABLE)
.....................................STANDARD(PACKAGE)
Question2: How to get the result as below:
--------------------------------------------------------------------------------------------------------------------
SPK_A(PACKAGE BODY)......SPK_A(PACKAGE)......T_A1(TABLE)
..................................................................T_A2(TABLE)
..................................................................STANDARD(PACKAGE)
.....................................SPK_B(PACKAGE)
.....................................STANDARD(PACKAGE)
Can anyone help me?
Any ideas appreciated.
Merry Chirstmas!
Last edited by anyoneokay; 12-25-2007 at 11:28 AM.
Bookmarks