Use of built-in functions Sqlcode and SQLERRM

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.