Search notes:

Oracle: SQL Loader example: Insert comma separated data

Insert data that is separated by a comma (CSV).

Create destination table

Create the table that is to be filled:

create table tq84_sql_loader_2 (
  field_1       varchar2(10),
  field_2       number
);

Github repository Oracle-Patterns, path: /SQLLoader/ex_02/create_table.sql

Control file

The control file.

The second field is specified as integer external. This means, that the numbers in the dat file are human readable rather than »real« bytes (in IEEE 754 floating point format).

load data
infile 'load_2.dat' "str '\r\n'"
insert
into table tq84_sql_loader_2
fields terminated by ','
(
  field_1      char,
  field_2      integer external
)

Github repository Oracle-Patterns, path: /SQLLoader/ex_02/load_2.ctl

The data

The name of this data file is specified with the infile statement in the control file.

one,1
two,2
fifty,50
eighty-eight,88
one hundred,100
fifteen,15

Github repository Oracle-Patterns, path: /SQLLoader/ex_02/load_2.dat

Load the data

$ sqlldr control=load_2.ctl userid=rene/password@ora.server.xy

Oracle: SQL Loader example: Insert comma separated data

Create destination table

Control file

The data

Load the data

See also