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