DBA Tips Archive for Oracle

SELECTing a value from a table into a Unix variable

by Jeff Hunter, Sr. Database Administrator

You can select a value from a database column directly into a Unix shell variable.

Look at the following shell script examples:

#!/bin/ksh
VALUE=`sqlplus -silent "/ as sysdba" <<END
set pagesize 0 feedback off verify off heading off echo off
select max(sequence#) from v\\\$log_history;
exit;
END`

if [ -z "$VALUE" ]; then
    echo "No rows returned from database"
    exit 0
else
    echo "Max Sequence Number: $VALUE"
fi



Max Sequence Number:        49

#!/bin/ksh
sqlplus -s > junk1 "/ as sysdba" <<EOF
column num_tables new_value num_tables format 9999
select count(*) num_tables from dba_tables;
exit num_tables
EOF
echo "Number of tables: $?"



Number of tables: 97

#!/bin/ksh

sqlplus -s "/ as sysdba" |&     # Open a pipe to SQL*Plus

print -p -- 'set feedback off   linesize 500   echo off'
print -p -- 'set pause off      pagesize 0     verify off'
print -p -- 'set heading off    term off       timing 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 -- 'select value from v$parameter where name = '\''dispatchers'\'';'
read  -p dispatchers

print -p -- exit

echo "SYSDATE      : $SYSDATE"
echo "USER         : $USER"
echo "GLOBAL_NAME  : $GLOBAL_NAME"
echo "dispatchers  : $dispatchers"



SYSDATE      : 24-SEP-04
USER         : SYS
GLOBAL_NAME  : ORCL
dispatchers  : (PROTOCOL=TCP) (SERVICE=orclXDB)