Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    33

    Unanswered: cron running sql script

    Hi All,

    I am running the script on Oracle8i under AIX.

    I have a pl/sql script(procedure) which should run for every week ie on monday. I need to put this script file(procSample.sql located at /usr/myProc directory for ex.) in a cron job. I dont want to go for dbms_jobs utility provided by oracle. My req is to put it in cron.

    Can any one suggest me in doing this one in detail.
    Thanks in advance

  2. #2
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    138
    Hi,
    I dont know how to use crontab exactly on AIX. But I think it is realy the same as SCO Unix.

    unload your crontab by using "crontab -l > mycron"
    Now edit the file mycron and append following row.
    30 1 * * 1 /usr/myProc/procSample.sql
    It means : execute the procSample.sql every Monday at 1:30 .

    Now save the file and load the crontab with the command
    "crontab mycron" on Unix Shell.

    Check the effect by typing "crontab -l" again.

    Remember that there is no empty row at the end of your mycron file.
    Otherwise an error occur.

    More help you will get by typing "man crontab" on your AIX-Unix Shell.
    Last edited by fla5do; 01-04-04 at 14:37.
    Greetings from germany
    Peter F.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by fla5do
    Hi,
    I dont know how to use crontab exactly on AIX. But I think it is realy the same as SCO Unix.

    unload your crontab by using "crontab -l > mycron"
    Now edit the file mycron and append following row.
    30 1 * * 1 /usr/myProc/procSample.sql
    It means : execute the procSample.sql every Monday at 1:30 .
    ...only it won't work since you can't ask shell to execute an sql script. You'll need to invoke sqlplus to run the script.

    1) type "crontab -e" to edit cron table: this will launch your visual editor (I guess default would be vi)
    2) create an entry in it similar to this:
    30 1 * * 1 . /home/yourloginid/.profile;sqlplus @/usr/myProc/procSample.sql
    3) save the cron table
    4) enjoy.

    Please note that you have to execute your profile explicitly before attempting to run sqlplus (assuming your profile contains all necessary Oracle environment variables).
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Sep 2003
    Posts
    33
    Hi n_i,

    Thanks for your reply but i have one doubt over here.

    You gave the following script:

    30 1 * * 1 . /home/yourloginid/.profile;sqlplus @/usr/myProc/procSample.sql


    Is it not necessary to mention UserId, Password and Connection String to connect to oracle in the above line?
    How it will be connected?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by kondaoracle


    30 1 * * 1 . /home/yourloginid/.profile;sqlplus @/usr/myProc/procSample.sql


    Is it not necessary to mention UserId, Password and Connection String to connect to oracle in the above line?
    How it will be connected?
    Well, yes. Obviously you need to connect. You could either specify it on the command line or use "/nolog" option and connect from your script...
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Sep 2003
    Posts
    33
    Hi n_i

    can u pls make it more clear...

    in my pl/sql i sud write /nolog ? thats what u r mentioning or
    see the following

    30 1 * * 1 . /home/yourloginid/.profile;sqlplus /nolog @/usr/myProc/procSample.sql


    i didnt get you properly

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by kondaoracle
    Hi n_i

    can u pls make it more clear...

    in my pl/sql i sud write /nolog ? thats what u r mentioning or
    see the following

    30 1 * * 1 . /home/yourloginid/.profile;sqlplus /nolog @/usr/myProc/procSample.sql


    You can connect to a database by two methods, as you probably know:

    1) specify userid, password, and service name on the sqlplus command line:

    sqlplus userid/pass@svcname @script.sql

    2) specify /nolog on the sqlplus command line, like this:
    sqlplus /nolog @script.sql

    and then use CONNECT command within the script

    See SQLplus reference manual for more details

Posting Permissions

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