sqlplus with crontab


Ever want to run oracle’s sqlplus with crontab? Most of the time, the script won’t be executed, even though you’re already using the right user account. This is because crontab does not source the user’s environment variable when executing the command, and this cause some specific ORACLE variables not having the correct vaules, and in turn caused sqlplus to be unable to find some files required to run.

To solve this, you need to source the environment variables on your script. This is how the crontab script looks like :

$ more gstat.sh
. /home/oracle/.profile
/scripts/gstatsql.sh

and gstatsql.sh contains the sqlplus command that I want to run, while the gstat.sh is the one called by crontab. This is how my crontab looks like :

30     19      *       *       *       /scripts/gstat.sh

With this, crontab will source the environment variables for the user oracle, and sqlplus will be able to run correctly.

Advertisements

2 Comments Add yours

  1. Santosh says:

    Hi,
    This is my script:
    ***********************************
    # more tosh.sh
    #!/usr/bin/ksh
    clear
    . /home/oracle/.profile
    echo “Good morning, world.”
    export ORACLE_HOME=/u01/app/oracle/product/9.0.1
    export PATH=$ORACLE_HOME/bin:/usr/local/bin
    export ORACLE_SID=xxxx

    /u01/app/oracle/product/9.0.1/bin/sqlplus -s xxxx/xxxx @/home/ivbbuild/rec.sql > rech.txt
    ***********************************

    However when I run it from crontab I dont get anything. What am I missing here.

    Tosh.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s