How to get the correct tree structure?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 1 of 1

Thread: How to get the correct tree structure?

  1. #1
    Join Date
    Nov 2007
    Location
    China
    Posts
    22

    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
  •  


Click Here to Expand Forum to Full Width