Re: reading data files into a newly created table

In article <jdennisCnr0vy.BBu_at_netcom.com> jdennis_at_netcom.com (John Dennis) writes:
>I would suggest you look at SQL-Loader. It is fairly flexiable and
>does *seem* to be able to handle you problem with a little rigging.
>It advantage is it is *fairly* fast and maintainable.
>

Does SQL-Loader provide a flexible mapping mechanism too? e.g.

In my source data, I have some free texts that need to be mapped.

Died----> 1
Dead----> 1
He is dead-----> 1
13(in old database value) ------> 1

I can't do this in the old version of SQL*Loader. Dunno what it is up to now?

What I am doing is first preprocess all source data into FREE FORMAT ascii files for each table. (" as the delimitor and , as the separator) The field number and order should be the same as the corresponding Oracle table.

Then I wrote Pro*C programs(loaders) to load each table. Of course, I have to do a lot of validations and code mapping(I set up a table in Oracle to map free text to oracle valid list). For some table, I have to check the foreign keys, so some tables have to be loaded first to insure data integrety.

Currently, we are transfering data to Oracle from Paradox, Fixed length data from tape, Dbase. Some Paradox or Dbase tables have to be broken down(doing join, projection, etc.) before generating the ascii file.

Now I am working on the generic loader to read information in Oracle or our own Data Dictionary to generate to loader code and validation for each table and field.(Can Oracle 7 do validation on the server level?)

Any comments and suggestions on DATA TRANSFER from other databases to ORACLE? How about Sybase? As you might feel, my scheme is quite flexible(powerful) but not very efficient.

ciao

--SQL
P.S. Please Cc: your mail to sli_at_biost.mskcc.org since I may not have

     time to read this group. Received on Tue Apr 05 1994 - 23:25:02 CEST