Re: External table using multiple source txt files

ccote_msl_at_yahoo.com wrote:
> HI,
> I am wondering if it is possible to use multiple source files with
> Oracle extenal tables. For example file A contains fname and lname
> column while file B contains also fname and lname columns. I would like
> to tell Oracle to use both files A and B to populate my external table.

Yes, you can do it in 10g. I'm pretty sure it also works in 9i.

$ echo "john,smith" > /tmp/file_a.dat
$ echo "jane,moore" >> /tmp/file_a.dat
$ echo "douglas,adams" > /tmp/file_b.dat
$ echo "john,tolkien" >> /tmp/file_b.dat

$ sqlplus system

SQL> create directory ext_tmp_dir as '/tmp';

Directory created.

SQL> grant read,write on directory ext_tmp_dir to scott;

Grant succeeded.

SQL> connect scott
Enter password:
Connected.

SQL> create table load_mult_src (fname varchar2(20), lname varchar2(20))
  2 organization external (
  3 default directory ext_tmp_dir
  4 access parameters (fields terminated by ',')   5 location('file_a.dat','file_b.dat')   6 );

Table created.

SQL> select * from load_mult_src;

FNAME                LNAME
-------------------- --------------------
john                 smith
jane                 moore
douglas              adams
john                 tolkien