cron and sqlldr
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: cron and sqlldr

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    anyone know how to run sqlldr by using a crontab?

    i can run sqlldr fine via a perl script or shell script, but when i call the script(s) from a crontab then i get an error telling me that it can find sqlldr. if i type the whole path in the script then i get a different error. any ideas?

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Crontab tbl_load.sh script at whatever schedule you wanted.

    <font face=courier>
    ::::::::::::::
    tbl_ldgrload.sh
    ::::::::::::::

    #!/bin/csh -f
    #
    echo "Starting ctlipt $0 on `date`."
    setenv ORACLE_SID xyzz
    setenv ORAENV_ASK NO
    source /usr/bin/coraenv
    #
    echo sqlldr control=l_budl parfile=ldirp.par
    sqlldr control='/data2/xyzz/ctl/l_budl' log='/data2/xyzz/log/l_budl.log' parfile
    =ldirp.par
    #
    echo sqlldr control=l_clprj parfile=lconv.par
    sqlldr control='/data2/xyzz/ctl/l_clprj' log='/data2/xyzz/log/l_clsprj.log' parfile=ldirp.par
    #
    echo sqlldr control=l_fdled parfile=ldirp.par
    sqlldr control='/data2/xyzz/ctl/l_fdled' log='/data2/xyzz/log/l_fdled.log' parfile=ldirp.par
    #
    echo "Script $0 on `date` has finished."
    #End of file.

    ::::::::::::::
    lcnvp.par
    ::::::::::::::
    userid = xyzz/*****
    rows = 2000
    discardmax = 10
    errors = 100
    direct = false
    silent = (discards)

    ::::::::::::::
    ldirp.par
    ::::::::::::::
    userid = xyzz/*****
    rows = 2000
    discardmax = 10
    errors = 100
    direct = true
    silent = (discards)
    </font>
    Reddy,Sam

  3. #3
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    err...forgive me for the ignorance...i am a little new to oracle. i basically know how to use sqlldr like so

    prompt$ sqlldr username/passwd control=somefile.dat

    so in my scripts this was as about as complicated as i got. this worked fine with the scripts, but when i ran the cron command i got errors. i could not find /usr/bin/coraenv on the box i have this script on. what does it mean?

    thank you for your help...it is appriciated.

  4. #4
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    the above should be somefile.ctl not .dat

  5. #5
    Join Date
    Feb 2001
    Posts
    123
    Hi Tron,

    I think that the problem you are encountering is because jobs submitted through cron do not get the same environment set up for them as when you log in to the server and run them interactively. There are a couple of simple solutions:

    1) Run the script from a privileged user and schedule an su command instead of scheduling the script directly. e.g. run the script from the root crontab, as follows:

    [schedule_info] su - [oracle_user] -c [name of script] >[log_file] 2>>&1

    obviously, in the above, objects enclosed in square braces require your system-specific information inserting in their place.

    2) At the start of your script, set up the environment for the script (ORACLE_SID, ORACLE_HOME, any environment variables required in the script - this is what the previously posted script was doing. You *may* be able to achieve this by 'sourcing' your .profile in the first line of the script. ('sourcing' a file is running it within the current shell environment, and is achieved by preceeding the script name with '. ' (dot space)), as below

    . /[path_to_login_directory]/.profile


    HTH

    David.

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