Re: Processing a footer file first

Ubiquitous wrote:

> I am attempting to load a file using SQL*LOADER which contains
> accounting information into a table. The file contains receipts
> that are itemized by line within the file, so for instance, a receipt
> with five items has five lines. What we've been doing in the past
> is to include the number of lines and the total dollar amount of the
> receipt in each line and just load it with SQL*LOADER, but due to
> prgram limitations on this new project, the person creating the
> load file cannot only put this information into a footer record at
> the end of the file. Has anyone found a clever way of reading the
> footer values first and then using them to load into the table, or
> does one have to write some sort of PL/SQL procedure which reads
> the footer first, then writes a new file containing the lines of the
> original record with the footer values appended? I briefly considered
> writting a PL/SQL script to load the table but am hesitant because
>
> writing a PL/SQL procedure to read the footer and then insert rows
> into the table but get the impression that this way would be much
> much slower than using SQL*LOADER. Any suggestions or input would be
> appreciated. :-)

Use SQL*Loader to push the entire thing into a table defined with a single VARCHAR2(4000) column.

Then use a stored procedure to read the last record, parse it out and put it where it belongs.

Depending on through-put requirements I prefer a good stored procedure to SQL*Loader logic any day.

Daniel Morgan Received on Fri Oct 18 2002 - 12:27:38 CDT