Search code examples
bashoraclecronsqlplus

sqlplus query doesn`t affects on database when run from crontab

I need to run some queries in crontab and they will be executed automatically at certain times.

I wrote a bash script for execute queries and some queries.

executeQuery.sh (file):

#!/usr/bin/env bash

export ORACLE_HOME=/usr/lib/oracle/11.2/client64/lib
export LD_LIBRARY_PATH="$ORACLE_HOME"
export PATH="$ORACLE_HOME:$PATH"

if ! command -v sqlplus > /dev/null ; then
        echo "This script requires sqlplus to be >installed and on your PATH. Exiting"
        exit 1
fi
if [  -z $1 -a  $1 = " " ]; then
        echo "query is empty"
        exit 1
fi

set -o allexport
source /home/oracle/scripts/.env
set +o allexport

sql="$(<"$1")"

echo "${sql}" | sqlplus -s   "${ORACLE_USERNAME}/${ORACLE_PASSWORD}@${ORACLE_HOST}:${ORACLE_PORT}/${ORACLE_SID}"

testQuery.sql(file):

update sample_database.sample_table sample_fields_1=0, sample_fields_2=0;
commit;

when run bash executeQuery.sh testQuery.sql It works properly and show me:

32 rows updated.

Commit complete.

but when run this script from crontab. This is not working :(

my crontab:

30 09 * * * /home/oracle/scripts/executeQuery.sh /home/oracle/scripts/testQuery.sql >> /home/oracle/scripts/log.log 2>&1

The result I get in the log file

32 rows updated.

Commit complete

Exactly the same output as when run script manually execution. But no any record has been updated in the database

And I have to manually run the script again to get it applied correctly to the database

I have given full access (777) to the all files

Solution

  • For connecting Oracle database through crontab it might be good to switch to oracle user.

    Try this:

    30 09 * * * su - oracle -c "/home/oracle/scripts/executeQuery.sh /home/oracle/scripts/testQuery.sql >> /home/oracle/scripts/log.log 2>&1"