RSS

8.6.1 Use SQLCODE and SQLERRM

07 May

SET SERVEROUTPUT ON

BEGIN

INSERT INTO ZIPCODE (zip, city, state, created_by, created_date, modified_by, modified_date)

VALUES (‘10027’, ‘NEW YORK’, ‘NY’, USER, SYSDATE, USER, SYSDATE);

COMMIT;

END;

 

BEGIN

 

*

ERROR at line 1:

ORA00001: unique constraint (STUDENT.ZIP_PK) violated

ORA06512: at line 2

 

SET SERVEROUTPUT ON

BEGIN

INSERT INTO ZIPCODE (zip, city, state, created_by, created_date, modified_by, modified_date)

VALUES (‘10027’, ‘NEW YORK’, ‘NY’, USER, SYSDATE, USER, SYSDATE);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

DECLARE

v_err_code NUMBER := SQLCODE;

v_err_msg VARCHAR2(100) := SUBSTR(SQLERRM, 1, 100);

BEGIN

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

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

END;

END;

 

Error code: 1

Error message: ORA00001: unique constraint (STUDENT.ZIP_PK) violated

PL/SQL procedure successfully completed.

Advertisements
 
Leave a comment

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