Mega Code Archive
Handling user-defined exceptions with a WHEN clause
<![CDATA[
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 table org_company_site(
2 company_id number(8) not null,
3 site_no number(4) not null
4 );
Table created.
SQL> insert into org_company_site values (1001,1);
1 row created.
SQL> insert into org_company_site values (1002,2);
1 row created.
SQL> insert into org_company_site values (1003,3);
1 row created.
SQL> insert into org_company_site values (1004,1);
1 row created.
SQL> insert into org_company_site values (1004,2);
1 row created.
SQL> insert into org_company_site values (1004,3);
1 row created.
SQL> insert into org_company_site values (1005,1);
1 row created.
SQL> insert into org_company_site values (1005,4);
1 row created.
SQL> insert into org_company_site values (1005,5);
1 row created.
SQL> insert into org_company_site values (1006,1);
1 row created.
SQL>
SQL> BEGIN
2 insert into company values (3,1007,'O Inc.','O Inc.');
3 COMMIT;
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> DECLARE
2 sites_undefined_for_org EXCEPTION;
3 v_cnt NUMBER;
4 BEGIN
5 SELECT COUNT(*)
6 INTO v_cnt
7 FROM org_company_site
8 WHERE company_id =1007;
9 IF (v_cnt=0)THEN
10 --explicitly raising the user-defined exception
11 RAISE sites_undefined_for_org;
12 END IF;
13 EXCEPTION
14 --handling the raised user-defined exception
15 WHEN sites_undefined_for_org THEN
16 dbms_output.put_line('There are no sites defined for organization 1007');
17 WHEN OTHERS THEN
18 dbms_output.put_line('ERR:An error occurred with info :'||
19 TO_CHAR(SQLCODE)||' '||SQLERRM);
20 END;
21 /
There are no sites defined for organization 1007
PL/SQL procedure successfully completed.
SQL>
SQL> drop table company;
Table dropped.
SQL>
SQL> drop table org_company_site;
Table dropped.
SQL>
]]>