[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