topic Re: Reading variable from SQL to Unix in Operating System - HP-UX

Reading variable from SQL to Unix

cool_reptile — Thu, 17 Sep 2009 19:30:16 GMT

Hi,
I have a korn shell script and a variable that I'm trying to load data into from a table in Oracle, this variable will then be used in a subject line in an email that will be sent from Unix. I have this so far but am having difficulty still trying to get the variable to display properly in the mail -s command. Can someone please help? Here is the code that only returns '0' in the subject line but I want to actually return the output from the SQL statement into that variable then display it in the Subject line of the Unix mail:

#!/bin/ksh
#COMMENT
. /apps/ppa/bin/.profile
#export ORACLE_HOME=
#export PATH=$PATH:$ORACLE_HOME/bin
export emailadd1=firstname.lastname@email.com
export VAR=0

sqlplus /nolog << EOF
connect opcppa/oracle
set echo off;
set termout on;
set linesize 100;
set pagesize 60;
set newpage 0;
set heading off;
set feedback off;
spool /tmp/output.lis;
select COMPANY into "$VAR" from CUSTOMERS where rownum<2;
spool off;
EOF
/usr/bin/uuencode /tmp/output.lis "TestFileOracleData.txt"|mail -s "$VAR" $emailadd1

#End-of-Script

Thanks!

Re: Reading variable from SQL to Unix

Hein van den Heuvel — Thu, 17 Sep 2009 19:50:34 GMT

sqlplus does NOT understand shell variables.

Its only communication channel back is really STDOUT data, and for numerics in a pinch the process exit status.

Google for "sqlplus select into shell variable"
You'll get a lot of useful example.
For example you find a construct like:

#!/bin/sh
COUNT=`sqlplus -s system/manager <set head off;
select count(*) from table1;
exit;
ABC`

[ I'd use the preferred $( ... ) ]

For more than one number you'll need the help of the Shell READ command and perhaps toss in an AWK or PERL filter.

So please play with that thought for a while and see if you need further help!

hth,
Hein van den Heuvel.

Re: Reading variable from SQL to Unix

Hein van den Heuvel — Thu, 17 Sep 2009 20:13:53 GMT

These two stood out....

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:315416808631

http://www.idevelopment.info/data/Oracle/DBA_tips/Unix/UNIX_3.shtml

hth,
Hein

Re: Reading variable from SQL to Unix

cool_reptile — Fri, 18 Sep 2009 13:28:22 GMT

Thanks,
I will look at these and others on google.