RSS

7.8 SQLCODE and SQLERRM

24 Apr

After completing this lab, you will be able to

  • Use SQLCODE and SQLERRM

Oracle gives you two built-in functions, SQLCODE and SQLERRM, used with the OTHERS exception handler. The SQLCODE function returns the Oracle error number, and the SQLERRM function returns the error message. The maximum length of a message returned by the SQLERRM function is 512 bytes.

DECLARE

    v_zip VARCHAR2(5) := ‘&sv_zip’;

    v_city VARCHAR2(15);

    v_state CHAR(2);

    v_err_code NUMBER;

    v_err_msg VARCHAR2(200);

BEGIN

    SELECT city, state

        INTO v_city, v_state

        FROM zipcode

        WHERE zip = v_zip;

    DBMS_OUTPUT.PUT_LINE (v_city||‘, ‘||v_state);

EXCEPTION

    WHEN OTHERS THEN

        v_err_code := SQLCODE;

        v_err_msg := SUBSTR(SQLERRM, 1, 200);

        DBMS_OUTPUT.PUT_LINE (‘Error code: ‘||v_err_code);

        DBMS_OUTPUT.PUT_LINE (‘Error message: ‘||v_err_msg);

END;

 

Notice that this output is more informative than the output produced by the previous version of the example, because it displays the error message. As soon as you know which runtime error has occurred in your program, you can take steps to prevent this error’s recurrence. Generally, the SQLCODE function returns a negative number for an error number. However, there are a few exceptions:

  • When SQLCODE is referenced outside the exception section, it returns 0 for the error code. The value of 0 means successful completion.
  • When SQLCODE is used with the user-defined exception, it returns +1 for the error code. SQLCODE returns a value of 100 when the NO_DATA_FOUND exception is raised.

The SQLERRM function accepts an error number as a parameter, and it returns an error message corresponding to the error number. Usually, it works with the value returned by SQLCODE. However, you can provide the error number yourself if such a need arises. Consider the following example:

 

BEGIN

    DBMS_OUTPUT.PUT_LINE (‘Error code: ‘||SQLCODE);

    DBMS_OUTPUT.PUT_LINE (‘Error message1: ‘||SQLERRM(SQLCODE));

    DBMS_OUTPUT.PUT_LINE (‘Error message2: ‘||SQLERRM(100));

    DBMS_OUTPUT.PUT_LINE (‘Error message3: ‘||SQLERRM(200));

    DBMS_OUTPUT.PUT_LINE (‘Error message4: ‘||SQLERRM(-20000));

END;

 

In this example, SQLCODE and SQLERRM are used in the executable section of the PL/SQL block. The SQLERRM function accepts the value of the SQLCODE in the second DBMS_OUTPUT.PUT_LINE statement. In the following DBMS_OUPUT.PUT_LINE statements, SQLERRM accepts the values of 100, 200, and –20,000 respectively. When executed, this example produces the following output:

Error code: 0

Error message1: ORA-0000: normal, successful completion

Error message2: ORA-01403: no data found

Error message3: -200: non-ORACLE exception

Error message4: ORA-20000:

PL/SQL procedure successfully completed.

The first DBMS_OUTPUT.PUT_LINE statement displays the value of the SQLCODE function. Because no exception is raised, it returns 0. Next, SQLERRM accepts as a parameter the value returned by the SQLCODE function. This function returns the message ORA-0000: normal, … . Next, SQLERRM accepts 100 as its parameter and returns ORA-01403: no data found. Notice that when SQLERRM accepts 200 as its parameter, it cannot find an Oracle exception that corresponds to the error number 200. Finally, when SQLERRM accepts 20,000 as its parameter, no error message is returned. Remember that 20,000 is an error number that can be associated with a named user-defined exception.

Advertisements
 
Leave a comment

Posted by on 04/24/2010 in ORACLE

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: