Tag: database script

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.