SQLPlus in Shell Script: Send COUNT() to Variable, Error to File

Question

I'm calling Oracle SQL*Plus from the Korn shell (ksh).

My ultimate goal is to get the value from a SQL statement's COUNT(*) into a shell-script variable.

However, if a SQL error is raised, I would like to report the SQL exception in full and terminate the script.

I'm having a hard time accounting for both scenarios to my satisfaction.

#!/bin/ksh

test_count=$(sqlplus -s scott/tiger << EOF1
    WHENEVER SQLERROR EXIT 1
    SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
    SELECT COUNT(*) FROM scott.emp WHERE empno > 7777;
    EXIT;
EOF1
)

sql_ret_code=$?

if [ $sql_ret_code -ne 0 ]
then
    echo "Error encountered."
    echo
    exit 1
fi

echo "test_count = $test_count"

exit 0

In the "happy path" case, the above works (in that it displays test_count = 8).

If I simulate a SQL error by referencing a nonexistent table (e.g., "scott.emp_bogus"), the code above prints only the generic message "Error encountered." I would prefer to report the full Oracle exception, something like this:

Error encountered.

SELECT COUNT(*) FROM scott.emp_bogus WHERE empno > 7777
                          *
ERROR at line 1:
ORA-00942: table or view does not exist

I have been playing around with redirecting my stderr to a file, to "cat" out in case of an exception, but then my non-error-condition output was also being directed to that file, which I don't want.

In the absence of any SQL error like this, I would simply prefer to direct the value of COUNT(*) to the shell-script variable test_count.

If, on the other hand, the SQL raises an exception, I would prefer to direct that error to a file, and then cat that file from within my "Error encountered" condition/if clause.

Is it possible?

Thank you.

J. Chomel · Accepted Answer · 2016-04-22 07:15:03Z

2

I may be missing something, but you have everything here to make it work!

Except you leave the script too soon (with `exit 1`):

You must not exit in error case, because the error you want do display is in your result variable test_count! so why not displaying it too? Here is what I can propose

[...]
if [ $sql_ret_code -ne 0 ]
then
  echo "Error encountered."
  echo
  echo "ERROR: $test_count"
  echo "abort."
  exit 1
fi

echo "test_count = $test_count"

exit 0

(hope I'm not blatantly out of the game ;)

answered Apr 22, 2016 at 7:15

J. Chomel

8,44215 gold badges44 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLPlus in Shell Script: Send COUNT() to Variable, Error to File

1 Answer 1

Except you leave the script too soon (with `exit 1`):

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Except you leave the script too soon (with exit 1):

Comments

Your Answer

Sign up or log in

Post as a guest

Related

Except you leave the script too soon (with `exit 1`):