NO data found : Handle Exception « PL SQL Programming « Oracle PL/SQL Tutorial

SQL>
SQL>
SQL> create table employee (
  2  id                             number,
  3  employee_type_id               number,
  4  external_id                    varchar2(30),
  5  first_name                     varchar2(30),
  6  middle_name                    varchar2(30),
  7  last_name                      varchar2(30),
  8  name                           varchar2(100),
  9  birth_date                     date  ,
 10  gender_id                      number );

Table created.

SQL>
SQL>
SQL> create table gender (
  2  id                             number,
  3  code                           varchar2(30),
  4  description                    varchar2(80),
  5  active_date                    date          default SYSDATE  not null,
  6  inactive_date                  date );

Table created.

SQL>
SQL>
SQL>
SQL> insert into gender ( id, code, description ) values ( 1, 'F', 'Female' );

1 row created.

SQL> insert into gender ( id, code, description ) values ( 2, 'M', 'Male' );

1 row created.

SQL> insert into gender ( id, code, description ) values ( 3, 'U', 'Unknown' );

1 row created.

SQL>
SQL>
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
  2
  3  d_birth_date                          employee.birth_date%TYPE;
  4  n_gender_id                           employee.gender_id%TYPE;
  5  n_selected                            number := -1;
  6  n_id                                  employee.id%TYPE;
  7  v_first_name                          employee.first_name%TYPE;
  8  v_last_name                           employee.last_name%TYPE;
  9  v_middle_name                         employee.middle_name%TYPE;
 10  v_name                                employee.name%TYPE;
 11
 12  begin
 13    v_first_name  := 'JOHN';
 14    v_middle_name := 'J.';
 15    v_last_name   := 'DOUGH';
 16    v_name        := rtrim(v_last_name||', '||v_first_name||' '||v_middle_name);
 17    d_birth_date  := to_date('19800101', 'YYYYMMDD');
 18
 19    begin
 20      select id into n_gender_id from gender where code = 'M';
 21    exception
 22      when OTHERS then
 23        raise_application_error(-20001, SQLERRM||' on select gender');
 24    end;
 25
 26    begin
 27      select id
 28      into   n_id
 29      from   employee
 30      where  name       = v_name
 31      and    birth_date = d_birth_date
 32      and    gender_id  = n_gender_id;
 33
 34      n_selected := sql%rowcount;
 35    exception
 36      when NO_DATA_FOUND then
 37        n_selected := sql%rowcount;
 38        DBMS_OUTPUT.PUT_LINE('Caught raised exception NO_DATA_FOUND');
 39      when OTHERS then
 40        raise_application_error(-20002, SQLERRM||' on select employee');
 41    end;
 42
 43    DBMS_OUTPUT.PUT_LINE(to_char(n_selected)||' row(s) selected.');
 44  end;
 45  /
Caught raised exception NO_DATA_FOUND
0 row(s) selected.

PL/SQL procedure successfully completed.

SQL>
SQL> drop table gender;

Table dropped.

SQL>
SQL> drop table employee;

Table dropped.

SQL>

24.15.Handle Exception
	24.15.1.	Code with No Exception Handler
	24.15.2.	Code with Conditional Control to Avoid an Exception
	24.15.3.	Code with Explicit Handler for Predefined Exception
	24.15.4.	Handling an Unnamed Exception
	24.15.5.	Handling a custom exception
	24.15.6.	An example showing continuing program execution after handling exception
	24.15.7.	The OTHERS Exception Handler
	24.15.8.	Assigning a Name to Predefined Exception Code
	24.15.9.	Using SQLCODE for error code and SQLERRM for error message
	24.15.10.	Catch custom exception
	24.15.11.	Handling exceptions without halting the program
	24.15.12.	Select into statement with exception catch statement
	24.15.13.	Check OTHERS exception
	24.15.14.	Error message code and text
	24.15.15.	Using PRAGMA EXCEPTION_INIT
	24.15.16.	NO data found
	24.15.17.	TOO_MANY_ROWS Exception
	24.15.18.	Use a nested block to catch exceptions from singleton SELECT.