Search notes:

Oracle: SQL Loader Express: Struggling with optionally_enclosed_by on Windows

It turns out, that it is quite a struggle to use SQL Loader express on Windows in cojunction with the optionally_enclosed_by parameter.

The following is the test case that I used to figure out how it is possible to specify optional double quotes (in cmd.exe, that is).

First, I needed to create a table into which I wanted to load the data:

create table tq84_sql_loader_express (
   col_1   varchar( 3),
   col_2   varchar(10),
   num     integer
);

Github repository Oracle-Patterns, path: /SQLLoader/ex-express-optionally_enclosed_by/create_table.sql

Then, I also needed a file with the data to be loaded. Note the second line that contains the double quotes:

col_1;col_2;col_3
foo,abc,2
bar,"def,hij",4
baz,lmn,6

Github repository Oracle-Patterns, path: /SQLLoader/ex-express-optionally_enclosed_by/some.dat

Finally, the batch file to load the data.

The important thing was that I used the optionally_enclosed_by parameter last in the batch file. If it was followed by another parameter, the load would not succeed.

sqlldr rene/rene@ora18                             ^
  data                   = some.dat                ^
  table                  = tq84_sql_loader_express ^
  external_table         = not_used                ^
  field_names            = first_ignore            ^
  characterset           = utf8                    ^
  errors                 = 0                       ^
  silent                 = header,feedback         ^
  optionally_enclosed_by ='\"'

Github repository Oracle-Patterns, path: /SQLLoader/ex-express-optionally_enclosed_by/import_data.bat

Oracle: SQL Loader Express: Struggling with optionally_enclosed_by on Windows

See also