SQL Loader - Job scheduling

[Kumud]

Hi!
I wrote a sql loader script to load data from flat files.
I want to schedule a job for data loading script using diffrent flat files every day.

How can I define different flat file name in same script automatically.

[reydp]

if you're using NT use can just use a BATCH FILE and schedule it with AT command in DOS PROMPT:

[jgmagnus]

On Unix just create a cron-job.

If you are asking how to run a flat-file automatically which could have a different name each time, let me know. It sounded like you were hinting at that.
If so, supply some file-name examples.

- magnus

[Kumud]

I know how to schedule job. My problem is how I supply flat file name (every day new) to my sqlloader script.
LOAD DATA
INFILE 'C:\DW\load\List1.TXT' (This is changing everyday)

[ngwh]

hey! of course u gotta know what it gonna chg rite? it has to be predictable...like a date/time related
filename or wat...
or else lottery are for what? I'll be a billionaire by now ...wat a silly qn..

[grjohnson]

Maybe in your cronjob,

you could

Pass the following to a variable

cut the most recent file name out of a ls-lt |head -2
and then build a INFILE name on the fly and pass it into the CONTROL file. Will this work? I have no idea, but it's worth looking at.

[reydp]

Hi Kumud,
I know now what you mean. I've already done this before though I can't remember the whole solution. But here is what we did.

1. we schedule a job to run a batch file which its task is to just list the files in a directory and put it in a file.
c:\mydir>DIR /B > list_of_files.txt
filename1.ext
filename2.ext
filename3.ext

So the file list_of_files.txt now have filename1.ext, filenam2.ext, filename3.ext

2. We make this filename visible to oracle by including the c:\mydir in UTL_FILE_DIR in initSID.ora.

3. We then dynamically create the control file and read the list_of_files.txt
through the use of IN and OUT of UTL_FILE package in a stored procedure.

4. If control filename is fix then you schedule the execution itself of the loading process, otherwise you will dynamically create the batch file that is going to be loaded with the new control filename parameter.

Hope this will leads you to something better.

[reydp]

____________________________________
3. We then dynamically create the control file and read the list_of_files.txt through the use of IN and OUT of UTL_FILE package in a stored procedure.
___________________________________

just to correct my self:
it is not IN and OUT, instead GET_LINE and PUT_LINE.

[Kumud]

Hey reydp,

Thanks very much for your input. DO you have example of utl_file (store procedure) .
Still I have a dout that can we pass parameter to sqlldr ???
In sql plus yes but I do not see in sqlldr.
I will try your input.

[svk]

Instead of using the INFILE parameter in the controlfile, can you try using the DATA parameter for the 'sqlldr' command itself ? This will allow you to have more flexibility.

And if this is not what you are looking for then :

Your OS ?
Why cann't you keep the file name same ?
Is there going to be any pattern in the file name ?