在CTL中使用参数 [英] Use parameters with CTL

问题描述

我正在使用CTL文件将文件中存储的数据加载到Oracle数据库中的特定表. 目前，我使用以下命令行启动加载程序文件:

I am using a CTL file to load data stored in a file to a specific table in my Oracle database. Currently, I launch the loader file using the following command line:

sqlldr user/pwd@db data=my_data_file control=my_loader.ctl

我想知道是否可以使用要在CTL文件中检索的指定参数.

I would like to know if it is possible to use specify parameters to be retrieved in the CTL file.

还可以检索CTL用来填充表的数据文件的名称吗?我也想为每一行插入它.我目前必须调用一个过程来更新以前插入的记录.

Also, is it possible to retrieve the name of the data file used by the CTL to fill the table ?I also would like to insert it for each row. I currently have to call a procedure to update previously inserted records.

任何帮助将不胜感激！

推荐答案

据我所知，没有任何方法可以将参数传递为ctrl中的变量.
但是您可以在ctl中使用常量并修改clt文件以在每次加载时更改该常量值(在ctl文件内容中).

As I know don't have any way to pass parametter as variable in ctrl.
But You can use constant in ctl and modify clt file to change that constant value (in ctl file content) for every loading times.

更具体.

my_loader.ctl:

my_loader.ctl:

--options
load data
infile 'c:\$datfilename$' --this is optional, you can specify here or from command line

into table mytable
fields....
(
datafilename constant '$datfilename$', -- will be replace by real datafname each load
datacol1  char(1),
....
)

dataload.bat:假定$ datfilename $是文本，将被数据文件的名称替换.

dataload.bat: assume that $datfilename$ is the text will be replace by datafile's name.

::sample copy
copy my_loader.ctl my_loader_temp.ctl

::replace the name of datafile (mainly the content to load into table's data column)
findandreplace my_loader_temp.ctl "$datafilename$" "%1"

::load
sqlldr user/pwd@db data=%1 control=my_loader_temp.ctl
::or with data be obmitted if you specified by infile in control file.
sqlldr user/pwd@db control=my_loader_temp.ctl

使用:dataload.bat mydatafile_2010_10_10.txt

这篇关于在CTL中使用参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！