 |
|
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.
DECLARE
emp_rec emp%ROWTYPE;
BEGIN
BEGIN
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) );
EXCEPTION
WHEN no_data_found
THEN
DBMS_OUTPUT.PUT_LINE( 'No Data Found for 9999 in inner block');
RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM);
END;
DBMS_OUTPUT.PUT_LINE('After the select.');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE( 'Handling the no_data_found error in outer
block');
RAISE_APPLICATION_ERROR( -12345, 'Rolling the error up as a UDE.');
END;
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.
edb=#
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.
Parameters
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. |
