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

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 22 Oct 2002 14:44:23 -0800
Message-ID: <F001.004F075E.20021022144423@fatcity.com>

> -----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: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.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 Tue Oct 22 2002 - 17:44:23 CDT