Oracle PL/SQL Tutorial/PL SQL Programming/Raise Exception
Содержание
- 1 Avoiding exceptions raised in declaration part and exception handler
- 2 Exceptions Raised in the Exception Handler
- 3 Propogating a Server-side Customized Error Number and Error Message to client program using PRAGMA EXCEPTION_INIT
- 4 Raise Exception in a function
- 5 Raising a custom exception
- 6 Raising an Exception in the Declaration Section
- 7 Raising an Exception Local PL/SQL Block
- 8 Raising NO_DATA_FOUND
- 9 User-Defined Errors
- 10 Using SQLCODE and SQLERRM
- 11 Using WHEN OTHERS clause
Avoiding exceptions raised in declaration part and exception handler
SQL> create or replace procedure p_validatezip (i_zipCode VARCHAR2)
2 is
3 e_tooShort EXCEPTION;
4 e_tooLong EXCEPTION;
5 e_badZip EXCEPTION;
6 pragma exception_init(e_badZip, -20998);
7 v_tempZip number:=to_number(i_zipCode);
8 begin
9 if length(i_zipCode)< 5 then
10 raise e_tooShort;
11 elsif length(i_zipCode)> 6 then
12 raise e_tooLong;
13 end if;
14 exception
15 when e_tooLong then
16 DBMS_OUTPUT.put_line("long zip");
17 raise e_badZip;
18 when e_tooShort then
19 DBMS_OUTPUT.put_line("short zip");
20 when VALUE_ERROR then
21 DBMS_OUTPUT.put_line("non-numeric zip");
22 raise e_badZip;
23 end;
24 /
Procedure created.
SQL>
SQL> declare
2
3 begin
4 p_validatezip("9123412341234");
5 end;
6 /
long zip
declare
*
ERROR at line 1:
ORA-20998:
ORA-06512: at "sqle.P_VALIDATEZIP", line 17
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 4
SQL>
Exceptions Raised in the Exception Handler
SQL>
SQL> create or replace procedure p_validatezip (i_zipCode VARCHAR2)
2 is
3 e_tooShort EXCEPTION;
4 e_tooLong EXCEPTION;
5 e_badZip EXCEPTION;
6 pragma exception_init(e_badZip, -20998);
7 v_tempZip NUMBER;
8 begin
9 if length(i_zipCode)< 5 then
10 Raise e_tooShort;
11 elsif length(i_zipCode)> 6 then
12 Raise e_tooLong;
13 end if;
14 v_tempZip := to_number(i_zipCode);
15 exception
16 when e_tooLong then
17 raise e_badZip;
18 when e_tooShort then
19 raise e_badZip;
20 when VALUE_ERROR then
21 raise e_badZip;
22 when e_badZip then
23 DBMS_OUTPUT.put_line("problem with Zip");
24 raise;
25 end;
26 /
Procedure created.
SQL> begin
2 p_validatezip("9406123123");
3 end;
4 /
begin
*
ERROR at line 1:
ORA-20998:
ORA-06512: at "sqle.P_VALIDATEZIP", line 17
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 2
SQL>
Propogating a Server-side Customized Error Number and Error Message to client program using PRAGMA EXCEPTION_INIT
SQL>
SQL> create table company(
2 product_id number(4) not null,
3 company_id NUMBER(8) not null,
4 company_short_name varchar2(30) not null,
5 company_long_name varchar2(60)
6 );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE org_proc
2 (p_flag_in VARCHAR2,
3 p_product_id NUMBER,
4 p_company_id NUMBER,
5 p_company_short_name VARCHAR2,
6 p_company_long_name VARCHAR2)
7 IS
8 v_error_code NUMBER;
9 BEGIN
10 IF (p_flag_in ="I")THEN
11 BEGIN
12 INSERT INTO company VALUES (p_product_id,p_company_id,p_company_short_name,p_company_long_name);
13 EXCEPTION WHEN OTHERS THEN
14 v_error_code :=SQLCODE;
15 IF v_error_code =-1 THEN
16 RAISE_APPLICATION_ERROR(-20000,"Organization "||
17 TO_CHAR(p_company_id)||" already exists.");
18 ELSIF v_error_code =-2291 THEN
19 RAISE_APPLICATION_ERROR(-20001,"Invalid Hierarchy Code "||
20 TO_CHAR(p_product_id)||" specified. Cannot create organization.");
21 END IF;
22 END;
23 ELSIF (p_flag_in ="C")THEN
24 BEGIN
25 UPDATE company
26 set company_short_name =p_company_short_name,
27 company_long_name =p_company_long_name
28 WHERE product_id =p_product_id
29 AND company_id =p_company_id;
30 IF SQL%NOTFOUND THEN
31 RAISE_APPLICATION_ERROR(-20002,"Organization "||
32 TO_CHAR(p_company_id)||
33 " does not exist.");
34 END IF;
35 END;
36 ELSIF (p_flag_in ="D")THEN
37 BEGIN
38 DELETE company
39 WHERE product_id =p_product_id
40 AND company_id =p_company_id;
41 IF SQL%NOTFOUND THEN
42 RAISE_APPLICATION_ERROR(-20003,"Organization "||
43 TO_CHAR(p_company_id)||
44 " does not exist. Cannot delete info for the same.");
45 END IF;
46 EXCEPTION WHEN OTHERS THEN
47 v_error_code :=SQLCODE;
48 IF v_error_code =-2292 THEN
49 RAISE_APPLICATION_ERROR(-20004,"Organization "||
50 TO_CHAR(p_company_id)||
51 " site details defined for it.");
52 END IF;
53 END;
54 END IF;
55 END;
56 /
Procedure created.
SQL>
SQL> DECLARE
2 v_product_id NUMBER := 6;
3 v_company_id NUMBER := 1010;
4 v_company_short_name VARCHAR2(30):= "O Inc.";
5 v_company_long_name VARCHAR2(60):= "O Inc.";
6 excep1 EXCEPTION;
7 PRAGMA EXCEPTION_INIT(excep1,-20000);
8 excep2 EXCEPTION;
9 PRAGMA EXCEPTION_INIT(excep2,-20001);
10 BEGIN
11 org_proc("I",v_product_id,v_company_id,v_company_short_name,v_company_long_name);
12 EXCEPTION
13 WHEN excep1 or excep2 THEN
14 DBMS_OUTPUT.PUT_LINE(SQLERRM);
15 WHEN OTHERS THEN
16 DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLCODE)||SQLERRM);
17 END;
18 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table company;
Table dropped.
SQL>
SQL>
Raise Exception in a function
SQL> set serveroutput on
SQL> DECLARE
2 quantity1 NUMBER := -2;
3 quantity2 NUMBER := 3;
4 total NUMBER := 0;
5 quantity_must_positive EXCEPTION;
6 FUNCTION find_cost (quant NUMBER) RETURN NUMBER IS
7 BEGIN
8 IF (quant > 0)
9 THEN
10 RETURN(quant * 20);
11 ELSE
12 RAISE quantity_must_positive;
13 END IF;
14 END find_cost;
15 BEGIN
16 total := find_cost (quantity2);
17 total := total + find_cost(quantity1);
18 EXCEPTION
19 WHEN quantity_must_positive
20 THEN
21 dbms_output.put_line("Total until now: " || total);
22 dbms_output.put_line("Tried to use negative quantity ");
23 END;
24 /
Total until now: 60
Tried to use negative quantity
PL/SQL procedure successfully completed.
SQL>
SQL>
Raising a custom exception
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> DECLARE
2 e_TooManyEmployee EXCEPTION; -- Exception to indicate an error condition
3 v_CurrentEmployee NUMBER(3);
4 v_MaxEmployee NUMBER(3);
5
6 BEGIN
7
8 v_CurrentEmployee := 5;
9
10 SELECT max(id)
11 INTO v_MaxEmployee
12 FROM employee;
13
14 IF v_CurrentEmployee > v_MaxEmployee THEN
15 RAISE e_TooManyEmployee;
16 END IF;
17 END;
18 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Raising an Exception in the Declaration Section
SQL> create or replace procedure p_validatezip (i_zipCode VARCHAR2)
2 is
3 e_tooShort EXCEPTION;
4 e_tooLong EXCEPTION;
5 e_badZip EXCEPTION;
6 pragma exception_init(e_badZip, -20998);
7 v_tempZip number:=to_number(i_zipCode);
8 begin
9 if length(i_zipCode)< 5 then
10 raise e_tooShort;
11 elsif length(i_zipCode)> 6 then
12 raise e_tooLong;
13 end if;
14 exception
15 when e_tooLong then
16 DBMS_OUTPUT.put_line("long zip");
17 raise e_badZip;
18 when e_tooShort then
19 DBMS_OUTPUT.put_line("short zip");
20 when VALUE_ERROR then
21 DBMS_OUTPUT.put_line("non-numeric zip");
22 raise e_badZip;
23 end;
24 /
Procedure created.
SQL>
SQL> declare
2
3 begin
4 p_validatezip("9123412341234");
5 end;
6 /
long zip
declare
*
ERROR at line 1:
ORA-20998:
ORA-06512: at "sqle.P_VALIDATEZIP", line 17
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 4
SQL>
Raising an Exception Local PL/SQL Block
SQL> --
SQL>
SQL>
SQL> create or replace function f_get_speed(i_distance NUMBER, i_timeSec NUMBER)
2 return NUMBER
3 is
4 v_out NUMBER;
5 begin
6
7 begin
8 v_out:= i_distance/i_timeSec;
9 exception
10 when zero_divide then
11 DBMS_OUTPUT.put_line("Divide by zero in the F_GET_SPEED");
12 end;
13 return v_out;
14 end;
15 /
Function created.
SQL>
SQL> select f_get_speed(1, 0) from dual;
F_GET_SPEED(1,0)
----------------
Divide by zero in the F_GET_SPEED
SQL>
Raising NO_DATA_FOUND
SQL>
SQL> DECLARE
2 TYPE t_NumberTableType IS TABLE OF NUMBER
3 INDEX BY BINARY_INTEGER;
4 v_NumberTable t_NumberTableType;
5 v_TempVar NUMBER;
6 BEGIN
7 v_TempVar := v_NumberTable(1);
8 END;
9 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 7
SQL>
SQL>
User-Defined Errors
Users can explicitly raise an exception with the RAISE command.
Steps for trapping a user-defined error include the following:
Declare the name for the user exception within the declaration section of the block.
Raise the exception explicitly within the executable portion of the block using the RAISE command.
Reference the declared exception with an error-handling routine.
24. 16. Raise Exception 24. 16. 1. User-Defined Errors 24. 16. 2. <A href="/Tutorial/Oracle/0480__PL-SQL-Programming/RaiseExceptioninafunction.htm">Raise Exception in a function</a> 24. 16. 3. <A href="/Tutorial/Oracle/0480__PL-SQL-Programming/RaisinganExceptionLocalPLSQLBlock.htm">Raising an Exception Local PL/SQL Block</a> 24. 16. 4. <A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Avoidingexceptionsraisedindeclarationpartandexceptionhandler.htm">Avoiding exceptions raised in declaration part and exception handler</a> 24. 16. 5. <A href="/Tutorial/Oracle/0480__PL-SQL-Programming/RaisinganExceptionintheDeclarationSection.htm">Raising an Exception in the Declaration Section</a> 24. 16. 6. <A href="/Tutorial/Oracle/0480__PL-SQL-Programming/ExceptionsRaisedintheExceptionHandler.htm">Exceptions Raised in the Exception Handler</a> 24. 16. 7. <A href="/Tutorial/Oracle/0480__PL-SQL-Programming/RaisingNODATAFOUND.htm">Raising NO_DATA_FOUND</a> 24. 16. 8. <A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Raisingacustomexception.htm">Raising a custom exception</a> 24. 16. 9. <A href="/Tutorial/Oracle/0480__PL-SQL-Programming/UsingWHENOTHERSclause.htm">Using WHEN OTHERS clause</a> 24. 16. 10. <A href="/Tutorial/Oracle/0480__PL-SQL-Programming/UsingSQLCODEandSQLERRM.htm">Using SQLCODE and SQLERRM</a> 24. 16. 11. <A href="/Tutorial/Oracle/0480__PL-SQL-Programming/PropogatingaServersideCustomizedErrorNumberandErrorMessagetoclientprogramusingPRAGMAEXCEPTIONINIT.htm">Propogating a Server-side Customized Error Number and Error Message to client program using PRAGMA EXCEPTION_INIT</a>
Using SQLCODE and SQLERRM
SQL> create table product(
2 product_id number(4) not null,
3 product_description varchar2(20) not null
4 );
Table created.
SQL>
SQL> insert into product values (1,"Java");
1 row created.
SQL> insert into product values (2,"Oracle");
1 row created.
SQL> insert into product values (3,"C#");
1 row created.
SQL> insert into product values (4,"Javascript");
1 row created.
SQL> insert into product values (5,"Python");
1 row created.
SQL>
SQL>
SQL> DECLARE
2 v_descr VARCHAR2(20);
3 BEGIN
4 SELECT product_description
5 INTO v_descr
6 FROM product
7 WHERE product_id =10;
8 dbms_output.put_line(v_descr);
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 dbms_output.put_line("ERR:Invalid Hierarchy Code 10");
12 WHEN OTHERS THEN
13 dbms_output.put_line("ERR:An error occurred with info :"||
14 TO_CHAR(SQLCODE)||" "||SQLERRM);
15 END;
16 /
ERR:Invalid Hierarchy Code 10
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table product;
Table dropped.
SQL>
Using WHEN OTHERS clause
SQL> create table product(
2 product_id number(4) not null,
3 product_description varchar2(20) not null
4 );
Table created.
SQL>
SQL> insert into product values (1,"Java");
1 row created.
SQL> insert into product values (2,"Oracle");
1 row created.
SQL> insert into product values (3,"C#");
1 row created.
SQL> insert into product values (4,"Javascript");
1 row created.
SQL> insert into product values (5,"Python");
1 row created.
SQL>
SQL>
SQL> DECLARE
2 v_descr VARCHAR2(20);
3 BEGIN
4 SELECT product_description
5 INTO v_descr
6 FROM product
7 WHERE product_id =10;
8 dbms_output.put_line(v_descr);
9 EXCEPTION WHEN OTHERS THEN
10 dbms_output.put_line("ERR:Invalid Hierarchy Code 10");
11 END;
12 /
ERR:Invalid Hierarchy Code 10
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> drop table product;
Table dropped.
SQL>