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.

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-1001,'An error occurred: ' || SQLCODE || ' ERROR NUMBER:-' ||SQLERRM);
END;

We can also insert the error / exception in a database table as shown below:

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; 

Tagged , , , , , , , , , , . Bookmark the permalink.