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 ?
TYPE family is RECORD
TYPE family_CV is REF CURSOR RETURN family;
OPEN familyInfo FOR
SELECT first_name,last_name,title AS abc
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?
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.
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.
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.
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
create or replace
package xyz_ref is
TYPE xyz is RECORD
TYPE xyz_CV is ref cursor return xyz;
PROCEDURE insertRecords(abc IN OUT xyz_CV);
create or replace
package BODY xyz_ref is
PROCEDURE insertRecords(abc IN OUT xyz_CV) is
open abc for
fetch abc into def;
exit when abc%Notfound;
SET SERVEROUTPUT ON
fetch test1 into testData;
EXIT when test1%NOTFOUND;
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>
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.
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.
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..