Re: sqlload CTL for fixed-width VARRAY?

From: Ed Prochak <prochak_at_my-deja.com>
Date: Sun, 26 Dec 1999 23:31:22 GMT
Message-ID: <84687e$3tl$1@nnrp1.deja.com>

In article <5xV84.77$WC4.11516_at_news.wenet.net>,   sorry_at_spam.invalid wrote:
> Ed Prochak <prochak_at_my-deja.com> wrote:
>

[snip]
>
> That was an oversight leaving the sig in. The first message I had
> taken it out. I was expecting followups rather than replies. (Since
> so many people have asked questions about VARRAYs, it seemed like
someone
> else besides me might benefit from an answer.)

I agree, many can benefit when the answers are posted.

>
> > Oracle's SQL*Loader can handle fixed length fields easily. You
specify
> > the field position by character position, like:
>
> > ...
> > color (31-35),
> > noun (36-40),
> > ...
>
> > would load 'brown' in the color column and 'fox ' in the noun
column.
> > Rest of control file information can be more easily found in the
> > manuals. Did you look there?
>
> Yes. Did you read the question? VARRAY. I've loaded fixed-width
> NON-VARRAY data into my tables, but I was having problems specifically
> with VARRAY. The examples in the manuals tried to combine several
> things into one control file (varray, secondary files, nested tables,
> etc.) and were for variable width, delimited data. I looked on the
> web when I couldn't find something understandable in the manuals. The
> newsgroup was a last resort. (Our $200.00/hour consultants from
> Oracle Corp, don't seem to be willing or perhaps able to answer the
> question either.)

Well, the phrasing on the question (especially the second posting) implied (to me at least) that the problem was fixed width data, not VARRAY columns.

I haven't played with VARRAY type enough, but I still have a suggestion:

• Given that it is fixed width data, you know how many columns there are in the incoming data (even if these are trailing, optional columns in the data file) you can load them into a staging table with fixed columns, then transfer the data to the table with the VARRAY later (via PL*SQL).

I've worked on a couple major conversion projects and each time we used this staging tables approach. This allows loading the data, scrubbing it, mapping it to a newer data model, and generating reports. But it sounds like you have decent experience, so maybe you are already doing this.

Alternate suggestion, If you have managed to load VARRAY for terminated fields, then you seem to be more than half way there. How about giving an example of what you have tried? It seems to me that all that needs to change is the field definitions.

My manuals aren't handy now, but there is a way to define fixed width fields that are relative to the previous column. I cannot think of it now, but that's likely what you need.

I hope this helps (you AND others).

--
Ed Prochak
Magic Interface, Ltd.
ORACLE Development, conversions, training and support 440-498-3700 magic_at_interfacefamily.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Dec 26 1999 - 17:31:22 CST