Re: Variable Length Records

On Feb 6, 11:12 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> Hi Again,
>
> Sorry to ask another question, but this is a hard one.  I'm trying to
> replace all the reading of flat files (UTL_FILE.GET_LINE) with
> external tables.
>
> We have a file with variable length records.  I do not think that is
> an issue with external tables.
>
> What is a problem is how they reference the different fields in the
> data file line:
>
> recnt_flag       := LTRIM ( SUBSTR ( line, (loop_cnt * 24 + 113) + 1,
> 1) );
> chg_ind          := LTRIM ( SUBSTR ( line, (loop_cnt * 24 + 114) + 1,
> 1) );
> chg_dayoffset    := LTRIM ( SUBSTR ( line, (loop_cnt * 24 + 115) + 1,
> 1) );
> cnfrm_dayoffset  := LTRIM ( SUBSTR ( line, (loop_cnt * 24 + 118) + 1,
> 1) );
> broker_code      := LTRIM ( SUBSTR ( line, (loop_cnt * 24 + 121) + 1,
> 1) );
>
> So, it is a loop, with a calculated position.  I highly doubt that can
> be done in a table......

That doesn't appear to be a variable-length record to me; all of the positions are fixed relative to the loop pass counter. And that must be one really LONG line of data; how many iterations does the loop execute before it terminates? Also, since this file really is filled with fixed-length records it's not impossible to use SQL*Loader to process this file. I doubt you can get an external table created with this, I'll agree, as external tables can't make use of multiple INTO TABLE statements to define multiple locations for data. However you could create a staging table to be loaded with SQL*Loader then finish processing the data from the staging table.

I would start reading here:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm#i1005798

and get a feel for how this could be accomplished.

David Fitzjarrell Received on Wed Feb 06 2008 - 12:56:24 CST