Thanks for the question, amr.
Asked: March 17, 2018 - 5:18 pm UTC
Last updated: March 18, 2018 - 3:32 am UTC
Version: 11g
Viewed 1000+ times
You Asked
i face prob with the returned cursor from function ,i created function to return cursor ,and in the script ,i use the function to give me the cursor after that in loop i try to print all data that is in the cursor .
but it didnt work , and it say invalid cursor
that is my code :
------------------ creating the function ------------------------------
CREATE OR REPLACE FUNCTION GETDATA
(
P_MANAGER_ID IN NUMBER
) RETURN SYS_REFCURSOR IS
EMPS sys_refcursor;
V_LNAME VARCHAR2(20);
V_FNAME VARCHAR2(20);
V_TOTAL NUMBER;
BEGIN
OPEN EMPS for SELECT LAST_NAME,FIRST_NAME
FROM EMPLOYEES
WHERE MANAGER_ID = P_MANAGER_ID ;
CLOSE EMPS;
RETURN EMPS;
END GETDATA;
--------------------------------------------------------------
--------------------lister the result --------------------
SET SERVEROUTPUT ON;
DECLARE
LISTS SYS_REFCURSOR ;
V_LAST EMPLOYEES.LAST_NAME%TYPE;
V_FIRST EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
LISTS :=GETDATA(100);
LOOP
FETCH LISTS INTO V_LAST ,V_FIRST;
EXIT WHEN LISTS%NOTFOUND;
dbms_output.put_line(V_LAST||','||V_FIRST);
END LOOP;
END;
thanks ;
and Connor said...
SQL> CREATE OR REPLACE FUNCTION GETDATA
2 (
3 P_MANAGER_ID IN NUMBER
4 ) RETURN SYS_REFCURSOR IS
5 EMPS sys_refcursor;
6 V_LNAME VARCHAR2(20);
7 V_FNAME VARCHAR2(20);
8 V_TOTAL NUMBER;
9
10 BEGIN
11 OPEN EMPS for SELECT LAST_NAME,FIRST_NAME
12 FROM hr.EMPLOYEES
13 WHERE MANAGER_ID = P_MANAGER_ID ;
14 RETURN EMPS;
15 END GETDATA;
16 /
Function created.
SQL>
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 LISTS SYS_REFCURSOR ;
3 V_LAST hr.EMPLOYEES.LAST_NAME%TYPE;
4 V_FIRST hr.EMPLOYEES.FIRST_NAME%TYPE;
5 BEGIN
6 LISTS :=GETDATA(100);
7 LOOP
8 FETCH LISTS INTO V_LAST ,V_FIRST;
9 EXIT WHEN LISTS%NOTFOUND;
10 dbms_output.put_line(V_LAST||','||V_FIRST);
11 END LOOP;
12 close lists;
13 END;
14 /
Kochhar,Neena
De Haan,Lex
Raphaely,Den
Weiss,Matthew
Fripp,Adam
Kaufling,Payam
Vollman,Shanta
Mourgos,Kevin
Russell,John
Partners,Karen
Errazuriz,Alberto
Cambrault,Gerald
Zlotkey,Eleni
Hartstein,Michael
PL/SQL procedure successfully completed.
SQL>
SQL>
Is this answer out of date? If it is, please let us know via a Comment