Home » SQL & PL/SQL » SQL & PL/SQL » Cursors vs new feautures in oracle
Cursors vs new feautures in oracle [message #243907] Sun, 10 June 2007 23:59 Go to next message
yog_23
Messages: 79
Registered: March 2007
Member
3 yrs back most developers in oracle have writing code in cursors (most old projects done 3 or 4 yrs back). But now most articles which I read online suggest to use BULK COLLECT blah blah. Does this mean for any new development we should avoid using cursors ? Couldn't understand what are the pros and cons ? All the old codes written by other developes are using cursors anyway and can't change them all.

Thank you
Yog
Re: Cursors vs new feautures in oracle [message #243935 is a reply to message #243907] Mon, 11 June 2007 01:24 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
BULK operations also use cursors. For each data retrieval operation, Oracle uses a cursor. A BULK operation is benificial when you have to fetch a LOT of data. But, and this is version dependent, it also has limitations in its use. It is hard to tell you whether you have to change all your code. It is up to you: if the code works and it performs well, is there a reason to change it? I'd look at the programs that take the most time and change those if possible. I use bulk operations, but I don't use them always. If I have to fetch one row, for example, there's no need for a bulk operation.

MHE
Re: Cursors vs new feautures in oracle [message #243936 is a reply to message #243907] Mon, 11 June 2007 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should ALWAYS avoid cursors. This has ALWAYS been true.
You use cursors when you can't do it in SQL.
Now you should use BULK operations each time you use cursor.

Regards
Michel
Re: Cursors vs new feautures in oracle [message #244050 is a reply to message #243936] Mon, 11 June 2007 08:23 Go to previous messageGo to next message
techno
Messages: 44
Registered: October 2003
Member
Hi michel,

I have a relevent question to ask you,

In my application, there are plenty of places where we just need to select a single random row from the table.

In the code developed, I found, opening a cursor fetching the first row and closing the cursor was followed in the entire application to meet the above requirement(The reason given was to avoid NO_data_found and Too_many_rows exceptions)

But, with a single select into .... and rownum =1 statement inside a pl/sql block with no_data_found exception handled shall replace the same requirement without opening a cursor for all the records.

My question is,which approach is the best way in terms of performance ?

Techno
Re: Cursors vs new feautures in oracle [message #244054 is a reply to message #244050] Mon, 11 June 2007 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In my application, there are plenty of places where we just need to select a single random row from the table.

I wonder what is the business need to get a random row from a table. Can you give me one?

Regards
Michel
Re: Cursors vs new feautures in oracle [message #244072 is a reply to message #244054] Mon, 11 June 2007 09:26 Go to previous messageGo to next message
techno
Messages: 44
Registered: October 2003
Member
We need to select a single value from the table having multiple records

For example selecting a random employee from emp table for a perticular deptno
Re: Cursors vs new feautures in oracle [message #244073 is a reply to message #244072] Mon, 11 June 2007 09:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again what is the business need?
Who needs to select an employee, anyone, from a department?
Do you want to increase salary of 1 employee in the department but it does not matter which one it is?

Regards
Michel
Re: Cursors vs new feautures in oracle [message #244078 is a reply to message #244073] Mon, 11 June 2007 09:52 Go to previous messageGo to next message
techno
Messages: 44
Registered: October 2003
Member
Hi Michel,
The datamodel is designed like that and I am encountering this in my project.

If you don't believe, DON'T AGAIN ASK SOME QUESTION pl. leave it. I will try to find the answer to my question elsewhere .

Regards
Techno
Re: Cursors vs new feautures in oracle [message #244082 is a reply to message #244078] Mon, 11 June 2007 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, I no more ask question, just say application must be developed to fit business needs no more no less.

If you regularly need rownum=1 then you have a bigger problem that knowing if it is better to use rownum=1 or to get too_many_rows exception.

Regards
Michel
Re: Cursors vs new feautures in oracle [message #244089 is a reply to message #244082] Mon, 11 June 2007 10:18 Go to previous messageGo to next message
techno
Messages: 44
Registered: October 2003
Member
Thanks for your reply Michel,

I didn't understand your last statement can you pl. explain

Regards
Techno
Re: Cursors vs new feautures in oracle [message #244091 is a reply to message #244089] Mon, 11 June 2007 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just say that if rownum = 1 or trapping too_many_rows exception is mandatory in your program there is a flaw in its architecture or data model or... and this surely leads to much more performances and other problems (like result consistency) and it is more important to fix it than to answer to your question.

Regards
Michel

[Updated on: Mon, 11 June 2007 10:29]

Report message to a moderator

Re: Cursors vs new feautures in oracle [message #244192 is a reply to message #244091] Mon, 11 June 2007 22:34 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The practice of using explicit cursors for singleton SELECTs is a hang-over from a Pro*C best-practice.

Implicit cursors (SELECT .. INTO) are translated internally into explicit cursors (OPEN, FETCH, CLOSE) by Oracle. In Pro*C, it translates to OPEN, FETCH, FETCH, CLOSE (unless you alter a compile option), which creates less efficient code. The reason behind it was that the 2nd FETCH would return success if NO_DATA_FOUND, and TOO_MANY_ROWS if a 2nd row was found.

When PL/SQL was invented, a lot of procedural code that was traditionally developed in Pro*C suddenly migrated to PL/SQL. Since PL/SQL also accepts implicit cursors, it seemed logical to follow the old Pro*C best practice.

The PL/SQL engine is much more tightly coupled to the SQL engine than Pro*C. An implicit cursor is still internally translated into an explicit cursor, but it does not perform 2 fetches - this can be demonstrated using SQL*Trace and TK*Prof. The handling of TOO_MANY_ROWS is handled somehow within the tightly coupled single fetch.

Despite this, the myth that PL/SQL should never use implicit cursors is still widespread. Blame the Pro*C programmers if you like.

Ross Leishman
Previous Topic: Assigning records to varchar2
Next Topic: full outer join + darabase link
Goto Forum:
  


Current Time: Tue Dec 06 08:29:36 CST 2016

Total time taken to generate the page: 0.28746 seconds