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.

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.