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.
1 | EXCEPTION |
2 | WHEN OTHERS THEN |
3 | raise_application_error(-1001,'An error occurred: ' || SQLCODE || ' ERROR NUMBER:-' ||SQLERRM); |
4 | END; |
We can also insert the error / exception in a database table as shown below:
1 | EXCEPTION |
2 | WHEN OTHERS THEN |
3 | error_code := SQLCODE; |
4 | error_message := substr(SQLERRM,1,300); |
5 | INSERT INTO error_table(error_num, error_msg) |
6 | VALUES(error_number, error_message); |
7 | END; |