How Does Oracle obtain a specified package?

Oracle obtains all objects dependent on a package, including its sub-objects.

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

At the same time, a number of layers are used to record the packages that have been traversed.

High efficiency and high scalability

Declare

-- Obtain the packages to be applied to a specific package

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 body ')

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

-- Add the root node to 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 nodes at this layer and obtain their subnodes

While p_count! = 0 loop

For p_loop in p_temp (p_level) loop

-- Next layer

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

-- Add the program to the temporary table if the program does not exist in the 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 corresponding subnode Information

Dbms_output.put_line (p_level | '-Name:' |

P_rec.referenced_name | '-Type:' |

P_rec.referenced_type );

End if;

End loop;

End loop;

-- Obtain whether the layer is empty

Select count (*)

Into p_count

From cux_common_imports_temp

Where attribute2 = p_level;

End loop;

End;