0

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

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

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.

Any help would be appreciated !

Hal
  • 591
  • 4
  • 10
  • 28

1 Answers1

0

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.

Edit: more specific.

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: 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

using: dataload.bat mydatafile_2010_10_10.txt

pinichi
  • 2,199
  • 15
  • 17
  • Thanks for your answer. The fact is that the name of my data file is suppose to change from an use to another (it would contain the date of the day it was generated or something), so even if using a variable would do the trick, this isn't really what I am searching for. – Hal Dec 29 '10 at 09:54
  • Sorry maybe my English not good enough but doesn't my suggestion allow you to have `my_loader_yymmdd.ctl` to be load as 1 column dynamically for each time you load? – pinichi Dec 29 '10 at 10:11
  • Sorry I may not have been clear enough. It's only the name of the data file that change, not the name of the CTL. I use the command line above to launch the CTL, in which I specify the name of the datafile and the name of the CTL. I would like to retrieve the name of the datafile inside the CTL because I don't want to specify it twice, both in the command line and in the CTL. As far as my researches have been, I have yet to find any solution to this problem. – Hal Dec 29 '10 at 10:19
  • My bad, misunderstood the datafilename and controlfilename but the idea still work. See my edited answer. – pinichi Dec 29 '10 at 11:58