CODEWITHSUNDEEP
oracle-databasesql-loader
Ramis
Ramis

Reputation: 65

sql loader without .dat extension

Oracle's sqlldr defaults to a .dat extension. That I want to override. I don't like to rename the file. When googled get to know few answers to use . like data='fileName.' which is not working. Share your ideas, please.

Error message is fileName.dat is not found.

Upvotes: 1

Views: 2956

Answers (3)

user8600255
user8600255

Reputation: 1

I have the same issue. I get a monthly download of reference data used in medical application and the 485 downloaded files don't have file extensions (@2gb). Unless I can load without file extensions I have to copy the files with .dat and load from there.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191560

You can't, at least in Unix/Linux environments. In Windows you can use the trailing period trick, specifying either INFILE 'filename.' in the control file or DATA=filename. on the command line. WIndows file name handling allows that; you can for instance do DIR filename. at a command prompt and it will list the file with no extension (as will DIR filename). But you can't do that with *nix, from a shell prompt or anywhere else.

You said you don't want to copy or rename the file. Temporarily renaming it might be the simplest solution, but as you may have a reason not to do that even briefly you could instead create a hard or soft link to the file which does have an extension, and use that link as the target instead. You could wrap that in a shell script that takes the file name argument:

# set variable from correct positional parameter; if you pass in the control
# file name or other options, this might not be $1 so adjust as needed
# if the tmeproary file won't be int he same directory, need to be full path
filename=$1

# optionally check file exists, is readable, etc. but overkill for demo
# can also check temporary file does not already exist - stop or remove

# create soft link somewhere it won't impact any other processes
ln -s ${filename} /tmp/${filename##*/}.dat

# run SQL*Loader with soft link as target
sqlldr user/password@db control=file.ctl data=/tmp/${filename##*/}.dat

# clean up
rm -f /tmp/${filename##*/}.dat

You can then call that as:

./scriptfile.sh /path/to/filename

If you can create the link in the same directory then you only need to pass the file, but if it's somewhere else - which may be necessary depending on why renaming isn't an option, and desirable either way - then you need to pass the full path of the data file so the link works. (If the temporary file will be int he same filesystem you could use a hard link, and you wouldn't have to pass the full path then either, but it's still cleaner to do so).

As you haven't shown your current command line options you may have to adjust that to take into account anything else you currently specify there rather than in the control file, particularly which positional argument is actually the data file path.

Upvotes: 1

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

Sqlloder has default extension for all input files data,log,control...

  • data= .dat
  • log= .log
  • control = .ctl
  • bad =.bad
  • PARFILE = .par

But you have to pass filename without apostrophe and dot

sqlloder pass/user@db control=control data=data

sqloader will add extension. control.ctl data.dat

Nevertheless i do not understand why you do not want to specify extension?

Upvotes: 1

Related Questions