7 Replies Latest reply: Dec 24, 2013 4:38 PM by rp0428 RSS

    How to call a procedure with SYS_REFCURSOR OUT parameter

    arizona9952

      Hi,

      Using Oracle 11g R2.

       

      I'd like to know if it is possible to display the results of a SYS_REFCURSOR in a query. For example, if I had the following stored procedure

       

       

       

      
      create or replace procedure testprocedure (result OUT sys_refcursor)
      as
      begin
         open result for
            select 1 from dual
            union all
            select 2 from dual;
      end;
      
      

       

      I'd like to call this procedure similar to the way a query is called and executed. Like this

       

      select * from testprocedure

       

      I've seen plenty of examples on the web which show how it is possible to loop through results of a sys_refcursor inside of an anonymous block and display the results using dbms_output.putline, but this isn't the method I am looking for.

        • 1. Re: How to call a procedure with SYS_REFCURSOR OUT parameter
          Partha Sarathy S

          You have mentioned as SYS_REFCURSOR as OUT parameter, but you have mentioned OUT clause in procedure parameter..

          You won't be able to call a procedure from a SELECT statement.

          Instead of looping through you could straight away display results of SYS_REFCURSOR as below.(code modified)

           

          create or replace procedure testprocedure (result OUT sys_refcursor)

          as

          begin

             open result for

                select 1 from dual

                union all

                select 2 from dual;

          end;

          /

           

           

           

          variable x refcursor;

          exec testprocedure(:x);

          print :x;

           

          OUTPUT:

           

                   1

          ----------

                   1

                   2

          • 2. Re: How to call a procedure with SYS_REFCURSOR OUT parameter
            arizona9952

            Parth272025 wrote:

             

            You have mentioned as SYS_REFCURSOR as OUT parameter, but you have mentioned OUT clause in procedure parameter..

            You won't be able to call a procedure from a SELECT statement.

            Instead of looping through you could straight away display results of SYS_REFCURSOR as below.(code modified)

             

            create or replace procedure testprocedure (result OUT sys_refcursor)

            as

            begin

               open result for

                  select 1 from dual

                  union all

                  select 2 from dual;

            end;

            /

             

             

             

            variable x refcursor;

            exec testprocedure(:x);

            print :x;

             

            OUTPUT:

             

                     1

            ----------

                     1

                     2

            Thanks. Yes, I forgot to place the OUT in the parameter list. Would I be able to call it if the testprocedure was a function?

            • 3. Re: How to call a procedure with SYS_REFCURSOR OUT parameter
              rp0428
              I'd like to know if it is possible to display the results of a SYS_REFCURSOR in a query. For example, if I had the following stored procedure

              No - you can only use schema object types (SQL) in SQL queries and only then if you call a function.

               

              The function can return a SQL collection type or it can be a PIPELINED function whose return value is a SQL collection type. Either way your query will use the TABLE function and be of the form:

              select * from TABLE(testfunction);

              This is sample code for a PIPELINED function based on the SCOTT.EMP table. The function takes a department number parameter and returns the EMP rows for that department:

              -- type to match emp record
              create or replace type emp_scalar_type as object
                (EMPNO NUMBER(4) ,
                 ENAME VARCHAR2(10),
                 JOB VARCHAR2(9),
                 MGR NUMBER(4),
                 HIREDATE DATE,
                 SAL NUMBER(7, 2),
                 COMM NUMBER(7, 2),
                 DEPTNO NUMBER(2)
                )
                /

              -- table of emp records
              create or replace type emp_table_type as table of emp_scalar_type
                /

              -- pipelined function


              create or replace function get_emp( p_deptno in number )
                return emp_table_type
                PIPELINED
                as
                 TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
                  emp_cv EmpCurTyp;
                  l_rec  emp%rowtype;
                begin
                  open emp_cv for select * from emp where deptno = p_deptno;
                  loop
                    fetch emp_cv into l_rec;
                    exit when (emp_cv%notfound);
                    pipe row( emp_scalar_type( l_rec.empno, LOWER(l_rec.ename),
                        l_rec.job, l_rec.mgr, l_rec.hiredate, l_rec.sal, l_rec.comm, l_rec.deptno ) );
                  end loop;
                  return;
                end;
                /
               
              select * from table(get_emp(20))

              • 4. Re: How to call a procedure with SYS_REFCURSOR OUT parameter
                arizona9952

                rp0428 wrote:

                 

                I'd like to know if it is possible to display the results of a SYS_REFCURSOR in a query. For example, if I had the following stored procedure

                No - you can only use schema object types (SQL) in SQL queries and only then if you call a function.

                 

                The function can return a SQL collection type or it can be a PIPELINED function whose return value is a SQL collection type. Either way your query will use the TABLE function and be of the form:

                select * from TABLE(testfunction);

                Thanks RP. If I change my testprocedure to a function, could you please be specific about how I can call the function in a query?

                I have to keep the return type as a sys_refcursor.

                • 6. Re: How to call a procedure with SYS_REFCURSOR OUT parameter
                  BluShadow

                  Read this:  PL/SQL 101 : Understanding Ref Cursors

                   

                  You are mistaken if you think that a ref cursor is a result set of data that you can query from.

                  • 7. Re: How to call a procedure with SYS_REFCURSOR OUT parameter
                    rp0428

                    If I change my testprocedure to a function, could you please be specific about how I can call the function in a query? 

                    I have to keep the return type as a sys_refcursor.

                    What I said, and you quoted, was as specific as it gets:

                    The function can return a SQL collection type or it can be a PIPELINED function whose return value is a SQL collection type