Shell Programming and Scripting

#!/bin/sh
sqlplus -s username/password<<END
execute procedure_name( parameters,....)
Commit;
exit;
END


eg:

sqlplus -s scott@dbase/tiger<<END
execute test_proc(nila,20,101);
commit;
exit;
END

$ cat use_oracle_proc.ksh
#!/bin/ksh

run_sql()
{
  $ORACLE_HOME/bin/sqlplus -S $scott@dbase/tiger <<EOF
  SET PAGESIZE 0;
  SET FEEDBACK OFF;
  SET SERVEROUT ON;
  VAR major NUMBER;
  VAR minor NUMBER;
  EXEC DBMS_PROFILER.GET_VERSION (:major, :minor);
  PRINT major;
  PRINT minor;
EXIT;
EOF
}

set -A ARRAY $(run_sql)
ARRAYCOUNT=${#ARRAY[*]}
ARRAYIDX=0

while (( $ARRAYIDX < $ARRAYCOUNT ))
do
 echo  "ARRAY[$ARRAYIDX]=(${ARRAY[ARRAYIDX]})"
 ARRAYIDX=$(($ARRAYIDX+1))
done

exit 0

$ ./use_oracle_proc.ksh
ARRAY[0]=(2)
ARRAY[1]=(0)