Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
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.