CODEWITHSUNDEEP
plsqloracle11g
sahar
sahar

Reputation: 569

How can I create and return a cursor from a plsql function?

I created a plsql function and I want to create a cursor and return this cursor from the function. Then I want to call this function in a Java class and retrieve data from the cursor. Note: the cursor returns one row. I wrote something like this,,

CREATE OR REPLACE
FUNCTION FUNCTION1 ( emp_id IN NUMBER)RETURN cursor AS

  cursor newCursor(e_id number) is  
    select * from table1 where employee_id = e_id;        
    type refCursor is ref cursor;

  BEGIN

  open newCursor(emp_id);    
  loop
  exit when newCursor%notfound;   
    fetch newCursor into refCursor;  
  end loop;
  RETURN refCursor;

END FUNCTION1;

What return type should I use if I want to return a cursor?

Upvotes: 1

Views: 14895

Answers (2)

user3134422
user3134422

Reputation: 57

sys_refcursor is oracle's generic implicit cursor use EXPLICITE cursor instead

Upvotes: 0

josephj1989
josephj1989

Reputation: 9709

Model it after the following function which works

create or replace function getemps return sys_refcursor is
v_curs sys_refcursor;
begin
open v_curs for select ename from emp;
return v_curs;
end;
/

Upvotes: 7

Related Questions