Re: sqlload CTL for fixed-width VARRAY?

From: <sorry_at_spam.invalid>
Date: Sat, 25 Dec 1999 01:58:25 GMT
Message-ID: <5xV84.77$WC4.11516@news.wenet.net>

Ed Prochak <prochak_at_my-deja.com> wrote:

> Note that your invalid email address in the header does not mean the
> spam bots cannot parse out the email address you provided in your
> signature. You only succeeded in making it harder for me and others to
> contact you.

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.)

> 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.)

>> We're moving from System 1032 (a wonderful database system) on a
>> VAX/VMS system to Oracle 8.0.5 on an RS/6000 AIX box. We have LOTS of
>> data in array fields, and for now want to keep them that way. Can
>> anyone provide me with an appropriate syntax for the SQL Loader
>> control file, given a fixed-width, undelimited ASCII data input file?
>> For a simple example, how would one write the control file for
>> something like:
>>
>> create or replace type NARRAY as varray(10) of number;
>> create or replace type CARRAY as varray(10) of varchar(5);
>>
>> create table DUMMY
>> (some_numbers NARRAY
>> ,some_letters CARRAY
>> );
>>
>> and an input file consisting of:
>>
>> 12121212121212121212the quickbrownfox jumpsover the lazy dogs back.
>> 1 2 3 4 5 6 7 8 9 0abcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcde
>>
>> (some_numbers always being two digits, and some_letters always being
>> left-justified, space-padded, 5-character text, for this example.)
Received on Fri Dec 24 1999 - 19:58:25 CST