1

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?

Community
  • 1
  • 1
user4050981
  • 13
  • 1
  • 3
  • Did you know that INFILE '$somedir/$somefile' is a valid syntax inside the control file (in Unix of course) and it will be correctly resolved by sqlldr. I did not know that until recently. Oracle should award anybody who finds where it is documented. – Waldemar Kosmider Dec 01 '21 at 14:23

2 Answers2

1

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.

Mofi
  • 46,139
  • 17
  • 80
  • 143
0

I used the for loop code

@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

and this worked.

I do not have the problem of multiple files as after I load it everytime, I move the file to a different folder. So at any given point I expect only one file.

desertnaut
  • 57,590
  • 26
  • 140
  • 166
user4050981
  • 13
  • 1
  • 3