Oracle/PLSQL Tutorial: SQLCODE Function

The SQLCODE function returns the error number associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code:

EXCEPTION
    WHEN exception_name1 THEN
        [statements]
    WHEN exception_name2 THEN
        [statements]
    WHEN exception_name_n THEN
        [statements]
    WHEN OTHERS THEN
        [statements]
END [procedure_name];

In an exception handler, the SQLCODE function returns the numeric code of the exception being handled. (Outside an exception handler, SQLCODE returns 0.)
For an exception that the database raises, the numeric code is the number of the associated Oracle Database error. This number is negative except for the error "no data found", whose numeric code is +100.
For a user-defined exception, the numeric code is either +1 (the default) or the Oracle Database error number associated with the exception by the EXCEPTION_INIT pragma.
A SQL statement cannot invoke SQLCODE. To use the value of SQLCODE in a SQL statement, assign it to a local variable first.
If a function invokes SQLCODE, and you use the RESTRICT_REFERENCES pragma to assert its purity, you cannot specify the constraints WNPS and RNPS.

You could use the SQLCODE function to raise an error as follows:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);
      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;