How does one SELECT a value from a table into a Unix variable? From SQL to Shell

One can select a value from a database column directly into a Unix environment variable. Look at the following shell script examples:

#!/bin/sh

VALUE=`sqlplus -silent user/password@instance < set pagesize 0 feedback off verify off heading off echo off

select max(c1) from t1;

exit;

END`

if [ -z “$VALUE” ]; then

echo “No rows returned from database”

exit 0

else

echo $VALUE

fi

Second example, using the SQL*Plus EXIT status code (can only return integer values smaller than 256):

#!/bin/ksh

sqlplus -s >junk1 /nolog < connect user/password@instance

column num_rows new_value num_rows format 9999

select count(*) num_rows

from table_name;

exit num_rows

EOF

echo “Number of rows are: $?”

Yet another example, only this time we will read multiple values from SQL*Plus into shell variables.

sqlplus -s /nolog |& # Open a pipe to SQL*Plus

print -p — ‘connect user/password@instance’

print -p — ‘set feed off pause off pages 0 head off veri off line 500’

print -p — ‘set term off time off’

print -p — “set sqlprompt ””

print -p — “select sysdate from dual;”

read -p SYSDATE

print -p — “select user from dual;”

read -p USER

print -p — “select global_name from global_name;”

read -p GLOBAL_NAME

print -p — exit

echo SYSDATE: $SYSDATE

echo USER: $USER

echo GLOBAL_NAME: $GLOBAL_NAME