integating Korn shell and SQLPlus

From: Shaun Troedson <sct_at_null>
Date: Wed, 14 May 2003 21:17:17 +0930
Message-ID: <3ec22d52$0$24252$afc38c87@news.optusnet.com.au>

Thought I'd share this script, it integrates SQL Plus into the Korn shell. Useful in scripts as it enables the blending of SQL Plus commands and shell commands, especially useful in enabling updates and commits of the same session to be interspersed through a script. It is in no way complete (eg I just noticed there's no insert() function) and I'd be surprised if it's bug free, edit at your will.

Copy the text below to a file, say sqlplus.sh Call it using ". sqlplus.sh", typical SQLPlus commands are then available as shell functions. Unfortunately "select" has been renamed as "shelect" as the former is a KShell command.
From the KShell prompt, call "connect scott/tiger" to start a session, or "quit" to end it.
an extra function called getrow() is like select but is useful for capturing data and setting a variable
eg "set -A data $(getrow sysdate,user from dual)" Be aware that most times the command will need to be escaped from the shell eg using double quotes
eg update "met_log set status='FAILED' where log_date in (select bla bla)"

---

set +u
connect()
{

  userpass=$*
  set +u
  if [[ ${sqlstarted:-0} -ne 1 ]]
  then

    if [[ "$userpass" = "" ]]
    then

      read user?"Enter user: "
      stty -echo
      read pass?"Enter password (TIGER): "
      stty echo
      echo
      if [[ "$pass" = "" ]]
      then
        pass=TIGER
      fi
      userpass="$user/$pass"

    fi

    #start the sqlplus command
    sqlplus $userpass |&
    #ensure timing is set on

    print -p "set timing on"
    print -p "set tab off"
    print -p "select 'X' from dual;"

    #get rid of initial junk
    line=""
    while [[ $line != Elapsed* ]]
    do

      if ! read -p line
      then
        echo Could not start sqlplus
        exit
      fi

    done
    sqlstarted=1
  fi
}

shelect ()
{

  if [[ $sqlstarted != 1 ]] then
    echo Not connected >&2
    return 1
  fi
  print -p "select $*;"
  line=""
  data=""
  while :
  do
    read -p line
    [[ $line = Elapsed* ]] && break
    echo $line
  done
}

#this one only returns one row of data, useful for capturing data getrow ()
{

  if [[ $sqlstarted != 1 ]] then
    echo Not connected >&2
    return 1
  fi
  print -p "select $*;"
  line=""
  savedata=0
  data=""
  while :
  do
    read -p line
    [[ $line = Elapsed* ]] && break
    if [[ savedata -eq 1 ]]
    then
      data="$data $line"
    fi
    if [[ $line = -* ]]
    then
      savedata=1
    fi
  done
  echo $data
}
quit ()
{

  if [[ $sqlstarted != 1 ]] then
    echo Not connected >&2
    return 1
  fi
  unset sqlstarted
  print -p "quit"

}
update ()
{

  if [[ $sqlstarted != 1 ]] then
    echo Not connected >&2
    return 1
  fi
  print -p "update $*;"
  line=""
  while [[ $line != Elapsed* ]]
  do
    read -p line
  done
}
delete ()
{

  if [[ $sqlstarted != 1 ]] then
    echo Not connected >&2
    return 1
  fi
  print -p "delete $*;"
  line=""
  while [[ $line != Elapsed* ]]
  do
    read -p line
  done
}
execute ()
{

  if [[ $sqlstarted != 1 ]] then
    echo Not connected >&2
    return 1
  fi
  print -p "execute $*"
  line=""
  while [[ $line != Elapsed* ]]
  do
    read -p line
  done
}
commit ()
{

  if [[ $sqlstarted != 1 ]] then
    echo Not connected >&2
    return 1
  fi
  print -p "commit;"
  line=""
  while [[ $line != Elapsed* ]]
  do
    read -p line
  done
}
truncate ()
{

  if [[ $sqlstarted != 1 ]] then
    echo Not connected >&2
    return 1
  fi
  print -p "truncate $*;"
  line=""
  while [[ $line != Elapsed* ]]
  do
    read -p line
  done
}
rollback ()
{

  if [[ $sqlstarted != 1 ]] then
    echo Not connected >&2
    return 1
  fi
  print -p "rollback;"
  line=""
  while [[ $line != Elapsed* ]]
  do
    read -p line
  done
} Received on Wed May 14 2003 - 06:47:17 CDT