1 / 14

Exceptions in PL/SQL

Exceptions in PL/SQL. Please use speaker notes for additional information!. Exceptions. SQL> edit preexcep1. SET SERVEROUTPUT ON DECLARE v_idno VARCHAR2(5); v_yrgoal NUMBER(7,2); BEGIN SELECT idno, yrgoal INTO v_idno, v_yrgoal FROM donornew WHERE idno > 77777;

jemma
Download Presentation

Exceptions in PL/SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Exceptions in PL/SQL Please use speaker notes for additional information!

  2. Exceptions SQL> edit preexcep1 SET SERVEROUTPUT ON DECLARE v_idno VARCHAR2(5); v_yrgoal NUMBER(7,2); BEGIN SELECT idno, yrgoal INTO v_idno, v_yrgoal FROM donornew WHERE idno > 77777; IF v_yrgoal > 250 THEN v_yrgoal := v_yrgoal * 1.1; END IF; UPDATE donornew SET yrgoal = v_yrgoal WHERE idno = v_idno; EXCEPTION WHEN TOO_MANY_ROWS THEN dbms_output.put_line('Multiple rows meet criteria'); WHEN NO_DATA_FOUND THEN dbms_output.put_line('Not a valid id #'); WHEN OTHERS THEN dbms_output.put_line('Undefined error occurred); END; / SET SERVEROUTPUT OFF There are no records with an id > 77777 so the no rows found error is returned and processed.

  3. output EXCEPTION WHEN TOO_MANY_ROWS THEN dbms_output.put_line('Multiple rows meet criteria'); WHEN NO_DATA_FOUND THEN dbms_output.put_line('Not a valid id #'); WHEN OTHERS THEN dbms_output.put_line('Undefined error occurred); END; SQL> @ preexcep1 Not a valid id # PL/SQL procedure successfully completed.

  4. Without exception SET SERVEROUTPUT ON DECLARE v_idno VARCHAR2(5); v_yrgoal NUMBER(7,2); BEGIN SELECT idno, yrgoal INTO v_idno, v_yrgoal FROM donornew WHERE idno > 77777; IF v_yrgoal > 250 THEN v_yrgoal := v_yrgoal * 1.1; END IF; UPDATE donornew SET yrgoal = v_yrgoal WHERE idno = v_idno; /* The exception routine has been turned into a comment. EXCEPTION WHEN TOO_MANY_ROWS THEN dbms_output.put_line('Multiple rows meet criteria'); WHEN NO_DATA_FOUND THEN dbms_output.put_line('Not a valid id #'); WHEN OTHERS THEN dbms_output.put_line('Undefined error occured'); */ END; / SET SERVEROUTPUT OFF SQL> edit preexcep1x SQL> @ preexcep1x DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 5

  5. Multiple records error SET SERVEROUTPUT ON DECLARE v_idno VARCHAR2(5); v_yrgoal NUMBER(7,2); BEGIN SELECT idno, yrgoal INTO v_idno, v_yrgoal FROM donornew WHERE idno < 77777; IF v_yrgoal > 250 THEN v_yrgoal := v_yrgoal * 1.1; END IF; UPDATE donornew SET yrgoal = v_yrgoal WHERE idno = v_idno; EXCEPTION WHEN TOO_MANY_ROWS THEN dbms_output.put_line('Multiple rows meet criteria'); WHEN NO_DATA_FOUND THEN dbms_output.put_line('Not a valid id #'); WHEN OTHERS THEN dbms_output.put_line('Undefined error occured'); END; / SET SERVEROUTPUT OFF SQL> edit preexcep1a SQL> @ preexcep1a Multiple rows meet criteria PL/SQL procedure successfully completed.

  6. SQL> SELECT * FROM orderz; ORDNO CUSTI ORDATE ------ ----- --------- 000001 11111 10-JUN-99 000002 12121 10-JUN-99 000003 12345 10-JUN-99 000004 11111 08-JUL-99 SQL> SELECT * FROM ordline; ORDNO ITEM NUMORD ------ ---- --------- 000001 1212 1 000001 2121 1 000001 2345 1 000002 1111 3 000002 3333 1 000003 3333 2 000003 3456 1 000004 1212 3 000004 1234 2 Oracle error - not predefined SQL> edit nonpreex1x SET SERVEROUTPUT ON DECLARE v_ordno ordline.ordno%TYPE :='123456' ; v_itemno ordline.itemno%TYPE :='1212'; v_numord ordline.numord%TYPE :=100; BEGIN INSERT INTO ordline VALUES(v_ordno, v_itemno, v_numord); END; / SET SERVEROUTPUT OFF SQL> @ nonpreex1x DECLARE * ERROR at line 1: ORA-02291: integrity constraint (SCOTT.ORDNO_FK) violated - parent key not found ORA-06512: at line 6

  7. Exception for non predefined error SQL> edit nonpreex1 SET SERVEROUTPUT ON DECLARE e_invalid_ordno EXCEPTION; PRAGMA EXCEPTION_INIT(e_invalid_ordno, -2291); v_ordno ordline.ordno%TYPE :='123456' ; v_itemno ordline.itemno%TYPE :='1212'; v_numord ordline.numord%TYPE :=100; BEGIN INSERT INTO ordline VALUES(v_ordno, v_itemno, v_numord); EXCEPTION WHEN e_invalid_ordno THEN dbms_output.put_line('Foreign key problem - no ordno on ORDERZ'); WHEN OTHERS THEN dbms_output.put_line('Undefined error occured'); END; / SET SERVEROUTPUT OFF SQL> @ nonpreex1 Foreign key problem - no ordno on ORDERZ PL/SQL procedure successfully completed.

  8. User defined SQL> edit userexcep1 SET SERVEROUTPUT ON DECLARE e_numord_over200 EXCEPTION; v_ordno ordline.ordno%TYPE :='000003' ; v_itemno ordline.itemno%TYPE :='1212'; v_numordordline.numord%TYPE:=300; BEGIN IF v_numord > 200 THEN RAISE e_numord_over200; END IF; INSERT INTO ordline VALUES(v_ordno, v_itemno, v_numord); EXCEPTION WHEN e_numord_over200 THEN dbms_output.put_line('NUMORD EXCEEDS 200 - USER RANGE ERROR'); WHEN OTHERS THEN dbms_output.put_line('Undefined error occured'); END; / SET SERVEROUTPUT OFF SQL> @ userexcep1 NUMORD EXCEEDS 200 - USER RANGE ERROR PL/SQL procedure successfully completed.

  9. constraint SQL> CREATE TABLE test_ordline 2 (ordno VARCHAR2(6), 3 itemno VARCHAR2(4), 4 numord NUMBER(3) CONSTRAINT valid_numord_ch CHECK (numord <= 200)); SQL> edit userexcep2 SET SERVEROUTPUT ON DECLARE v_ordno ordline.ordno%TYPE :='000003' ; v_itemno ordline.itemno%TYPE :='1212'; v_numord ordline.numord%TYPE :=300; BEGIN INSERT INTO test_ordline VALUES(v_ordno, v_itemno, v_numord); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error occurred); END; / SET SERVEROUTPUT OFF SQL> @ userexcep2 Error occurred PL/SQL procedure successfully completed. SQL> SELECT * FROM test_ordline; no rows selected

  10. Constraint - version 2 SQL> edit userexcep2a SET SERVEROUTPUT ON DECLARE v_err_code NUMBER; v_err_msg VARCHAR2(255); v_ordno ordline.ordno%TYPE :='000003' ; v_itemno ordline.itemno%TYPE :='1212'; v_numord ordline.numord%TYPE :=300; BEGIN INSERT INTO test_ordline VALUES(v_ordno, v_itemno, v_numord); EXCEPTION WHEN OTHERS THEN v_err_code:= SQLCODE; v_err_msg := SQLERRM; dbms_output.put_line('Error '||TO_CHAR(v_err_code)||' '||v_err_msg); END; / SET SERVEROUTPUT OFF SQL> @ userexcep2a Error -2290 ORA-02290: check constraint (SCOTT.VALID_NUMORD_CH) violated PL/SQL procedure successfully completed.

  11. Constraint - version 3 SQL> edit userexcep2b SET SERVEROUTPUT ON DECLARE e_invalid_numord EXCEPTION; PRAGMA EXCEPTION_INIT(e_invalid_numord, -2290); v_err_code NUMBER; v_err_msg VARCHAR2(255); v_ordno ordline.ordno%TYPE :='000003' ; v_itemno ordline.itemno%TYPE :='1212'; v_numord ordline.numord%TYPE :=300; BEGIN INSERT INTO test_ordline VALUES(v_ordno, v_itemno, v_numord); EXCEPTION WHEN e_invalid_numord THEN dbms_output.put_line('Constraint problem - numord > 200'); WHEN OTHERS THEN v_err_code:= SQLCODE; v_err_msg := SQLERRM; dbms_output.put_line('Error '||TO_CHAR(v_err_code)||' '||v_err_msg); END; / SET SERVEROUTPUT OFF SQL> @ userexcep2b Constraint problem - numord > 200 PL/SQL procedure successfully completed.

  12. SQL> edit funcexcep1 SET SERVEROUTPUT ON DECLARE e_numord_over200 EXCEPTION; v_err_code NUMBER; v_err_msg VARCHAR2(255); v_ordno ordline.ordno%TYPE :='500003' ; v_itemno ordline.itemno%TYPE :='1212'; v_numord ordline.numord%TYPE :=200; BEGIN IF v_numord > 200 THEN RAISE e_numord_over200; END IF; INSERT INTO ordline VALUES(v_ordno, v_itemno, v_numord); EXCEPTION WHEN e_numord_over200 THEN dbms_output.put_line('NUMORD EXCEEDS 200 - USER RANGE ERROR'); WHEN OTHERS THEN v_err_code:=SQLCODE; v_err_msg :=SQLERRM; dbms_output.put_line('Undefined error '||TO_CHAR(v_err_code)||' '||v_err_msg); END; / SET SERVEROUTPUT OFF SQL> @ funcexcep1 Undefined error -2291 ORA-02291: integrity constraint (SCOTT.ORDNO_FK) violated - parent key not found PL/SQL procedure successfully completed.

  13. LOOP SQL> edit testexception SET SERVEROUTPUT ON DECLARE e_yrgoal_less100 EXCEPTION; v_idno donortest.idno%TYPE; v_name donortest.name%TYPE; v_yrgoal donortest.yrgoal%TYPE; CURSOR c_donor IS SELECT idno, name, yrgoal FROM donortest; BEGIN OPEN c_donor; LOOP FETCH c_donor INTO v_idno, v_name, v_yrgoal; EXIT WHEN c_donor%NOTFOUND; IF v_yrgoal < 100 THEN RAISE e_yrgoal_less100; END IF; dbms_output.put_line('Row processed: '|| v_name); END LOOP; EXCEPTION WHEN e_yrgoal_less100 THEN dbms_output.put_line(‘YRGOAL < 100 ERROR’); WHEN OTHERS THEN dbms_output.put_line('Undefined error occurred); END; / SET SERVEROUTPUT OFF

  14. LOOP SQL> select * from donortest; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 1237.16 Amy Costa 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 885.78 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 100 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 209.09 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 66.55 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa 56789 Alice Riley 123 Elm Chicago IL 07812 12-DEC-98 450 John Adams 67676 Jim Ryan 12 Elm St. Hingham MA 02043 20-AUG-99 6666 Amy Costa SQL> @ testexception Row processed: Stephen Daniels Row processed: Jennifer Ames Row processed: Carl Hersey Row processed: Susan Ash YRGOAL < 100 ERROR PL/SQL procedure successfully completed.

More Related