Re: Newbie - SQL Loader - Multiple Input Files

From: Anurag <avdbi_at_hotmail.com>
Date: Tue, 23 Jul 2002 00:41:11 -0400
Message-ID: <ujpnjaiv9q9j8e@corp.supernews.com>

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

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 ?
Received on Mon Jul 22 2002 - 23:41:11 CDT