Catch user-defined exception : Exception Handle « PL SQL « Oracle PL / SQL

Catch user-defined exception

    

SQL> CREATE TABLE myHotel(
  2     room_id INTEGER,
  3     resident_count INTEGER,
  4     room_capacity INTEGER,
  5     name VARCHAR2(20)
  6  );

Table created.

SQL>
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(101, 20, 20, 'First Room');

1 row created.

SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(102, 19, 20, 'Second Room');

1 row created.

SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(103, 10, 20, 'Third Room');

1 row created.

SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(104, 0, 20, 'Fourth Room');

1 row created.


SQL>
SQL> CREATE OR REPLACE PROCEDURE addstudent (roomin IN INTEGER)
  2  IS
  3     roomname       VARCHAR2 (20);
  4     residentcount   PLS_INTEGER;
  5     capacity       PLS_INTEGER;
  6     noroom         EXCEPTION;
  7  BEGIN
  8     SELECT resident_count, room_capacity, name
  9       INTO residentcount, capacity, roomname
 10       FROM myHotel
 11      WHERE room_id = roomin;
 12
 13     IF residentcount > capacity - 1
 14     THEN
 15        RAISE noroom;
 16     ELSE
 17        UPDATE myHotel
 18        SET resident_count = residentcount + 1
 19        WHERE room_id = roomin;
 20        COMMIT;
 21        DBMS_OUTPUT.put_line ('Student count:'||residentcount||' in '|| roomname);
 22     END IF;
 23  EXCEPTION
 24     WHEN noroom
 25     THEN
 26        DBMS_OUTPUT.put_line ('There is no room in ' || roomname);
 27     WHEN OTHERS
 28     THEN
 29        DBMS_OUTPUT.put_line ('Error ' || SQLERRM || ' occurred.');
 30  END;
 31  /

Procedure created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> drop table myHotel;

Table dropped.

SQL>
SQL>
SQL>

SQL>

1.	Check exception type
2.	Deal with multiple exception branches
3.	when other exceptions then
4.	Handle update exception
5.	declaration exception
6.	handle exception of duplicate value on index
7.	when other then not user-defined exception
8.	Using PRAGMA EXCEPTION_INIT
9.	Different Values of SQLCODE and SQLERRM
10.	The OTHERS Exception Handler
11.	Error-handling features of PL/SQL: log exception
12.	PLS-483 error: Duplicate Handlers
13.	NO_DATA_FOUND exception.
14.	The scope of exceptions.
15.	Sub block in exception section
16.	Catch all exceptions
17.	Check zero divide exception
18.	Combines declaring an EXCEPTION variable
19.	Insert error message to a table in exception handler
20.	Mapping a user-defined error code to an EXCEPTION variable
21.	This script demonstrates the EXCEPTION_INIT pragma.
22.	Error Handling Call
23.	Raise exception in if statement

Catch user-defined exception : Exception Handle « PL SQL « Oracle PL / SQL

Related examples in the same category