Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » Working with Oracle » Cursor use in Oracle
Add to briefcase
11 posts, Page 1 of 2
12»»

Cursor use in Oracle Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
pandeharsh
Posted Friday, July 24, 2009 2:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 28, 2012 4:19 AM
Points: 94, Visits: 290
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
Post #758880
PaulB-TheOneAndOnly
Posted Wednesday, September 09, 2009 11:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 3,016, Visits: 4,470
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.
Post #785172
pandeharsh
Posted Saturday, September 12, 2009 1:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 28, 2012 4:19 AM
Points: 94, Visits: 290
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
Post #786836
Jeff Moden
Posted Saturday, September 12, 2009 10:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #786947
PaulB-TheOneAndOnly
Posted Tuesday, September 22, 2009 5:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 3,016, Visits: 4,470
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.
Post #791666
sqlvogel
Posted Tuesday, September 22, 2009 1:00 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:11 AM
Points: 407, Visits: 2,866
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.

David
Post #792112
Jeff Moden
Posted Saturday, October 10, 2009 10:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #801224
PaulB-TheOneAndOnly
Posted Friday, March 11, 2011 2:39 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 3,016, Visits: 4,470
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.
Post #1077184
Jeff Moden
Posted Saturday, March 12, 2011 9:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1077328
PaulB-TheOneAndOnly
Posted Thursday, May 19, 2011 1:34 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 3,016, Visits: 4,470
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.
Post #1111989
« Prev Topic | Next Topic »

Add to briefcase
11 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.