How to get output status of sqlplus command from shell/shell script?

Question

I want to run a sqlplus command from shell script and check the status of sqlplus command from shell script (i.e. success or failure i.e. in bash it's $? ). Can you provide me hints on how to do that?

For example following script works, but I am interested in a shell script which will tell me whether the schema/account has "alter database link" privileges or not (i.e. how the shell script can interpret output of sqlplus and tell me whether it's success or failure) -

#!/bin/bash

schema=$1
password=$2
database=$3

echo "schema is $schema"
echo "password is $password"
echo "database is $database"

sqlplus -s "$schema/$password@$database" <<ENDOFSQL
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
select privilege from session_privs where PRIVILEGE = 'ALTER DATABASE LINK';
exit;
ENDOFSQL
##### END OF SCRIPT

Thanks in advance. -Shashi Divekar

Answer 1

You ask for two separate things in your question. First you ask about checking the "output status" and then you ask about "interpret[ing] output", which is different. Here's an answer for your first question about output status, i.e. exit code:

...
exit;
ENDOFSQL

if [ $? -ne 0 ]; then
    echo "whoops"
else
    echo "all good"
fi
##### END OF SCRIPT

See the docs for more info.

Answer 2

When you query the session_privs table for the privilege column, you can expect an output along the following lines:

PRIVILEGE
-----------------------
CREATE VIEW
DEBUG CONNECT SESSION
CREATE SESSION
ALTER SESSION
...

And I know that you have tried to remove everything extraneous from the query output, nevertheless you have to check it anyway. This is since the sqlplus exit status is only about the success or failure of running the query, and not the query output, so why don’t you simply grep for the privilege you want to find?

...
SQLPLUS_OUTPUT=$(sqlplus -s "$schema/$password@$database" <<ENDOFSQL
select privilege from session_privs;
exit;
ENDOFSQL
) || { echo "Failed to run query"; exit 1; }

if [ -n "$(printf '%s\n' $SQLPLUS_OUTPUT | grep 'ALTER DATABASE LINK')" ]; then
    echo "Privilege located!"
else
    echo "Privilege NOT located."
fi
...