IF| ELSE condition to check if Oracle database query output shows any result or NULL

Question

I am finding it hard to write an if|else statement for the below condition. What I am doing is I have an Oracle database where I am running the below query and it returns output in rows (it can show records or it can return NULL values), what I want is if the row returned as output then it should execute couple of commands and if no rows are returned then it should execute second set of commands.

Example 1 -- it shows the output.

SQL> select value from v$parameter where name='spfile';
VALUE
---------------
+DATA_01/TESTDB/spfileTESTDB.ora

Example 2 -- In some cases it can return no rows.

SQL> select value from v$parameter where name='spfile';
VALUE
--------------

Answer 1

You might want to do something like this:

db_connect="user/pass@db"

output=$(
    sqlplus -s "$db_connect" <<'END' | sed '/^$/d'
        set heading off
        select value from v$parameter where name='spfile';
END
)

The -s option and set heading off sqlplus command are meant to minimize extraneous output. sed '/^$/d' is to delete empty lines. The result in $output should either be an empty string (for null result) or the line(s) of query output.

Note that the END heredoc terminator must not have any other characters on that line. That's why it's not indented like the other lines.

Then you can do:

if [[ -n "$output" ]]; then
    stuff if there is output
else
    stuff if there is null output
fi