Oracle® PL/SQL® Interactive Workbook, Second Edition

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) := ...