SQLERRM Function in Oracle SQL – PLSQL

The SQLERRM Function in Oracle SQL / PLSQL is used to get the error message related with the latest generated exception.
The SQLERRM function should be used in the exception handling segment of the code.

A typical exception handling portion of code may look like;

EXCEPTION
WHEN exception_name1 THEN
[Statements / business logic]

WHEN exception_name2 THEN
[Statements / business logic]

WHEN exception_name3 THEN
[Statements / business logic]
.
.
WHEN exception_nameN THEN
[Statements / business logic]
END [procedure_name];

We can use the SQLERRM function to raise an error / exception as shown below.
[sourcecode language=”sql”]
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-1001,’An error occurred: ‘ || SQLCODE || ‘ ERROR NUMBER:-‘ ||SQLERRM);
END;
[/sourcecode]

We can also insert the error / exception in a database table as shown below:
[sourcecode language=”sql”]
EXCEPTION
WHEN OTHERS THEN
error_code := SQLCODE;
error_message := substr(SQLERRM,1,300);
INSERT INTO error_table(error_num, error_msg)
VALUES(error_number, error_message);
END; [/sourcecode]


Leave a Reply

Your email address will not be published. Required fields are marked *