 |
|
PL/SQL Useful Functions
Oracle Tips by
Burleson
|
PL/SQL provides two useful functions that allow you to identify
errors: SQLCODE() and SQLERRM(). These functions are
specific to PL/SQL and can’t be used in your SQL statements;
attempting to do so will result in an error. However, the output of
these functions can be assigned to variables in your PL/SQL blocks.
Let’s take a look at each function.
Using the SQLCODE() Function
The SQLCODE() function is used to return the number of the
most recent Oracle error message during the execution of a PL/SQL
block. For instance, a reference to a nonexistent table or view causes
Oracle error ORA-00942 to occur. For this error, the SQLCODE()
function would return:
-00942
Listing 8.9 illustrates how the SQLCODE() function can be
used in your exception handlers.
Listing 8.9 Calling the SQLCODE() function in an
exception handler.
EXCEPTION
WHEN OTHERS THEN
IF (SQLCODE = -942) THEN
RAISE xMISSING_TABLE;
ELSE
ROLLBACK;
INSERT
INTO SYSTEM_ERRORS
(error_time,
error_parameters,
display,
error_text,
error_number,
error_object)
VALUES (SYSDATE,
Active_Student_rec.ssn,
'N',
SQLERRM || ' at tracepoint ' || to_char (iTracePoint),
SQLCODE,
'Calculate_Student_Grades');
COMMIT;
END IF;
END;
The error numbers returned by this function are always negative.
Using the SQLERRM() Function
The SQLERRM() function returns the complete text of the last
Oracle error message to occur during the execution of a block of
PL/SQL code. Calling this function is quite simple:
vErrorText := SQLERRM;
Calling this function when no errors have occurred returns:
ORA-0000: normal, successful completion
Summary
Debugging is a skill that relies heavily on a developer’s
experience and familiarity with the code. Debugging is the largest
part of a developer’s job and probably the most tedious aspect. This
chapter covers several techniques that can ease the burden of
debugging your code if you plan ahead when you’re writing the code.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
