Behavior of the NO_DATA_FOUND exception.

  
SQL>
SQL> CREATE TABLE place (
  2    room_id          NUMBER(5) PRIMARY KEY,
  3    building         VARCHAR2(15),
  4    room_number      NUMBER(4),
  5    number_seats     NUMBER(4),
  6    description      VARCHAR2(50)
  7    );

Table created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20001, 'Building 7', 201, 1000, 'Large Lecture Hall');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20002, 'Building 6', 101, 500, 'Small Lecture Hall');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20003, 'Building 6', 150, 50, 'Discussion Room A');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20004, 'Building 6', 160, 50, 'Discussion Room B');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
  2             VALUES (20005, 'Building 6', 170, 50, 'Discussion Room C');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20006, 'Music Building', 100, 10, 'Music Practice Room');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20007, 'Music Building', 200, 1000, 'Concert Room');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20008, 'Building 7', 300, 75, 'Discussion Room D');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
  2             VALUES (20009, 'Building 7', 310, 50, 'Discussion Room E');

1 row created.

SQL>
SQL> set serveroutput on
SQL>
SQL>
SQL> DECLARE
  2    v_RoomData   place%ROWTYPE;
  3  BEGIN
  4    SELECT *
  5      INTO v_RoomData
  6      FROM place
  7      WHERE room_id = -1;
  8
  9    IF SQL%NOTFOUND THEN
 10      DBMS_OUTPUT.PUT_LINE('SQL%NOTFOUND is true!');
 11    END IF;
 12  EXCEPTION
 13    WHEN NO_DATA_FOUND THEN
 14      DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND raised!');
 15  END;
 16  /
NO_DATA_FOUND raised!

PL/SQL procedure successfully completed.

SQL>
SQL> drop table place;

Table dropped.

SQL>

Related examples in the same category

1.	If cursor not found, reset the value
2.	Explicit Cursor and notfound
3.	SQL%NOTFOUND and if statement
4.	Exit When Cursor%NOTFOUND
5.	Incorrect location of the EXIT WHEN statement.
6.	Use 'EXIT WHEN studentCursor%NOTFOUND' just after fetch statement
7.	NO_DATA_FOUND vs. %NOTFOUND
8.	Loop till exit when cursorName%notfound
9.	Output last row in a cursor
10.	fetch cursor till notfound

Behavior of the NO_DATA_FOUND exception. : Cursor Not Found « Cursor « Oracle PL / SQL

Related examples in the same category