Cursor use in Oracle

Question

Cursor use in Oracle

pandeharsh

Ten Centuries

Points: 1106
More actions
July 24, 2009 at 2:24 am

#223883

Hi,
I am newbie in oracle.
Should we use cursor in oracle store procedure either in select statement or insert statement.
Thanks
[font="Verdana"]Regards
Kumar Harsh[/font]
:unsure:

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

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

pandeharsh (7/24/2009)
I am newbie in oracle.
Should we use cursor in oracle store procedure either in select statement or insert statement.

A cursor would always be populated by a select statement no matter your rdbms of choice.

Not sure what you mean in regards to using a cursor on an insert statement... certainly you can issue insert statements taking data from a previously populated cursor.

_____________________________________
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.

pandeharsh Ten Centuries Points: 1106 More actions · Answer 2

Hi,

No ,Sql Server we do not use cursor and also in sql server we can do without cursor.

But in oracle I see that using cursor is must.

Also,I read here that,we should avoid cursor as much as possible.

Can someone put light light on my doubts ?

Thanks

[font="Verdana"]Regards
Kumar Harsh[/font]

:unsure:

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

pandeharsh (9/12/2009)
Hi,
No ,Sql Server we do not use cursor and also in sql server we can do without cursor.
But in oracle I see that using cursor is must.
Also,I read here that,we should avoid cursor as much as possible.
Can someone put light light on my doubts ?
Thanks

Agh... no. A cursor is NOT a must in Oracle except for one spot... if you want to return something to an app, you must use a "reference cursor" because Oracle will not return a result set to an app just using a SELECT. As a side bar, it's best if you put such reference cursors into a package with a global cursor variable.

Reference cursors are one of the reasons why I don't particularly care for Oracle.

Another place where you are forced into RBAR is in Oracle triggers... there's no such thing as INSERTED and DELETED tables, just INSERTED and DELETED variables that can only handle one row at a time. If you want to have the trigger handle more than one row, you must use the FOR EACH ROW syntax.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

pandeharsh (9/12/2009)
Sql Server we do not use cursor and also in sql server we can do without cursor.

This is confusing at least. Not sure about the context of your statement but I do use cursors efficiently in SQL Server as as I do in Oracle.

pandeharsh (9/12/2009)
But in oracle I see that using cursor is must.

This is not correct. You use a cursor when a cursor is needed.

"Reference Cursor" -as pointed out in previous comment- does not refers as a "cursor" per se but to a very different Oracle concept than unfortunately got the same name.

pandeharsh (9/12/2009)
Also,I read here that,we should avoid cursor as much as possible.

This is not correct. Is like saying you should avoid surgery as much as possible - why if surgery is your most effective alternative?

_____________________________________
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.

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 5

pandeharsh (9/12/2009)
Hi,
No ,Sql Server we do not use cursor and also in sql server we can do without cursor.
But in oracle I see that using cursor is must.
Also,I read here that,we should avoid cursor as much as possible.
Can someone put light light on my doubts ?

As a rule of thumb I'd estimate that cursors are not the simplest, most maintainable or most efficient solution to at least 99% of data manipulation problems in SQL. It's unfortunately very easy for an inexperienced SQL developer to fall into the trap of writing lots of procedural cursor-based solutions before they get to grips with writing set-based SQL statements. That's because many people just starting out with SQL will already be familiar with procedural programming languages and so cursor based solutions just seem more familiar and more convenient. To steer beginners away from that trap is one reason why it's common to find people advising that you should avoid cursors as much as possible.

Of course it's an over-simplification to say that cursors should be avoided altogether. That's not always possible and sometimes cursor-based solutions are more efficient or even simpler than any alternative. My advice for someone starting out in SQL would be to avoid learning about cursors until you are very familiar with the rest of the SQL language and then to seek a second opinion if you still think a cursor might be a good idea.

As already stated, ref cursors in Oracle are something different and nothing to do with what standard SQL and other DBMSs call a cursor. The above comments do not apply to ref cursors.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 6

PaulB-TheOneAndOnly (9/22/2009)
pandeharsh (9/12/2009)
Also,I read here that,we should avoid cursor as much as possible.
This is not correct. Is like saying you should avoid surgery as much as possible - why if surgery is your most effective alternative?

Actually, that is not entirely correct... Heh... I find that too many people go for "elective surgery" instead of finding the better way.

You should indeed avoid cursors "as much as possible". I'll agree that it's sometimes not possible but most folks simply give up too early.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 7

Jeff Moden (10/10/2009)
PaulB-TheOneAndOnly (9/22/2009)
pandeharsh (9/12/2009)
Also,I read here that,we should avoid cursor as much as possible.
This is not correct. Is like saying you should avoid surgery as much as possible - why if surgery is your most effective alternative?
Actually, that is not entirely correct... Heh... I find that too many people go for "elective surgery" instead of finding the better way.
You should indeed avoid cursors "as much as possible". I'll agree that it's sometimes not possible but most folks simply give up too early.

Jeff - I do agree set based operations are what we should look after but you may agree that in certain cases there is no other way than going RBAR 😀

_____________________________________
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.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 8

I absolutely agree but, just like I said a year and a half ago, too many people simply give up too easily on finding a high performance set based solution. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 9

Jeff Moden (3/12/2011)
I absolutely agree but, just like I said a year and a half ago, too many people simply give up too easily on finding a high performance set based solution. 😛

Aleluya! I waited a year and a half for your reply and I was certain it will come sooner or later 😀 By the way, I do agree.

_____________________________________
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.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 10

PaulB-TheOneAndOnly (5/19/2011)
Jeff Moden (3/12/2011)
I absolutely agree but, just like I said a year and a half ago, too many people simply give up too easily on finding a high performance set based solution. 😛
Aleluya! I waited a year and a half for your reply and I was certain it will come sooner or later 😀 By the way, I do agree.

You waited for the wrong thing then because I said exactly the same thing that year and a half ago. 😉

Jeff Moden (10/10/2009)
PaulB-TheOneAndOnly (9/22/2009)
pandeharsh (9/12/2009)
Also,I read here that,we should avoid cursor as much as possible.
This is not correct. Is like saying you should avoid surgery as much as possible - why if surgery is your most effective alternative?
Actually, that is not entirely correct... Heh... I find that too many people go for "elective surgery" instead of finding the better way.
You should indeed avoid cursors "as much as possible". [font="Arial Black"] I'll agree that it's sometimes not possible but most folks simply give up too early.[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)