Variable Input file handling in Oracle SQL*Loader


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.

6 thoughts on “Variable Input file handling in Oracle SQL*Loader

  1. 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.

    Like

  2. 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

    Like

  3. 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

    Like

  4. 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

    Like

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s