This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Sep 13, 2013 4:09 AM by bencol RSS

PL Cursors

user8167598 Newbie
Currently Being Moderated

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;


Body:


PROCEDURE insertNewRecord

AS


  familyInfo family_CV;


BEGIN


  OPEN familyInfo FOR

  SELECT first_name,last_name,title AS abc

  FROM employees;


END insertNewRecord;

  • 1. Re: PL Cursors
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    Hi,

     

    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,

    Nikolay

  • 3. Re: PL Cursors
    BluShadow Guru Moderator
    Currently Being Moderated

    To further support what Billy explained, please read this:

     

    PL/SQL 101 : Understanding Ref Cursors

  • 4. Re: PL Cursors
    user8167598 Newbie
    Currently Being Moderated

    hi

     

    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
    user8167598 Newbie
    Currently Being Moderated

    Hi

     

    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 Pro
    Currently Being Moderated

    Hi,

     

    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.

     

    Regards

    Andre

  • 7. Re: PL Cursors
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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
    end-while
    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
    user8167598 Newbie
    Currently Being Moderated

    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:

    http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/06_ora.htm#1449


    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);


    END;


    -- Body

    create or replace

    package BODY xyz_ref is



    PROCEDURE insertRecords(abc IN OUT xyz_CV) is


    def xyz;


    begin


    open abc for

    select first_name,last_name

    from employees;


    /*

    LOOP

      fetch abc into def;

      exit when abc%Notfound;

    SYS.dbms_output.put_line(def.last_name);

    END LOOP;

    */

    end insertRecords;


    END xyz_ref;


    -- Client

    SET SERVEROUTPUT ON

    declare


    test1 store.xyz_ref.xyz_CV;

    testData store.xyz_ref.xyz;


    begin


    store.xyz_ref.insertRecords(test1);

    SYS.dbms_output.put_line('client');

    LOOP

      fetch test1 into testData;

      EXIT when test1%NOTFOUND;

     

    dbms_output.put_line(testData.first_Name);


    END LOOP;



    null;


    end;

    /


  • 9. Re: PL Cursors
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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>
    SQL> -- client variable
    SQL> var c refcursor
    SQL>
    SQL> -- client makes server call
    SQL> exec FooProc( :c );
    
    PL/SQL procedure successfully completed.
    
    SQL>
    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.
    
    SQL> 
    
  • 10. Re: PL Cursors
    user8167598 Newbie
    Currently Being Moderated

    Hi

     

    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
    user8167598 Newbie
    Currently Being Moderated

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

  • 12. Re: PL Cursors
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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
    BluShadow Guru Moderator
    Currently Being Moderated

    user8167598 wrote:

     

    Hi

     

    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
    user8167598 Newbie
    Currently Being Moderated

    Hi

     

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points