SQL*Loader INFILE using environment variable?

[nshen]

Hi! I will like to replace the hardcoded data file path by an environment variable in my SQL*Loader control file, something like:

LOAD DATA
INFILE "%GOLDPATH%\GoldData.txt"

or
LOAD DATA
INFILE %GOLDPATH%"\GoldData.txt"

or
LOAD DATA
INFILE %GOLDPATH%\GoldData.txt

But I got the same error from SQL*Loader in all above cases:
SQL*Loader-350: Syntax error at line 2.
Illegal combination of non-alphanumeric characters

Then I copied the control file over to the source directory and tried to invoke the control file as %GOLDPATH%\GoldData.ctl; now SQL*Loader complained it could not open the source file due to file-not-found. Apparently SQL*Loader has to be invoked from the location where the control file resides.

I went through the SQL*Loader manual and METALINK site but did not find any reference to use environment variable in the INFILE clause. Has anybody done it?

Thanks in advance!
Regards, Nancy

 

[zephan]

I dont know if it is possible, I suggest rather to use a shell script wich print its result in the control file, using the environment variables. You may call the sqlldr command in the same shell script, after closing the ctrl file.

 

[nshen]

Not sure if I understand it correctly:
Did you mean something like :
1) placing the control file and data file in the same %GOLDPATH% directory
2) in the script/batch file, first cd to %GOLDPATH%, then invoke sqlldr from there?

That was my last alternative. I was hoping to keep Oracle specific control files out of that %GOLDPATH% directory, where we stored all the data files to be imported to various RDBMS.
Thanks!
Regards, Nancy

 

[ddrillich]

You can have a place holder in your 'master' control file, which will be replaced with the actual file name by the invoking script as in:

sed s/!!IN_FILE_NAME!!/$file/g master.ctl > $ctl_ctl
$ORACLE_HOME/bin/sqlldr control=$ctl_file userid=user/password@db

Or you can specify the input file as a parameter to the sqlldr utility, as in:

$ORACLE_HOME/bin/sqlldr control=$ctl_file userid=user/password@db data=%GOLDPATH%/GoldData.txt

Regards,
Dan

 

[nshen]

Oh! Yah! I can pass the file name with the environment variable when calling sqlldr!
Thank you very much for this reminder, Dan!

Regards, Nancy

 

[HelenWU]

Hi Nshen,
Have you got your problem solved? I have the same problems that I need to get resolved. I need to pass the file name from sqlldr commend line to control file so I can insert it into my table.
Do you have any suggestions?
Also, do you happen to know if I can calculate two fields in a flat file and insert the result into table in by using the control file???
Anyone has any suggestions???
NEED YOUR HELP!!!!
Regards,
Helen

 

[nshen]

No, I finally had to resolve to making duplicated copies of import data files into Oracle specific directory and placed them together with batch command and SQL*Loader control files.
Regards, Nancy