sqlplus output from ksh.

UNIX Solaris
1

Hi All, I have the below simple script.
It runs just fine by itself when I manually invoke it.
But once I put it in the crontab with entry:

* * * * * /users/myuser/test.ksh >> /users/myuser/log/test.txt

"

It does NOT print the returned value ($REMAIN) from the DB!?

The result in the text file is just nothing like this:

start: Sat Dec 17 17:59:00 EST 2011
Returned value=

Anyone has any idea? It is obviously not a permission issue otherwise even those static texts would not have come back.....and when I run it manually it returnes the count. What else can it be? Thx.

#!/bin/ksh
 
echo "start:" $(date)
 
REMAIN=`sqlplus -silent uid/pwd@MYDB <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
 
select count(*) from my_table;
 
EXIT;
EOF`
 
echo Returned value=$(REMAIN)

P.S. Additionally, if I have multiple records coming back, or at least mutiple fields from the same record, how would that be handled in a simple way? But this is the next step, first I need to see why crontab does not get the return value to the text file output.[/SIZE][/FONT]

2

cron doesn't share your usual shell unless you explicitly define it, so your not necessarily getting to access oracle the way you're expecting. The output might be an empty resultset, but it's probably more like an error output. Be sure you're getting the proper environment from within cron.

You're also embedding backticks into a $() syntax...two additional forks off of your script's run. You might intend for defining your variable with double-quotes instead.

3

Right, was kind of thinking along the same tracks. How do I make sure I get proper env from within cron? I am using crontab in my own user, which points to: /usr/bin/crontab
what "user" does crontab execute the script as?
And how do I further define oracle parm? Just set ORACLE_HOME within the sript you mean?

p.s. As for the $(REMAIN) I just use $REMAIN...

---------- Post updated at 10:23 PM ---------- Previous update was at 10:11 PM ----------

Hey, I set ORACLE_HOME and PATH and ORACLE_SID at the top of the script and it worked !! Thanks a lot dude....

p.s. Wouldn't there be an easier way? someone suggested putting "#!/bin/ksh" in the crontab file at the top ..but that did nothing!

---------- Post updated at 10:24 PM ---------- Previous update was at 10:23 PM ----------

Now where is a sample script to get mutiple values from the DB insead of just 1 variable?

4

The shell has nothing to do with the incomplete environment in this case.
You may define all your variables in a separate file and source it from all the scripts that need those variables.

Please post a sample of the result set and an example of the expected result/output.