returning value to cursor plsql

Question

Hi I have a procedure which return a recordset using cursor in output what am trying to do is i use cursor to get the data and same have to return it to output cursor .I can do like below

PROCEDURE test(value_one IN someTabel.somecolumn%TYPE,                             
           valu_two   IN someTabel.somecolumn%TYPE,
           Outputcursor OUT SYS_REFCURSOR) IS
mydeclaration goes here 
output_value_one sometable.somecolumn%Type;
---
cursor test_select is 
select statement
begin 
for val in test_select loop 
fetch test_select into output_value_one; -- I want my cursor outputcursor to     be return instead of output_value_one 
end loop;
end;

Answer 1

You send the deptno the cursor return the query where you can handle from frontend.

 create or replace Procedure Transproc(p_deptno    IN  emp.deptno%TYPE,
                        Outputcursor Out Sys_Refcursor,
                        p_recordset Out Varchar) 
            Is
            Vquery varchar2(200);
            Begin
            Vquery:='select * from emp where deptno='|| p_deptno ||'';

            Open Outputcursor For Vquery;
            OMessage:='Success';
            Exception 
            When others then 
            OMessage:='Fail';
            End;

OR try below one as per your requriement.

CREATE OR REPLACE PROCEDURE get_emp_rs (p_deptno    IN  emp.deptno%TYPE,
                      p_recordset OUT SYS_REFCURSOR) AS 
BEGIN 
  OPEN p_recordset FOR
    SELECT ename,
           empno,
           deptno
    FROM   emp
    WHERE  deptno = p_deptno
    ORDER BY ename;
END GetEmpRS;
/

To test :-

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_cursor  SYS_REFCURSOR;
  l_ename   emp.ename%TYPE;
  l_empno   emp.empno%TYPE;
  l_deptno  emp.deptno%TYPE;
BEGIN
  get_emp_rs (p_deptno    => 30,
              p_recordset => l_cursor);

  LOOP 
    FETCH l_cursor
    INTO  l_ename, l_empno, l_deptno;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
  END LOOP;
  CLOSE l_cursor;
End;
/