How Oracle Gets the specified package

Oracle gets all the objects that a package relies on including its child objects

A temporary table is used to record the nodes that have been traversed

Also uses layers to record the packets that have been traversed

Efficiency is general, can change big

Declare

--Gets the package that the appropriate package needs to apply

Cursor P_cur (p_name varchar2) is

Select Dd.name, Dd.type, Dd.referenced_name, Dd.referenced_type

From Dba_dependencies DD

where 1 = 1

and Dd.referenced_type in

(' PACKAGE ', ' synonym ', ' TABLE ', ' SEQUENCE ')

and Dd.type in (' PACKAGE ', ' PACKAGE ')

and dd.name = P_name;

Cursor P_temp (l_level number) is

Select Attribute1, Attribute2

From Cux_common_imports_temp

where attribute2 = L_level;

P_root_name VARCHAR2 (30);

P_level number: = 0;

P_count number: = 0;

Begin

--Put the root node into the table

P_root_name: = ' Cux_sbu_common ';

INSERT INTO Cux_common_imports_temp

(Attribute1, Attribute2)

Values

(P_root_name, P_level);

P_count: = 1;

--loop this layer of nodes and get their child nodes

While P_count!= 0 loop

For P_loop in P_temp (P_level) loop

--The next level

P_level: = P_level + 1;

P_root_name: = p_loop.attribute1;

For P_rec in P_cur (P_root_name) loop

If P_rec.referenced_type = ' PACKAGE ' Then

--If you don't have this program in the table, add it to the temp table

Select COUNT (*)

Into P_count

From Cux_common_imports_temp

where attribute1 = P_rec.referenced_name;

If P_count = 0 Then

INSERT INTO Cux_common_imports_temp

(Attribute1, Attribute2)

Values

(P_rec.referenced_name, P_level);

End If;

--Output the appropriate child node information

Dbms_output.put_line (P_level | | '-name: ' | |

P_rec.referenced_name | | '-type: ' | |

P_rec.referenced_type);

End If;

End Loop;

End Loop;

--Gets whether the layer is empty

Select COUNT (*)

Into P_count

From Cux_common_imports_temp

where attribute2 = P_level;

End Loop;

End