Re: Loading external data without specifying column names

Brian Peasland ha scritto:

> Chrean wrote:
> > Hello folks,
> > I'd need to do exactly what I wrote in subject.
> > Starting from a set of txt files, each containing data to be loaded in
> > my oracle db's tables, I have to write a script that loops on each file
> > and load it into the db.
> > I tried with sqlldr, but it requires that I specify every column name.
> > I don't want to do that, 'cause my routine will always find a match
> > between db columns and datafiles fields.
> >
> > Correct syntax is:
> >
> > load data
> > infile 'example.dat' "fix 11"
> > into table example
> > fields terminated by ',' optionally enclosed by '"'
> > (col1, col2)
> >
> > while I'd want something like this:
> >
> > load data
> > infile 'example.dat' "fix 11"
> > into table example
> > fields terminated by ',' optionally enclosed by '"'
> >
> > 'cause I already know my table will have 2 columns (and my datafile
> > will always have 2 fields, of course), not necessarily named "col1",
> > "col2".
> >
> > Thanks in advance,
> > Chrean
> >
>
> Have you considered External Tables? This still uses the SQL*Loader
> engine, so you will still have to perform some dynamic creation here for
> your input file as suggested earlier in this thread. But you can create
> a script to scan the input file and dynamically generate the CREATE
> TABLE..ORGANIZATION EXTERNAL command for you. Then the data becomes
> available as if it were a table in your database. This may or may not
> help you...
>
> HTH,
> Brian
>
> --
> ===================================================================
>
> Brian Peasland
> dba_at_nospam.peasland.net
> http://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown

I've heard about external tables, I was just pondering whether or not they can be of any help to me.
I'll try to explain what I have to accomplish: maybe it will be easier for you to give me a hint, since I'm not su experienced with Oracle, as it can be easily seen.

I have an old application running on Informix SE 7.25, and I want to migrate it under Oracle 10g.
Every customer has a different database schema, because this application creates a number of new tables according to customer settings and needs. This is why I can't write down a static script for DB's migration.
Oracle Migrating Desktop can't do the job, 'cause it doesn't support such an old DB as Informix SE 7.x, as far as I know. I do an automatic export of every table from Informix with UNLOAD statements, then, and with dbschema tool I extract the schema. My scripts edit the schema's file making the changes needed to load it into Oracle with sqlplus.
Finally I need to import data, and that's when I asked for help, 'cause I don't know how many or which tables and columns my db has got. I don't like the idea to retrieve such infos from dbschema file: it will be a hard parsing work, easily subdued to errors. The sqldr would be very fine, if it could just import data without caring for columns' names.

I hope everything it's clearer now,
thanks anyway for every help you gave me and every help you will possibly give me.

Gabriel Sambarino Received on Tue Nov 14 2006 - 17:04:32 CST