Handling user-defined exceptions with a WHEN clause : Your Exception « PL SQL Programming « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Programming
3. Your Exception

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> 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>
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>
SQL> drop table company;

Table dropped.

SQL>
SQL> drop table org_company_site;

Table dropped.

SQL>