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  |
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 #289709 is a reply to message #289699] |
Tue, 25 December 2007 14:34  |
 |
Barbara Boehmer
Messages: 9100
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 #289834 is a reply to message #289724] |
Wed, 26 December 2007 04:28 |
 |
Littlefoot
Messages: 21823
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 |
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 #289917 is a reply to message #289915] |
Wed, 26 December 2007 10:17 |
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!
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 03:12:17 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
