what is the difference between Implicit, Explicit, Ref Cursors?

Question

Post reply

what is the difference between Implicit, Explicit, Ref Cursors?

Suresh Arumugam

Hall of Fame

Points: 3370
More actions
April 12, 2010 at 5:01 am

#221137

Hi,

I came across a question about the different type of cursors

(Implicit, explicit, ref curosors) for which I did NOT have any clue.

My online search is mostly landing towards ORACLE documents/links and confusing me. Could any one please clarify my doubt?

Thanks in advance,

Suresh

Regards,
Suresh Arumugam

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

It comes from how the cursor is deallocated. Are you explicitly deallocating a cursor or just allowing the server to deallocate it without a specific command to deallocate.

There is no declaration for an implicit or explicit cursor.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 2

Thanks for your time Jason.

The question was more in terms of understanding the differences between Implicit, explicit and ref cursors.

So, Do you mean implicit cursors are deallocated automatically by the system and Explicit cursor deallocations are to be done explicity??

Any idea about the ref.cursors?

Thanks in advance,

Suresh

Regards,
Suresh Arumugam

SQLRNNR SSC Guru Points: 281324 More actions · Answer 3

What it sounds like is either you were in an Oracle interview or somebody was trying to trip you up. Ref cursors are Oracle.

As for the implicit and explicit, that is what I meant (re: deallocation)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 4

Thanks Jason.

It was one of the questions I got when I got assessment call in my company (our company does assessment for employees every year).

One more thing. Is there any example how implicit cursor is handled because the cursor definitions we use to deallocate explicitly. So, I can not think of any example for implicit cursors.

Thanks,

Suresh

Regards,
Suresh Arumugam

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 5

As pointed by Jason an Oracle person wrote the question.

In the Oracle world a explicit cursor is a cursor you define in the "declare" section - this is pl/sql stuff - while an implicit cursor is what Oracle does for you each time you do not define an explicit one.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 6

Suresh Kumar-284278 (4/13/2010)

Thanks Jason.

It was one of the questions I got when I got assessment call in my company (our company does assessment for employees every year).

One more thing. Is there any example how implicit cursor is handled because the cursor definitions we use to deallocate explicitly. So, I can not think of any example for implicit cursors.

Thanks,

Suresh

In SQL, just don't do the following on your cursor - not recommended though.



Deallocate cursor @cursorname

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281324 More actions · Answer 7

PaulB-TheOneAndOnly (4/13/2010)

As pointed by Jason an Oracle person wrote the question.

In the Oracle world a explicit cursor is a cursor you define in the "declare" section - this is pl/sql stuff - while an implicit cursor is what Oracle does for you each time you do not define an explicit one.

Thanks for the assist.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 8

Suresh Arumugam

Hall of Fame

Points: 3370

April 14, 2010 at 2:02 am

#1151157

Thanks Jason & Pablo for the clarification.

Suresh

Regards,
Suresh Arumugam

SQLRNNR SSC Guru Points: 281324 More actions · Answer 9

SQLRNNR

SSC Guru

Points: 281324

April 14, 2010 at 8:36 am

#1151404

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 10

Suresh Kumar-284278 (4/14/2010)

Thanks Jason & Pablo for the clarification.

glad to help 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.