Use of built-in functions Sqlcode and SQLERRM

We often have error handling in our proc program, and in error handling we often have to output error messages to help us analyze and resolve the cause of the error to correct the data. Sqlcode and SQLERRM will be used at this time.

SQLCode: The return code of the database operation, where 0 indicates success, 1 indicates failure, 100 means no data is retrieved, and +1 represents a user-defined exception.

SQLERRM function: Returns the error message for the specified error code.

An Oracle error message can contain up to 512 bytes of error code. If no exception is triggered, Sqlcode returns 0,SQLERRM return "Ora-0000:normal, successful completion".

How is SQLERRM used?
Sqlcode and SQLERRM cannot be used directly in SQL statements, they must be assigned to a variable before they can be used in SQL statements , as follows:

DECLARE

......
V_trans_error number;
V_trans_error_msg VARCHAR2 (512);

......

BEGIN

......

EXCEPTION

......

When OTHERS Then
V_trans_error: = SQLCODE;
V_trans_error_msg: = SUBSTR (sqlerrm,1,512);

Dbms_output. Put_Line (V_trans_error | | '---' | | V_TRANS_ERROR_MSG);

......

END;

Use of built-in functions Sqlcode and SQLERRM