RAISE_APPLICATION_ERROR EXCETION SQLCODE SQLERRM PRAGMA EXCEPTION_INIT İçeren Örnek uygulama

create table company(
2 product_id number(4) not null primary key ,
3 company_id NUMBER(8) not null,
4 company_short_name varchar2(30) not null,
5 company_long_name varchar2(60)
6 );

Table created.
begin
insert into company values(1,1001,'A Inc.','Long Name A Inc.');
insert into company values(1,1002,'B Inc.','Long Name B Inc.');
insert into company values(1,1003,'C Inc.','Long Name C Inc.');
insert into company values(2,1004,'D Inc.','Long Name D Inc.');
insert into company values(2,1005,'E Inc.','Long Name E Inc.');
insert into company values(2,1006,'F Inc.','Long Name F Inc.');
end;

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> 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.



kaynak www.java2s.com