PL/SQL Useful Functions
Oracle Tips by
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:
Listing 8.9 illustrates how the SQLCODE() function can be
used in your exception handlers.
Listing 8.9 Calling the SQLCODE() function in an
WHEN OTHERS THEN
IF (SQLCODE = -942) THEN
SQLERRM || ' at tracepoint ' || to_char (iTracePoint),
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
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.