Skip to Main Content
  • Questions
  • i face prob with the returned cursor from function

Breadcrumb

Question and Answer

Connor McDonald

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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library