RSS

8.6 SQLCODE and SQLERRM

06 May

DECLARE

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

v_city  VARCHAR2(15);

v_state CHAR(2);

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

DBMS_OUTPUT.PUT_LINE (‘An error has occurred’);

END;

Enter value for sv_zip: 07458

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

new   2:    v_zip VARCHAR2(5) := ‘07458’;

An error has occurred

PL/SQL procedure successfully completed.

 

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;

When executed, this example produces the following output:

Enter value for sv_zip: 07458

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

new   2:    v_zip      VARCHAR2(5) := ‘07458’;

Error code: -6502

Error message: ORA-06502: PL/SQL: numeric or value error:

character string buffer too small

PL/SQL procedure successfully completed.

 

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;

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.

Advertisements
 
Leave a comment

Posted by on 05/06/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: