Running SQLPLUS Script in CRONTAB

Shell Programming and Scripting
1

Hi,
Can someone please help me here with this one.

This is my script:

# more tosh.sh
#!/usr/bin/ksh
clear
. /home/oracle/.profile
echo "Good morning, world."
export ORACLE_HOME=/u01/app/oracle/product/9.0.1
export PATH=$ORACLE_HOME/bin:/usr/local/bin
export ORACLE_SID=xxxx

/u01/app/oracle/product/9.0.1/bin/sqlplus -s xxxx/xxxx @/home/ivbbuild/rec.sql > /home/ivbbuild/rech.txt

However when I run it from crontab I dont get anything. What am I missing here.

My CRONATAB entry:

0,5,10,15,20,25,30,35,40,45,50,55 * * * * /home/ivbbuild/tosh.sh > /home/ivbbuild/tile.txt 2>&1

The tile.txt file is produced with just "Good morning, world.". The file with SQL output is not produced (/home/ivbbuild/rech.txt)
Tosh.

2

Hi Tosh,

Have you terminated the SQL statement inside '/home/ivbbuild/rec.sql' with semi colon?

Also exit is expected inside the SQL file when you do it in this fashion. Refer below example

$ cat 1.sh
sqlplus -s username/passwd @1.sql > 1.txt
$ cat 1.sql
select * from dual;
exit
$ sh 1.sh
$ cat 1.txt

D
-
X

$
3

Maybe something is missing in the environment? Missing another variable you need to export?
You could also try to add a 2>&1 in the end of the sqlplus line, just in case to catch everything.

Is the SQL statement running fine when being run on the command line? If yes, then maybe I'd go for a missing environment variable.

4

Hi ranjithpr/Zaxxon,

Here is my .sql file:

$ more rec.sql
select sum (balancechange)
from thistory INNER JOIN account ON 
accounthistory.subscriberid = account.subscriberid 
where ((account.msisdn not like'200%') 
and (account.msisdn not like '100%')) 
and changedate between to_date(SYSDATE) and to_date(SYSDATE + 1)
and UPDATEMETHODID in ('334');
exit;

Also where do I add 2>&1 in the .sql file (before the semi colon or not)?

Thanks in advance guys.

Tosh.

5

Zaxxon mean in 'sqlplus' line, not in sql file. You have to add it like

/u01/app/oracle/product/9.0.1/bin/sqlplus -s xxxx/xxxx @/home/ivbbuild/rec.sql  > /home/ivbbuild/rech.txt 2>&1
6

Hi Ranjithpr,

Will the exit clause in the .sql file have a semi colon? Please advise.

7

It is not required, but if you give it won't create any problem.

In command line, are you able to execute script '/home/ivbbuild/tosh.sh', Are you getting the expected result?

Above cron tab entry is under which user, is it under 'oracle' or 'root'?

8

Hi Ranjithpr,

Yes I am able to execute '/home/ivbbuild/tosh.sh' from any directory. Additionally I am able to execute:

/u01/app/oracle/product/9.0.1/bin/sqlplus -s xxxx/xxxx @/home/ivbbuild/rec.sql  > /home/ivbbuild/rech.txt

from anywhere.

Also I am cron tab entry is under root user not oracle. I thought thats why we put the environment variables right?

I have also added new variables:

export TNS_ADMIN=/u01/app/oracle/product/9.0.1/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib

Please advise. Thanks in advance.

Tosh.

---------- Post updated at 12:36 AM ---------- Previous update was at 12:35 AM ----------

Hi All,

Also

 /home/ivbbuild/rech.txt 2>&1

does not yield any error logs. The rech.txt simply wont generate.

regards,
Tosh.

9

Just as a sidenode: Even if you run it from the oracle crontab you would have to set up all environmental things like for example

. ./.profile
. ./someenv
export ...

When running a crontab, there will be no environmental stuff used like when you log in normal with that user.

If you run this script for testing purpose in the crontab of user oracle, including sourcing it's .profile and environment files, it would be interessting if it works.
If yes, then something in the environment of root might be still missing.

10

If the permission of file '$ORACLE_HOME/bin/oracle' is not set properly then 'sqlplus' won't work for non-oracle users.

Permission of this file must be like '-rwsr-s--x'.

Regards,

Jith

11

Hi Jith,

The permission of file '$ORACLE_HOME/bin/oracle' is set to '-rwsr-s--x' from the start.

Still no luck.

Has anyone ever run this before?

regards,
Santosh.

12

Hi Guys,

Any more suggestions?

regards,
Santosh.