Use Oracle Exception
I want to use Oracle Exception to handle errors that might happen in the code below. If the user provides a workbook ID and / or employee ID that does not exist in the database, the NO_DATA_FOUND exception is thrown. This way how can I find out which instruction is causing 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 suggestions or recommendations. Thank.
+1
source to share
2 answers
You can set a variable before the specified operations. How;
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;
0
source to share
There are various ways you can do this:
OPTION 1
Another option is to create multiple start and 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
Another option is to use cursors;
% NOTFOUND cursor used
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;
+4
source to share