| Cursor Variable : Ref Cursor Behavior [message #649207] |
Thu, 17 March 2016 06:35  |
 |
Arunpandi
Messages: 4
Registered: March 2016
|
Junior Member |
|
|
See the below example. In this select written for the cursor pcEmployee was given errors. Because of a Address was not found the employee in global table. But while executing this function it was not returning any error. while iteration of the result set(pcEmployee) it giving the error message.
Could any one can help to know why it is not raising the error while it is executing?
CREATE OR REPLACE PACKAGE PKG_EMP
AS
TYPE tEmployee IS RECORD (
Id M_EMPLOYEE.EMP_ID%TYPE,
Name M_EMPLOYEE.EMP_NAME%TYPE
);
TYPE cEmployee IS REF CURSOR RETURN tEmployee;
FUNCTION fGetEmployeeDtls(
pcEmployee OUT cEmployee
) RETURN NUMBER;
END fGetEmployeeDtls;
/
CREATE OR REPLACE PACKAGE BODY PKG_EMP
AS
FUNCTION fGetEmployeeDtls(
pcEmployee OUT cEmployee
) RETURN NUMBER
vReturn NUMBER DEFAULT 0;
BEGIN
OPEN pcEmployee FOR
SELECT
EMP_ID,
EMP_NAME
FROM
M_EMPLOYEE;
RETURN vReturn;
EXCEPTION
WHEN OTHERS THEN
vReturn := -1;
RETURN vReturn;
END;
END fGetEmployeeDtls;
/
CREATE OR REPLACE FUNCTION fGetEmpAddress
(
pEmpId IN M_EMPLOYEE.EMP_ID%TYPE
) RETURN VARCHAR2
AS
pAddress M_GLOBAL_ADDRESS.ADDRESS%TYPE;
BEGIN
SELECT
Address
INTO
pAddress
FROM
M_GLOBAL_ADDRESS
WHERE
EMP_ID = pEmpId;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'Address Not Found');
END;
[mod-edit: code tags added by bb]
[Updated on: Thu, 17 March 2016 13:43] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Cursor Variable : Ref Cursor Behavior [message #649214 is a reply to message #649207] |
Thu, 17 March 2016 14:45  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your question is unclear and your code does not compile. Your package names at the top and the bottom of the package and package body must match and they don't. Also, you are missing AS or IS in your packaged function. I am guessing that you are using some other code that you have not posted to iterate through the result set of the packaged function and select from the other function. I am guessing that you probably want to return the value 'address not found' rather than raise it as an error. Otherwise, it will stop iterating at the first address that is not found. Please see the demonstration below. Please note that I am just trying to correct the functioning and am not suggesting anything as the best way. This is certainly a complicated way of doing what could be done with a simple select statement with an outer join and nvl.
SCOTT@orcl> -- tables for testing:
SCOTT@orcl> CREATE TABLE m_employee AS
2 SELECT empno AS emp_id, ename AS emp_name
3 FROM emp
4 /
Table created.
SCOTT@orcl> CREATE TABLE m_global_address AS
2 SELECT empno AS emp_id, loc AS address
3 FROM emp, dept
4 WHERE emp.deptno = dept.deptno
5 AND emp.deptno != 10
6 /
Table created.
SCOTT@orcl> -- package and function:
SCOTT@orcl> CREATE OR REPLACE PACKAGE PKG_EMP
2 AS
3 TYPE tEmployee IS RECORD
4 (Id M_EMPLOYEE.EMP_ID%TYPE,
5 Name M_EMPLOYEE.EMP_NAME%TYPE);
6 TYPE cEmployee IS REF CURSOR RETURN tEmployee;
7 FUNCTION fGetEmployeeDtls
8 (pcEmployee OUT cEmployee)
9 RETURN NUMBER;
10 END PKG_EMP;
11 /
Package created.
SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> CREATE OR REPLACE PACKAGE BODY PKG_EMP
2 AS
3 FUNCTION fGetEmployeeDtls
4 (pcEmployee OUT cEmployee)
5 RETURN NUMBER
6 IS
7 vReturn NUMBER DEFAULT 0;
8 BEGIN
9 OPEN pcEmployee FOR
10 SELECT EMP_ID, EMP_NAME
11 FROM M_EMPLOYEE;
12 RETURN vReturn;
13 EXCEPTION
14 WHEN OTHERS THEN
15 vReturn := -1;
16 RETURN vReturn;
17 END fGetEmployeeDtls;
18 END PKG_EMP;
19 /
Package body created.
SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> CREATE OR REPLACE FUNCTION fGetEmpAddress
2 (pEmpId IN M_EMPLOYEE.EMP_ID%TYPE)
3 RETURN VARCHAR2
4 AS
5 pAddress M_GLOBAL_ADDRESS.ADDRESS%TYPE;
6 BEGIN
7 SELECT Address
8 INTO pAddress
9 FROM M_GLOBAL_ADDRESS
10 WHERE EMP_ID = pEmpId;
11 RETURN pAddress;
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 RETURN ('Address Not Found');
15 WHEN OTHERS THEN
16 RAISE_APPLICATION_ERROR(-20001, 'Address Not Found');
17 END fGetEmpAddress;
18 /
Function created.
SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> -- iterating through cursor using function:
SCOTT@orcl> DECLARE
2 v_num NUMBER;
3 v_name VARCHAR2(15);
4 v_ref SYS_REFCURSOR;
5 v_id NUMBER;
6 BEGIN
7 v_num := pkg_emp.fGetEmployeeDtls (v_ref);
8 LOOP
9 FETCH v_ref INTO v_num, v_name;
10 EXIT WHEN v_ref%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE (v_num || ' ' || v_name || ' ' || fGetEmpAddress(v_num));
12 END LOOP;
13 CLOSE v_ref;
14 END;
15 /
7369 SMITH DALLAS
7499 ALLEN CHICAGO
7521 WARD CHICAGO
7566 JONES DALLAS
7654 MARTIN CHICAGO
7698 BLAKE CHICAGO
7782 CLARK Address Not Found
7788 SCOTT DALLAS
7839 KING Address Not Found
7844 TURNER CHICAGO
7876 ADAMS DALLAS
7900 JAMES CHICAGO
7902 FORD DALLAS
7934 MILLER Address Not Found
PL/SQL procedure successfully completed.
SCOTT@orcl> -- simpler way:
SCOTT@orcl> SELECT m_employee.emp_id, emp_name, NVL (address, 'Address Not Found')
2 FROM m_employee, m_global_address
3 WHERE m_employee.emp_id = m_global_address.emp_id(+)
4 ORDER BY emp_id
5 /
EMP_ID EMP_NAME NVL(ADDRESS,'ADDR
---------- ---------- -----------------
7369 SMITH DALLAS
7499 ALLEN CHICAGO
7521 WARD CHICAGO
7566 JONES DALLAS
7654 MARTIN CHICAGO
7698 BLAKE CHICAGO
7782 CLARK Address Not Found
7788 SCOTT DALLAS
7839 KING Address Not Found
7844 TURNER CHICAGO
7876 ADAMS DALLAS
7900 JAMES CHICAGO
7902 FORD DALLAS
7934 MILLER Address Not Found
14 rows selected.
|
|
|
|
| Re: Cursor Variable : Ref Cursor Behavior [message #649217 is a reply to message #649214] |
Fri, 18 March 2016 00:03 |
|
Arunpandi
Messages: 4
Registered: March 2016
|
Junior Member |
|
|
Sorry for the wrong code. Please find the below code.
DROP TABLE M_GLOBAL_ADDRESS;
DROP TABLE M_EMPLOYEE;
CREATE TABLE M_EMPLOYEE (
EMP_ID NUMBER(10),
EMP_NAME VARCHAR2(100 CHAR)
);
/
ALTER TABLE M_EMPLOYEE ADD CONSTRAINT PK_EMP_ID PRIMARY KEY (EMP_ID);
CREATE TABLE M_GLOBAL_ADDRESS (
G_ADD_ID NUMBER(10),
EMP_ID NUMBER(10),
ADDRESS VARCHAR2(500 CHAR)
);
/
ALTER TABLE M_GLOBAL_ADDRESS ADD CONSTRAINT PK_G_ADD_ID PRIMARY KEY (G_ADD_ID);
ALTER TABLE M_GLOBAL_ADDRESS ADD CONSTRAINT FK_G_ADD_EMP_ID FOREIGN KEY (EMP_ID) REFERENCES M_EMPLOYEE(EMP_ID);
INSERT INTO M_EMPLOYEE VALUES (1, 'Arun');
INSERT INTO M_EMPLOYEE VALUES (2, 'Pandi');
INSERT INTO M_GLOBAL_ADDRESS VALUES (1, 1, 'Address 1');
CREATE OR REPLACE FUNCTION fGetEmpAddress
(
pEmpId IN M_EMPLOYEE.EMP_ID%TYPE
) RETURN VARCHAR2
AS
pAddress M_GLOBAL_ADDRESS.ADDRESS%TYPE;
BEGIN
SELECT
Address
INTO
pAddress
FROM
M_GLOBAL_ADDRESS
WHERE
EMP_ID = pEmpId;
RETURN pAddress;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'Address Not Found');
END fGetEmpAddress;
/
SHOW ERRORS FUNCTION fGetEmpAddress
/
CREATE OR REPLACE PACKAGE PKG_EMP
AS
TYPE tEmployee IS RECORD (
Id M_EMPLOYEE.EMP_ID%TYPE,
Name M_EMPLOYEE.EMP_NAME%TYPE,
Address M_GLOBAL_ADDRESS.ADDRESS%TYPE
);
TYPE cEmployee IS REF CURSOR RETURN tEmployee;
FUNCTION fGetEmployeeDtls(
pcEmployee OUT cEmployee
) RETURN NUMBER;
END PKG_EMP;
/
SHOW ERRORS PACKAGE PKG_EMP
/
CREATE OR REPLACE PACKAGE BODY PKG_EMP
AS
FUNCTION fGetEmployeeDtls(
pcEmployee OUT cEmployee
) RETURN NUMBER
AS
vReturn NUMBER(1) := 0;
BEGIN
OPEN pcEmployee FOR
SELECT
EMP_ID,
EMP_NAME,
fGetEmpAddress(EMP_ID)
FROM
M_EMPLOYEE;
RETURN vReturn;
EXCEPTION
WHEN OTHERS THEN
vReturn := 1;
RETURN vReturn;
END fGetEmployeeDtls;
END PKG_EMP;
/
SHOW ERRORS PACKAGE BODY PKG_EMP
/
If you are running the below select you will get the error.
SELECT
EMP_ID,
EMP_NAME,
fGetEmpAddress(EMP_ID)
FROM
M_EMPLOYEE;
ORA-20001: Address Not Found
ORA-06512: at "RFP_ADM.FGETEMPADDRESS", line 20
Same query was i used for a cursor variable in a function. while executing the function i can get the error.
DECLARE
vRet NUMBER(1);
pcEmployee PKG_EMP.cEmployee;
BEGIN
vRet := PKG_EMP.fGetEmployeeDtls(pcEmployee);
DBMS_OUTPUT.PUT_LINE(vRet);
END;
/
It seems query was not executed at the time of function execution.
While trying to access the records in the cursor pcEmployee i am getting the error.
What i have to do if i want to get the error while executing the function?
Thanks in advance.
[mod-edit: code tags added by bb; please start adding them yourself]
[Updated on: Fri, 18 March 2016 01:37] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Cursor Variable : Ref Cursor Behavior [message #649228 is a reply to message #649217] |
Fri, 18 March 2016 03:03 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The exception that is being raised, which is being handled by your WHEN OTHERS clause is actually NO_DATA_FOUND. There is a note about this in some older documentation. Please see the link (click then scroll down) and excerpt below. I could not find a similar note in more recent documentation, but I may have missed it. What I believe it means, in your case, is that, if your fGetEmpAddress causes a no_data_found exception, regardless of how you handle it, such as using when others, then the error may or may not be propagated back to whatever called it. So, when you use it in a simple select statement by itself, the error may be propagated, but when that same select statement is used within another function like your fGetEmployeeDtls, then it may not be propagated.
https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/07_errs.htm#i9355
"NO_DATA_FOUND A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table.
Because this exception is used internally by some SQL functions to signal that they are finished, you should not rely on this exception being propagated if you raise it within a function that is called as part of a query."
[Updated on: Fri, 18 March 2016 03:08] Report message to a moderator |
|
|
|
| Re: Cursor Variable : Ref Cursor Behavior [message #649231 is a reply to message #649228] |
Fri, 18 March 2016 04:46 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
What you are describing Barbara can't happen in this case because the WHEN OTHERS catches the error locally and transforms it into -20001
Actually the issue is that the function isn't run when you open the ref cursor.
Opening a ref cursor doesn't cause oracle to go and execute the query and store the result somewhere. It only starts executing the query when you fetch from the ref cursor. So the function isn't executed till that happens.
It becomes clear if I modify the function slightly:
CREATE OR REPLACE FUNCTION fGetEmpAddress
(
pEmpId IN M_EMPLOYEE.EMP_ID%TYPE
) RETURN VARCHAR2
AS
pAddress M_GLOBAL_ADDRESS.ADDRESS%TYPE;
BEGIN
SELECT
Address
INTO
pAddress
FROM
M_GLOBAL_ADDRESS
WHERE
EMP_ID = pEmpId;
dbms_output.put_line('running function at '||to_char(sysdate, 'HH24:MI:SS'));
RETURN pAddress;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'Address Not Found');
END fGetEmpAddress;
/
Then in sqlplus declare a ref cursor variable and open the ref cursor:
SQL> var x refcursor
SQL> set serveroutput on
SQL> DECLARE
vRet NUMBER(1);
BEGIN
vRet := PKG_EMP.fGetEmployeeDtls(:x);
dbms_output.put_line('ref cursor open at '||to_char(sysdate, 'HH24:MI:SS'));
END;
/ 2 3 4 5 6 7 8
ref cursor open at 09:35:53
PL/SQL procedure successfully completed.
Note the dbms_output I added to the function hasn't been run. Now lets fetch from the ref cursor:
SQL> print :x
ERROR:
ORA-20001: Address Not Found
ORA-06512: at "LIVE.FGETEMPADDRESS", line 21
no rows selected
running function at 09:35:59
That runs the function and throws the error.
If we remove the exception handler from the function:
CREATE OR REPLACE FUNCTION fGetEmpAddress
(
pEmpId IN M_EMPLOYEE.EMP_ID%TYPE
) RETURN VARCHAR2
AS
pAddress M_GLOBAL_ADDRESS.ADDRESS%TYPE;
BEGIN
SELECT
Address
INTO
pAddress
FROM
M_GLOBAL_ADDRESS
WHERE
EMP_ID = pEmpId;
dbms_output.put_line('running function at '||to_char(sysdate, 'HH24:MI:SS'));
RETURN pAddress;
END fGetEmpAddress;
/
And re-execute in sqlplus:
SQL> DECLARE
vRet NUMBER(1);
BEGIN
vRet := PKG_EMP.fGetEmployeeDtls(:x);
dbms_output.put_line('ref cursor open at '||to_char(sysdate, 'HH24:MI:SS'));
END;
/ 2 3 4 5 6 7 8
ref cursor open at 09:44:09
PL/SQL procedure successfully completed.
SQL> print :x
EMP_ID
----------
EMP_NAME
--------------------------------------------------------------------------------
FGETEMPADDRESS(EMP_ID)
--------------------------------------------------------------------------------
1
Arun
Address 1
2
Pandi
EMP_ID
----------
EMP_NAME
--------------------------------------------------------------------------------
FGETEMPADDRESS(EMP_ID)
--------------------------------------------------------------------------------
running function at 09:44:14
SQL>
We now get the behaviour Barbara described
|
|
|
|
|
|
|
|
|
|
| Re: Cursor Variable : Ref Cursor Behavior [message #649316 is a reply to message #649283] |
Tue, 22 March 2016 09:20 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
I'm curious, why are you going out of your way to do your ref cursor the most complicated way you can. As Barbara showed a ref cursor using a simple select will work much faster and cleaner
CREATE OR REPLACE PACKAGE BODY PKG_EMP
AS
FUNCTION fGetEmployeeDtls(
pcEmployee OUT cEmployee
) RETURN NUMBER
AS
BEGIN
OPEN pcEmployee FOR
SELECT
EMP_ID,
EMP_NAME,
NVL (address, 'Address Not Found') address
FROM m_global_address b,
M_EMPLOYEE a
where a.emp_id = b.emp_id(+)
order by a.emp_id;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RETURN 1;
END fGetEmployeeDtls;
END PKG_EMP;
[Updated on: Tue, 22 March 2016 09:24] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
