Use Oracle Exception

Question

I want to use Oracle exception to handle errors that might happen in the code below. If a user provides the book ID and/or employee ID that doesn't exist in the database, NO_DATA_FOUND exception will be raised. Thus, how can I know which statement raises this exception.

CREATE OR REPLACE PROCEDURE TEST_EXCEPTION
(
    book_id_in IN book.book_id%TYPE,
    emp_id_in IN emp.emp_id%TYPE
)
IS

v_book_desc book.description%TYPE;
v_emp_name emp.emp_name%TYPE;

BEGIN

SELECT description into v_book_desc FROM book WHERE book_id = book_id_in;

...

SELECT emp_name into v_emp_name FROM emp WHERE emp_id = emp_id_in;

...

EXCEPTION
     WHEN NO_DATA_FOUND THEN
         /* Do something */


END TEST_EXCEPTION;

I appreciate any suggestion or guidance. Thank you.

Answer 1

You can set a variable before the given statements. Like;

CREATE OR REPLACE PROCEDURE TEST_EXCEPTION ( book_id_in IN book.book_id%TYPE, emp_id_in IN emp.emp_id%TYPE ) IS

v_book_desc book.description%TYPE; v_emp_name emp.emp_name%TYPE;
statementIndex number(1, 0);

BEGIN

statementIndex := 1;
SELECT description into v_book_desc FROM book WHERE book_id = book_id_in;

...

statementIndex := 2;
SELECT emp_name into v_emp_name FROM emp WHERE emp_id = emp_id_in;

...

EXCEPTION WHEN NO_DATA_FOUND THEN 
if statementIndex = 1
then 
/* Do something */
else
/* Do something */
endif;

END TEST_EXCEPTION;

Answer 2

There are various ways in which this can be done:

OPTION 1

The other option is to create multiple begin / end blocks

CREATE OR REPLACE PROCEDURE TEST_EXCEPTION (
     book_id_in IN book.book_id%TYPE,
     emp_id_in IN emp.emp_id%TYPE )
IS

v_book_desc book.description%TYPE; v_emp_name emp.emp_name%TYPE;
statementIndex number(1, 0);

BEGIN

     BEGIN

     SELECT description into v_book_desc FROM book WHERE book_id = book_id_in;

     EXCEPTION WHEN NO_DATA_FOUND THEN 
      -- do your handling or raise a custom exception to be handled at end
     END; 

     BEGIN

     SELECT emp_name into v_emp_name FROM emp WHERE emp_id = emp_id_in;

      EXCEPTION WHEN NO_DATA_FOUND THEN 
      -- do your handling or raise a custom exception to be handled at end
     END; 

EXCEPTION WHEN_OTHERS THEN 

END TEST_EXCEPTION;

---

OPTION 2

The other option is to use Cursors;

The use cursor%NOTFOUND

See (http://www.unix.com.ua/orelly/oracle/langpkt/ch01_09.htm) to understand about cursors

---

OPTION 3: Copied from Yapiskan

CREATE OR REPLACE PROCEDURE TEST_EXCEPTION ( book_id_in IN book.book_id%TYPE, emp_id_in IN emp.emp_id%TYPE ) IS

v_book_desc book.description%TYPE; v_emp_name emp.emp_name%TYPE;
statementIndex number(1, 0);

BEGIN

statementIndex := 1;
SELECT description into v_book_desc FROM book WHERE book_id = book_id_in;

...

statementIndex := 2;
SELECT emp_name into v_emp_name FROM emp WHERE emp_id = emp_id_in;

...

EXCEPTION WHEN NO_DATA_FOUND THEN 
if statementIndex = 1
then 
/* Do something */
else
/* Do something */
endif;

END TEST_EXCEPTION;

Answer 3

I want to know which is better...cursor or exception in this scenario