Check stored procedure in shell script-CodePudding

I want to know how to write a shell script that access oracle database and check a procedure that executed earlier if it is succeeded or returned error

CodePudding user response：

If you want to know about procedure that executed earlier (in a different session?), you can't, unless the results were persisted somehow. Oracle by itself doesn't store info about every procedure call.

If you just want to run and check the stored procedure immediately from the shell script, that's a standard pattern, use whenever sqlerror/oserror clause:

sqlplus [connection_string] <<-EOF
  whenever oserror exit failure;
  whenever sqlerror exit failure;
  exec [stored_procedure];
EOF

if [[ $? != 0 ]]; then
  echo "Procedure failed!"
  exit 1
fi

->Reference