TOO_MANY_ROWS Exception : Handle Exception « PL SQL Programming « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. PL SQL Programming
  3. Handle Exception
SQL>
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>
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   := 'DOE';
 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 into n_id from employee;
 28      n_selected := sql%rowcount;
 29    exception
 30      when NO_DATA_FOUND then
 31        n_selected := sql%rowcount;
 32        DBMS_OUTPUT.PUT_LINE('Caught raised exception NO_DATA_FOUND');
 33      when TOO_MANY_ROWS then
 34        n_selected := sql%rowcount;
 35        DBMS_OUTPUT.PUT_LINE('Caught raised exception TOO_MANY_ROWS');
 36      when OTHERS then
 37        raise_application_error(-20002, SQLERRM||' on select employee');
 38    end;
 39
 40    DBMS_OUTPUT.PUT_LINE(to_char(n_selected)||' row(s) selected.');
 41  end;
 42  /
Caught raised exception NO_DATA_FOUND
0 row(s) selected.

PL/SQL procedure successfully completed.

SQL>
SQL> drop table gender;

Table dropped.

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.