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




EnterpriseDB: Exception Propagation
Oracle Tips by Burleson

Exceptions are propagated from one program to another until the exception is handled.  You should always handle an exception at its highest level of importance.  That means that you should not check for every possible exception in every single stored procedure.  If an exception is important to the highest level calling procedure, let the exception propagate to that procedure. 

It is bad practice to handle an exception and pass back a code unless it is the user level or highest-level procedure in a process.  If you must handle an exception in one procedure, and that same exception is important in a higher, calling procedure, you can re-raise the exception or use the RAISE_APPLICATION_ERROR procedure to deal with it. 

  emp_rec emp%ROWTYPE; 

    SELECT *
      INTO emp_rec
      FROM emp
      WHERE empno = 9999; 

    DBMS_OUTPUT.PUT_LINE( 'Emp Name: ' || emp_rec.ename ||
          ', Dept No: ' ||  to_char(emp_rec.deptno) );
    WHEN no_data_found
      DBMS_OUTPUT.PUT_LINE( 'No Data Found for 9999 in inner block');

  DBMS_OUTPUT.PUT_LINE('After the select.');

    DBMS_OUTPUT.PUT_LINE( 'Handling the no_data_found error in outer block');
    RAISE_APPLICATION_ERROR( -12345, 'Rolling the error up as a UDE.');

INFO:  No Data Found for 9999 in inner block
INFO:  Handling the no_data_found error in outer block
ERROR:  EDB--12345: Rolling the error up as a UDE.


SPL Stored Procedures

As we have seen, SPL is a block-oriented language.  We have covered anonymous (un-named) blocks in great detail and have seen plenty of examples of those.  An anonymous block is reusable from the perspective that you can run it repeatedly but it is not really reusable from the perspective of sharing its logic. 

Procedures, functions and packages allow you to share logic from one module with another, possibly unrelated, module.  This section will show you how to declare and call procedures, functions and packaged code.  It will also cover a special case of stored procedure called a trigger and how to use XML in your SPL code.


A parameter allows you to send information from one sub-program to another.  As we saw above, a cursor can take parameters that it will then apply in the select statement to change the values returned in a result set.  In the same way, you can pass parameters to stored procedures and functions.

There are three types of procedural parameters:  IN, OUT and IN OUT. 

An IN parameter is a read-only, "pass by reference" parameter.  It is essentially a pointer to a location in memory.  The called program cannot change the value of an IN parameter.

An OUT parameter is a "pass by value" parameter.  The calling program does not initialize the variable or set its value, the called program does this. 

An IN OUT parameter is also a "pass by value" parameter.  The calling program initializes the variable and sets its value (even if it just sets it to null) and passes it to the called program.  The called program may or may not alter the value of the parameter.

An unhandled exception within the called program resets the value of OUT and IN OUT parameters to the value they contained before the sub-program that caused the exception.    This is normally not an issue but is something to keep in mind.

Parameters also contain the data type of the parameter value.  A parameter does not include maximum size, scale or precision of the value; it contains the data type only.

A parameter may also, optionally, set a default value for an IN parameter.    Parameters that include a default are not required to be passed in by the calling program.

This is the syntax of a parameter:

<parameter name> <IN|OUT|IN OUT> <data type> [DEFAULT|:= <default value>]

I will show many examples of parameter usage below.


This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.


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