I always liked bash programming, and sometimes need to set Bash variables using information from Oracle tables.
To achieve that I’m using below solution, which I explain in details later.
# SQLPlus should return a string with all Bash commands $ORACLE_HOME/bin/sqlplus -S -L -R 3 / as sysdba > /tmp/sqlplus.log <<-EOF SET PAGES 0 FEEDBACK OFF TIMING OFF VERIFY OFF LINES 1000 SELECT 'OK:DBNAME='||UPPER(D.NAME)||'; INST_NAME='||I.INSTANCE_NAME AS STR FROM V\$DATABASE D, V\$INSTANCE I; EOF # Now, tests if sqlplus exit fine, and check if result string starts with OK keyword if [ $? -eq 0 ] && [ "$( cat /tmp/sqlplus.log | head -n 1 | cut -d: -f1 )" == "OK" ]; then sed -i 's/OK://g' /tmp/sqlplus.log while read r; do eval "$r"; done </tmp/sqlplus.log else echo "Failed to search local instance $ORACLE_SID" return 2 fi
In the first part, I call sqlplus, which select should return an string that contains valid bash commands, to set all variables I need. In this example, sqlplus returns Database Name and Instance Name:
OK:DBNAME=xpto; INST_NAME=xpto_1;
The second part, exists only for consistency checks. It verify if result string starts with “OK” keywork. If all went fine, it execute the result string using the bash command eval.
eval – That is where magic happens!
The command eval, can be used to evaluate (and execute) an ordinary string, using the current bash context and environment. That is different than when you put your commands in a subshell.
The below source code, reads sqlplus.log and execute every command using eval:
while read line; do eval "$line"; done </tmp/sqlplus.log
Cassiano.
