I am a little struggling at the moment with the following problem.
I want to know which package calls which packages. The table is not hierarchical. Its the table user_dependencies.
The code so far:
CREATE OR REPLACE PACKAGE object_x is
type ObjectRec is record(
dName varchar2(250),
level number
);
type ObjectTemp is table of ObjectRec;
function Referenced(dname VARCHAR2, level NUMBER, maxl NUMBER) return ObjectTemp pipelined;
end;
/
CREATE OR REPLACE PACKAGE BODY object_x is
function Referenced(dname VARCHAR2, level NUMBER, maxl NUMBER) return ObjectTemp pipelined is
rData ObjectRec;
begin
if level >= maxl then
return;
end if;
if level = 1 then
rData.dName := name;
rData.Level := maxl;
pipe row(rData);
end if;
for r in (
select referenced_name
from user_dependencies
where name = upper(dname)
and type = 'PACKAGE BODY'
and referenced_type = 'PACKAGE'
and referenced_name != UPPER(dname)
and referenced_name != name
)
loop
rData.dName := LPAD(' ', 3, ' ') || r.Referenced_name;
rData.level := level 1;
pipe row(rData);
rData.Name := r.Referenced_name;
for r2 in (select * from table(Referenced(rData.Name, level 1, maxl))) loop
rData.Name := LPAD(' ', 3, ' ') || r2.dName;
rData.Level := r2.Level;
pipe row(rData);
null;
end loop;
end loop;
RESULT:
Level Dname
---------- --------------------------------------------------------------------------------
1 PAC1
2 PAC2
2 PAC3
2 PAC4
2 PAC5
3 PAC6
3 PAC2
3 PAC7
3 PAC8
4 PAC9
4 PAC10
5 PAC6
5 PAC11
5 PAC3
5 PAC9
5 PAC12
5 PAC6
5 PAC3
5 PAC9
5 PAC4
5 PAC8
3 PAC10
4 PAC6
4 PAC11
4 PAC3
4 PAC9
4 PAC12
4 PAC4
EXPECTED RESULT:
Level Dname
---------- --------------------------------------------------------------------------------
1 PAC1
2 PAC2
2 PAC3
2 PAC4
2 PAC5
3 PAC6
3 PAC2
3 PAC7
3 PAC8
4 PAC9
4 PAC10
5 PAC6
5 PAC11
5 PAC3
5 PAC9
5 PAC12
5 PAC6
5 PAC3
5 PAC9
5 PAC4
5 PAC8
3 PAC10 LOOP!!!!
-----BREAK------
CONTINUE WITH OTHER PACKAGES………..
Thanks for any advice.
CodePudding user response：
Ultimately, yes, it is possible to do what you want.
One way to do this is to use a nested table to store the chain of dependent packages that you've built up through the current chain of recursive function calls. You can use the MEMBER OF operator with this nested table to check whether a package is already in this chain (and so you've found a loop). If not, you can call your Referenced function recursively, but adding an extra item to this table for the current package.
I made these changes to your code and ended up with the following:
CREATE OR REPLACE TYPE varchar2_table AS TABLE OF VARCHAR2(250);
/
CREATE OR REPLACE PACKAGE object_x is
type ObjectRec is record(
dName varchar2(250),
dLevel number
);
type ObjectTemp is table of ObjectRec;
function Referenced(dname VARCHAR2, dlevel NUMBER, maxl NUMBER) return ObjectTemp pipelined;
function Referenced(dname VARCHAR2, dlevel NUMBER, maxl NUMBER, pkgs_so_far IN varchar2_table) return ObjectTemp pipelined;
end;
/
CREATE OR REPLACE PACKAGE BODY object_x is
function Referenced(dname VARCHAR2, dlevel NUMBER, maxl NUMBER) return ObjectTemp pipelined is
rData ObjectRec;
begin
for r2 in (select * from table(Referenced(dname, dlevel, maxl, varchar2_table(dname))))
loop
rData.dName := r2.dName;
rData.dLevel := r2.dLevel;
pipe row(rData);
end loop;
end;
function Referenced(dname VARCHAR2, dlevel NUMBER, maxl NUMBER, pkgs_so_far IN varchar2_table) return ObjectTemp pipelined is
rData ObjectRec;
new_pkgs_so_far varchar2_table;
loop_detected boolean;
begin
if dlevel >= maxl then
return;
end if;
if dlevel = 1 then
rData.dName := dname;
rData.dLevel := 1;
pipe row(rData);
end if;
for r in (
select referenced_name
from user_dependencies
where name = upper(dname)
and type = 'PACKAGE BODY'
and referenced_type = 'PACKAGE'
and referenced_name != UPPER(dname)
and referenced_name != name
)
loop
loop_detected := r.referenced_name MEMBER OF pkgs_so_far;
rData.dName := LPAD(' ', 3, ' ') || r.Referenced_name;
if loop_detected then
rData.dName := rData.dName || ' --- LOOP';
end if;
rData.dLevel := dLevel 1;
pipe row(rData);
if not loop_detected THEN
new_pkgs_so_far := pkgs_so_far;
new_pkgs_so_far.EXTEND(1);
new_pkgs_so_far(new_pkgs_so_far.COUNT) := r.referenced_name;
for r2 in (select * from table(Referenced(r.referenced_name, dLevel 1, maxl, new_pkgs_so_far))) loop
rData.dName := LPAD(' ', 3, ' ') || r2.dName;
rData.dLevel := r2.dLevel;
pipe row(rData);
end loop;
end if;
end loop;
end;
end;
/
I've modified your pipelined function to add the pkgs_so_far parameter, which contains the table of packages currently encountered. I've added another pipelined function with the same signature as your original function, and this just calls the other one with the table of packages containing only the given package.
In the modified function, we now use the MEMBER OF operator to check whether we've already come across the current package. If we have, we can add an extra --- LOOP marker to indicate this. If we haven't come across this package before, we add the package name to the table and continue to recursively examine its references. Note that assigning the table in the line new_pkgs_so_far := pkgs_so_far; appears to set new_pkgs_so_far to a copy of the table in pkgs_so_far, so we can mutate new_pkgs_so_far without this affecting pkgs_so_far.
However, there were also a number of other issues I cleared up with your code:
- The package body appears to be missing the final two
end;lines, one to end the procedure and one to end the package body itself. - Your code contains some references to the
namefield of your record, but it is nameddName. I've tidied these all up to usedName. - Oracle didn't seem to like the name
levelbeing used, so I changed it todLevel. (LEVELis an Oracle keyword, it's used in hierarchical SQL.) - I'm not sure it's correct to pipe out a row with
dLevelset tomaxlin the case where the variabledlevelis 1, so I've changed this to1instead. - When calling your
Referencesfunction recursively, I've changed the first parameter to ber.referenced_name, as then we don't need to assign torData.dName. - I've removed an unnecessary
null;statement within the loop that iterates through a recursive call to your procedure.
I did also note that you've written LPAD(' ', 3, ' ') twice, but I'm not sure why: this is a long-winded way of writing ' ', where there are three spaces in the string.
Some of these errors may have arisen because you anonymised your code before posting it here but you didn't check whether the code you posted compiles before posting. In future, please ensure your code compiles, as otherwise anyone who wishes to help you will have to first fix your compilation errors.
I also don't have all of your packages (and I suspect in reality they aren't named PAC1, PAC2 and so on), so I created three packages PAC1 to PAC3. PAC2 and PAC3 both reference each other and PAC1, and the output I got was as follows:
SQL> COLUMN dname FORMAT a30
SQL> select * from table(object_x.referenced('PAC2', 1, 10));
DNAME DLEVEL
------------------------------ ----------
PAC2 1
PAC1 2
PAC3 2
PAC1 3
PAC2 --- LOOP 3