-
How to get the correct tree structure?
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
);
insert into dba_dependX values('TEST', 'PACKAGE', 'SPK_A', 'TEST', 'TABLE' , 'T_A1' );
insert into dba_dependX values('TEST', 'PACKAGE', 'SPK_A', 'TEST', 'TABLE' , 'T_A2' );
insert into dba_dependX values('TEST', 'PACKAGE', 'SPK_A', 'SYS ', 'PACKAGE', 'STANDARD');
insert into dba_dependX values('TEST', 'PACKAGE BODY', 'SPK_A', 'TEST', 'PACKAGE', 'SPK_A' );
insert into dba_dependX values('TEST', 'PACKAGE BODY', 'SPK_A', 'TEST', 'PACKAGE', 'SPK_B' );
insert into dba_dependX values('TEST', 'PACKAGE BODY', 'SPK_A', 'SYS', 'PACKAGE', 'STANDARD');
insert into dba_dependX values('TEST', 'PACKAGE', 'SPK_B', 'SYS', 'PACKAGE', 'STANDARD');
insert into dba_dependX values('TEST', 'PACKAGE BODY', 'SPK_B', 'TEST', 'PACKAGE', 'SPK_B ' );
insert into dba_dependX values('TEST', 'PACKAGE BODY', 'SPK_B', 'TEST', 'TABLE' , 'T_B2' );
insert into dba_dependX values('TEST', 'PACKAGE BODY', 'SPK_B', 'SYS', 'PACKAGE', 'STANDARD');
insert into dba_dependX values('TEST', 'PACKAGE BODY', 'SPK_B', 'TEST', 'TABLE' , 'T_B1' );
commit;
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 12:28 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|