What is ref cursor in pl sql

what is ref cursor in pl sql

Difference between cursor and a ref cursor

Introduction to PL/SQL cursor variables A cursor variable is a variable that references to a cursor. Different from implicit and explicit cursors, a cursor variable is not tied to any specific query. Meaning that a cursor variable can be opened for any query. A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a REF CURSOR is a pointer or a handle to a result set on the database. REF CURSOR s are represented through the OracleRefCursor likedatingen.com class. REF CURSOR s have the following characteristics.

Answer: Here are some working examples of ref cursors. A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result. The advantage that a ref cursor has over a plain cursor is that is can be passed as a variable to a procedure or a function.

This is a powerful capability in that the cursor can be opened, then passed to another block for processing, then returned to the original block to be closed.

The cursor variable can also be returned by a function and assigned to another variable. Before assigning a cursor variable, a cursor type must be defined. Below is the same cursor that is strongly typed. By how to fold a hat from paper typing the cursor variable, you define what the cursor can return.

A strongly typed cursor type is less flexible but how to make falling snow prone to programming errors.

Once the cursor type is defined, the how to put bearings in wheels variable can be defined as the cursor type. Now c1 is a variable of a cursor type. It is opened using a SQL statement. Now c1 has all the attributes of the actual cursor. As with any cursor it is important to close the cursor as soon as you have completed processing. The Cursor is open. Row Count is 1 The Cursor is closed.

Here some of the cursor attributes are used to process the cursor. Even though the example how to put movies on my zune variable is defined, the record can not use it because the cursor variable return type is not defined.

Because this is defined in line 2, the record defined in line 4 can now use it. Cursor variables that are weakly typed can be defined to return any values. In the example below, the cursor variable c1 is defined as three different statements. Although the block does not do anything but open and close the cursor variable, it does demonstrate that weakly typed variables can be defined differently each time the variable is opened. But what happens when a cursor variable is defined with a SQL statement but returns unexpected values?

Below is an example of defining a cursor variable of one type and the record as another. The cursor variable is returning all columns in the book table, but the receiving variable is defined as a record of columns from the author table. That is because the block successfully compiled and only threw the error when it was executed.

By changing the definition of the cursor type to a strongly typed definition, the compiler will catch this error when the code is compiled. Now the compiler catches the error. So far the examples have been using the cursor variables as regular cursors. The real advantage of using a cursor variable is the ability to pass it as a parameter.

In the example below a local function is used to open a cursor called c1 and return it. The block body calls the function to assign the cursor to the cursor variable c2. The block body then processes the cursor and closes it. Line 2 defines the cursor type. Lines 3 and 4 define the cursor and return record used in the body. The cursor c2 is processed and finally closed on line Note that c1 opened the cursor and c2 closed it. This is an important point.

The example contains only ONE cursor. When c2 is assign the value of c1both variables point to the same cursor. Remember that c1 and c2 are variables that point to or reference the actual cursor. The same basic example is shown below except, the output is generated by a local procedure. First, the body is pretty simple to understand. Get the authors, print the names, and close the cursor.

Second, you can pass cursor variables to procedures and functions, and functions can return the variables. Lastly, it can become confusing about when a cursor variable should be closed. Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA how to deposit check at atm tuning consulting professionals.

Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback.

Just e-mail: and include the URL for the page. All rights reserved by Burleson. Can I see some examples of working ref cursor examples, especially passing a ref cursor between two programs? Search BC Oracle Sites.

Blog Archive

PL/SQL Ref Cursors examples A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result. The advantage that a ref cursor has over a plain cursor is that is can be passed as a variable to a procedure or a function. The REF CURSOR can be assigned to other REF CURSOR variables. Ref Cursor in Oracle PL/SQL. PM PL/SQL No comments. A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. This example demonstrates how to define and open a REF CURSOR variable, and then pass it as a procedure parameter. The cursor variable is specified as an IN OUT parameter so that the result set is made available to the caller of the procedure.

There he asked one question as what is the difference between cursor and ref cursor, and when would you appropriately use each of these. Could you please tell me how can I effectively answer this question in short. Your input will be greatly appreciated. Thanks and regards, Praveen. A "normal" plsql cursor is static in defintion. Ref cursors may be dynamically opened or opened based on logic. The ref cursor can be anything.

Another difference is a ref cursor can be returned to a client. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account. Questions Difference between cursor and a ref cursor. Question and Answer. You Asked Hi Tom, Feels good that i got a chance to ask you a question.

Good Explain Tom last week I saw a review on this site in which writer name with unknown language to me. Here I code the name and title of review in my mother language "URDU-Pakistan" do you see it and most important if yes, that How I configure oracle forms and reports for this. December 07, - am UTC. Thanks tom for that great answer with an example. This will be really helpful.

You are simply the best.. I wanted to know the same difference and your explanation is what I needed. Thanks a lot for being there and help so many people. Excellent explanation. Thanks Much. Absolutely understandable I have done it in Arabic. It really works! A reader, December 29, - pm UTC. December 29, - pm UTC. I learned at least two things, a better grasp of when to use reference cursors, and how to push unrelated questions onto the stack.

It is really great to see these differences, but why these were not explained in Oracle Books so simple.. Devesh joshi, March 24, - am UTC. Thanks for helping me out. Clear and comprehensive. No wonder I love your book - Expert One-on-One. I ask about how to change my forms and reports to read in arabic plz send 2 me thx.

November 30, - am UTC. This answer gave me the clear difference between cursors and ref cursors I was looking for. Thank you very much Tom. Krit, April 27, - am UTC. I have no words to describe how simply the concept was explained. I was very confused till now. Thanks Tom,Absoloutely wonderful!! Nice difference Gaurav C. Pandey, May 14, - am UTC. Now i am able to distingiush between Ref. Absolutely Wonderful!! Really happy to get such concepts cleared.

Hi Tom, Most useful, but I need one clarificaiton though. ASP vbscript will loop through recordset for display. In this case there is no need to use Ref Cursor to pass data to client. Please comment. April 06, - am UTC. You will be using a ref cursor to return a result set to vb.

Ram, April 06, - am UTC. April 07, - pm UTC. In Oracle - the ref cursor is the way to do it. Hi Tom, Understood Sir.. I am getting ready for myself, since there is a possibility to migrate to Oracle.

Thanks Regards, Ram. Hi Tom, Super explanation. I also thank Praveen who started this. Clear simple explanation kannan, May 18, - am UTC. Thanks for the simple and clear explanation of the differences between Cursors and Ref Cursors.

I really enjoyed it. I am clear with ref cursor no, that was great. I understand the use of it when i am calling any function through front end VB etc But in my project, i am calling functions through UNIX only. Thanks in advance. May 25, - pm UTC. Tom, How does Cursors and bulk collect differ? Which one is better and why? Hope my question makes sense..

I am just trying to understand the fundamental concepts [:- ] after working in Oracle for many years.. This difference is really very helpful for developers. Thanks, Chandra. Tom, Unfortunately, I do not have access to that book right away.

But I promise I'm going to buy after these holidays ;- Thanks,. May 27, - pm UTC. A cursor and bulk collect do not "differ" In order to bulk collect - you need a cursor. Explanation of difference between cursor and ref cursor is too good. Archana, December 07, - am UTC. Hi Tom, I am a new visistor to this site. I was really looking for the difference between cursor and ref cursor. December 11, - pm UTC.

Can you explain this in detail? A reader, December 12, - am UTC. Tom, You have given differences in original answer above , but can you explain more on this point? December 12, - pm UTC. December 22, - am UTC. But at the end of the day - it is processed not any differently by Oracle than 'dynamic sql' is - because it really is dynamic sql under the covers.

Its very good explanation. It helps me a lot in understanding cursors. I have one doubt. I didn't get it. Thanks and regards Gowtham sen. December 24, - am UTC.

Great answer!!!

What is ref cursor in pl sql: 4 comments

Add a comment

Your email will not be published. Required fields are marked *