SQL Loader - Job scheduling
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: SQL Loader - Job scheduling

  1. #1
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182

    Angry

    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.



  2. #2
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    if you're using NT use can just use a BATCH FILE and schedule it with AT command in DOS PROMPT:

  3. #3
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Red face on Unix


    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

  4. #4
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    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)

  5. #5
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    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..
    ngwh,
    Singapore.

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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.
    OCP 8i, 9i DBA
    Brisbane Australia

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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.


  8. #8
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    ____________________________________
    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.

  9. #9
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    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.



  10. #10
    Join Date
    Jul 2000
    Posts
    521
    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 ?
    svk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width