Oracle-L: RE: SQL*Plus question - a bit urgent

From: <Stephen.Lee_at_DTAG.Com>
Date: Wed, 05 Nov 2003 07:44:30 -0800
Message-ID: <F001.005D5AB9.20031105074430@fatcity.com>

Rather than try to get output using the ` characters, see what you can do with this method:

{
sqlplus -s <<-XXX

	$USER/$PASS@$SID
	set heading off feedback off trims on lines 300 pages 9999
	set whatever else
	do this;
	do that;
	do the other thing;

XXX
} | while read LINE; do

	parse $LINE with sed, awk, whatever
	if [ this is true ]; then
		do something
	fi

done

If all you want is to do a simple select that is supposed to return one line, one cheap, but not especially robust, way of doing it is like

{
sqlplus -s <<-XXX

	$USER/$PASS@$SID
	set heading off feedback off trims on lines 300 pages 9999
	set whatever else
	select 'DOINK',name from v$database;
	-- or select 'DOINK '||name from v$database;

XXX
} | while read DOINK LINE; do

	if [ "$DOINK" = "DOINK" ]; then
		DBNAME="$LINE"
	fi

done

Now, if one is proficient in sed and awk, more elegant and robust means can be devised.

> -----Original Message-----
> From: Charu Joshi [mailto:joshic_at_mahindrabt.com]
> Sent: Wednesday, November 05, 2003 6:05 AM
> To: Multiple recipients of list ORACLE-L
> Subject: SQL*Plus question - a bit urgent - Can we suppress
> 'Connected.'
> message?
>
>
> Hello all,
>
> I am calling SQL*Plus from a unix shell script and storing the
> results of the query executed in a shell variable. It goes like
> this:
>
> FL_SUFFIX=`sqlplus -s /nolog <<EndOfSQL
> SET ECHO OFF
> SET FEEDBACK OFF
> SET VERIFY OFF
> SET PAGESIZE 0
>
> CONN $ORA_ID/$ORA_PASS
>
> SELECT dummy FROM dual; -- Dummy query.. unrelated to
> the question.
>
> EXIT SQL.SQLCODE
>
> EndOfSQL`
>
> But the contents of the FL_SUFFIX are 'Connected.' instead of the
> value returned by the query.
>
> This is obviously because of the 'CONN $ORA_ID/$ORA_PASS'
> statement. Is there a way to suppress the 'Connected.' message
> that comes on connecting to database?
>
> I have thought about 2 solutions:
>
> 1. Use sqlplus -s $ORA_ID/$ORA_PASS :- This would be the last
> alternative in case everything else fails .. obviously from
> security point of view.
>
> 2. Create a .sql script as:
>
> SET ECHO OFF
> SET FEEDBACK OFF
> SET VERIFY OFF
> SET PAGESIZE 0
>
> SET TERMOUT OFF -- The important bits.
> SPOOL /dev/null --
> CONN $ORA_ID/$ORA_PASS
> SPOOL OFF --
> SET TERMOUT ON -- The important bits.
>
> SELECT dummy FROM dual; -- Dummy query.
>
> EXIT SQL.SQLCODE
>
> and then call this script as
>
> FL_SUFFIX=`sqlplus -s /nolog @a.sql`
>
> I think solution 2 will work, but I am loathe to writing a script
> for a single SQL statement unless there is no other way.
>
> Any new ideas would be greatly appreciated, the quicker the
> better.
>
> Thanks & regards,
> Charu.
>
> *********************************************************
> Disclaimer
>
> This message (including any attachments) contains
> confidential information intended for a specific
> individual and purpose, and is protected by law.
> If you are not the intended recipient, you should
> delete this message and are hereby notified that
> any disclosure, copying, or distribution of this
> message, or the taking of any action based on it,
> is strictly prohibited.
>
> *********************************************************
>
> Visit us at http://www.mahindrabt.com
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Charu Joshi
> INET: joshic_at_mahindrabt.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <Stephen.Lee_at_DTAG.Com
  INET: Stephen.Lee_at_DTAG.Com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Nov 05 2003 - 09:44:30 CST

RE: SQL*Plus question - a bit urgent - Can we suppress 'Connected