Shell Programming and Scripting

All,

I am trying to connect to sqlplus from my unix shell, i am able to connect but is there a way that i can get the status of whether the database is running ? That is i want to store the output code from the sqlplus command into a variable and write the rest of my code based on that.

Can anyone help ???

Heres what i am trying to do

#!/bin/ksh
dbstatus=`sqlplus -s ptx_msgpoller/ptx01_glovia@prdm.world << !
exit !`

echo $dbstatus

The output of this script returns a large chunk of data, i want to grep for the string Connected in this output but i am not able to.

Heres the output i get.

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Dec 8 12:11:05 2004 Copyright (c
) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle8i En
terprise Edition Release 8.1.7.4.0 - Production With the Partitioning option JSe
rver Release 8.1.7.4.0 - Production SQL> Disconnected from Oracle8i Enterprise E
dition Release 8.1.7.4.0 - Production With the Partitioning option JServer Relea
se 8.1.7.4.0 - Production

the grep command does not work on this output, how is it done ?

Please help


Thanks in Advance
Swaraj





#cat output

Did u try .... ?


echo $dbstatus | grep "str"



Is this u want ????

Hi Bhargav

I tried that it gives me the same output, but not the String value.


Swaraj

Now ,
i got what u are looking for.

Can u redirect yr sql o/p to a file instead of a variable.

do some thing like this :


if the o/p is not writing new line char at the end , you add new line at the end ;

But 'echo' adds that ......


Try some thing like this, though 'echo' adds one new line


echo $dbstatus
echo "$dbstatus\n" | grep "Disconnected"

sqlplus will run the script file using @filename not just the filename.

See:

Code:

#!/bin/sh

A=`sqlplus -s /nolog @test.sql`

echo $A

test.sql should contain something like.

Code:

connect scott/tiger 
set heading off
select 55 from dual;
exit

This example should simply return "55".

If your script returns many rows (say more than 20 rows), I would not load it in to a variable first, just grep from the sqlplus output.

For your actuall scenario:

Code:

#!/bin/sh
sqlplus -s /nolog @mywrapper.sql | grep string

where mywrapper.sql looks like this:

Code:

connect scott/tiger
set heading off pages 0
@ptx_msgpoller/ptx01_glovia@prdm.world
exit