Mega Code Archive
TOO_MANY_ROWS exception and select into command
<![CDATA[
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>
]]>