Use a nested block to catch exceptions from singleton SELECT. : Handle Exception « PL SQL Programming « Oracle PL/SQL Tutorial

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

SQL> CREATE TABLE good_for (
  2     product_id NUMBER,
  3     good_for INTERVAL YEAR(2) TO MONTH NOT NULL
  4  );

Table created.

SQL>
SQL> CREATE OR REPLACE FUNCTION get_expiration (p_product_id NUMBER)
  2     RETURN DATE
  3  AS
  4     v_good_for good_for.good_for%type;
  5     expiration_date DATE;
  6     found_flag BOOLEAN;
  7  BEGIN
  8    BEGIN
  9      SELECT gf.good_for INTO v_good_for
 10      FROM good_for gf
 11      WHERE gf.product_id = p_product_id;
 12      found_flag := TRUE;
 13    EXCEPTION
 14       WHEN OTHERS THEN
 15         found_flag := FALSE;
 16    END;
 17    IF found_flag THEN
 18      expiration_date := TRUNC(SYSDATE) + v_good_for;
 19    ELSE
 20      expiration_date := null;
 21    END IF;
 22
 23    RETURN expiration_date;
 24  END;
 25  /

Function created.

SQL>
SQL> INSERT INTO good_for (product_id, good_for)
  2     VALUES (1, INTERVAL '1-4' YEAR TO MONTH);

1 row created.

SQL>
SQL> SELECT get_expiration(1) FROM DUAL;

GET_EXPIR
---------
24-NOV-09

SQL>
SQL>
SQL> drop table good_for;

Table dropped.

SQL>
SQL>