Shell + PL/SQL

[janjacina]

Hi,

I have a problem with unix and procedure in oracle.

in package i have a procedure, which just create a file a write in it some information.

i call this procedure from unix shell script.

and the problem is...

when i run the script from shell command line everything is ok, the file is created.
BUT
when i put script into crontab... the file is NOT created...

in shell script and in oracle procedure every path is absolut.
the script is running (exactly by crontab specification) all echos are written into crontaboutput.file, sql log is ok -> procedure succesfully...
Everything works, only the file which created procedure doesn't exist....

CRONTAB ------------------------------------------------------------
00 10 * * * /u03/app/DEV2/janjaci/monitoring/jmon > cronoutput.file

SHELL SCRIPT -------------------------------------------------------
#!/bin/sh
#
#
ARPATH="/u03/app/DEV2"
JMONPATH="$ARPATH/janjaci/monitoring"
USER="ar"
PASS=`cat $ARPATH/.arpw`
DB="BP_DEV2"
echo Generating report html file...
sqlplus /nolog > $JMONPATH/jmon_sqlplus.log < conn $USER/$PASS@$DB
set serveroutput on
execute osk\$jmon.ahaha
show error
EOF
echo report done
echo sending mail
mail `cat $JMONPATH/jmonlist` < $ARPATH/data/disp/out/jmon_report.html
echo mail sent
echo delete report from directory
rm $ARPATH/data/disp/out/jmon_report.html
echo jmon done
exit

PROCEDURE AHAHA -------------------------------------------------
PROCEDURE ahaha
IS
vfile utl_file.file_type;
BEGIN
vfile := utl_file.fopen('/u03/app/DEV2/data/disp/out','jmon_report.html','w');
utl_file.put_line(vfile,'halo halo');
utl_file.fclose(vfile);
END ahaha;



JMON_SQLPLUS.LOG ----------------------------------------------
SQL*Plus: Release 8.0.4.0.0 - Production on Wed Feb 25 10:10:34 2004

(c) Copyright 1997 Oracle Corporation. All rights reserved.

SQL> Connected.
SQL> SQL>
PL/SQL procedure successfully completed.

SQL> No errors.
SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production


CRONOUTPUT.FILE -------------------------------------------------
Generating report html file...
report done
sending mail
mail sent
delete report from directory
jmon done

------------------------------------------------------------------

I don't know what should be wrong... from shell command line it works without any problems BUT from crontab doesn't create jmon_report.html file....

HEEELP !!

[ales]

You must set ORACLE_HOME, ORACLE_SID etc.
cron does not run your .profile, you need to run it yourself. The easiest way is call it with absolute path from inside your script before calling sqlplus, e.g. ". /u03/app/DEV2/janjaci/.profile"

[janjacina]

I don't think so that there is a problem, because...

the procedure was run => ORACLE_HOME, ORACLE_SID is ok

in my .profile -> there isn't anything (only information for mail...)

:(

[janjacina]

you're right, sorry for doubt raising...

[oracle_faq]

If you are running the script through crontab, .profile is not executed before the shell script exceution. When you execute the script from unix prompt, your env is setup by execution of .profile (at the time of login).

I would suggest to add ". .profile" to your script and see if it works.

Add it after "#!/bin/sh".