14 Replies Latest reply: Jul 25, 2014 12:33 AM by Billy~Verreynne RSS

    Collection of cursors

    NewLearner

      Hi Gurus,

       

      Is there any way/approach/collection where I can store cursors.

      I am calling a procedure in a loop and that procedure is returning me a cursor. At the end I have to pass all the cursors to the client.

      My problem is all the cursors returned are dependent on input string and neither the columns nor the tables where the select is being made is same. How can I code this thing in oracle?

      how can I have a collection of cursors?

        • 1. Re: Collection of cursors
          34MCA2K2

          Sorry your question is not clear. Can you share your sample code? PL/SQL 101 : Understanding Ref Cursors should help you.

           

          Regards,

          • 2. Re: Collection of cursors
            NewLearner

            I don't have a code yet . this is my clients requirement.

             

            Procedure A is calling procedure B in a loop.

            Procedure B is returning a refcursor based on the input passed.

            how can my procedure A have list of all cursors returned as an output parameter?

            Can we store cursors as a collection? If so how?

            • 3. Re: Collection of cursors
              34MCA2K2
              Can we store cursors as a collection? If so how?

              No We cannot. Probably because Things can be done in a simpler way If you explain the requirement plainly without thinking about the solution, Experts here can help you out.

               

              Regards,

              • 4. Re: Collection of cursors
                Mike Kutz

                Why do you keep asking the same question?

                collection of cursors
                Dynamic cursor!!!

                 

                Please continue discussion in one of the other threads.

                 

                MK

                • 5. Re: Collection of cursors
                  kendenny

                  I'm curious what kind of client is calling procedure A and what type of parameter it's using. I don't know of any client language which can accommodate a collection of ref cursors.

                  • 6. Re: Collection of cursors
                    rp0428
                    Is there any way/approach/collection where I can store cursors.

                    No - there is no 'cursor' datatype.

                    I am calling a procedure in a loop and that procedure is returning me a cursor.

                    Then you likely have a major flaw in your architecture since that is NOT a typical use of PL/SQL.

                    At the end I have to pass all the cursors to the client.

                    Then you need to have an OUT parameter for each of the cursors.

                    My problem is all the cursors returned are dependent on input string and neither the columns nor the tables where the select is being made is same. How can I code this thing in oracle?

                    That also sounds like a MAJOR architectural flaw. Post sample code that shows the entire process from beginning to end so we can see what you are trying to do,

                    how can I have a collection of cursors?

                    You can't - nor should you need such a thing.

                    • 7. Re: Collection of cursors
                      Billy~Verreynne

                      rp0428 wrote:

                      Is there any way/approach/collection where I can store cursors.

                      No - there is no 'cursor' datatype.

                       

                      There actually is - integer. Use DBMS_SQL to create a cursor and have it return an integer as the cursor's handle to you.

                       

                      So in theory one can create a collection or array of integers, where the integer is a cursor handle.

                       

                      And DBMS_SQL enables one to convert that cursor int handle to a sys_refcursors. So indirectly the int array is a collection of ref cursors.

                       

                      Whether this approach is sensible in anyway, is of course the question.

                      • 8. Re: Collection of cursors
                        Billy~Verreynne

                        NewLearner wrote:

                         

                        Is there any way/approach/collection where I can store cursors.

                         

                        How would you use a collection of cursors? What problem will it solve?

                        • 9. Re: Collection of cursors
                          rp0428

                          This is the requirement that OP posted.

                          I am calling a procedure in a loop and that procedure is returning me a cursor.

                          So how would you call that function and save that returned 'cursor' into a variable/collection?

                          • 10. Re: Re: Collection of cursors
                            Billy~Verreynne
                            
                            SQL> create or replace type TSqlCursor is object(
                              2          handle  integer,
                              3          name    varchar2(100),
                              4  
                              5          constructor function TSqlCursor( c in out sys_refcursor, curName varchar2 default null ) return self as result,
                              6          member function GetRefCursor( self in out TSqlCursor ) return sys_refcursor
                              7  );
                              8  /
                            
                            Type created.
                            
                            SQL> 
                            SQL> create or replace type body TSqlCursor is
                              2          constructor function TSqlCursor( c in out sys_refcursor, curName varchar2 default null ) return self as result is
                              3          begin
                              4                  self.handle := DBMS_SQL.to_cursor_number( c );
                              5                  self.name := curName;
                              6                  return;
                              7          end;
                              8  
                              9          member function GetRefCursor( self in out TSqlCursor ) return sys_refcursor is
                             10          begin
                             11                  return(
                             12                           DBMS_SQL.to_refcursor( handle )
                             13                  );
                             14          end;
                             15  end;
                             16  /
                            
                            Type body created.
                            
                            SQL> 
                            SQL> create or replace type TSqlCursorArray is table of TSqlCursor;
                              2  /
                            
                            Type created.
                            
                            SQL> 
                            SQL> 
                            SQL> 
                            SQL> var c1 refcursor
                            SQL> var c2 refcursor
                            SQL> var c3 refcursor
                            SQL> 
                            SQL> declare
                              2          curArray        TSqlCursorArray;
                              3          c               sys_refcursor;
                              4  begin
                              5          curArray := new TSqlCursorArray();
                              6  
                              7          -- create a ref cursor and add it to the collection
                              8  
                              9          open c for select * from emp order by 1;
                             10          curArray.Extend(1);
                             11          curArray(curArray.Count) := new TSqlCursor(c);
                             12  
                             13          open c for select * from dept order by 1;
                             14          curArray.Extend(1);
                             15          curArray(curArray.Count) := new TSqlCursor(c);
                             16  
                             17          open c for select count(*) from emp;
                             18          curArray.Extend(1);
                             19          curArray(curArray.Count) := new TSqlCursor(c);
                             20  
                             21          -- grab the 3 cursors from the collection and return
                             22          -- it as ref cursors
                             23          :c1 := curArray(1).GetRefCursor();
                             24          :c2 := curArray(2).GetRefCursor();
                             25          :c3 := curArray(3).GetRefCursor();
                             26  end;
                             27  /
                            
                            PL/SQL procedure successfully completed.
                            
                            SQL> 
                            SQL> print c1
                            
                                 EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
                            ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
                                  7369 SMITH      CLERK           7902 1980/12/17 00:00:00        800                    20
                                  7499 ALLEN      SALESMAN        7698 1981/02/20 00:00:00       1600        300         30
                                  7521 WARD       SALESMAN        7698 1981/02/22 00:00:00       1250        500         30
                                  7566 JONES      MANAGER         7839 1981/04/02 00:00:00       2975                    20
                                  7654 MARTIN     SALESMAN        7698 1981/09/28 00:00:00       1250       1400         30
                                  7698 BLAKE      MANAGER         7839 1981/05/01 00:00:00       2850                    30
                                  7782 CLARK      MANAGER         7839 1981/06/09 00:00:00       2450                    10
                                  7788 SCOTT      ANALYST         7566 1987/04/19 00:00:00       3000                    20
                                  7839 KING       PRESIDENT            1981/11/17 00:00:00       5000                    10
                                  7844 TURNER     SALESMAN        7698 1981/09/08 00:00:00       1500          0         30
                                  7876 ADAMS      CLERK           7788 1987/05/23 00:00:00       1100                    20
                                  7900 JAMES      CLERK           7698 1981/12/03 00:00:00        950                    30
                                  7902 FORD       ANALYST         7566 1981/12/03 00:00:00       3000                    20
                                  7934 MILLER     CLERK           7782 1982/01/23 00:00:00       1300                    10
                            
                            14 rows selected.
                            
                            SQL> print c2
                            
                                DEPTNO DNAME          LOC
                            ---------- -------------- -------------
                                    10 ACCOUNTING     NEW YORK
                                    20 RESEARCH       DALLAS
                                    30 SALES          CHICAGO
                                    40 OPERATIONS     BOSTON
                            
                            SQL> print c3
                            
                              COUNT(*)
                            ----------
                                    14
                            
                            SQL>
                            
                            
                            
                            
                            • 11. Re: Re: Re: Collection of cursors
                              padders

                              Nice. If you altered the GetRefCursor method to remove the OUT parameter (I can only apologise for my archaic use of uppercase)...

                               

                              MEMBER FUNCTION getrefcursor
                                RETURN SYS_REFCURSOR
                              IS
                                l_cursor INTEGER := SELF.handle;
                              BEGIN
                                RETURN dbms_sql.to_refcursor (l_cursor);
                              END;
                              

                               

                              ...it would be callable from SQL and you could return the array as a cursor, e.g.

                               

                              SQL> DECLARE
                                2    curarray tsqlcursorarray;
                                3  BEGIN
                                4    curarray := NEW tsqlcursorarray();
                                5
                                6    OPEN :c FOR
                                7        SELECT empno, ename, job
                                8        FROM  emp;
                                9
                              10    curarray.EXTEND(1);
                              11    curarray(curarray.COUNT) := NEW tsqlcursor(:c, 'EmpCursor');
                              12
                              13    OPEN :c FOR
                              14        SELECT deptno, dname
                              15        FROM  dept
                              16        ;
                              17    curarray.EXTEND(1);
                              18    curarray(curarray.COUNT) := NEW tsqlcursor(:c, 'DeptCursor');
                              19
                              20    OPEN :c FOR
                              21        SELECT t.name, VALUE(t).getrefcursor() handle
                              22        FROM  TABLE (curarray) t;
                              23  END;
                              24  /
                              PL/SQL procedure successfully completed.
                              
                              SQL>
                              

                               

                              Which could be used to return the 'array' of cursors to a client...

                               

                              SQL> PRINT c;
                              NAME                HANDLE
                              -------------------- --------------------
                              EmpCursor            CURSOR STATEMENT : 2
                              CURSOR STATEMENT : 2
                                  EMPNO ENAME      JOB
                              ---------- ---------- ---------
                                    7369 SMITH      CLERK
                                    7499 ALLEN      SALESMAN
                                    7521 WARD      SALESMAN
                                    7566 JONES      MANAGER
                                    7654 MARTIN    SALESMAN
                                    7698 BLAKE      MANAGER
                                    7782 CLARK      MANAGER
                                    7788 SCOTT      ANALYST
                                    7839 KING      PRESIDENT
                                    7844 TURNER    SALESMAN
                                    7876 ADAMS      CLERK
                                    7900 JAMES      CLERK
                                    7902 FORD      ANALYST
                                    7934 MILLER    CLERK
                              14 rows selected.
                              DeptCursor          CURSOR STATEMENT : 2
                              CURSOR STATEMENT : 2
                                  DEPTNO DNAME
                              ---------- --------------
                                      10 ACCOUNTING
                                      20 RESEARCH
                                      30 SALES
                                      40 OPERATIONS
                              
                              SQL>
                              
                              • 12. Re: Collection of cursors
                                Billy~Verreynne

                                Interesting.

                                 

                                The reason for explicitly defining self as a out param is that it exists as an implicit in parameter for member functions. So one cannot set self in a function by default - which makes sense as functions are typically not setters. For member procedures, self is an implicit out parameter too. Which again makes sense as these are usually setters.

                                 

                                I did thought (when writing it) to make the function as you did - copying the cursor handle to a local variable and thus not having to have to set the member property in the function. Did not like it - for no particular reason. Or thought of using the member function from SQL (as you so nicely did)...

                                 

                                But looking at all this - interesting, but mostly useless in my view. I do not see a practical use for this. In fact, something like this (collections of cursors) in production would be kind of scary.

                                • 13. Re: Collection of cursors
                                  rp0428

                                  All of that is interesting but that is a custom type and is NOT a procedure that returns a cursor. So IMHO that is NOT what OPs use case is.

                                  I am calling a procedure in a loop and that procedure is returning me a cursor.

                                  That is the classic stored procedure that uses an OUT parameter that is a a cursor and opens that cursor in the procedure.

                                   

                                  Similar to Example 6-29 Stored Procedure to Open a Ref Cursor in the Oracle docs.

                                  Using Static SQL

                                  Example 6-29 Stored Procedure to Open a Ref Cursor

                                  CREATE PACKAGE emp_data AS
                                    TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
                                    PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp);
                                  END emp_data;
                                  /
                                  CREATE PACKAGE BODY emp_data AS
                                    PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
                                    BEGIN
                                      OPEN emp_cv FOR SELECT * FROM employees;
                                    END open_emp_cv;
                                  END emp_data;
                                  /

                                  So OPs question is: how do you create a collection of those cursors that are returned by that procedure.

                                  1. call the procedure once - put the cursor into a collection

                                  2. call the procedure again - put that new cursor into a collection

                                  3. and so on

                                   

                                  That is the question I think OP was asking. Ridiculous - I agree

                                  • 14. Re: Collection of cursors
                                    Billy~Verreynne

                                    The sample approach I provided will work for any ref cursor created by function or procedure, or returned by function or procedure, or in a loop. So in my view it does answer the OP's question regarding creating a ref cursor collection.