asked on

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

GeekMan

ASKER

BTW...I'm willing to abandon my old method if there's a better way....

Tintin

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

GeekMan

ASKER

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

ASKER CERTIFIED SOLUTION

GeekMan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GeekMan

ASKER

typo...thats
var1=`sqlplus -s ${RDBMS_CONNECT} <<- endsql

JJSmith

output is:
COUNT(*) ------------ 761612
I can parse that....

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