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@news.oh.voyager.net>

I'm using Designer Forms 4.5.
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 - 14:51:08 CDT