Problem in executing script through crontab
 

Hi,
I have written one script which is connecting to the the daabase and generating one CSV, it is running fine when i ran it manually though it is throwing any warning but CSV is genearting 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, though it is giving below message but running fine

$ sh test.sh
SP2-0734: unknown command beginning "sqlplus ct..." - rest of line ignored.

Session altered.
CSV geneartaed

Same Script giving error while triggering by crontab as below

$ sqlplus: not found

The env for cron jobs is entirely separate and usually much more minimal than a user's env.
In short, use absolute full paths to all cmds and files used or source a file that creates the correct env at the top of the shell file.

Incidentally, I'd definitely try to fix those errors; it may be important later ...

Update
 

Hi Chris,
Thanks, all the given paths are absoulute in the files for all the files.

issue seems to be that script is not able to connect to database by the sqlplus -s /nolog << EOF while running through crontab .

but when running manually script is connecting to database and output is coming with some message "SP2-0734: unknown command beginning "sqlplus ct..." - rest of line ignored."

Kindly assist.

Then what's this?
"sqlplus" is not absolute, it relies on the directory containing the "sqlplus" command being in the PATH environment variable, which is different for the user than it is in cron, as chrism01 was saying.

As he said earlier:
If you don't know the full path to "sqlplus", then in a terminal use "which sqlplus", and put the result in your script.

Update
 

Hi,

Now I have Set the sqlplus absoulte path as below
/export/home/oracle/app/oracle/product/11.2.0/client_1/bin/sqlplus -s /nolog << EOF


New Error occured as below, kindly assist.

Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Pretty self-explanatory...it needs the ORACLE_HOME environment variable to be set. See what your user has it set to and add the appropriate setting in the script.

Hi,
it seems already set oracle_home environment

$ env
ORACLE_HOME=/export/home/oracle/app/oracle/product/11.2.0/client_1

In script i am connecting as below.


/export/home/oracle/app/oracle/product/11.2.0/client_1/bin/sqlplus -s /nolog << EOF

what i need to set in script?

After setting in the environment varibale..

below error appears when it triggers via crontab.


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

manual script run working fine. Please assist.

As I said and suicidaleggroll re-iterated, use FULL path to aLL CMDS & ALL files.
Alternately, use a wrapper to ensure you cd to the correct dir before you start.

Seriously:

1. write shell wrapper and add
at the top.

Also ensure you log all output eg

Sorry chris, but iam using everywhere absoulte path only , please refer below a small snippet.

crontab :

Script code:


Error:

By the above error it seems script is not able to connect to Database , please assist me what exact changes needs to be done in Script..

anyone assist here?

What is ./.profile?

./ means "here", are you sure that .profile is in the directory where the cronjob is being executed? That should probably be "$HOME/.profile".


is extract_data.sql a local or remote file? I'm not too familiar with sql.

Hi,

Yes, ./.profile is the same which other cron jobs are executing successfully but those are connecting to database.

extract_data.sql is the local file,and resides at same location where script is there.

Then you need to use its full path, and has been said countless times in this thread.

Use absolute paths for all commands and files.

Update
 

Hi,
Changes done as suggested and getting below..

Script code :


Error: