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

[Claudio Cilloni]

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

[Jean-Luc Oudart]

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

[Duncan Edmonstone · Accepted solution]

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

[Claudio Cilloni]

Really COOL! thanks Duncan!

Ciao
Claudio

[Hein van den Heuvel]

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]

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

[Arturo Galbiati]

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]

thread closed. thanks to all.