Lab 11.3 SQLCODE and SQLERRM


Lab Objective

After this Lab, you will be able to:

Use SQLCODE and SQLERRM


In Chapter 7, you learned about the Oracle exception OTHERS. You will recall that all Oracle errors can be trapped with the help of the OTHERS exception handler. Consider the following example.

FOR EXAMPLE

 
 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; 

When "07458" is entered for the value of zipcode, 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';   An error has occurred   PL/SQL procedure successfully completed.  

This output informs you that an error has occurred at runtime. However, you do not know what the error is and what caused it. Maybe there is no record in the ZIPCODE table corresponding to the value provided at runtime, or maybe there is a datatype mismatch caused by the SELECT INTO statement. As you can see, even though this is a simple example, there are a number of possible runtime errors that can occur.

Of course, you cannot always know all of the possible runtime errors that may occur when a program is running. Therefore, it is a good practice to have the OTHERS exception handler in your script. To improve the error-handling interface of your program, Oracle provides you with 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.

Consider what happens if you modify the preceding by adding the SQLCODE and SQLERRM functions as follows (all changes are shown in bold letters ):

FOR EXAMPLE

 
 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 output shown:

 
  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   PL/SQL procedure successfully completed.  

In this example, you declare two variables : v_err_code and v_err_msg . Then, in the exception-handling section of the block, you assign SQLCODE to the variable v_err_code , and SQLERRM to the variable v_err_msg . Next, you use the DBMS_OUTPUT.PUT_LINE statements to display the error number and the error message on the screen.

Notice that this output is more informative than the output produced by the previous version of the example because it displays the error message. Once 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:

FOR 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, the SQLERRM accepts the values of 100, 200, and -20,000 respectively. When executed, this example produces the output shown:

 
  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. Since there is no exception raised, it returns 0. Next, the value returned by the SQLCODE function is accepted as a parameter by SQLERRM. This function returns the message "ORA-0000: normal,...." Next, SQLERRM accepts 100 as its parameter and returns "ORA-01402: no data...." Notice that when the SQLERRM accepts 200 as its parameter, it is not able to find an Oracle exception that corresponds to the error number 200. Finally, when the 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.



Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net