Checked for relevance on 19-SEP-2008 

PURPOSE
-------

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.

 
SCOPE & APPLICATION
-------------------

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.

Due to Bug 1271684 this workaround can only be used in 8.1.6.3 and greater
on Unix platforms.

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.