SQL LOADER-如何将数据文件作为变量传递? [英] SQL LOADER - How to pass data file as a variable?

问题描述

我有一个SQL加载程序命令，该命令是从批处理脚本调用的.数据文件名不是恒定的.每次生成并填充文件时，都会在其上附加一个时间戳.因此，我的批处理文件将此数据文件存储在变量Fname中.

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

现在，当Sqlldr命令从批处理文件运行时

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%

我得到了错误

LRM-00112: multiple values not allowed for parameter 'data'

我不能将变量Fname用单引号引起来.那是行不通的.

I cannot enclose the variable Fname in single quotes. That does not work.

我检查了如何在sqlldr的批处理脚本中使用变量?

如果我使用上面讨论中指定的方法并以 infile %Fname%的形式将其包含在ctl文件中，则由于变量Fname显示为dir C:\Temp\TEST_*.dat /b，我仍然会收到错误消息收到错误消息，提示找不到文件.

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.

我该如何解决?

推荐答案

下面的简单解决方案始终使用文件夹C:\Temp中的第一个TEST_*.dat文件执行命令.

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

一个更好的解决方案是始终根据最近的修改日期，使用文件夹C:\Temp中最新的TEST_*.dat文件执行命令.

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

可以将找到的文件的名称分配给FOR循环中的环境变量，然后运行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.

这些批处理代码段是通过在命令提示符窗口for /?和dir /?中运行来使用帮助信息输出开发的.

Those batch code snippets were developed using the help information output by running in a command prompt window for /? and dir /?.

2^>nul会将命令dir的错误输出重定向到设备NUL，如果在此文件夹中找不到文件，以抑制这种特殊情况下的不需要的错误消息.

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.

我的最后提示:

在批处理文件中，始终指定要使用完整路径和文件扩展名运行的应用程序(如果路径/文件名中有空格，则用双引号引起来)，以避免依赖于环境变量 PATH 中的目录，除非这是无法执行该操作，因为在批量执行时不知道应用程序可执行文件的存储位置.

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.

这篇关于SQL LOADER-如何将数据文件作为变量传递?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！