Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Cursor use in Oracle Expand / Collapse
Author
Message
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, August 3, 2014 10:22 PM
Points: 97, Visits: 324
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
Posted Wednesday, September 9, 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: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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
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, August 3, 2014 10:22 PM
Points: 97, Visits: 324
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
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 @ 4:36 PM
Points: 35,546, Visits: 32,131
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #786947
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: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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
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: Wednesday, November 19, 2014 12:12 PM
Points: 451, Visits: 3,466
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
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 @ 4:36 PM
Points: 35,546, Visits: 32,131
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #801224
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: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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
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 @ 4:36 PM
Points: 35,546, Visits: 32,131
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1077328
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: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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 12»»

Permissions Expand / Collapse