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.

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

  echo "
    set feedback off verify off heading off pagesize 0
  " |  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 
  echo "user $u has userid $i"