Re: multiple infiles into a same table/record

Venkatasubramanian Ramachandran wrote:
> hi,
> i have a requirement that i have to load data from multiple
> datafiles into a table and the records are matched between the
> datafiles using a column.(say something like if the first 5 characters
> of a physical record in datafile1 matches with first 5 characters of
> datafiles2, it means both should be joined and put into the same
> record. has anyone done this? any insight into how to do this?
> thanks,
>

It always helps if you include your version of Oracle. If you are on 9i, you could use external tables for this. In pseudo code it looks like this:

create table t1 (
<column definition>)
organization external (type oracle_loader default directory imp_dir [...]) location ('file1.dat');

create table t2 (
<column definition>)
organization external (type oracle_loader default directory imp_dir [...]) location ('file2.dat');

insert into t3 select t1.col1, t1.col2, t2.col1, t2.col2 ... from t1, t2 where <join condition>

If you do an full outer join, then you'll get every row.

Details on how to do this with examples can be found on

        http://tahiti.oracle.com

start with the book 'sql reference' for details on create table ... organization external, and check out chapter 11 (for Oracle 9iR2) in the utilities guide.

Regards,

Holger Received on Fri May 28 2004 - 04:33:08 CDT