Populer des variables shell avec du SQL #oracle #shell

Populer des variables shell avec du SQL.md

How does one SELECT a value from a table into a Unix variable?

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 << END
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 /nolog << EOF
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.

#!/bin/ksh
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