Re: sqlload CTL for fixed-width VARRAY?

In article <fzL94.119$WC4.23134_at_news.wenet.net>,   sorry_at_spam.invalid wrote:
>
> Thanks. Our big enemy is time: We were supposed to have a year to

there's never enough time. I agree.

> convert 30 years of data... But then the purchase of our new machine
> was delayed, and folks here are doing the Y2K paranoia polka. I guess
> I was hoping for a simpler solution. I've loaded fixed-width non-
> array stuff, but I'm REALLY new to SQL. (So far, I haven't seen
> ANYTHING that's easier in SQL than it is in our current system. But I
> have no choice in the matter -- other than seeking employment
> elsewhere.) I haven't tried delimited fields in the input, because
> the relational database system that we have now is very comfortable
> with fixed width data and arrays, -- no need for control files,
> dumping and loading data is done in a very simple way:
>
> OPEN DATASET xyz;
> LOAD DATA xyz.dat;
> or
> OPEN DATASET xyz;
> FIND ALL;
> DUMP DATA xyz.dat;
>
> (A dataset being akin to a table, and "FIND ALL" being a "SELECT *
> FROM xyz".) That reads or writes a file based on the DDL. The DDL
> can incorporate information about the external lengths... or not, as
> the designer chooses. To get comma separated stuff out of the system
> takes a lot more work. Especially if it has to cope with arrays.
> It's do-able but it's a nusance for something that seems so
> straightforward to me. (Not to mention that the resulting file size
> gets bigger with all the extra commas.)
>

[snip my comments]

>
> The examples that I've seen used terminated fields, but I haven't
> tried them myself. When I get into my office, I can post an example,
> but I think it was in the back of Oracle 8: The Complete Reference
> (hardcover ~ 1000 pages).

That's a nice beginner's book, but I hardly classify it as a reference (800+pages of tutorial on SQL and related topics, and only about 200-300 pages of real reference material). I was disappointed when I bought it.

Get hold of the Oracle Manuals (be nice to your DBA).

>
> > 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.
>
> "POSITION(*)" if memory servers me. Mind you, I've only seen it once
> while hunting through the books, so I may be wrong about that.
>
> > I hope this helps (you AND others).
>
> Thanks again, and I hope I didn't sound too hostile/whatever. Just
the
> time crunch and the frustration, I guess.
>

No offense taken. I truely understand your frustration. I am wondering at this point two things:

1. In your new ORACLE database, do you really want to use VARRAY? (You don't have to answer now.) Would that data really be better off in it's own table?
2. VARRAY is new to ORACLE 8. It may be that the loader is not yet able to understand that type. In which case there has to be a workaround. (I did not see any SQL*Loader type that would match a VARRAY type.)

I looked back at your example and I have a few questions, and possibly a few answers. Let's discuss this offline, then we can present the solution in the newsgroup later.

Enjoy.

--
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 Mon Dec 27 1999 - 14:32:18 CST