René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Connecting SQL*PLUS with a shell script

Here's a piece that allows to execute select statements on an Oracle database from a shell script. It consists of two parts: a shell function (read_sql_stmt) and the part that actually uses the function. 
#!/bin/bash

read_sql_stmt() {
  typeset stmt=$1
  typeset login=$2

  echo "
    set feedback off verify off heading off pagesize 0
    $stmt;
    exit 
  " |  sqlplus -s  $login
}
The second part: read_sql_stmt is executed with an SQL statement and a login, its output is piped into an ordinary shell while loop: 
read_sql_stmt "select username, user_id from dba_users" "system/system_pw" | while read u i 
do
  echo "user $u has userid $i"
done