Infolinks

Friday 10 August 2012

Oracle/PLSQL: SQLERRM Function


Oracle/PLSQL: SQLERRM Function


What does the SQLERRM Function do?

The SQLERRM function returns the error message 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];

You could use the SQLERRM 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;

No comments:

Post a Comment