Writing sqlplus output to shell script variable

I want to execute a sql statement using sqlplus inside a KSH script. I want to execute a simple count(*) on a number of tables and send the results out to variables in my script. The following code worked with our old Alpha servers but doesn't now that we've switched to HP-UX's. Can someone tell me what I nee to change to get this script to work?
 
 As shown in the code snippet example, I want to send to number of rows in each table to the variables var1 and var2 where I can do comparisons and print out the values.
Thanks.

var1=`sqlplus ${C21_CONNECT} <<-        endsql
                select count(*) from CES_CUSTOMERS
                exit
        endsql`
 
        var2=`sqlplus ${RDBMS_CONNECT} <<-      endsql1
                        select count(*) from CES_CUSTOMERS
                        exit
                endsql1`

Open in new window

GeekManAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
GeekManConnect With a Mentor Author Commented:
Got it....
needed -s to suppress the chatter and / character to send output to the variable so the code looks like this
var1='sqlplus ${RDBMS_CONNECT} <<-           endsql
             select count(*) from Table1
             /
            exit
endsql

output is:
COUNT(*) ------------ 761612
 I can parse that....
0
 
GeekManAuthor Commented:
BTW...I'm willing to abandon my old method if there's a better way....
0
 
TintinCommented:
When using here docs, it's always easier to use them without whitespace, eg:


var1=`sqlplus ${C21_CONNECT} <<endsql
                select count(*) from CES_CUSTOMERS
                exit
endsql`

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
GeekManAuthor Commented:
I suppose I should add that the script appears to run but the variables get assigned what looks like all this Oracle chatter. See attached results when I run the script....and maybe this is more a sqlplus thing. I need to supress all the Oracle connectvity and execution chatter and somehow cut through to the results of the sql.
C21= SQL*Plus: Release 10.1.0.3.0 - Production on Tue Feb 12 16:26:17 2008 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production With the Partitioning and Data Mining options SQL> 2 3 Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production With the Partitioning and Data Mining options
 
OMS- SQL*Plus: Release 10.1.0.3.0 - Production on Tue Feb 12 16:26:17 2008 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production With the Partitioning and Data Mining options SQL> 2 3 Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production With the Partitioning and Data Mining options

Open in new window

0
 
GeekManAuthor Commented:
typo...thats
var1=`sqlplus -s ${RDBMS_CONNECT} <<-        endsql
0
 
JJSmithCommented:
output is:
COUNT(*) ------------ 761612
 I can parse that....

insert: 'set head off;' before the select and the 'COUNT(*) --------' will not appear on the output.
0
All Courses

From novice to tech pro — start learning today.