Shell Script that calls a PLS/SQL Proc

[jayjaybigs]

Hello,

Does anyone has a shell script that could be excuted from the crontab that calls a PL/sql PROCEDUR from certain directory. The script should take into consideration the following:
-- $PROC_NAME is the procedure that needs to be executed
-- $ORACLE_HOME is path inwhich oracle is installed.
-- $DB_USER
-- $DB_PASSWD
-- $ORACLE_INSTANCE are Database user name, password & instance you are connecting.
-- parms list.... is list of parameters that may be quired.
-- RETURN_VALUE will be having the values printed from the SQL Block.

Please your help will be appreciated.

 

[stefanhei]

Hi.
Have a look at faq186-2220 in Oracle 8i-FAQ.
I'd use a sqlplus commendscript instead of a procedure (or call the procedure from inside the script) and would be careful with scripts/procs returning or printing something when using cron.

Stefan

 

[Ghodmode]

[ul]
[li]If it's procedure code stored in a file, you can't pass it any parameters because the parameters are defined in that file.[/li]
[li]If it's a procedure definition in that file, All you can do from cron using that file is define the procedure over and over again, never executing it.[/li]
[li]If you have a stored procedure in the database and you want to execute it from a script executed from cron which calls the procedure and passes the necessary arguments, that's not a problem, but then there isn't any file involved[/li]
[/ul]

What do you need to do?

--
-- Ghodmode