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
<source lang="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:=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></source>
Exceptions Raised in the Exception Handler
<source lang="sql">
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></source>
Propogating a Server-side Customized Error Number and Error Message to client program using PRAGMA EXCEPTION_INIT
<source lang="sql">
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></source>
Raise Exception in a function
<source lang="sql">
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></source>
Raising a custom exception
<source lang="sql">
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.</source>
Raising an Exception in the Declaration Section
<source lang="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:=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></source>
Raising an Exception Local PL/SQL Block
<source lang="sql">
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></source>
Raising NO_DATA_FOUND
<source lang="sql">
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></source>
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
<source lang="sql">
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></source>
Using WHEN OTHERS clause
<source lang="sql">
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></source>
