Accessing SQL*Plus using a Korn Shell Coprocess

Overview

The Korn shell, or POSIX shell, allows you to run one or more commands as background processes. These commands, run from within a shell script, are called coprocesses.

Designate a coprocess by placing the |& operator after a command. Both standard input and output of the command are piped to your script.

A coprocess must meet the following restrictions:

• Include a newline character at the end of each message
• Send each output message to standard output
• Clear its standard output after each message

Coprocesses, sometimes called coroutines, consist of two or more processes programmed to run simultaneously, and typically communicate with each other.  The parent program (script) will start a command or program in the background that is able to have two-way communication with it.  In more technical terms, the standard input and output of the background program is connected to the parent program with a pipe.

Coprocesses are useful if you want to provide an interface to existing/legacy programs for example SQL*Plus

If you start a background process by appending |& to a command instead of &, the Korn shell will set up a special two-way pipeline between the parent shell and the new background process. The command read -p in the parent shell reads a line of the background process standard output; similarly, print -p in the parent shell feeds the standard input of the background process.

Notice, that the parent shell communicates with the background process independently of its own standard input and output. The background process need not have any idea that a shell script is communicating with it in this manner. This means that the background process can be any pre-existing program that uses its standard input and output in normal ways, for example SQL*Plus.

Example

Here is an Example which checks the Oracle database for invalid objects using SQL*Plus and a parent shell script. If any invalid objects are found a message is mailed to an administrator.

#!/bin/ksh

# -------------------------------------------------------------
#
# Purpose:     Check for INVALID objects
#
# -------------------------------------------------------------
#

# DEBUG
# set -x

ORACLE_HOME=/opt/oracle/product/10.2.0
export ORACLE_HOME

SENDTO="xxx.yyy@zzz.com"
export SENDTO

USR_PWD="usr/pwd";
export USR_PWD

SID_LIST="TEST"
for i in ${SID_LIST}
do
ORACLE_SID=${i}
export ORACLE_SID

# Open pipe to Oracle sqlplus
${ORACLE_HOME}/bin/sqlplus -s ${USR_PWD} |&

# Initialize Buffer
OUT=""

# Check for INVALID objects
print -p "SET heading OFF;"
print -p "SELECT count(*)
                    FROM dba_objects
                   WHERE status != 'VALID'
                     AND owner NOT IN ('PUBLIC');"
read  -p OUT

# Remove empty lines from output
((i=0))
while [ "${OUT}" = "" -a "${i}" -lt "5" ]; do
   read -p OUT 2>/dev/null
   ((i=i+1))
done

# Found any INVALID objects? (OUT > 0)
if [ "${OUT}" != "0" ]
then
   mail -s "`uname -n`: ${OUT} invalid objects
           found in ${ORACLE_SID}" $SENDTO
fi

# Close sqlplus and Close
        # co-processes' file descriptors
print -p "exit 0" 2>/dev/null
exec 3>&p
done