Updating Database Table From Text File
From: Courtney Wright <lili96ilil_at_netzero.com>
Date: Wed, 15 Sep 1999 15:51:08 -0400
Message-ID: <1NSD3.2119$34.253162_at_news.oh.voyager.net>
I'm using Designer Forms 4.5.
[Quoted] I'm accessing a text file using the Text_IO Built-ins. The text file contains information that needs to be plugged into an Oracle database table. To update the Oracle table, I'm using SQL; Update...Set...Where etc. Is this the most efficient way to go about this? Without the Update SQL line included, the procedure takes a few seconds to run (8-12). Adding the line of SQL adds minutes on to the time it take for the procedure to run. (The SQL line is in a loop and is run 20 or so times) Here's my code:
BEGIN Clear_List('LTesT');
RFile := Text_IO.FOpen('C:\Users\Geodev\TaxDist\'||:DFile||'.dat', 'R');
Date: Wed, 15 Sep 1999 15:51:08 -0400
Message-ID: <1NSD3.2119$34.253162_at_news.oh.voyager.net>
I'm using Designer Forms 4.5.
[Quoted] I'm accessing a text file using the Text_IO Built-ins. The text file contains information that needs to be plugged into an Oracle database table. To update the Oracle table, I'm using SQL; Update...Set...Where etc. Is this the most efficient way to go about this? Without the Update SQL line included, the procedure takes a few seconds to run (8-12). Adding the line of SQL adds minutes on to the time it take for the procedure to run. (The SQL line is in a loop and is run 20 or so times) Here's my code:
Note: Loop does not run 1000 times. It goes through 20-30 times.
DECLARE
RFile Text_IO.File_Type; LineBuf VarChar2(500); sIPID VarChar2(15);
BEGIN Clear_List('LTesT');
RFile := Text_IO.FOpen('C:\Users\Geodev\TaxDist\'||:DFile||'.dat', 'R');
For l IN 1..1000 LOOP
Text_IO.Get_Line(RFile, LineBuf);
sIPID := SubStr(LineBuf, InStr(LineBuf, ' ', 1, 18) + 1, InStr(LineBuf,
' ', 1, 19)- InStr(LineBuf, ' ', 1, 18)-1);
UPDATE SegCabl SET TaxDist = :DFile WHERE IPID = sIPID;
Text_IO.Put(LineBuf);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN Text_IO.FClose(RFile);
Commit;
END;
Is the another, more efficient way of updating records from a text file? Or
can this code be modified to only called the 'Update' SQL line once?
Courtney
Received on Wed Sep 15 1999 - 21:51:08 CEST