Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: Ref Cursor Question

    Hello expert people

    I have a question about oracle ref cursors. My company seems to be using ref cursors extensively in a lot of the application that has already been developed for internal use.

    I used to read from the MSSQL forum that cursors are computationally expensive. I am not sure how ref cursors work in oracle, i wonder if the same statement is applicable for oracle cursors?

    Here are what we are using cursors for.
    We use it for datarecord retrieval, programmatically as i understand we are retrieving the records row by row through our .net applications, logically it shouldn't have been done that way, since a request needs to be passed from the .net application to the oracle database server for every row that is retrieved, but the people that works here seem to be claiming that it works faster that way. I am a bit puzzled.

    The other question is if we don't use cursors, i don't know any other way to retrieve and entire table from the db to my .net application by calling a store procedure, can you experts please suggest how that might be done.

    I am new to oracle

    Cheers
    James

  2. #2
    Join Date
    Aug 2003
    Posts
    111
    Hi

    doesn't anyone have any comments?
    i hope my greeting "hello expert people" didn't scare off people.


  3. #3
    Join Date
    Sep 2003
    Posts
    16
    I don't know about being an "expert" but here goes :-)

    You've got essentially two choices for retrieving recordsets from stored procedures.

    Either you go down the ref cursor route, or you can declare an output parameter for every field in the result set you want back, as a table of <data_type>, indexed by binary integer. You loop through the cursor in the procedure, populating each PL/SQL table with the relevant value indexed by a counter, until you have processed all rows.

    The calling application sets up a counter and loops through the recordset, getting every row by the counter value. It's all a bit of a pain to code, having to package and unpackage the recordset at each end of the call.

    Personally, I always use ref cursors since it is much simpler to code in the procedure, with the added bonus that you don't have to change the procedure interface if another field gets added to the recordset definition. The caller program needs to get changed, but with a bit of luck it won't be you having to do the work ;-)

    So far I've not had any issues regarding poor performance either.

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    go for refcursors

    Great advice from Paul above!

    The refcursors are not slow at all, they are the fastest way to get data from Oracle actually. In fact, a customer of mine, which is really really huge and thinks always to performance, has standardized their Oracle code to "stored packages returning ref cursors".

    Or, you can listen to the gurus - that technique is actually the one advised by them, for the good reasons exposed by Paul AND for performance.

    That .net people have thought they are traveling the network for every record, instead of getting them all (in batch of 10-15) if they used a refcursor ? Not to mention the parsing, the gets for the blocks in the buffer cache everytime (and their latch), and so on ...

    Test is - on a single user machine, it will show great - until you go on multiuser one, and there it will shine!

    AL

  5. #5
    Join Date
    Aug 2003
    Posts
    111
    Cheers guys, Thanx for clearing me up.

    My company do use ref cursor extensively and that's the only way i know how to do retrieve an entire table using Oracle.

    Using MSSQL however entire tables can be retrieve with no fuss of packaging and unpackaging on each end. And from what I gathered, cursor usage is considered inefficient.

    James

  6. #6
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    Oracle is not SqlServer

    In Oracle either cursors or ref cursors are the fastest and best way to retrieve rows. This is one of the few cases on which one can sat something with absolute certainty.

    In SqlServer they probably consider cursors "inefficient" because SqlServer locks either the table or the "page" that contains the row(s) to be retrieved even when an application selects (read).

    So probably they prefer to read one row at a time in SqlServer to minimize the lock time, and maximize concurrency. But that is terribly slow anyway, and a pain to maintain.

    Oracle is totally different: in Oracle you never lock anything when you read, so you can use the best way: cursors or ref cursors, whatever suits you better.

    HTH
    Al

  7. #7
    Join Date
    Dec 2003
    Posts
    4

    Re: Ref Cursor Question

    I have never used ref cursors. Can anybody tell me how to cast a ref cursor into ADO recordset ? I am using ADO command object to execute ORACLE procedure that returns ref cursor.

    thanks for any help.

  8. #8
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    This is a message I sent to someone a long time ago, in respone to a 'MySql / Oracle' comparison. His text did not fully grasp the Oracle cursor implentation so I felt the need to reply.

    ===================================
    To: xxxxxxxxxxxxx
    Sent: 29/03/2003
    Hi xxxxxx,

    I've just taken a quick look at your "xxxxxxxxxxxxxxxx" as linked in your signature.

    Having looked at that, and your other posts etc - I'm impressed with your open view on what is good for what. It is refreshing to see someone with a realistic view of where a flat file is better than [Oracle/Db2/PostGres etc].

    However (there's always one of them!) regarding your statement about cursors....

    >
    >"cursors
    >Cursors are useful, but Id say only 99.99%
    >of people writing SQL applications would find
    >the need to run a cursor (in a stored
    >procedure no less). Does it hurt the product
    >to leave it out? Doubtful"
    >
    maybe different implementations use the name "cursor" to mean different things, but certainly on Oracle they are a core part of the engine. Even when you just type "select ......" into any front end, Oracle opens a cursor. The reason.....

    user a types select * from table where value >10 and value <20.

    Oracle optimises, maybe performs disk reads and opens an in memory dataset for the user to be scrolling through.

    user b then types select * from table where value >11 and value <19. Oracle knows not to perform any optimisation or disk reads, the result set is there and so it creates a pointer to the set already open.

    This is a *very* (emphasise very) simplistic description, there is a lot more to it (strong and weak cursors/implicit/explicit cursors etc). But trust me, they are a major and significant part of a solid/decent performing DBMS.

    ====================================
    As someone mentioned earlier, the use of the word "cursor" can differ wildly between implementations. Even Oracle's use of it can be very ambiguous against their own implentation.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •