1 2 Previous Next 21 Replies Latest reply: Sep 13, 2013 6:09 AM by bencol RSS

    PL Cursors


      I have a couple of questions about cursors. If I want to return records in a ref cursor variable, and insert a sub set of the fields into a new table, can I do that in one loop ? How does the cursor get closed ? If a cursor is then closed is all the data gone, or do I have to wait until the cursor variable gets out of scope ? How can I identify open cursors within the database ?

      Package spec:

      TYPE family is RECORD


        first_name VARCHAR2(50),

        last_name VARCHAR2(50),

        title VARCHAR2(50)


      TYPE family_CV is REF CURSOR RETURN family;

      PROCEDURE insertNewRecord;


      PROCEDURE insertNewRecord


        familyInfo family_CV;


        OPEN familyInfo FOR

        SELECT first_name,last_name,title AS abc

        FROM employees;

      END insertNewRecord;

        • 1. Re: PL Cursors

          Some basics first.


          Cursors are SQL compiled programs. A cursor is basically (shared) executable code. The execution plan of a cursor is the flowchart of the program.


          An open cursor command creates a private copy of this program. The code is shareable (SQL shared pool). The data however for this program is private and pertains only to your cursor variable (UGA memory). Kind of like a Windows DLL or a Linux/Unix shared lib. One instance of the DLL in memory. DLL code uses the data segment of the caller.


          A fetch cursor command executes the cursor program. It runs. It finds rows. It outputs rows. It maintains it state data to know from where to continue finding rows when you call fetch again.


          A close cursor command releases your private copy of the cursor program.


          All SQLs are parsed, stored, and executed as cursors.


          Cursors are not result sets in memory. Cursors do not hold data. Cursors outputs data - following the fundamental program execution principle that existed since the dawn of computers. Input. Processing. Output.


          In the cursor's case, input is binding values to the variables of the cursor (kind of like passing parameters as input a program). Processing and output are repeated by cursor fetching - until the processing finds no more data (which will set the cursor state variable to indicate no-data-found).


          A ref cursor is a pointer to a cursor program on the server - allowing PL/SQL to create the cursor and the pass a reference to it to a client (e.g. Java or C#) that can make cursor calls using it.


          So how does what you want to do, relate to ref cursors and cursors as programs?

          • 2. Re: PL Cursors
            Nikolay Savvinov



            if you care about performance of your application then you don't want any looped inserts. What you want is INSERT/SELECT statements which are fast and efficient.


            If you absolutely cannot do without PL/SQL, at least use FETCH .. BULK COLLECT INTO ... and INSERT ALL. But this is generally inferior to pure SQL from the performance and scalability points of view.


            Best regards,


            • 3. Re: PL Cursors

              To further support what Billy explained, please read this:


              PL/SQL 101 : Understanding Ref Cursors

              • 4. Re: PL Cursors



                Thanks for the reply, how would i do a single select statement into a ref cursor that would return to a calling program (Java / ,NET) and insert a subset of the fields into another table ? I am convinced that I need a select statement and then loop through the cursor.

                • 5. Re: PL Cursors



                  Thanks for your reply.


                  So, if I have a cursor, which allows a Java / .NET program to proccess the result set, if I fetch the data first and insert a subset of the data into another table, how is the cursor reset to the beginning ?

                  • 6. Re: PL Cursors
                    Vite DBA



                    Why do you feel you need to loop through a cursor to achieve the desired result? If your sole purpose is to insert into a table the results of a definable query, then a simple insert/select statement is the easiest, most performant method.




                    • 7. Re: PL Cursors

                      You need to explain "resetting the cursor to the beginning" - I'm not sure what you want to do.


                      Each and every single (valid) SQL is parsed and executed as a cursor. Cursor variables and cursor data types, such as reference cursors, implicit and explicit cursors, are interfaces (in the client language) for using server-based SQL cursors.


                      To "reset" a cursor typically means closing the cursor. However, the same SQL cursor can be used and reused repeatedly - and this is the preferred method in a client language like C# or Java.


                      For example, a single cursor can be used over and over again for inserting data:

                      create cursor variable
                      parse cursor( 'insert into footable(col1,col2) values(:1,:2)' )
                      bind client var1,var2 to cursor variables :1 and :2
                      while some-flag is true
                          var1 = some value read from file
                          var2 = some value read from file
                         execute cursor
                      close cursor

                      Keep in mind that a cursor IS NOT a result set.


                      And that if you want to move (insert/update) data in the database, from one table to another, use  a SINGLE cursor (single SQL statement like MERGE) to do it.


                      Do not create one cursor (program) to read data from the database into client variables, and then use another cursor (program) to read the client variables and insert/update database tables. This is slow. This does not scale.

                      • 8. Re: PL Cursors

                        The problem is I need to return data to the calling program and insert data into a separate table. The program below returns no data when the body procedure iterates through the ref cursor.

                        This document:


                        under Why user cursor variables, paragraph 3,

                        f you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side. Also, you can reduce network traffic by having a PL/SQL block open (or close) several host cursor variables in a single round trip.

                        Seems to indicate otherwise

                        -- Spec:

                        create or replace

                        package xyz_ref is

                        TYPE xyz is RECORD


                          first_name store.employees.first_name%TYPE,

                          last_name store.employees.first_name%TYPE


                        TYPE xyz_CV is ref cursor return xyz;

                        PROCEDURE insertRecords(abc IN OUT xyz_CV);


                        -- Body

                        create or replace

                        package BODY xyz_ref is

                        PROCEDURE insertRecords(abc IN OUT xyz_CV) is

                        def xyz;


                        open abc for

                        select first_name,last_name

                        from employees;



                          fetch abc into def;

                          exit when abc%Notfound;


                        END LOOP;


                        end insertRecords;

                        END xyz_ref;

                        -- Client

                        SET SERVEROUTPUT ON


                        test1 store.xyz_ref.xyz_CV;

                        testData store.xyz_ref.xyz;





                          fetch test1 into testData;

                          EXIT when test1%NOTFOUND;



                        END LOOP;




                        • 9. Re: PL Cursors

                          This is how a ref cursor interface should be used - using SQL*Plus as the client. (the client typically would be a Java or C# client)


                          SQL> -- server code
                          SQL> create or replace procedure FooProc( c out sys_refcursor ) is
                            2  begin
                            3          open c for select ename, empno from emp order by 1;
                            4  end;
                            5  /
                          Procedure created.
                          SQL> -- client variable
                          SQL> var c refcursor
                          SQL> -- client makes server call
                          SQL> exec FooProc( :c );
                          PL/SQL procedure successfully completed.
                          SQL> -- client processes cursor
                          SQL> print c
                          ENAME           EMPNO
                          ---------- ----------
                          ADAMS            7876
                          ALLEN            7499
                          BLAKE            7698
                          CLARK            7782
                          FORD             7902
                          JAMES            7900
                          JONES            7566
                          KING             7839
                          MARTIN           7654
                          MILLER           7934
                          SCOTT            7788
                          SMITH            7369
                          TURNER           7844
                          WARD             7521
                          14 rows selected.
                          • 10. Re: PL Cursors



                            Yes and that works - but in the oracle documentation I quoted it states that both client and server can fetch data from cursors..

                            • 11. Re: PL Cursors

                              so do I use two cursors one for the client and one for the insert ?

                              • 12. Re: PL Cursors

                                Correct - both client and server code use cursors. They can even use the same cursor. However, when a row is fetched from the cursor's output, that row is done and dusted. So if the PL/SQL code fetches it, it cannot be re-fetched by the client.


                                Standard processes concepts also dictates that PL/SQL code fetching some rows from the cursor, and the client code fetching the other rows from the same cursor, is a flawed approach.


                                As for using two cursors. Typically not. Effective processing means using as few cursors as possible.


                                Instead of using 2 cursors on the same data and hitting the same data twice - hit the data once with a single pass. Less I/O. Better performance. Better scalability.


                                Instead of using 2 cursors for reading (SELECT' cursor) and writing (INSERT/UPDATE cursor) data - use a single cursor (INSERT..SELECT, UPDATE (SELECT) or MERGE cursor). Less I/O. Better performance. Better scalability.

                                • 13. Re: PL Cursors

                                  user8167598 wrote:




                                  Yes and that works - but in the oracle documentation I quoted it states that both client and server can fetch data from cursors..


                                  As Billy says, yes, you can use a ref cursor on both client or server side, but you still seem to be missing the point that you may have got if you read the link I posted, and which seems to be indicated by the way your procedure name is called "insertrecords" when all it is really doing is opening a ref cursor.


                                  Get the idea out of your head that when you open a ref cursor then the ref cursor is populated with the data... it's not.


                                  When you open a ref cursor, the query is executed on the database and the rows "identified" (theoretically), but no data has been fetched back.

                                  As you loop around fetching, you are not fetching "from" the cursor, you are fetching "through" the cursor, and once you've fetched through the cursor, you cannot re-fetch the same data because it's already been fetched.


                                  As an analogy, consider there is a room full of people wearing different coloured t-shirts.

                                  You open the door and place an instruction on it to say you want people with Blue t-shirts.

                                  Then you ask for the first person to come through the door.

                                  Then you ask for the next person to come through the door.

                                  and so on.

                                  You cannot then ask for the first person to come through the door again because they've already come through.  To do that you'd have to shut the door and start again (by shutting the door the scenario is 'reset').


                                  So, opening the door is like opening a ref cursor, it creates an opening through which the required data can be obtained, but no data has yet come through that opening.

                                  As the people come through the door, this is the same as fetching rows of data.

                                  Once you close the door, you can restart the experiment again.


                                  Now, in terms of using the ref cursor on both client and server etc. that would be like you standing at the door asking for the first 2 people, and then passing your clipboard to another person who then asks for another X people to come through.  You get the first 2 people and the other person get's as many as they want, but they cannot have your 2.  In programming terms, as Billy says, this wouldn't be good practice, as you would have two disperate pieces of code dealing with portions of the same data.  It would be very rare to create such a situation.

                                  • 14. Re: PL Cursors



                                    Thanks for your reply.


                                    I wil try and get the idea out of my head. But I have a C/C/++ / Java bacground and have written llinked list programs (both single and double linked).


                                    Now re analogy, as people come in the room, I give the first one a ticket (a pointer to dynamically allocated memory), when the next one comes in  I also assign them memory, a unique ticketand link the two tickts both ways and son on. Then given any ticket, I can find previous tickets and tickets issuef after. Further I can invite friends in to read the list as well.. I can also reset as many times as I want by remembering the first ticket issued without affecting my friends position.


                                    When I ask the people to leave, I can then de-allocte the memory and re-link the list remaining people as necessary..

                                    1 2 Previous Next