1

The question: How (where) can I specify the line terminator string of DAT file in case, that I pass the name of the DAT file on the command line using "data" parameter and not in CTL file? I am using Oracle 11.2 SQL Loader.

The goal: I need to load fast huge amount of data from CSV file into Oracle 11.2 (or above). The field (column) separator is hexa 1F (US character = unit separator), the string delimiter is the double quote, the record (row) separator is hexa 1E (RS character = record separator).

The problem: Using "stream record format" with "str terminator_string" of SQL Loader is fine, but just only in case, that I can specify the name of the DAT file using "infile" directive inside CTL. But the name of my DAT file is varying, so I pass the name of the DAT file on the command line as the "data parameter". And in this case I do not know, how (where) can I specify the line terminator string of DAT file in case.

Remark: The problem is the same as in the unsolved problem in this question.

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
Stepan RYBAR
  • 67
  • 1
  • 5

2 Answers2

1

Admittedly, more a workaround than a proper solution, but it should work if you have a fixed name in the controlfile, and then copy/rename/sym link each file to the fixed name and process. Or, have a control which has a infile entry "THE_DAT_FILE", and then run "sed" to change this to the required file name and then invoke sqlldr using this sed'd file.

So, something like:

  1. Get the data file F1
  2. Copy/SymLink F1 to the_file.dat (sym link asuming Unix/Linux/Cygwin)Admi
  3. RUn sqlldr with STR which refers to INFILE as "the_file.dat"
  4. When complete, delete/unlink the_file.dat
  5. Repeat 1-4 for next file(s) F1, F2, ... Fn

E.g.

for DAT_FILE in *.dat
do
   ln -s $DAT_FILE /tmp/the_file.dat
   sqlldr ..... 
   rm /tmp/the_file.dat
done

Or

for DAT_FILE in *.dat
do
   cat the_ctl_file | \
        sed "s/THE_DAT_FILE/£DAT_FILE/" > /tmp/ctl_$DAT_FILE.cf
   sqlldr ..... controlfile=tmp/ctl_$DAT_FILE.cf
done
TenG
  • 3,843
  • 2
  • 25
  • 42
  • Yes, the second case (using sed) is the exactly how I solved this already, but this is workaround from my point of view. I suppose, that Oracle (should) has better solution, which I do not know. Regarding the above sed command, since $dat_file has full path like "/tmp/dat_file_20140916074901.123456.dat" (directory, name, datetime, pid, suffix) and the directory has "/" character, I need to use ! instead / as the delimiter of search definition like: `sed 's!THE_DAT_FILE!£DAT_FILE!' ...` – Stepan RYBAR Sep 16 '14 at 05:51
1

I just ran into a similar situation, where I need to use the same control file for a set of files, all with the windows EOL character for EOR with embedded newlines in text fields.

Rather than code a specific control file for each with the name on the INFILE directive, I coded the name as /dev/null with the STR as:

INFILE '/dev/null' "STR '\r\n'"

And then on the sqlldr command line I use the DATA option to specify the actual flat file.

Tim Neilson
  • 11
  • 2