UNIX for Dummies Questions & Answers

Hello

I would like to perform a select from a oracle table and return those values to my shell script

For example:

site=head -1 $infile | cut -c1-15 | awk '{printf "s%", $0}
sqlplus -s /nolog |& #Open pipe to sql

select col1, col2, col3, col4
from oracle_table
where col5 = $site

exit # return to UNIX Script

perform various operations using the retuned values col1, col2, col3 & col4 from the select statement

Thank you

Pat Houtakker

I don't see a question in your post. I don't want to sound like Alex Trebek, but a question would help... As it is, I must first guess at your question and then provide an answer.

My guess: How to use a ksh coprocess? I don't know mysql, so I'll use a program that I do know: adb. Here is an interactive adb session on an HP-UX 11.0 system...

Code:

# adb -k /stand/vmunix /dev/kmem
maxuprc/D
maxuprc:
maxuprc:        200
nproc/D
nproc:
nproc:          1264
$q
#

The stuff in bold is what I typed. $q looks odd, but that is an adb command to quit. Notice that I get two lines of output with the first line being rather useless. My script will need to eat that line. I must take the output as it comes. So here is the script...

Code:

#! /usr/bin/ksh

adb -k /stand/vmunix /dev/kmem |&

print -p 'maxuprc/D'
read -p garbage
read -p garbage maxuprc
echo maxuprc = $maxuprc

print -p 'nproc/D'
read -p garbage
read -p garbage nproc
echo nproc = $nproc

print -p '$q'
wait
exit 0

So "print -p" sends stuff to the coprocess. And "read -p" gets the results. I don't need to "get back" to the script...I never go anywhere. I don't really need to wait for the coprocess to exit, but I always do...just habit.

I hope that you weren't actually asking an sql question.

To all:

sorry - my question must not have been clearly stated - my problem is how do I assign the results of the sql select statement to variables that I can use in my shell script - i.e. connect to Oracle, execute the select statement and return to UNIX and have the ability to use the results of the select statement

thank you

You can try this

#!/bin/sh
var1=`sqlplus -s scott/tiger <<EOF
set head off;
select something from table;
exit;
EOF`
echo $var1

From a previous post thread

OUT PARAMETER FROM ORACLE

Ever wanted to execute an
Oracle Procedure which contains
an out parameter, and use the
value returned from the out
parameter in the shell variable.

Try out this. The procedure
test1 adds two nos. and returns
output into the out parameter. Here is the

export ORACLE_HOME=< type in your Oracle Home Path >
export ORACLE_SID=< Mention the Oracle SID >
export PATH=$ORACLE_HOME:$ORACLE_HOME/bin:$PATH # This Path
is set to access the sqlplus executable
dummyvar=`sqlplus -s tcon4iqalib/tcon4iqalib <<end
set pagesize 0 feedback off ver off heading off echo off
serverout on
variable verr_mesg number
exec test1(4,5,:VERR_MESG)
print verr_mesg
exit;
end`
echo " Error is " $dummyvar
echo " Result is " $dummyvar
echo $dummyvar
#end of shell script

Thanks for the replies - sorry for the slow post, but this is what I actually ended up using.


fields=`sqlplus -s / <<END
set pagesize 0 feedback off verify off heading off echo off
select col1 || ':' || col2 || ':' ||
col3 || ':' || col4|| ':' || col5|| ':' || col6 || ':'
from table_name
where col4 = '$siteid_upper';
exit;
END`

col1=`echo $fields | awk -F: '{printf "%s", $1}'`
col2=`echo $fields | awk -F: '{printf "%s", $2}'`
col3=`echo $fields | awk -F: '{printf "%s", $3}'`
col4=`echo $fields | awk -F: '{printf "%s", $4}'`
col5=`echo $fields | awk -F: '{printf "%d", $5}'`
col6=`echo $fields | awk -F: '{printf "%d", $6}'`

Wow, someone actually revisited the boards to post their solution! lol