SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor use in Oracle


Cursor use in Oracle

Author
Message
pandeharsh
pandeharsh
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 338
Hi,

I am newbie in oracle.
Should we use cursor in oracle store procedure either in select statement or insert statement.

Thanks

Regards
Kumar Harsh

Unsure
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5327 Visits: 4639
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
pandeharsh
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 338
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

Regards
Kumar Harsh

Unsure
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84677 Visits: 41069
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5327 Visits: 4639
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
sqlvogel
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 3706
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
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84677 Visits: 41069
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5327 Visits: 4639
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 :-D

_____________________________________
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
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84677 Visits: 41069
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. :-P

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5327 Visits: 4639
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. :-P


Aleluya! I waited a year and a half for your reply and I was certain it will come sooner or later :-D 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search