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


 

HTML Text

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

  
 

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