Home » SQL & PL/SQL » SQL & PL/SQL » LOOP Insert record cursor from one table to another (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0)
LOOP Insert record cursor from one table to another [message #315450] Mon, 21 April 2008 09:17 Go to next message
oramad
Messages: 11
Registered: March 2008
Location: LA
Junior Member
Hi advanced members,
I have the following code. I'm trying to select records from one table, fetch into record cursor and update it on another table. My problem is table update is taking far too long to update.

- How do I fix it?

DECLARE
  i NUMBER :=0;
--  
  CURSOR c1 IS
    SELECT terminal, market_id, retailer, to_char(day,'DAY')AS DAY, hour, NVL(AVG(vol),0)AS vol
      FROM v_water_usg v, holiday_tbl h
       WHERE v.day>=trunc(sysdate-14) and v.day!=h.holiday_date
       GROUP BY terminal, market_id, retailer, to_char(day,'DAY'), hour;
-- declare record variable that represents a row fetched from the v_nem_rm16 table
   r1 c1%ROWTYPE; 
BEGIN
-- open the explicit cursor and use it to fetch data into r1
  OPEN c1;
    LOOP  
      FETCH c1 INTO r1;
        UPDATE local_water_usg set terminal=r1.terminal, market_id=r1.market_id,retailer=r1.retailer, change_date=sysdate, DAY=r1.day, vol=r1.vol;                    
        i :=i+1;
		if mod(i,1000)=0 then --update only 1000 records then exit
		commit;
		exit;
		end if;        
    END loop;
  close c1;
END;
/

Re: LOOP Insert record cursor from one table to another [message #315451 is a reply to message #315450] Mon, 21 April 2008 09:25 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I notice that you didn't specify a WHERE-clause: are you aware that for each record you fetch, you update all the records of table LOCAL_WATER_USG ?

If you'll fix that, you could stumble into another problem as well: locking. Two sessions that performs an upate the same row(s) will lock each other

HTH
marc
Re: LOOP Insert record cursor from one table to another [message #315453 is a reply to message #315451] Mon, 21 April 2008 09:30 Go to previous messageGo to next message
oramad
Messages: 11
Registered: March 2008
Location: LA
Junior Member
Quote:
I notice that you didn't specify a WHERE-clause: are you aware that for each record you fetch, you update all the records of table LOCAL_WATER_USG ?

Yes. The table Local_WATER_USG is just an empty table.

Quote:
Two sessions that performs an upate the same row(s) will lock each other
Could you show me a workaround for this?
Re: LOOP Insert record cursor from one table to another [message #315457 is a reply to message #315453] Mon, 21 April 2008 09:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
The table Local_WATER_USG is just an empty table.

Then why update it?
Re: LOOP Insert record cursor from one table to another [message #315472 is a reply to message #315453] Mon, 21 April 2008 10:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
You seem to be confusing INSERT and UPDATE. Your message title says, "LOOP Insert record cursor from one table to another" and I gather that you actually want to INSERT, although your post says, "I'm trying to select records from one table, fetch into record cursor and update it on another table." and you also use UPDATE in your code, but you also say, "The table Local_WATER_USG is just an empty table." INSERT is used for adding rows to a table. UPDATE is used for changing the values of rows that already exist in the table. So, if Local_WATER_USG is empty, then there are no rows to update.

Assuming that INSERT is what you actually want to do, then looping through a cursor and inserting a group of rows at a time and committing after each group of rows is likely to be a slow way to do it and is likely to cause other problems as well, unless perhaps you are using bulk fetch and bulk insert. The best method is probably just (untested due to no create table and insert statements):

INSERT INTO local_water_usg 
  (terminal, market_id, retailer, change_date, day, vol)
SELECT v.terminal, 
       v.market_id, 
       v.retailer, 
       SYSDATE, 
       to_char(v.day,'DAY') AS day, 
       NVL(AVG(v.vol),0) AS vol 
FROM   v_water_usg v, holiday_tbl h
WHERE  v.day >= trunc(sysdate-14) 
and    v.day != h.holiday_date
GROUP  BY v.terminal, 
          v.market_id, 
          v.retailer, 
          to_char(v.day,'DAY');


If you want to try bulk fetch and bulk insert with limits and compare the speed, then search for those terms in the online documentation for syntax and examples.





Re: LOOP Insert record cursor from one table to another [message #315565 is a reply to message #315472] Mon, 21 April 2008 22:13 Go to previous messageGo to next message
oramad
Messages: 11
Registered: March 2008
Location: LA
Junior Member
Thank you all for your replies,I'll try what you suggested.

regards,
Giorgio
Re: LOOP Insert record cursor from one table to another [message #315708 is a reply to message #315565] Tue, 22 April 2008 08:45 Go to previous messageGo to next message
oramad
Messages: 11
Registered: March 2008
Location: LA
Junior Member
Quote:
Error report:
SQL Error: ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model.
*Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.

The column 'DAY' has DATE Data Type. The data I 'group by' using to_char(day,'Day'). Converting DAY to to_char(day,'DD-MON-YYYY') worked but defeated the purpose of grouping by day. There must be another way round..any suggestion?
Re: LOOP Insert record cursor from one table to another [message #316378 is a reply to message #315708] Thu, 24 April 2008 19:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
It is not clear what you want or what you have tried. You need to post create table and insert statements for sample data, the results that you want based on that data and why, and a copy and paste of a run testing that with that sample data with line numbers, including the complete error message with line number. You do not have to supply your actual confidential table names or data as mentioned in your PM. You only need to supply the columns involved and you can make up table and column names and data for your test. I do not respond to problems via PM as it defeats the whole purpose of the forums, which is for everybody to be able to contribute and share and learn while avoiding duplicate questions and responses. Sometimes I get busy as I have been lately and do not have time to respond and you might have received a response from someone else before now had you posted it here. Please familiarize yourself with the forum guidelines at the top of the forum page highlighted in yellow/gold.
Re: LOOP Insert record cursor from one table to another [message #316668 is a reply to message #316378] Sun, 27 April 2008 07:41 Go to previous messageGo to next message
oramad
Messages: 11
Registered: March 2008
Location: LA
Junior Member
It's OK. I have had the problem resolved myself.
Re: LOOP Insert record cursor from one table to another [message #316780 is a reply to message #316668] Mon, 28 April 2008 05:36 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
ok... ok...

why don't you share how you have resolved with details...

yours
dr.s.raghunathan
Previous Topic: Closing the cursor
Next Topic: problem procedure
Goto Forum:
  


Current Time: Tue Dec 06 08:19:21 CST 2016

Total time taken to generate the page: 0.15527 seconds