Oracle – SQL*Loader DOS format


[adsense id=”0514458240″ width=”468″ height=”60″]

DOS formatted files consist of a line followed by a carriage return (\r) and line feed (\n). On UNIX/Linux platforms a line feed is sufficient for indicating a new line. As such when attempting to load a DOS formatted data file using SQL*Loader into Oracle can become problematic.

There are two possibilities:

  • Convert the data file by removing the carriage return characters
  • Load the data but indicate to SQL*Loader that lines are separate by carriage return and line feed characters (\r\n).

On UNIX/Linux there are utilities available which can convert a file from DOS formatted new lines to UNIX/Linux new line format. One such utility is called dos2unix. This utility takes the set of files to be converted as parameters. The output is written back to the same file name.

shell> file test.txt
test.txt: ASCII text, with CRLF line terminators
shell> dos2unix test.txt
dos2unix: converting file test.txt to UNIX format ...
shell> file test.txt
test.txt: ASCII text

The other alternative is to change the SQL*Loader configuration to indicate stream formatted information while identifying the carriage return and line feed format as a new line.

shell> cat test.ctl
load data infile 
'/home/oracle/loader/test.txt'
"str '\r\n'" 
into table names
fields terminated by ',' optionally enclosed by '"'
(id, name)
shell> sqlldr userid=ronan/ronan control=test.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Nov 3 17:42:39 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 4
shell> sqlplus username/password

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 3 17:44:48 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> col name format a10;
SQL> select * from names;

	ID NAME
---------- ----------
	 1 Ronan
	 2 Tom
	 3 Dick
	 4 Harry