December 2002
Intermediate to advanced
928 pages
85h 29m
English
Built-in function that returns the SQL error message for the current exception. You can include this function inside the exception section’s WHEN OTHERS clause in order to handle specific errors by number. The EXCEPTION_INIT pragma allows you to handle errors by name.
The following examples illustrate the use of SQLERRM and SQLCODE:
CREATE TABLE err_test
(widget_name VARCHAR2(100)
,widget_count NUMBER
,CONSTRAINT no_small_numbers CHECK
(widget_count > 1000));
BEGIN
INSERT INTO err_test (widget_name, widget_count)
VALUES ('Athena',2);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -2290
AND SQLERRM LIKE '%NO_SMALL_NUMBERS%'
THEN
DBMS_OUTPUT.PUT_LINE('widget_count is too small');
ELSE
DBMS_OUTPUT.PUT_LINE('Exception not handled,'
||'SQLcode='||SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END IF;
END;When PL/SQL executes this code, it produces the following output:
widget_count is too small