I have a SQL loader command which I am calling from a batch script. The data filename is not constant. We have a timestamp appended to it each time the file is generated and populated. So my batch file gets this data file in a variable Fname.
SET Fname=dir C:\Temp\TEST_*.dat /b
Now, when the Sqlldr commmand runs from the batch file
sqlldr USERID=xyz/xyz@db CONTROL='C:\Temp\TEST.ctl' LOG='C:\Temp\TEST.log' DATA= %Fname%
I get the error
LRM-00112: multiple values not allowed for parameter 'data'
I cannot enclose the variable Fname in single quotes. That does not work.
I checked How can I use a variable in batch script at sqlldr?
If I use the method specified in the discussion above and include it in the ctl file as infile %Fname% I still get the error as the variable Fname appears as dir C:\Temp\TEST_*.dat /b and I get error saying file not found.
How do I resolve this?
The simple solution below executes the command always with first TEST_*.dat file found in folder C:\Temp.
@echo off
for %%F in ("C:\Temp\TEST_*.dat") do (
sqlldr USERID=xyz/xyz@db CONTROL='C:\Temp\TEST.ctl' LOG='C:\Temp\TEST.log' "DATA=%%F"
goto AfterLoop
)
:AfterLoop
A perhaps better solution is executing the command always with newest TEST_*.dat file found in folder C:\Temp according to last modification date.
@echo off
for /F "delims=" %%F in ('dir "C:\Temp\TEST_*.dat" /B /O-D /TW 2^>nul') do (
sqlldr USERID=xyz/xyz@db CONTROL='C:\Temp\TEST.ctl' LOG='C:\Temp\TEST.log' "DATA=%%F"
goto AfterLoop
)
:AfterLoop
It would be possible to assign the name of the found file to an environment variable inside the FOR loop and run the command below AfterLoop. But this requires additional code to check if at least 1 file was found in the folder.
Those batch code snippets were developed using the help information output by running in a command prompt window for /? and dir /?.
2^>nul is redirecting the error output of command dir to device NUL if no file is found in folder to suppress the unwanted error message for this special use case.
My last hint:
In batch files always specify applications to run with full path and file extension (in double quotes if space in path/file name) to avoid being dependent on directories in environment variable PATH, except this is not possible as storage location of application executable is not known on batch execution.