Re: sqlload CTL for fixed-width VARRAY?

Thanks. Our big enemy is time: We were supposed to have a year to 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.)

>> I've loaded fixed-width NON-VARRAY data into my tables, but I was >> having problems specifically with VARRAY.

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

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

> 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. Received on Mon Dec 27 1999 - 09:27:07 CST