SQLCODE Function in Oracle SQL – PLSQL

The SQLCODE Function in Oracle SQL / PLSQL is used to get the error number related with the latest generated exception.
The SQLCODE 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 SQLCODE 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 *