Pass Variables in Column in SQL loader

No, you can't pass a variable in on the command line or through the environment. The is no command-line option to pass in arbitrary values, and the control file doesn't recognise references to environment variables.

The best you could do, I think, is to have a template control file with placeholders where you want to use variable values, and then generate your real control file from the template, substituting your variables as you create it (e.g. with sed).

Say your table has three columns three columns and you want defaults for two of them, one string and one number for simplicity. You could have a file called template.ctl which includes:

...
fields terminated by ','
trailing nullcols
(
id,
col2 "nvl(:col2, '<DEFAULT_STR>')",
col3 "nvl(:col3, <DEFAULT_NUM>)"
)

Then in your shell script (hard-coding the actual default values for now, as you haven't said where they will come from):

DEFAULT_STR="Some value"
DEFAULT_NUM=42

sed -e "s/<DEFAULT_STR>/${DEFAULT_STR}/" \
    -e "s/<DEFAULT_NUM>/${DEFAULT_NUM}/" \
    template.ctl > real.ctl

sqlldr usr/pwd control=real.ctl file=...

The real.ctl will be generated with the actual defaults substituted:

...
fields terminated by ','
trailing nullcols
(
id,
col2 "nvl(:col2, 'Some value')",
col3 "nvl(:col3, 42)"
)

so when you run SQL*Loader it will use those defaults.

If you could be running this multiple times simultaneously then you could make the 'real' control file name unique, e.g. by appending the current shell process ID.

Another option is to load to a staging table which allows nulls, and then populate your real table from the staging table - applying the transformation for null values to their defaults at that point, using positional parameters passed in to SQL*Plus, as part of the same shell script.

In your shell script, call SQL*Loader, but with the control file pointing to a staging table:

sqlldr usr/pwd control=stage.ctl file=...

then call SQL*Plus, either to a script that accepts positional parameters to the defaults, or inline as it's fairly simple; again, say your tables have three columns and you want defaults for two of them, one string and one number for simplicity:

DEFAULT_STR="Some value"
DEFAULT_NUM=42

sqlplus -l -s usr/pwd <<!EOF
whenever sqlerror exit failure rollback
insert into real_table (id, col2, col3)
select id, nvl(col2, '${DEFAULT_STR}'), nvl(col3, $DEFAULT_NUM)
from staging_table;
commit;
exit;
!EOF

(untested, but hopefully gives you the gist!)

1. Create your sqlldr file, with a distinct unique string wherever you want the ${env_var} to go.

for example, (often, the infile datafile is what changes), so create a file ${standardFileName}_TEMPLATE.ctl in that sqlldr TEMPLATE file where the line 'inflile is, put:

    infile 'xxxxxxxx'               

(xxxxxxxx is the unqique string)

2. Then from shell, do something like:

    for i in `ls -1 all_infiles*.csv`  
    do 

    echo "-----------------------------";  
    echo $i;  
    
    cp ${i} ./${standardFileName};  
    cp ${standardFileName}_TEMPLATE.ctl load_${standardFileName}.ctl;  

    sed -i "s/xxxxxxxx/${i}/g" load_${standardFileName}.ctl

    sqlldr $CONN_INFO control=load_${standardFileName}.ctl log=load_${i}.ctl.log;    

    done;

This should allow you to run a sqlldr script that picks up a specific env_variables for each pass.