Transcript Exceptions in PL/SQL - Bristol Community College

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
There are no records
WHERE idno > 77777;
with an id > 77777 so
IF v_yrgoal > 250 THEN
the no rows found
v_yrgoal := v_yrgoal * 1.1;
error is returned and
END IF;
processed.
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
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.
Without
exception
SQL> edit preexcep1x
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> @ preexcep1x
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5
Multiple records
error
SQL> edit preexcep1a
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> @ preexcep1a
Multiple rows meet criteria
PL/SQL procedure successfully completed.
Oracle error not predefined
SQL> SELECT * FROM orderz;
SQL> SELECT * FROM ordline;
ORDNO
-----000001
000002
000003
000004
ORDNO
-----000001
000001
000001
000002
000002
000003
000003
000004
000004
CUSTI
----11111
12121
12345
11111
ORDATE
--------10-JUN-99
10-JUN-99
10-JUN-99
08-JUL-99
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
ITEM
NUMORD
---- --------1212
1
2121
1
2345
1
1111
3
3333
1
3333
2
3456
1
1212
3
1234
2
SQL> @ nonpreex1x
DECLARE
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.ORDNO_FK) violated - parent key not found
ORA-06512: at line 6
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.
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_numord ordline.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.
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
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)||'
END;
/
SET SERVEROUTPUT OFF
'||v_err_msg);
SQL> @ userexcep2a
Error -2290 ORA-02290: check constraint (SCOTT.VALID_NUMORD_CH) violated
PL/SQL procedure successfully completed.
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.
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)||'
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.
'||v_err_msg);
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
LOOP
SQL> select * from donortest;
IDNO
----11111
12121
22222
23456
33333
34567
56789
67676
NAME
--------------Stephen Daniels
Jennifer Ames
Carl Hersey
Susan Ash
Nancy Taylor
Robert Brooks
Alice Riley
Jim Ryan
STADR
--------------123 Elm St
24 Benefit St
24 Benefit St
21 Main St
26 Oak St
36 Pine St
123 Elm
12 Elm St.
CITY
---------Seekonk
Providence
Providence
Fall River
Fall River
Fall River
Chicago
Hingham
ST
-MA
RI
RI
MA
MA
MA
IL
MA
ZIP
----02345
02045
02045
02720
02720
02720
07812
02043
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.
DATEFST
YRGOAL CONTACT
--------- --------- -----------03-JUL-98
1237.16 Amy Costa
24-MAY-97
885.78 Susan Jones
03-JAN-98
100 Susan Jones
04-MAR-92
209.09 Amy Costa
04-MAR-92
66.55 John Adams
04-APR-98
63.53 Amy Costa
12-DEC-98
450 John Adams
20-AUG-99
6666 Amy Costa