Oracle PL/SQL Tutorial/PL SQL Programming/raise application error
Содержание
A complete example using RAISE_APPLICATION_ERROR
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 "||TO_CHAR(p_company_id)||" already exists.");
17 ELSIF v_error_code =-2291 THEN
18 RAISE_APPLICATION_ERROR(-20001,"Invalid Hierarchy Code "||TO_CHAR(p_product_id)||" specified.");
19 END IF;
20 END;
21 ELSIF (p_flag_in ="C")THEN
22 BEGIN
23 UPDATE company
24 set company_short_name =p_company_short_name,
25 company_long_name =p_company_long_name
26 WHERE product_id =p_product_id
27 AND company_id =p_company_id;
28 IF SQL%NOTFOUND THEN
29 RAISE_APPLICATION_ERROR(-20002,"Organization "||TO_CHAR(p_company_id)||" does not exist.");
30 END IF;
31 END;
32 ELSIF (p_flag_in ="D")THEN
33 BEGIN
34 DELETE company
35 WHERE product_id =p_product_id
36 AND company_id =p_company_id;
37 IF SQL%NOTFOUND THEN
38 RAISE_APPLICATION_ERROR(-20003,"Organization "||TO_CHAR(p_company_id)||" does not exist.");
39 END IF;
40 EXCEPTION WHEN OTHERS THEN
41 v_error_code :=SQLCODE;
42 IF v_error_code =-2292 THEN
43 RAISE_APPLICATION_ERROR(-20004,"Organization "||TO_CHAR(p_company_id)||" site details defined for it.");
44 END IF;
45 END;
46 END IF;
47 END;
48 /
Procedure created.
SQL>
SQL> DECLARE
2 v_product_id NUMBER := 6;
3 v_company_id NUMBER := 1010;
4 v_company_short_name VARCHAR2(30):= "Office Inc.";
5 v_company_long_name VARCHAR2(60):= "Office 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>
Check the result of count aggregation function and then raise exception
SQL>
SQL> create table ord(
2 order_no integer primary key
3 ,cust_no integer
4 ,order_date date not null
5 ,total_order_price number(7,2)
6 ,deliver_date date
7 ,deliver_time varchar2(7)
8 ,payment_method varchar2(2)
9 ,emp_no number(3,0)
10 ,deliver_name varchar2(35)
11 ,gift_message varchar2(100)
12 );
Table created.
SQL>
SQL>
SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO" , 2, "Cora", "Happy Birthday from John");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI", 3, "Larry", "Happy New Year from Lawrence");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created.
SQL>
SQL> create or replace procedure delete_cust
2 (p_Cust_no in number)
3 as
4 l_count number;
5
6 begin
7
8 select count(*) into l_count
9 from ord
10 where cust_no = p_cust_no;
11 if l_count != 0 then
12 raise_application_error(-20000, "cannot delete active cust");
13 end if;
14 end;
15 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> EXEC delete_cust(13);
PL/SQL procedure successfully completed.
SQL>
SQL> drop table ord;
Table dropped.
SQL>
SQL>
Raise applocation error
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> -- display data in the table
SQL> select * from Employee
2 /
no rows selected
SQL>
SQL> create or replace trigger emp_biu
2 BEFORE INSERT OR UPDATE
3 of salary
4 on employee
5 for each row
6 declare
7 v_error VARCHAR2(2000);
8 begin
9 if :new.salary > 10000
10 then
11 v_error:=:old.first_name||" cannot have that much!";
12 raise_application_error(-20999,v_error);
13 end if;
14
15
16 end;
17 /
Trigger 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"), 11232.78,"Vancouver", "Tester")
3 /
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
*
ERROR at line 1:
ORA-20999: cannot have that much!
ORA-06512: at "sqle.EMP_BIU", line 7
ORA-04088: error during execution of trigger "sqle.EMP_BIU"
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Use RAISE_APPLICATION_ERROR to re throw exceptions
SQL> --
SQL>
SQL>
SQL> create table employee
2 (
3 empl_no integer primary key
4 ,lastname varchar2(20) not null
5 ,firstname varchar2(15) not null
6 ,midinit varchar2(1)
7 ,street varchar2(30)
8 ,city varchar2(20)
9 ,state varchar2(2)
10 ,zip varchar2(5)
11 ,zip_4 varchar2(4)
12 ,area_code varchar2(3)
13 ,phone varchar2(8)
14 ,company_name varchar2(50));
Table created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");
1 row created.
SQL>
SQL>
SQL>
SQL> create table employee_temp as select * from employee where 0=1;
Table created.
SQL> alter table employee_temp add constraint ct_pk primary key (empl_no);
Table altered.
SQL>
SQL> begin
2 for x in (select * from employee order by empl_no) loop
3 begin
4 insert into employee_temp values x;
5 exception
6 when others then
7 raise_application_error(-20000,
8 "Fatal error, call support");
9 end;
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employee_temp;
Table dropped.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
Using RAISE_APPLICATION_ERROR
SQL>
SQL> CREATE OR REPLACE PROCEDURE Register is
2 BEGIN
3 RAISE_APPLICATION_ERROR(-20000, "Can""t add more Employee");
4
5 EXCEPTION
6 WHEN NO_DATA_FOUND THEN
7 RAISE_APPLICATION_ERROR(-20001, " doesn""t exist!");
8 END Register;
9 /
Procedure created.
SQL>
SQL> call Register();
call Register()
*
ERROR at line 1:
ORA-20000: Can"t add more Employee
ORA-06512: at "sqle.REGISTER", line 3
SQL>