Example 1 for SQL*Loader

This example shows how to insert records into an empty table. One column is filled with the current sysdate.

First, the table to be filled is created:

create table sql_loader_1 (
  load_time            date,
  field_1              varchar2(10),
  field_2              varchar2(10)
);

Here's the control file. Note that the positions 11 through 20 are loaded into field_1 and positions 1 through 10 into field_2. The field load_time is filled with the current time (sysdate) of the load.

load_1.ctl

load data
infile 'load_1.dat' "str '\r\n'"
insert
into table sql_loader_1
(
  load_time    sysdate,
  field_2      position( 1:10),
  field_1      position(11:20)
)

Here's the data. The name of the file (load_1.dat) had been specified with the infile statement in the control file.

load_1.dat

0123456789abcdefghij
**********##########
foo       bar
here comes a very long line
and the next is
short

sqlldr control=load_1.ctl userid=rene/rene

LOAD_TIME           FIELD_1    FIELD_2
------------------- ---------- ----------
23.08.2004 15:42:43 abcdefghij 0123456789
23.08.2004 15:42:43 ########## **********
23.08.2004 15:42:43 bar        foo
23.08.2004 15:42:43  a very lo here comes
23.08.2004 15:42:43 xt is      and the ne
23.08.2004 15:42:43            short


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback