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

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

CodePudding user response：

For connecting Oracle database thorugh 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"

CodePudding user response：

You miss a lot of Oracle RDBMS specific variables in your script. When you use terminal they are (probably) set, but not in cron. So add something like in you bash script (on the second line for example.

source /home/oracle/.bashrc

this will add all the settings you get when login in to the machine.