TOO_MANY_ROWS exception and select into command

    
SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );

Table created.

SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'Enn', 'F');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');

1 row created.

SQL>
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
  2     v_emp emp.fname%TYPE;
  3  BEGIN
  4
  5
  6     
  7     BEGIN
  8        SELECT fname INTO v_emp FROM emp WHERE UPPER(lname) = 'H';
  9
 10     EXCEPTION
 11        WHEN TOO_MANY_ROWS
 12        THEN
 13           DBMS_OUTPUT.PUT_LINE(' ');
 14           DBMS_OUTPUT.PUT_LINE('EXCEPTION HANDLER for nested block 2');
 15           DBMS_OUTPUT.PUT_LINE('If this is printing, then the both nested');
 16           DBMS_OUTPUT.PUT_LINE('blocks'' exception handler worked!');
 17     END;
 18
 19  END;
 20  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table emp;

Table dropped.

SQL>

Related examples in the same category

1.	Select value from table into variable
2.	Oracle returns an error when a SELECT statement returns more than one row
3.	Catch too_many_rows Exception for 'Select into' statement
4.	Multiple-Row SELECT Command with Several Exception-Handling Routines
5.	Select count result into a variable
6.	Output variable after 'select into'
7.	SELECT into value pair
8.	Select the number of employees into the l_emp_count variable
9.	Select into and subquery
10.	Select single value into variable
11.	Select two columns into a cursor variable
12.	Select value into a number variable in a for loop
13.	Select value to variable one by one
14.	If no records are retrieved for a SELECT - INTO statement the following error is returned
15.	If too many records are returned for a SELECT - INTO statement the following error is returned
16.	no_data_found from select ... into
17.	select bulk collect into table collection
18.	Use subquery in pl/sql block
19.	This script demonstrates how to do a non-bulk select into elements of a PL/SQL table.
20.	Store max(salary) to a variable
21.	Store max(tableName.column) to tableName.column.type variable
22.	Bulk Collection: fetch a single row from the ALL_OBJECTS table.
23.	Calculate salary by adding salary with max(salary)
24.	Select data for update
25.	Select value from aggregate function to variable

TOO_MANY_ROWS exception and select into command : Select Into « PL SQL « Oracle PL / SQL

Related examples in the same category