Hi,
I have a cursor which calls a function on a value in the SELECT. The function can sometimes fail and raise and exception. I am re-throwing the exception by using RAISE.
My understanding was that if the function call in a SELECT of a CURSOR fails, it will fail the whole CURSOR and exception will propagate to the enclosing exception handler. However, I observe that even if the function call raises (rethrows) exception, my cursor continues processing the next record. Is this how exception handling is supposed to be behaving? Why is re-thrown error (RAISE) not "bubbling up" to the enclosing exception block
For the below package and it execution, I expect the CURSOR to fail at the first row, but that is not happening and is completing normally:
My package script is as follows:
Package Script
<pre>
CREATE OR REPLACE PACKAGE xx_test_pkg
AS
FUNCTION get_sal(empno scott.emp.empno%TYPE)
RETURN NUMBER;
PROCEDURE main;
END xx_test_pkg;
/
CREATE OR REPLACE PACKAGE BODY xx_test_pkg
AS
FUNCTION get_sal(empno scott.emp.empno%TYPE)
RETURN NUMBER
IS
BEGIN
RAISE NO_DATA_FOUND;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RAISE; /* should this not cause the cursor in main() to fail */
END get_sal;
PROCEDURE main
IS
CURSOR C
IS
SELECT empno, ename , get_Sal (empno) sal
FROM scott.emp;
BEGIN
FOR r IN C
LOOP
dbms_output.put_line ('Name='||r.ename||' Salary='||r.sal);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line ('Exception:'||SQLERRM);
END main;
END xx_test_pkg;
/
</pre>
Run in SQL Plus
<pre>
SQL> set serveroutput on
SQL> exec xx_test_pkg.main;
Name=SMITH Salary=
Name=ALLEN Salary=
Name=WARD Salary=
Name=JONES Salary=
Name=MARTIN Salary=
Name=BLAKE Salary=
Name=CLARK Salary=
Name=SCOTT Salary=
Name=KING Salary=
Name=TURNER Salary=
Name=ADAMS Salary=
Name=JAMES Salary=
Name=FORD Salary=
Name=MILLER Salary=
PL/SQL procedure successfully completed.
</pre>
Database version
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
Thanks