User-Defined Exception:
Unless you used the pragma EXCEPTION_INIT to relate the exception name with an Oracle error number, in that case SQLCODE returns which error number and SQLERRM returns the corresponding error message. A maximum length of an Oracle error message is 512 characters involving the error code, nested messages and message inserts like as tables and column names.
If no exception has been raised then SQLCODE returns zero and SQLERRM returns the message.
ORA-0000: normal, successful completion
An error number can be passed to SQLERRM, in that case SQLERRM returns the message related with that error number. Make sure you pass It must be ensured in which negative error numbers are not passed to SQLERRM. In the subsequent example, you pass positive numbers and so get unwanted results:
DECLARE
...
err_msg VARCHAR2(100);
BEGIN
...
/* Get all Oracle error messages. */
FOR err_num IN 1..9999 LOOP
err_msg := SQLERRM(err_num); #wrong statement; should be -err_num#
INSERT INTO errors VALUES (err_msg);
END LOOP;
END;
SQLERRM always returns the message whenever we Passing a positive number
Example
When +100 is passed, in that case SQLERRM returns the message:
ORA-01403: no data found
Passing a zero to SQLERRM always returns the further message:
ORA-0000: normal, successful completion
SQLCODE or SQLERRM cannot be used straightly in a SQL statement. In the other words, their values must assigned to local variables, that can then be used in the SQL statement, as the subsequent example displays:
Example
DECLARE
err_num NUMBER;
err_msg VARCHAR2(100);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
INSERT INTO errors VALUES (err_num, err_msg);
The string function SUBSTR ensures in which a VALUE_ERROR exception (for truncation) is not raised when you assign the value of SQLERRM to err_msg. SQLERRM and SQLCODE are especially useful in the OTHERS exception handler since they tell that internal exception was increased.