Home » Other » Training & Certification » How to get the correct tree structure?
How to get the correct tree structure? [message #289699] Tue, 25 December 2007 10:37 Go to next message
kevin_ye
Messages: 37
Registered: December 2007
Location: Shanghai,China
Member
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!

[Updated on: Tue, 25 December 2007 10:40]

Report message to a moderator

Re: How to get the correct tree structure? [message #289701 is a reply to message #289699] Tue, 25 December 2007 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

If you only have at most 2 levels you don't need a "connect by" SQL.

Regards
Michel
Re: How to get the correct tree structure? [message #289709 is a reply to message #289699] Tue, 25 December 2007 14:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9099
Registered: November 2002
Location: California, USA
Senior Member
In your insert statements, you have a space after one of your SPK_B ('SPK_B ') that should not be there and will alter your results. You should not be selecting literal values, just values from the table. Since you are using 8.1.7, you cannot use the newer hierarchical features. Your desired output is also not the typical hierarchical output either. It looks like you would be better off doing an outer join of the table to itself. You can concatenates parentheses and columns to get name(type). You can use the SQL*Plus BREAK command to suppress repetition of columns, remembering that you must order by the columns that you break on. You will also want to make sure that your first column of names and types are those that are not in the referenced names and types, so that you only start with the two roots. I have provided partial code and results below, just to show that it can be done this way and get you started.

SCOTT@orcl_11g> BREAK ON ...
SCOTT@orcl_11g> SELECT ... AS level1,
  2  	    ... AS level2,
  3  	    ... AS level3
  4  FROM   dba_dependx d1, dba_dependx d2
  5  WHERE  ... -- join condition
  6  AND    ... -- join condition
  7  AND    ... NOT IN
  8  	    (SELECT ...
  9  	     FROM   ...)
 10  ORDER  BY ...
 11  /

LEVEL1               LEVEL2               LEVEL3
-------------------- -------------------- --------------------
SPK_A(PACKAGE BODY)  SPK_A(PACKAGE)       T_A1(TABLE)
                                          T_A2(TABLE)
                                          STANDARD(PACKAGE)
                     SPK_B(PACKAGE)       STANDARD(PACKAGE)
                     STANDARD(PACKAGE)
SPK_B(PACKAGE BODY)  SPK_B(PACKAGE)       STANDARD(PACKAGE)
                     STANDARD(PACKAGE)
                     T_B1(TABLE)
                     T_B2(TABLE)

9 rows selected.


Re: How to get the correct tree structure? [message #289716 is a reply to message #289709] Tue, 25 December 2007 21:13 Go to previous messageGo to next message
kevin_ye
Messages: 37
Registered: December 2007
Location: Shanghai,China
Member
thank you very much for your reply.
but i don't much undertand your sql code.
so, can you substitue ... for detail sql?

Thank you again!!!
Re: How to get the correct tree structure? [message #289718 is a reply to message #289716] Tue, 25 December 2007 21:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9099
Registered: November 2002
Location: California, USA
Senior Member
Since this is the homework forum, I am not going to provide you with the complete code, as you would not learn anything that way. All you have to do is fill in the missing code where the dots (...) are. I described everything you need to complete the code. You need to try to do it yourself, then if you get stuck, post a copy and paste of a run of what you tried, complete with error message and/or results and what you don't understand. The basic concept is a "self join" instead of "start with" and "connect by prior". You can search for detailed explanations and examples on all of it in the online documentation.


Re: How to get the correct tree structure? [message #289724 is a reply to message #289718] Tue, 25 December 2007 22:21 Go to previous messageGo to next message
kevin_ye
Messages: 37
Registered: December 2007
Location: Shanghai,China
Member
Thank you so much,
You are a accommodating people.
I will try in free time.
Re: How to get the correct tree structure? [message #289834 is a reply to message #289724] Wed, 26 December 2007 04:28 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure I understood you correctly, but I interpreted it this way: "Shame on you, Barbara, for not spending your free time to solve my homework; I'll, though, try to do something about it if/when I have some spare time."

If that's what you really said, well, shame on you! If not, I sincerely apologize, but would like to explain what you really meant.
Re: How to get the correct tree structure? [message #289898 is a reply to message #289834] Wed, 26 December 2007 08:40 Go to previous messageGo to next message
kevin_ye
Messages: 37
Registered: December 2007
Location: Shanghai,China
Member
I think there's been some misunderstanding.
If Barbara Boehmer think i had hurt him, i will apologize, and take back what i said.

But i must clear up the misunderstanding.
I am not doing my homework, i met a problem in my program, which is like what i have asked on the forum. it is so difficult to resolve. and i was so confused by this problem for serveral days. but luckly i found another idea to resolve it today. and i was so busy today, so i said i will try in free time.Only this reason, i have no other meanning.

In fact, i have tried serveral hours ago.
I respect Barbara Boehmer, and appreciate his help so much.

I know you are warmhearted, thank you reminded me of that.

[Updated on: Wed, 26 December 2007 09:13]

Report message to a moderator

Re: How to get the correct tree structure? [message #289900 is a reply to message #289898] Wed, 26 December 2007 08:54 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you for the explanation! I'm glad you found the solution, and - consequentially - sincerely apologize.

P.S. Just to mention: Barbara is female.
Re: How to get the correct tree structure? [message #289902 is a reply to message #289900] Wed, 26 December 2007 08:57 Go to previous messageGo to next message
kevin_ye
Messages: 37
Registered: December 2007
Location: Shanghai,China
Member
TO Littlefoot: it doen't matter, thank you reminded me of that.
To Barbara Boehmer: sorry, thank you for your help!

[Updated on: Wed, 26 December 2007 09:12]

Report message to a moderator

Re: How to get the correct tree structure? [message #289905 is a reply to message #289902] Wed, 26 December 2007 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you found a solution, what about posting it here?

Regards
Michel
Re: How to get the correct tree structure? [message #289909 is a reply to message #289905] Wed, 26 December 2007 09:11 Go to previous messageGo to next message
kevin_ye
Messages: 37
Registered: December 2007
Location: Shanghai,China
Member
i pasted the question on this forum before i found my solution today.

By the way, although i can solve the problem with my solution, i don't think my solution is good.

[Updated on: Wed, 26 December 2007 09:16]

Report message to a moderator

Re: How to get the correct tree structure? [message #289915 is a reply to message #289909] Wed, 26 December 2007 09:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post it and we will tell you what's wrong or how to improve it.

Regards
Michel
Re: How to get the correct tree structure? [message #289917 is a reply to message #289915] Wed, 26 December 2007 10:17 Go to previous messageGo to next message
kevin_ye
Messages: 37
Registered: December 2007
Location: Shanghai,China
Member
thank you for your replay.

I used a nested procedure(the point) to realize it, it can't get the tree structure, but it can give me the correct result set.
and my requirement is to get the result set.
but it's better to get the tree structure and result set meanwhile.
So, i think my solution is not good.

In fact, what i want is to get the package's structure, including its associated tables、views、function、procedure、package、package body、sequence、synonym,and so on. the package's structure looks like a tree.

But i can not get the structure to use some oracle data dictionary, such as dba_dependencies、dba_objects.......
May be these views have some defects.

If i can use oracle data ditionary to get the tree structure and result set, i think it's perfectly

Here is the nested procedure.
--------------------------------------------------------------
create or replace procedure sp_dependX(in_country_flag varchar2,
in_ownerX varchar2,
in_typeX varchar2,
in_nameX varchar2,
in_start_type_flagX varchar2,
in_start_name_flagX varchar2) as

cursor cu_src_depend(in_country_flag varchar2, in_src_owner varchar2, in_src_type varchar2, in_src_name varchar2) is
select dd.country_flag,
dd.owner,
dd.type,
dd.name,
dd.referenced_owner,
dd.referenced_type,
dd.referenced_name
from dba_depend dd
where dd.country_flag = in_country_flag
and dd.owner = in_src_owner
and dd.type = in_src_type
and dd.name = in_src_name
and dd.name <> dd.referenced_name
and referenced_owner not in ('SYS')
and referenced_name not in ('DUAL')
--and dd.referenced_name not in ('SO_INTER_BC_KRNL','SPK_BUSINESS_PARTNERS')
order by dd.country_flag,
dd.owner,
dd.type,
dd.name,
dd.referenced_owner,
dd.referenced_type,
dd.referenced_name;
rec_src_depend cu_src_depend%rowtype;

cursor cu_cnt_depend(in_country_flag varchar2, in_src_owner varchar2, in_src_type varchar2, in_src_name varchar2) is
select count(*)
from (select dd.country_flag,
dd.owner,
dd.type,
dd.name,
dd.referenced_owner,
dd.referenced_type,
dd.referenced_name
from dba_depend dd
where dd.country_flag = in_country_flag
and dd.owner = in_src_owner
and dd.type = in_src_type
and dd.name = in_src_name
and dd.name <> dd.referenced_name
and referenced_owner not in ('SYS')
and referenced_name not in ('DUAL')
--and dd.referenced_name not in ('SO_INTER_BC_KRNL','SPK_BUSINESS_PARTNERS')
order by dd.country_flag,
dd.owner,
dd.type,
dd.name,
dd.referenced_owner,
dd.referenced_type,
dd.referenced_name);

cursor cu_dst_type is
select type_name from T_typeX t order by type_id;
rec_dst_type cu_dst_type%rowtype;

lv_country_flag varchar2(50);
lv_ownerX varchar2(50);
lv_typeX varchar2(50);
lv_nameX varchar2(50);
lv_start_type_flagX varchar2(50);
lv_start_name_flagX varchar2(50);
lv_src_owner varchar2(50);
lv_src_type varchar2(50);
lv_src_name varchar2(50);
lv_dst_owner varchar2(50);
lv_dst_type varchar2(50);
lv_dst_name varchar2(50);
lv_cnt_depend number;
lv_test_type varchar2(50);

begin
lv_country_flag := upper(in_country_flag);
lv_ownerX := upper(in_ownerX);
lv_typeX := upper(in_typeX);
lv_nameX := upper(in_nameX);
lv_start_type_flagX := upper(in_start_type_flagX);
lv_start_name_flagX := upper(in_start_name_flagX);

for rec_src_depend in cu_src_depend(lv_country_flag,
lv_ownerX,
lv_typeX,
lv_nameX) loop
lv_src_owner := rec_src_depend.owner;
lv_src_type := rec_src_depend.type;
lv_src_name := rec_src_depend.name;
lv_dst_owner := rec_src_depend.referenced_owner;
lv_dst_type := rec_src_depend.referenced_type;
lv_dst_name := rec_src_depend.referenced_name;
insert into t_dependX
(country_flag,
start_name_flag,
start_type_flag,
owner,
type,
name,
referenced_owner,
referenced_type,
referenced_name)
values
(lv_country_flag,
lv_start_name_flagX,
lv_start_type_flagX,
lv_src_owner,
lv_src_type,
lv_src_name,
lv_dst_owner,
lv_dst_type,
lv_dst_name);

for rec_dst_type in cu_dst_type loop
lv_test_type := rec_dst_type.type_name; open cu_cnt_depend(lv_country_flag,
lv_dst_owner,
lv_test_type,
lv_dst_name);
fetch cu_cnt_depend
into lv_cnt_depend;
close cu_cnt_depend;
if lv_cnt_depend > 0 then
sp_dependX(lv_country_flag,
lv_dst_owner,
lv_test_type,
lv_dst_name,
lv_start_type_flagX,
lv_start_name_flagX);
end if;
end loop;
end loop;
commit;

end;
/


thank you for your attention!
Re: How to get the correct tree structure? [message #289920 is a reply to message #289917] Wed, 26 December 2007 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 25 December 2007 18:17

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

I add: indent your code.

dba_depend is not an Oracle table/view.
You can do it with the Oracle catalog.
Here are two scripts:
http://www.dba-village.com/dba/village/dvp_scripts.ScriptDetails?ScriptIdA=1237
http://www.dba-village.com/dba/village/dvp_scripts.ScriptDetails?ScriptIdA=1236
They require "select any dictionary" privilege.

Regards
Michel

[Updated on: Wed, 26 December 2007 10:30]

Report message to a moderator

Re: How to get the correct tree structure? [message #290872 is a reply to message #289920] Tue, 01 January 2008 20:58 Go to previous message
kevin_ye
Messages: 37
Registered: December 2007
Location: Shanghai,China
Member
thank you very much for your reply.

I am sorry for no reply on time.

for some reason i didn't surf the net for serveral days.
Previous Topic: Help with GROUP BY
Next Topic: Parameters Passing - Difference between Postional, Named, and Combination Methods
Goto Forum:
  


Current Time: Mon Nov 04 05:35:30 CST 2024