Re: Newbie - SQL Loader - Multiple Input Files

Yes, In Oracle 8 and earlier, it is impossible with sqlloader to read more than one file.

sad but true.

   Ed

Anurag wrote:
>
> Paul (?),
>
> It would have helped if you had told us which oracle version....
>
> In 8i, I could not think of a prettier way to do this than your method.
> Though I might have looked into the option of loading it into two temp tables
> and then inserting into the main table ... or if the files were very small ... by
> merging them together and then loading them.
>
> However, the picture in 9i becomes much better. External Tables solve this
> problem rather nicely. So just for the fun of it, I'll show you a very simple
> way of how to do this in 9i:
>
> -- Need create directory permissions to do the following
> SQL> create directory load_src as 'c:\test';
> Directory created.
>
> SQL> create table x (symbol varchar2(10),
> cur_unit number(10),
> currency varchar2(10));
>
> Table created.
>
> SQL> create table x_cur (symbol varchar2(10), currency varchar2(10))
> 2 organization external (type oracle_loader default directory load_src
> 3 access parameters (fields terminated by '|')
> 4 location ('x_cur.dat'));
>
> Table created.
>
> SQL> create table x_unit (symbol varchar2(10), cur_unit number(10))
> 2 organization external (type oracle_loader default directory load_src
> 3 access parameters (fields terminated by '|')
> 4 location ('x_unit.dat'));
>
> Table created.
>
> -- The following two selects are from the external table
> -- .. i.e. I'm selecting directly from the data file (pipe delimited)
> SQL> select * from x_cur;
> SYMBOL CURRENCY
> ---------- ----------
> IBM USD
> ORCL USD
> SAPE USD
>
> SQL> select * from x_unit;
> SYMBOL CUR_UNIT
> ---------- ----------
> IBM 300
> ORCL 400
> SAPE 1
>
> SQL> insert into x (symbol, cur_unit, currency)
> 2 (select u.symbol, u.cur_unit, c.currency from x_unit u, x_cur c
> 3 where u.symbol = c.symbol);
> 3 rows created.
>
> SQL> commit;
> Commit complete.
>
> SQL> select * from x;
> SYMBOL CUR_UNIT CURRENCY
> ---------- ---------- ----------
> IBM 300 USD
> ORCL 400 USD
> SAPE 1 USD
>
> BTW: Here are what the dat files contained:
> x_cur.dat
> IBM|USD
> ORCL|USD
> SAPE|USD
>
> x_unit.dat
> IBM|300
> ORCL|400
> SAPE|1
>
> :) All things explained in the Utilities documentation.
>
> Anurag
>
> "paul" <pquigley_at_whpi.com> wrote in message news:98da33ef.0207221143.62d253b4_at_posting.google.com...
> > Hi,
> >
> > I have 2 flat files containing data which merges to form 1 record.
> >
> > e.g.
> >
> > Flat File A
> > SYMBOL = IBM
> > UNITS = 300
> >
> > Flat File B
> > SYMBOL = IBM
> > CURRENCY = USD
> >
> > becomes;
> > SYMBOL = IBM
> > UNITS = 300
> > CURRENCY = USD
> >
> > Currently, I use 2 ctrl files and a temp table. The data in Flat File
> > A is loaded into the base table and the data in Flat File B is loaded
> > into the temp table. I use an sql statement to update the base table
> > CURRENCY values.
> >
> > Is there a way to do the above using just one sql loader file ?

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com