O'Reilly logo

Oracle® PL/SQL® Interactive Workbook, Second Edition by Elena Silvestrova, Benjamin Rosenzweig

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required