 |
|
Oracle Dependencies
Oracle Tips by
Burleson
|
A dependency is created when one object is referenced by
another. The structure of the ALL_DEPENDENCIES view is shown in
Listing 4.5.
Listing 4.5 The structure of the ALL_DEPENDENCIES
view.
name varchar2 (30)
type varchar2 (12)
owner varchar2 (30)
referenced_name varchar2 (30)
referenced_type varchar2 (12)
referenced_owner varchar2 (30)
The ALL_DEPENDENCIES view in Oracle’s data dictionary has a
list of all the dependencies between objects stored in the database.
Determining
the Effects of a Code Change
You can use the following query to determine which objects could be
affected by modifications to a stored procedure:
SELECT type || ' ' || owner || '.' || name
FROM ALL_DEPENDENCIES
WHERE referenced_owner = upper ('&1')
AND referenced_name = upper ('&2')
ORDER BY type;
In this example, &1 is the schema of
the object that you need to modify, and &2 is the name of the
object. The query will produce output like this:
PROCEDURE ACCOUNTING.CALCULATE_AGENT_COMMISSION
PROCEDURE ACCOUNTING.CALCULATE_AGENT_BONUS
PROCEDURE SALES.FIND_AVAILABLE_LOTS
PROCEDURE SALES.CALCULATE_LOT_SIZE
PACKAGE BODY ACCOUNTING.PERFORMANCE_REVIEWS
Parameters
A parameter is a value that is passed to and/or returned
from a stored procedure or function. Listing 4.6 illustrates how
parameters are defined for a stored procedure.
Listing 4.6 Defining parameters for a stored procedure.
PROCEDURE Calculate_Lot_Size (nWidth IN number,
nLength IN number,
nLotSize OUT number);
Datatypes
Like variables and constants, parameters for procedures must have a
datatype specified. The datatype for a parameter can be either a
scalar or user-defined datatype. Parameters of a user-defined datatype
must make a reference to a type definition, typically inside a package
spec.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
