Cursor use in Oracle

  • pandeharsh

    SSC Eights!

    Points: 996

    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:

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    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

    SSC Eights!

    Points: 996

    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: 993884

    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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    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: 9415

    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: 993884

    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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    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: 993884

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    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: 993884

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

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

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