Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

sqlplus: I want to avoid the 'Connected.' string

SOLVED
Go to solution
Claudio Cilloni
Honored Contributor

sqlplus: I want to avoid the 'Connected.' string

hi all. second question for today.

I need do to some queries inside a shell script.
I'm doing so:

RESULT=$(sqlplus -silent /nolog <SET HEADING OFF
SET NEWPAGE NONE
SET FEEDBACK OFF
CONN / AS SYSDBA
SELECT ---- something ----;
EOF)

I was able to hide any output other than the query result (SET HEADING OFF, SET NEWPAGE NONE, SET FEEDBACK OFF). Now... the 'CONN / AS SYSDBA' command still prints 'Connected.'. This is really annoying.
How can I remove it using sqlplus commands or options? Or... could you suggest an easy and safe way to remove that line inside my script after the sqlplus command?

thanks.

Claudio
7 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: sqlplus: I want to avoid the 'Connected.' string

if you can change the connection with :
$(sqlplus -silent user/pass <SET HEADING OFF
SET NEWPAGE NONE
SET FEEDBACK OFF
SELECT ---- something ----;
EOF)


You won't have the connected message anymore.
Regards,
Jean-Luc
fiat lux
Duncan Edmonstone
Honored Contributor
Solution

Re: sqlplus: I want to avoid the 'Connected.' string

Here's a code snippet I use in my sql based shell scripts:

RunSQL ()
{

{
sqlplus -s /nolog << EOJ
connect / as sysdba ;
set echo off ;
set heading off ;
${1}
exit ;
EOJ
} | sed -e '/^Connected/d' -e '/^$/d'

}

Then you can use the function as in:

RunSQL 'select * from v$session'

or whatever

HTH

Duncan

HTH

Duncan
Claudio Cilloni
Honored Contributor

Re: sqlplus: I want to avoid the 'Connected.' string

Really COOL! thanks Duncan!

Ciao
Claudio
Hein van den Heuvel
Honored Contributor

Re: sqlplus: I want to avoid the 'Connected.' string


Claudio, Whenever I need to do something along the lines you describe I find is more productive to just look for the stuff that I want to latch onto and process it right there, typically in perl. Something along the lines of:

foreach $_ (`sqlplus user/pass < script.sql`) {
if (/^\s+(\d+)\s+(\d+)/) { ...
}

Hope this helps, full example below.

Hein.

On a Tru64 box I wanted to report the Oracle client and slave processes with the actual processor they each run on in NUMA context [4 cpus per RAD's (=cell)] grouped by client node/system.
So Oracle knows the client-slave connection,
but Unix knows the processor assignments and I need to join that data, but only for oracle tasks. Perl solution:

# special sort we like to use
sub by_rad_node_pid {
int($psr{$a}/4) . $node{$a} . $a
cmp
int($psr{$b}/4) . $node{$b} . $b; }

# collect arrays of Oracle info

foreach $_ (`sqlplus system/manager < process.sql`) {
if (/^\s+(\d+)\s+(\d+)\s+(\d+)\s+(\w+)/) {
$pid=$2;
$node{$pid}=$4;
$remote{$pid}=$3;
$oracle{$pid}=$1;
}
}

#collect arrays of Tru64 info

foreach $_ (`ps -AOpsr`) {
$psr{$1}=$2 if (/^\s+(\d+)\s+(\d+)/ && $oracle{$1} )
}

# report groups

foreach $pid (sort by_rad_node_pid keys %psr) {
if ($node{$pid} eq $old_node) {
print "\t$pid";
} else {
$old_node = $node{$pid};
printf "\n%2d %8s %d", $psr{$pid}/4, $old_node, $pid;
}
}

----- process.sql -------
column id format 999;
column Local format 99999999;
column Remote format 99999999;
column Node format a9;
column Local_Program format a20;
column Remote_Program format a24;
set pages 9999;
set lines 132;

select pid "Id", spid "Local", process "Remote",
s.machine "Node", SUBSTR(p.program,1,20) "Local Program",
SUBSTR(s.program,1,24) "Remote Program"
from v$process p, v$session s
where addr = paddr
order by machine, spid
/
Steven E. Protter
Exalted Contributor

Re: sqlplus: I want to avoid the 'Connected.' string

When you go Oracle 9.2 you may need a slightly different sysdba login

sqlplus '/ as sysdba'

This replaces connect internal which was deprecated with 9.2.

I don't know how to integrate it with the excellent scripting above, I just know i had to do it in all my scripts.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Arturo Galbiati
Esteemed Contributor

Re: sqlplus: I want to avoid the 'Connected.' string

Hi Claudio,
passing the logon in the same command line of sqlplus is not a good pratice, because another user can catch it using ps -ef|grep sqlplus.
The better way to avoid your problem is:
RESULT=$(sqlplus -silent</ AS SYSDBA
SET HEADING OFF
SET NEWPAGE NONE
SET FEEDBACK OFF
SELECT ---- something ----;
EOF)

Art
Claudio Cilloni
Honored Contributor

Re: sqlplus: I want to avoid the 'Connected.' string

thread closed. thanks to all.