You can call this function in another subprogram like below. But can you please explain your requirement so we can able give a better solution
create or replace function fun_test (v_sal in number) return sys_refcursor is
open v_cur for select * from emp where sal>=v_sal;
CREATE OR REPLACE FUNCTION test_fun1
OPEN v_in FOR SELECT fun_test(100) FROM dual;
Message was edited by: 000000
A cursor (reference, explicit or implicit) is NOT a physical result set!
A cursor is like a program - that outputs data. Each fetch call made, executes that cursor, resulting in it finding the next matching row(s) in the database, and returning these to the caller.
A ref cursor is a pointer to the cursor created in server memory. This pointer can then be passed by the code that created the cursor (typically PL/SQL), to the calling code (typically .Net or Java) - enabling that client code to execute that cursor and receive its output data (if any).
SQL> create or replace procedure FooProc( nameMask in varchar2, refCur in out sys_refcursor ) is 2 begin 3 open refCur for 4 select deptno, count(*) from emp where ename like nameMask group by deptno 5 order by 2 desc, 1 asc; 6 end; 7 / Procedure created. SQL> SQL> SQL> -- typical use of ref cursor interface from client SQL> -- SQL> -- client defines a host variable to receive pointer SQL> var c refcursor SQL> SQL> -- client makes PL/SQL call using bind variables SQL> exec FooProc( 'A%', :c ) PL/SQL procedure successfully completed. SQL> SQL> -- client processes cursor pointer (fetch and SQL> -- display in this case) SQL> print c DEPTNO COUNT(*) ---------- ---------- 20 1 30 1 SQL> SQL> SQL> -- from a PL/SQL perspective a ref cursor would SQL> -- be handled as follows: SQL> declare 2 deptNo integer; 3 empCount integer; 4 c sys_refcursor; 5 begin 6 FooProc( 'A%', c ); 7 loop 8 -- bulk fetch should be used 9 fetch c into deptNo, empCount; 10 exit when c%NotFound; 11 12 dbms_output.put_line( 13 'There is/are '||empCount|| 14 ' matching employee(s) in department '||deptNo 15 ); 16 end loop; 17 close c; 18 end; 19 / There is/are 1 matching employee(s) in department 20 There is/are 1 matching employee(s) in department 30 PL/SQL procedure successfully completed. SQL>