Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    May 2006
    Posts
    13

    Unanswered: scheduling of ctl file in sqlloader

    hi to everybody. this is my first post in oracle forum.
    I am having one problem,
    I will be getting some text file everyday.then that file i can update in my database (oracle 9i) using SQLloader but i want to schedule this work so that it can happen everyday on schedule time without anyones interference.
    will anybody able to help me .
    or any other way to do this.
    thank you

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I'd use cron.
    HTH & YMMV!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As you've already decided to use SQL*Loader (instead of, for example, external table), scheduling will depend on operating system possibilities. You didn't mention which OS you use; UNIX scheduling is different than MS Windows or VMS scheduling.

    Furthermore, you didn't provide much information about how this file gets to you. What if it isn't there at the time it is supposed to? Will the file name be the same all the time or will it differ? What will you do with the "old" file? Will it be overwritten or should be stored somewhere? Does your OS support versioning? Etc. etc.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    As mentioned by Little, what is the naming convention??
    having one will always help.

    regardless, if you are on unix then I guess it doesn't matter.
    you can search a folder for any file with a date of "today" and
    grab that filename as a parameter and pass it into
    the controlfile.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    May 2006
    Posts
    13
    thanks everybody for responding ,
    yes i am able to get solutions, my OS is solaris unix so using CRON i am able to schedule , ok as per that i should have same filename everyday ,
    what if my filename will be different everyday . since my table structure is
    oracle_table (telephoneno(primary key),request) , and i will be getting text file as (telephoneno,request), how i should do if I will be getting new filename everyday.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    if you know what the filename should be and the location then simply
    write a shell script to use that input file.
    you can pass environment variables into sqlloader controlfiles.
    Here is an example controlfile:
    PHP Code:
    OPTIONS
    SKIP 0,
      
    ERRORS 100000,
      
    DIRECT FALSE,
      
    PARALLEL FALSE
    )

    LOAD DATA
    INFILE 
    '$DATA_FILE' "str '\n'"
    BADFILE '$BAD_FILE'

    APPEND
    INTO TABLE ADD_METER_READ_TXN
    FIELDS TERMINATED BY 
    ',' optionally enclosed by '"' TRAILING NULLCOLS
    (
            
    ORG_ID          CHAR,
            
    CUST_ID         CHAR,
            
    METER_ID        CHAR,
            
    RDNG_DT         "to_date(:RDNG_DT,'MM/DD/YYYY HH24:MI:SS')",
            
    RDNG_NBR        CHAR,
            
    USAGE_NBR       CHAR,
            
    STAT_CD         CHAR,
            
    BATCH_ID        constant 'XXbatch_nameXX',
            
    BATCH_DT        "sysdate"

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    May 2004
    Posts
    40

    sql loader

    what is sql loader?
    what is the control file able to do at oracle.

    thanks
    gar

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by garyww
    what is sql loader?
    Quote from the Oracle manual at
    http://download-west.oracle.com/docs...96652/ch03.htm

    "SQL*Loader loads data from external files into tables of an Oracle database"

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by garyww
    what is sql loader?
    what is the control file able to do at oracle.

    thanks
    gar
    ** bangs head against desk **
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    SQL*Loader is Oracle utility frequently used in IT department of the Investment Bank of Hong Kong

  11. #11
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Quote Originally Posted by Littlefoot
    SQL*Loader is Oracle utility frequently used in IT department of the Investment Bank of Hong Kong
    touche! :P

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by Littlefoot
    SQL*Loader is Oracle utility frequently used in IT department of the Investment Bank of Hong Kong
    nice!
    The Foot is ON FIRE!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    May 2006
    Posts
    13
    this is in response to my initial query ;
    i am able to do ftp
    and sqlloading at a same time using a shell file (getdata.sh)whose content are .
    ftp -niv 10.0.0.1<<EOF
    user username password
    bin
    get 'textfilename'
    bye
    EOF
    sqlldr username/password@SID control='controlfilename.ctl'

    ____
    then scheduling this file getdata.sh in cron tab

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    so you know the filename to ftp.
    now pass that filename into your controlfile and you are all set.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  15. #15
    Join Date
    Jun 2006
    Posts
    6
    Greetings,

    I'm a long time DB and unix programmer, familiar with shell scripting and bulk loading programs (bcp, isql) for DBMS other than Oracle.

    However, I've now been thrust into the Oracle realm, and in my search for reference material to answer a question, I came accross this recent thread.

    I understand how sql loader works with the ctl file (much like a format file with bcp) however, I need several of the column in the ctl file to be dynamicly created on the fly. My first thought is to build the ctl file in korn shell using print statements redirected to a file. However, someone mentioned earlier in this thread that you can pass variables into the ctl file.

    Can someone show me an example of how I can pass a variable from korn shell to an already created ctl file.

    Thanks in advance for any help you may be albe to provide.

Posting Permissions

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