在CTL中使用参数

本文介绍了在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