Skip to Main Content
  • Questions

  • SQL Loader: Where can I specify the line terminator string of DAT file in case of varying DAT file name specified on command line using "data" parameter?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Stepan.

Asked: December 08, 2014 - 12:32 pm UTC

Last updated: December 22, 2014 - 7:43 pm UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

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 do not want to make temtporary copy of master CTL file for each import and replace 'variableFileName' in such CTL with real fileName using shell script as I am doing it now. I want to have the possibility to write CTL's instruction line infile without fileName but with "str x'1e'" specification. I am using Oracle 11.2 SQL Loader.

My master CTL file (simplified) 

load data 
  characterset UTF8
  infile 'variableFileName' "str x'1e'"
  append 
  into table myTable 
  fields terminated by '|' enclosed by x'1f' 
    ( 
      colA varchar(255), 
      colB varchar(255) 
    )


Thank You in advance for Your tips. Stepan

and Tom said...

str only goes with infile. What I've done in the past for a requirement like this to either

a) have the script that is calling sqlldr generate a ctl file on the fly for me. 99.999% of the time we are not invoking sqlldr from the command line - we are calling it from a script. Therefore, adding one line to the script (a call to sed to filter a template ctl file into a temporary one) is pretty easy to do.

b) use an external table and invoke an alter table to change the location associated with it before selecting from it


CREATE TABLE "SYS_SQLLDR_X_EXT_MYTABLE"
(
  "COLA" VARCHAR2(255),
  "COLB" VARCHAR2(255)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY DATA_DIR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY 0x'1E' CHARACTERSET UTF8
    DATA IS LITTLE ENDIAN
    BADFILE 'DATA_DIR':'test.bad'
    LOGFILE 'test.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "|" ENCLOSED BY 0x'1F' LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "COLA" VARCHAR(2,255),
      "COLB" VARCHAR(2,255)
    )
  )
  location
  (
    'test.dat'
  )
)REJECT LIMIT UNLIMITED

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

One more choice of solving this problem I have found meantime

Stepan RYBAR, December 10, 2014 - 3:57 pm UTC

One more choice of solving this problem I have found meantime

on Unix: cat myFile.dat | sqlldr ... control=my.ctl data=\"-\"
on Windows: type myFile.dat | sqlldr ... control=my.ctl data=\"-\"

and my.ctl contains

infile "-" "str x'1e'"

Tom Kyte
December 10, 2014 - 4:37 pm UTC

thanks - that's great

Usage of "-\" in data parameter

Rajeshwaran Jeyabal, December 22, 2014 - 12:48 pm UTC

Tom / Stepan,

Can any one of you explain the usage of "-\" in the data parameter ? also instead of having a filename what does this "-" mean in the INFILE parameter (inplace of filename specification) in control file ? But I am able to get the STR part (using STR attribute, we can define a new end of line character)
Tom Kyte
December 22, 2014 - 7:43 pm UTC

dash is unix for stdin - standard input. it tells the program to read from the console instead of a regular file. That is why you "type" or "cat" the file into the program.

another way

Artem, June 11, 2015 - 4:46 pm UTC

Hi
It is possible to specify inside ctl
infile 'AnyName.txt' "str x'1e'"
and specify another name of file in cmd line. SQLLDR will load file what specified in cmd line with str option from ctl. Name of DataFile in ctl does not matter in this case

Where r you

Fahd, July 21, 2015 - 7:49 am UTC

Where are you tom, Absent from 7 months. Such a long absent.

Is this end of AskTom?

Dilip Patel, August 06, 2015 - 8:54 pm UTC

Tom,
Its been more than 7 months and you are not around this site. Is this end of AskTom?

Thanks,

Dilip
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.