-
Shell + PL/SQL
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 !!
-
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"
Ales The whole difference between a little boy and an adult man is the price of toys
-
?
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...)
:(
-
i'm sorry
you're right, sorry for doubt raising...
-
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".
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|