[SOLVED] Error with Unix script

santosh0782 · 01-02-2015, 12:44 AM

Hi,

I have a written a script:

Code:

#!/usr/bin/ksh
#--------------------------------------------------------------------------------------------------------------#
#qz='sqlplus barcqz/xdr56yhn@BARP2'
#wdc='sqlplus barcwdc/xdr56yhn@BARP2'
#--------------------------------------------------------------------------------------------------------------#
sqlplus barcqz/xdr56yhn@BARP2 <<EOF
update qz_dates set QZ_DT = sysdate;
update qz_dates_back set QZ_DT = sysdate;
update wdc_dates set WDCDATE = sysdate;
update WDC_PAY_EXRATE set WPESENTDATE = to_char(sysdate,'DD-MON-YY:HH:MI:SS') ;
update WDC_OU_STARTTIME set WOSBUSINESSDATE =sysdate;
commit;
exit
EOF

sqlplus barcwdc/xdr56yhn@BARP2 <<EOF
update WDC_PAY_EXRATE set WPESENTDATE=(substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),1,10)||'T'||substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),12,19));
commit;
exit
EOF

when i run this script on command prompt, it runs well, but when i schedule it in crontab, it gives an error as:

Code:

/devapp1_01/quartz/qzprod1/santosh/scripts/update_date.sh[6]: sqlplus:  not found.
/devapp1_01/quartz/qzprod1/santosh/scripts/update_date.sh[16]: sqlplus:  not found.
/devapp1_01/quartz/qzprod1/santosh/scripts/update_date.sh[6]: sqlplus:  not found.
/devapp1_01/quartz/qzprod1/santosh/scripts/update_date.sh[16]: sqlplus:  not found.

could someone please help on this?

sag47 · 01-02-2015, 01:33 AM

cron typically has an unset path. You should either manually set the $PATH for crontab or use the full path in your script e.g. (/usr/bin/sqlplus).

Code:

which sqlplus

santosh0782 · 01-02-2015, 03:34 AM

the complete path for sqlplus is:

Code:

barza_uat_app1[] /devapp1_01/quartz/qzprod1 > which sqlplus
/usr/oracle/client_11g/product/11.2.0/client11g/bin/sqlplus

should i include above complete path in script like this:

Code:

#!/usr/bin/ksh
#--------------------------------------------------------------------------------------------------------------#
#qz='sqlplus barcqz/xdr56yhn@BARP2'
#wdc='sqlplus barcwdc/xdr56yhn@BARP2'
SQL_PATH="/usr/oracle/client_11g/product/11.2.0/client11g/bin/"
#--------------------------------------------------------------------------------------------------------------#
/usr/oracle/client_11g/product/11.2.0/client11g/bin/sqlplus barcqz/xdr56yhn@BARP2 <<EOF
update qz_dates set QZ_DT = sysdate;
update qz_dates_back set QZ_DT = sysdate;
update wdc_dates set WDCDATE = sysdate;
update WDC_PAY_EXRATE set WPESENTDATE = to_char(sysdate,'DD-MON-YY:HH:MI:SS') ;
update WDC_OU_STARTTIME set WOSBUSINESSDATE =sysdate;
commit;
exit
EOF

/usr/oracle/client_11g/product/11.2.0/client11g/bin/sqlplus barcwdc/xdr56yhn@BARP2 <<EOF
update WDC_PAY_EXRATE set WPESENTDATE=(substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),1,10)||'T'||substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),12,19));
commit;
exit
EOF

unSpawn · 01-02-2015, 06:18 AM

You could also modify the path inside the script once at the top:

Code:

export PATH=$PATH:/usr/oracle/client_11g/product/11.2.0/client11g/bin

sag47 · 01-03-2015, 07:52 PM

Yes, referencing by using the full path to the executable or including it in the PATH as unSpawn suggests.

santosh0782 · 01-04-2015, 11:11 PM

Thanks a lot, have modified the script as :

Code:

#!/usr/bin/ksh
#--------------------------------------------------------------------------------------------------------------#
export PATH=$PATH:/usr/oracle/client_11g/product/11.2.0/client11g/bin
#qz='sqlplus barcqz/xdr56yhn@BARP2'
#wdc='sqlplus barcwdc/xdr56yhn@BARP2'
#--------------------------------------------------------------------------------------------------------------#
sqlplus barcqz/xdr56yhn@BARP2 <<EOF
update qz_dates set QZ_DT = sysdate;
update qz_dates_back set QZ_DT = sysdate;
update wdc_dates set WDCDATE = sysdate;
update WDC_PAY_EXRATE set WPESENTDATE = to_char(sysdate,'DD-MON-YY:HH:MI:SS') ;
update WDC_OU_STARTTIME set WOSBUSINESSDATE =sysdate;
commit;
exit
EOF

sqlplus barcwdc/xdr56yhn@BARP2 <<EOF
update WDC_PAY_EXRATE set WPESENTDATE=(substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),1,10)||'T'||substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),12,19));
commit;
exit
EOF

will wait for its next run, and will let you know if any issues appears.

Thanks a lot for all your help:-)

santosh0782 · 01-05-2015, 10:17 PM

in the logs i got below message after putting export path in script

Code:

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
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

evo2 · 01-05-2015, 10:24 PM

Hi,

do you normally set ORACLE_HOME (and possibly other variables) in your shell? If so, they will need to be set in the cron job too.

Evo2.

santosh0782 · 01-05-2015, 10:30 PM

crontab entry for this script is as:

Code:

10 6 * * * /devapp1_01/quartz/qzprod1/santosh/scripts/update_date.sh >>/devapp1_01/quartz/qzprod1/santosh/scripts/update_date.log 2>&1

evo2 · 01-05-2015, 10:37 PM

Hi,

ok, let me be explicit. What is the output of the following?

Code:

env | grep ORACLE

Evo2.

santosh0782 · 01-05-2015, 10:52 PM

output is:

Code:

barza_uat_app1[] /devapp1_01/quartz/qzprod1 > env|grep ORACLE
ORACLE_PATH=/usr/oracle/client_11g/product/11.2.0/client11g/bin
ORACLE_SID=BARP2
ORACLE_HOME=/usr/oracle/client_11g/product/11.2.0/client11g

sag47 · 01-05-2015, 11:09 PM

Quote:

Originally Posted by santosh0782

output is:

Code:

barza_uat_app1[] /devapp1_01/quartz/qzprod1 > env|grep ORACLE
ORACLE_PATH=/usr/oracle/client_11g/product/11.2.0/client11g/bin
ORACLE_SID=BARP2
ORACLE_HOME=/usr/oracle/client_11g/product/11.2.0/client11g

Add those environment variables to your cron script next to PATH. As was mentioned by evo2 the cron environment doesn't source normal user login scripts so all of the environment variables you normally depend on don't exist. That's why you have to explicitly set them.

SAM

santosh0782 · 01-05-2015, 11:14 PM

ok, i have added those environment variables to my script next to PATH as:

Code:

#!/usr/bin/ksh
#--------------------------------------------------------------------------------------------------------------#
export PATH=$PATH:/usr/oracle/client_11g/product/11.2.0/client11g/bin
ORACLE_PATH=/usr/oracle/client_11g/product/11.2.0/client11g/bin
ORACLE_SID=BARP2
ORACLE_HOME=/usr/oracle/client_11g/product/11.2.0/client11g
#qz='sqlplus barcqz/xdr56yhn@BARP2'
#wdc='sqlplus barcwdc/xdr56yhn@BARP2'
#--------------------------------------------------------------------------------------------------------------#
sqlplus barcqz/xdr56yhn@BARP2 <<EOF
update qz_dates set QZ_DT = sysdate;
update qz_dates_back set QZ_DT = sysdate;
update wdc_dates set WDCDATE = sysdate;
update WDC_PAY_EXRATE set WPESENTDATE = to_char(sysdate,'DD-MON-YY:HH:MI:SS') ;
update WDC_OU_STARTTIME set WOSBUSINESSDATE =sysdate;
commit;
exit
EOF

sqlplus barcwdc/xdr56yhn@BARP2 <<EOF
update WDC_PAY_EXRATE set WPESENTDATE=(substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),1,10)||'T'||substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),12,19));
commit;
exit
EOF

is it correct now?

sag47 · 01-05-2015, 11:20 PM

Quote:

Originally Posted by santosh0782

ok, i have added those environment variables to my script next to PATH as:
...
is it correct now?

Only trial and error will tell. Let it run again and see if it works. I don't use Oracle much so I can't be sure.

santosh0782 · 01-06-2015, 10:03 PM

I am still getting the same error message:

Code:

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
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