Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, DIPU.

Asked: November 02, 2016 - 11:20 am UTC

Last updated: November 04, 2016 - 10:08 am UTC

Version: 10g

Viewed 1000+ times

You Asked

Hi,

Is loop (for,while) creates implicit or explicit cursor ?

To me, it looks like a implicit cursor because it open the memory area implicitly. But the definition of implicit cursor is any SELECT and DML operation defined a implicit cursor. Could you please elaborate this?

Thanks,
DIPU

and Connor said...

for i in ( select .... ) loop

is an implicit cursor

for i in 1 .. 10 loop

is not a cursor at all (no SQL being issued)

for i in my_cursor loop

where "my_cursor" is defined *explicitly* earlier in the code is self-explanatory

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DIPU K, November 03, 2016 - 6:40 am UTC

Thank you Connor for the explanation.

As i have mentioned in my question, the definition of the implicit cursor - all DML and SELECT operation. But, I have read somewhere that the SELECT operation which fetches only one row is implicit cursor. Is this correct?

Thanks,
DUPU


Connor McDonald
November 04, 2016 - 2:39 am UTC

"SELECT operation which fetches only one row is implicit cursor" is true.

But that is not the same as the converse, ie, that all implicit cursors are single row SELECT.

DIPU K, November 04, 2016 - 5:29 am UTC

Thanks Connor.

It means SELECT clause with more than one row does not create cursor implicitly but with only one row it creates. what is the reason for this ?

Thanks,
DIPU
Chris Saxon
November 04, 2016 - 10:08 am UTC

All SQL statements open a cursor. An explicit cursor is simply one you've defined and declared before using it. e.g.:

declare
  cursor explicit is select * from t;


You manage the opening and closing of it.

Implicit cursors are those where you haven't done this! Oracle manages the opening and closing of it for you. For more details read:

http://docs.oracle.com/database/121/LNPLS/static.htm#LNPLS00602
more

Connor and Chris don't just spend all day on AskTOM. 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.