Re: PL/SQL cursor handling

In article <1994Jul21.043911.5955_at_exu.ericsson.se>, hans_at_ebu.ericsson.se (Hans Bergsten) writes:
|> Hi,
|>
|> I would like to know how ORACLE handles cursors in PL/SQL packages.
|> When are they parsed, when are they released, are PL/SQL cursors
|> included in the OPEN_CURSORS count, what about implicit vs explicit
|> cursors etc.?
|>
|> Of special interest is the difference between declaring a cursor
|> local to a Procedure or Function compared to declaring the cursor on
|> Package Body level. Is one of these ways better in terms of memory
|> resources, performance (parsed less times) etc.?
|>
|> I am currently cleaning up a rather large PL/SQL application right now
|> and would like to know if I should change my local cursors to global
|> cursors at the same time. Appreciate any insight in this area.
|>
|> Regards,
|> Hans
|> ---
|> Hans Bergsten
|> Business Networks, a division of Internet : ebuhob_at_ebu.ericsson.se
|> Ericsson GE Mobile Communications MEMO : VAX.EBUHOB..EBU.ERICSSON.SE..INET
|> 5757 Plaza Drive Voice : +1 (714) 236-6784
|> Cypress, CA 90630-0007, USA FAX : +1 (714) 236-6039
|>
|>

There is no difference performance wise between cursors local to a procedure and global to the package, assuming that the cursor is opened, fetched from, and closed entirely within the procedure. If the cursor is local to the procedure, when the procedure ends the cursor is closed, as the local variables are released. If the cursor is declared outside the procedure (ie in the package) then it will still be around after the procedure ends. Depending on your application, you may be able to keep the cursor open longer, which would reduce the number of parses that need to be done.

PL/SQL cursors are included in OPEN_CURSORS. Each SQL statement has a cursor associated with it, and all of these are part of the count. Implicit cursors are opened, fetched from (if the SQL is a select) and then closed as part of the statement processing. Received on Thu Jul 21 1994 - 20:28:11 CEST