SQL*Loader cannot handle variable input file names. This can be needed for periodic loads to Oracle database with file names changing each time. This article shows four workarounds to use variable input file names with SQL*Loader.
This article is helpful for SQL*Loader users need to load from input files with variable names. It covers four detailed workaround examples.
How to Pass the Input File with Variable Names to SQL*Loader Dynamically
SQL*Loader cannot handle variable input file names. The parameter INFILE specified in the control file doesn’t allow variable names – if the name of the data file is changed, then the control file must be edited and the name of the inputfile must also be changed.
This article describes 4 workarounds, how to pass the input file to SQL*Loader dynamically, without changing the control file every time the input file name is changed.
For a better understanding we will consider the following test case:
— The table to be loaded
CREATE TABLE LDR_TEST
(
ID NUMBER,
TEXT VARCHAR2(10)
);
— Input data records (file LDR.DAT)
1;Text 1
2;Text 2
3;Text 3
1. The simplest method to do this is to rename the input file (accordingly to the control file parameter INFILE). If the control file (LDR1.CTL) was defined as:
LOAD DATA
INFILE ‘LDR.DAT’
TRUNCATE
INTO TABLE LDR_TEST
FIELDS TERMINATED BY “;”
(
ID,
TEXT
)
then every new input file must be renamed to LDR.DAT before starting the load process.
******************
* SAMPLE 1 *
******************
—————————– Shell begin —————————–
#!/bin/sh
# This shell (LOAD.SH) renames the input file name ($1)
# to LDR.DAT that will be later used by SQL*Loader.
# Note: The shell must have at least 774 permissions to run. (chmod 774)
# This works only for UNIX platforms
# Rename (or copy) the file
mv $1 LDR.DAT
# cp $1 LDR.DAT
# Start SQL*Loader with the new control file
sqlldr scott/tiger control=LDR1.CTL
—————————— Shell end ——————————
Change permissions for the LOAD.SH shell to -rwxrwxr–:
#> chmod 774 LOAD.SH
Start the shell with:
#> ./LOAD.SH /tmp/files/LDR.DAT
where /tmp/files/LDR.DAT is the new inputfile.
2. Pass the data file to SQL*Loader with the command line parameter DATA. In this case the control file (LDR2.CTL) doesn’t contain the parameter INFILE.
LOAD DATA
TRUNCATE
INTO TABLE LDR_TEST
FIELDS TERMINATED BY “;”
(
ID,
TEXT
)
The data file is specified in the command line (parameter DATA).
******************
* SAMPLE 2 *
******************
—————————– Shell begin —————————–
#!/bin/sh
# This shell (LOAD.SH) passes the inputfile ($1) to SQL*Loader through
# the parameter DATA in command line
# Start SQL*Loader with the new control file
sqlldr scott/tiger control=LDR2.CTL data=$1
—————————— Shell end ——————————
Change permissions for the LOAD.SH shell to -rwxrwxr–:
#> chmod 774 LOAD.SH
Start the shell with:
#> ./LOAD.SH /tmp/files/LDR.DAT
where /tmp/files/LDR.DAT is the new inputfile.
3. Use a pipe to transfer the data from data file to SQL*Loader. First, the records from the data file will be sent in a pipe, and then SQL*Loader will receive the records from the pipe and insert them in the database. The command lines for different operating systems are the following:
VMS (7.2+) pipe type ldr.dat | sqlldr scott/tiger control=ldr3.ctl
UNIX cat LDR.DAT | sqlldr scott/tiger control=LDR3.CTL
NT type ldr.dat | sqlldr scott/tiger control=ldr3.ctl
The appropriate control files (LDR3.CTL) are these:
UNIX and NT
———–
LOAD DATA
INFILE “-”
TRUNCATE
INTO TABLE LDR_TEST
FIELDS TERMINATED BY “;”
(
ID,
TEXT
)
VMS
—
LOAD DATA
INFILE SYS$INPUT
TRUNCATE
INTO TABLE TEST_LDR
FIELDS TERMINATED BY “;”
(
ID,
TEXT
)
******************
* SAMPLE 3 *
******************
—————————– Shell begin —————————–
#!/bin/sh
# This shell (LOAD.SH) uses a pipe to pass input file ($1) to SQL*Loader.
# Start SQL*Loader with the new control file
cat $1 | sqlldr scott/tiger control=LDR3.CTL
—————————— Shell end ——————————
Change permissions for the LOAD.SH shell to -rwxrwxr–:
#> chmod 774 LOAD.SH
Start the shell with:
#> ./LOAD.SH /tmp/files/LDR.DAT
where /tmp/files/LDR.DAT is the new inputfile.
4. Use a place holder in control file and automatically change it before starting the load process. In this example the control file (LDR_TEMPLATE.CTL) looks like this:
LOAD DATA
INFILE $INPUT
TRUNCATE
INTO TABLE LDR_TEST
FIELDS TERMINATED BY “;”
(
ID,
TEXT
)
The idea is to programatically change the name of the place holder $INPUT before starting SQL*Loader. This will be done with the following UNIX shell:
******************
* SAMPLE 4 *
******************
—————————– Shell begin —————————–
#!/bin/sh
# This shell (LOAD.SH) passes the input file name ($1) to the SQL*Loader
# control file – it searches the place holder $INPUT in the control file
# LDR_TEMPLATE.CTL, changes it to the new name ($1) and save it in the new
# control file named LDR4.CTL. This file will be later used by SQL*Loader.
# Change the place holder
cat LDR_TEMPLATE.CTL | sed -e “s%\$INPUT%’$1’%” > LDR4.CTL
# Start SQL*Loader with the new control file
sqlldr scott/tiger control=LDR4.CTL
—————————— Shell end ——————————
Start the shell with:
#> ./LOAD.SH /tmp/files/LDR.DAT
where /tmp/files/LDR.DAT is the new input file.
Know-Oracle 
Luke is way too critical. I thought this was a fine article. If it’s not universally true it’s only changed recently. The fragments were readable by any average dba. The 774 permissions takes into account that the group is normally dba.
LikeLike
I have to rate this as very poor for the simple fact that the repeated statement “SQL*Loader cannot handle variable input file names” is not universally true. Organization of the presented samples is also a bit fragmented with no initial summary. Unix security “requirements” (chmod 774) reflects obsolete coding and encourages risk (why would I ever want my executing code to be writable?). Some clarification between standard input (use of a pipe character |) and named pipes should be addressed.
-Luke
LikeLike
If we can change the data file name to a fixed file name before sqlLoader it, after loading, we can change its name to one marked as processed.
for example: LoadMeYYYY_MM_DD.dat to LoadMe.dat, then changed it to LoadMeYYYY_MM_DD.ldd, in this way we do not need to change the INFILE parameter in the control file.
-Hank
LikeLike
4 Nov 2009
Typically I would provide the input file name on the command line, excluding the INFILE within the control file. However, I’ve found that I am able to set a variable file name in a calling batch file in a Windows 2003 Server environment and use that value in the control file successfully when running SQL*Loader: Release 10.2.0.1.0. Just to demonstrate the approach:
Batch file:
set IN_FILE=’c:\inbound\load_me.txt’
.
.
.
sqlldr user/pswd@db PARFILE=’c:\parameters.txt’
Parameter file:
errors=500000
rows=50000
control=%CTL_FILE%
bad=%BAD_FILE%
discard=%DSC_FILE%
log=%LOG_FILE%
Control file:
LOAD DATA
INFILE ‘%IN_FILE%’
INSERT
INTO TABLE table_to_be_loaded
(
)
I’m really interested to see if this would work on Unix. I always circumvented this investigation by putting the full control of sqlldr processing on the command line.
-Luke
LikeLike