Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




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
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:



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);


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.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter