0

LOAD DATA INFILE '/XXINSTANCEXX/applmgr/CUSTOM/xbol/12.0.0/bin/XX_DATA.csv'

REPLACE INTO TABLE XX_STAGING_TABLE

FIELDS TERMINATED BY ","

TRAILING NULLCOLS

This is part of my CTL used in my concurrent program. I need to update the INFILE with every run. I will be aware of the path but i need to change the file name in this example XX_DATA.csv to something else.

Let us assume that we are going to have a particular pattern for the CSV file which I am going to get. So if the file arrives on 9th of April 2015 it will be named as NEWFILE09042015 and a file arriving next day will have the file name of NEWFILE10042015 , a day after NEWFILE11042015 and so on. So we are effectively aware of the file name that we will get but need to find a way by which I can update the same in my CTL file.

How can I achieve this ?

Nitin Govindan
  • 11
  • 1
  • 2
  • You say its a concurrent program, So how this ctl file is getting called, do you have any shell script which is calling this. – vakul Sep 30 '15 at 15:13

2 Answers2

1

You need to specify the filename on the command line via the DATA option. You will most likely need a wrapper script that will call sqlldr with the right filename.

See this reply for some other alternatives that may work for your situation: insert timestanp of INFILE into a column from SQLLOADER

Community
  • 1
  • 1
Gary_W
  • 9,933
  • 1
  • 22
  • 40
1

You can use one or more exported system variables (at least in Unix) in the filename specified by INFILE.

All you have to do is use double quotes instead of single quotes around the file path specified by INFILE.

E.g.:

LOAD DATA INFILE "/XXINSTANCEXX/applmgr/CUSTOM/xbol/12.0.0/bin/$FILENAME.csv"
Durban_legend
  • 199
  • 1
  • 4