plsql - How to rxecute an ad-hoc stored procudure in UNIX shell script? Also is it possible to have IF-ELSE/WHILE LOOPS in sql-plus? -

i have 3 part question arises out of following requirement. have been asked automate stored procedures using shell scripts. earlier nature of stored proc(not definition, way of executing in pl/sql) used this

begin   sp_rtb_update('declare v_var1;                  begin                    insert tradingaccount(taextrefseqid, tradingaccountid,                                               lastupdatedby, lastupdatedate,                                               externalsystemreferencevalue)                       values (seq_ta_ext_ref_id.nextval,12345,                               9999,sysdate,                               v_var1)                        returning taextrefseqid v_pk;                     insert extrefstatus( taextrefseqid, sincedatetime,                                               statusid, lastupdatedby )                      values ( v_pk, sysdate, 54, 9999 );'); end; 

so basically, stored proc adhoc 1 accepts entire query first parameter. internally, proc runs audit tables (along query) store maintain history/changes. now, want design shell script can automate stored proc without manual intervention.

i have done this

#!/bin/bash  echo "please enter username"  read dbuser  echo "please enter password"  read dbuserpassword  echo "please enter database name"  read dbsid  sqlplus -s $dbuser/$dbuserpassword@$dbsid <<eof  if [ $? -eq 0 ]               echo "connection ok"              echo "please insert rms code added"              read rms              insert tradingaccount(taextrefseqid,tradingaccountid,                                        lastupdatedby, lastupdatedate,                                         externalsystemreferencevalue )               values (seq_ta_ext_ref_id.nextval,12345,                       9999,sysdate,                       '$rms')               returning taextrefseqid v_pk;           insert extrefstatus( taextrefseqid, sincedatetime,                                    statusid, lastupdatedby )            values ( v_pk, sysdate, 54, 9999 );     commit;  echo "done successfully" else  echo "connection not ok" fi 

now there many issue piece of code. following doubts.

1.if [ $? eq 0 ].. throwing error "unknown command, remaining line neglected". so, possible have if-else/while loops in sqlplus(unix) statements

1. also, returning option of insert statement not working. can possible equivalent of when run in unix(sqlplus)?

3.is @ possible call adhoc stored procedure within script like

execute sp_rtb_updates('declare v_var1....'); 

or have separate queries , write additional queries handle audit tables? fail-safe stored proc?

please advise. in advance!

this partial answer - mixing shell , sqplus commands created here document - typing directly sqlplus.

if [ $? -eq 0 ] 

is shell command not sqlplus. never inside here document if sqlplus fails. not test see if worked way.

sqlplus usr/pwsd@instance <<-eof  -- sqlplus dml , pl/sql commands in here: select, update, etc.     eof [ $? -ne 0 ] && echo 'failure' 

likewise: read rms before here document.

with regard creating "whole command ad-hoc", use exec immediate - dynamic sql

rms=13 user=foo pswd=foo mycommand=$(printf "exec immediate 'select blah table blah = %s';"  "$rms" )  sqlplus @user/$pwsd/@instance <<-eof     $mycommand eof 

the above passing example, please learn how here, please follow links @ bottom of page on dynamic sql:

http://docs.oracle.com/cd/b19306_01/appdev.102/b14261/executeimmediate_statement.htm