RE: How to pass value from SQL*Plus to Unix scripts?

From: Stephen Lee <slee_at_dollar.com>
Date: Fri, 25 Oct 2002 12:18:51 -0800
Message-ID: <F001.004F42DA.20021025121851@fatcity.com>

I've been gone a few days, so a bit of a delayed reply here. Example:

{
sqlplus -s <<-XXX
username/password

--junk
--junk
--junk

set heading off
set feedback off
select whatever;
exit; -- optional
XXX

} | sed 's/^[ 	]*//g; s/[ 	]*$//g; /^$/d' | while read LINE; do
	code to mess with your LINE

done

Note: the brackets in the sed statement have a space and a tab. The sed statement gets rid of blank lines after deleting leading and trailing whitespace. The deleting of leading and trailing whitespace might be overkill; but I would definitely get rid of blank lines. Your code to mess with LINE should parse for the lovely and useful characters "ORA-". Note that LINE could actually be more than one word, such as WORD1 WORD2 etc.

The --junk lines are optional and deal mostly with when you have a bum username or password.

-----Original Message-----
Sent: Tuesday, October 22, 2002 5:44 PM
To: Multiple recipients of list ORACLE-L

> -----Original Message-----
> From: Mandal, Ashoke [mailto:ashoke.k.mandal_at_medtronic.com]
>
> SQL> select value from v$nls_parameters where
> parameter='NLS_CHARACTERSET';
>
> VALUE
> ----------------------------------------------------------------
> US7ASCII
>
> Now in the Unix shell script I want to pass this character
> set as follows
>
> /user/oracle/export/export_db.sh /uexports tcqscm system
> manager AMERICAN_AMERICA.US7ASCII
>
> How can I pass the character set to the export script?

One way would be to create a spool file with export commands, and then run the spool file as a shell script.
Sample script:
--- setting environment variables
set termout off
set verify off
set heading off
set newpage none
set trimspool on
set feedback off
column database_name new_value db_name_var column instance_name new_value inst_name_var column host_name new_value host_name_var select

  a.name as database_name, 
  b.instance_name as instance_name, 
  b.host_name as host_name 

from
  v$database a, v$instance b ;
spool x.ksh
select 'export DB_NAME="&db_name_var"' from dual union
select 'export INST_NAME="&inst_name_var"' from dual union
select 'export HOST_NAME="&host_name_var"' from dual ; spool off
exit

Sample run:
# sqlplus -s user_at_database_alias @x.sql Enter password:
# cat x.ksh
export DB_NAME="SDU1806A"
export HOST_NAME="irvlvru01"
export INST_NAME="sdu1806a"
# . x.ksh
# echo $DB_NAME
SDU1806A
# echo $HOST_NAME
irvlvru01
# echo $INST_NAME
sdu1806a
#

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephen Lee
  INET: slee_at_dollar.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 Fri Oct 25 2002 - 15:18:51 CDT