Home » SQL & PL/SQL » SQL & PL/SQL » Use of Oracle Cursors?
Use of Oracle Cursors? [message #244318] Tue, 12 June 2007 05:45 Go to next message
Lavjeet
Messages: 5
Registered: June 2007
Junior Member
Hi All,

Can anyone tell me the place where there is absolutely neccessary to use Oracle cursors and we cannot use any other thing ? Or the places where using Oracle cursor is suitable than using normal DMl queries ?

Thanks.
Re: Use of Oracle Cursors? [message #244323 is a reply to message #244318] Tue, 12 June 2007 05:56 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
In my opinion use a cusor when you have to do row-by-row processing of some kind.

E.g. You want to update/insert/delete 1000 rows through some logic.

You could use normal DML to update all at once.

But If you expect there are to be some rows with "bad" data (regarding the program/business logic) I usually use a cursor and process them row-by-row, flag each row with an appropriate ok/failed/etc status.
Re: Use of Oracle Cursors? [message #244328 is a reply to message #244323] Tue, 12 June 2007 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thomas,

I think you'll have some benefits to read the new bulk operations and exception clause that comes with it.

Regards
Michel
Re: Use of Oracle Cursors? [message #244349 is a reply to message #244318] Tue, 12 June 2007 07:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Normally, if you have to do massive calculations and apply a lot of business logic, it is impossible to do it using plain sql statements. This kind of processing is often found in batch-processes.
Re: Use of Oracle Cursors? [message #244372 is a reply to message #244328] Tue, 12 June 2007 08:36 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I think you'll have some benefits to read the new bulk operations and exception clause that comes with it.


Are there any benefits aside performance?

I have read a little in that direction, and have written a few new procedures that had to be done new anyway with bulk operations.

At the moment I don't like to blow up

FOR l_emp IN ( SELECT * FROM emp )
   <... some statements ...>
END LOOP;


into

DECLARE
  CURSOR empl_cur IS
  SELECT *
    FROM emp;
  
  TYPE empl_tab_typ IS TABLE OF EMPL_CUR%ROWTYPE;

  empl_tab EMPL_TAB_TYPE;

BEGIN

   OPEN empl_cur;
   LOOP
   FETCH empl_cur BULK COLLECT INTO empl_tab LIMIT 1000;
       EXIT WHEN empl_cur%NOTFOUND;
       FOR i IN empl_tab.FIRST .. empl_tab.LAST LOOP
       <... some statements ...>
       END LOOP;
   END LOOP;
   CLOSE empl_cur;
END;


if I know the whole thing will speed up from 2 seconds to 0.2 seconds, and runs once a week on a Sundays.

But of course, here we are still on 9.0.2. (where I don't have the exeption clause and other new things I think). I will have to re-read most of that stuff again before/after we switch to 10.

Thomas
Re: Use of Oracle Cursors? [message #244408 is a reply to message #244372] Tue, 12 June 2007 12:48 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I totally agree with you there, Thomas.
The bold statements about avoiding cursors and using bulk collection without knowing anything about the data are too principally thought.
It all depends on size of (handled) data and what you do with your data.
Previous Topic: regarding undefined object type
Next Topic: Functions and exceptions
Goto Forum:
  


Current Time: Wed Dec 07 12:25:06 CST 2016

Total time taken to generate the page: 0.13255 seconds