Script not working via crontab

Shell Programming and Scripting
1

Hi,
I have written one script which is connecting to the the database and generating one CSV, it is running fine when i ran it manually though it is throwing any warning but CSV is generating and working fine but same script when i have configured in crontab not working and giving error, kindly help.

script contents:

#!/bin/bash
sqlplus -s /nolog << EOF
CONNECT plmc/plmb@whb
@csvgenerator.sql
end;
/
EOF

-----------------------------------
Script running successfully as below, running fine

$ sh test.sh
Session altered.
CSV generated

In addition to the above info the oracle home profile is already set and each using absolute path for each file as below.

Crontab entry:

06 00 * * * ksh  ". ./.profile; /data2/users/cmw/PKG12/test_data.sh" > /tmp/testoutput.txt 2>&1

Script:

#!/bin/ksh
/export/home/oracle/app/oracle/product/11.2.0/client_1/bin/sqlplus -s /nolog << EOF
CONNECT user/user_1234@CBDTEST
@extract_data.sql [checked absolute path for too]
end;
/
EOF
2

Good to see that you know about cron 's environment restrictions and take adequate measures.

But, regarding your problem: Not sure I understand.

You are happily running one script from the command line, but are worried that executing a totally different script using a different shell connecting to a different user on a different host is giving you errors.
That is not an immediate contradiction.

What are the errors that you encounter? Are you sure the right .profile is being sourced?

3

Also note that using ksh like this, is problematic, since there is no path specified to this shell, so it may be that ksh will not be found.
Also the syntax is incorrect. To make ksh run a command specified on the command line, it requires the -c option.

Since the script contains a shebang, just make sure it is executable and run it with the absolute path to the script (or make sure the PATH is set and that it contains the directory where the script is located):

/data2/users/cmw/PKG12/test_data.sh > /tmp/testoutput.txt 2>&1

Where the profile is sourced within the script, also with an absolute path to the profile..

4

can anyone assist here?

5

Not unless you provide answers.

6

Also please be so kind as to inform us what - in your view - is wrong with the two reactions you have already received and why -in your view - these do not qualify as "assistance".

7

Seems there is no issue in executing script

Below is the error i am getting when iam running by crontab.

SP2-0310: unable to open file "extract_data.sql "
SP2-0042: unknown command "end" - rest of line ignored.
SP2-0103: Nothing in SQL buffer to run.

Scrutinizer, seems there is no problem in shell [ksh/sh] as the later part of the script is giving output as expected and i think script is failing to connect to DB server and not generating CSV..

#!/bin/ksh
/export/home/oracle/app/oracle/product/11.2.0/client_1/bin/sqlplus -s /nolog << EOF
CONNECT user/user_1234@CBDTEST
@extract_data.sql [checked absolute path for too]
end;
/
EOF