Today (Saturday) We will make some minor tuning adjustments to MySQL.

You may experience 2 up to 10 seconds "glitch time" when we restart MySQL. We expect to make these adjustments around 1AM Eastern Daylight Saving Time (EDT) US.

Pass a VARIABLE to sqlplus script

Login or Register to Reply

Thread Tools Search this Thread
# 1  
Pass a VARIABLE to sqlplus script

Hi Team,

I am trying to run a sqlplus script against several databases via a FOR/LOOP and also passing the loop variable to a sqlplus script I am calling, as follows:

export ORACLE_SID=plgc1
export ORACLE_HOME=/opt/oracle/product/
export PATH=$PATH:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/opt/dell/srvadmin/bin:/home/oracle/bin:/usr/local/bin:$ORACLE_HOME

##for VARIABLE in sat11cr sat11cru sat11dm sat11dmu saos14 sat14cru sabizos sat14dmu
##for VARIABLE in sat11dm sat11dmu
#for VARIABLE in saos14 sat14cru
for VARIABLE in sabizos sat14dmu


/opt/oracle/product/ -s /nolog << EOF

conn oradba/"password"@${VARIABLE}
spool ${VARIABLE}_table_count.csv
select name||','||cnt from table_count_${VARIABLE} order by name;
spool off




However, when trying to call @insert_table_counts_master.sql i am not able to pass the ${VARIABLE} variable to that same script.
ie I want also to pass ${VARIABLE} to the script insert_table_counts_master.sql

The script insert_table_counts_master.sql is as follow:

set pagesize 0
set linesize 200
set verify off
set echo off
set feedback off
set termout off
set trimspool on
truncate table table_count_${VARIABLE};
spool insert_count_${VARIABLE}.sql
select 'insert into table_count_${VARIABLE} '||chr(10)||'select '''||owner||'.'||table_name||''', count(*) from '||owner||'.'||table_name||';'||chr(10)||'commit;'
from dba_tables
where owner in ('DATAOWNER','DSMOWNERPLI')
and table_name not like 'USLOG%'
and table_name not like 'MLOG%'
-- and num_rows < 10000000
-- and num_rows <> 0
order by 1;
spool off

Example output would be;
insert into table_count_${VARIABLE}

insert into table_count_${VARIABLE}

Question is, how can I pass the ${VARIABLE} to the called sql script?

Thanks for any help,


Last edited by vbe; 04-17-2019 at 01:22 PM.. Reason: extra code tags
# 2  
How to Pass Arguments to a Bash Script

Arguments are accessed inside a script using the variables $1, $2, $3, and so on. The variable $1 refers to the first argument, $2 to the second argument, and $3 to the third argument. This is illustrated in the following example:
You can also use the getopts() method, for example.

Please read the tutorial above or search this site, as this question has been asked many times over the years.

Login or Register to Reply

Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
How to pass Variable from shell script to select query for SqlPlus?
echo "set echo off"; echo "set feedback off"; echo "set linesize 4000"; echo " set pagesize 0"; echo " set sqlprompt ''"; echo " set trimspool on"; Select statement is mentioned below echo "select res.ti_book_no from disney_ticket_history res where res.ti_status =${STATUS} and...... Shell Programming and Scripting
Shell Programming and Scripting
How to pass value of pwd as variable in SED to replace variable in a script file
Hi all, Hereby wish to have your advise for below: Main concept is I intend to get current directory of my script file. This script file will be copied to /etc/init.d. A string in this copy will be replaced with current directory value. Below is original script file: ...... Red Hat
Red Hat
How to pass parameter from sqlplus(procedure completed) to your shell script
if then # mail -s "Import failed file does not exist" echo "FILE does not exist" exit 1 fi echo "FILE EXIST" size=-1 set $(du /export/home/oracle/nas/scott21.dmp.gz) while do echo "Inside the loop" size=$1 set $(du...... Shell Programming and Scripting
Shell Programming and Scripting
How to pass unix variable to SQLPLUS
hi fellows, can any body tell me how to pass unix variables to oracle code is... #! /bin/ksh echo ENTER DATE VALUE's read START_DATE END_DATE sqlplus xyx/abc@oracle select * from table1 where coloumn1 between $START_DATE and $END_DATE; is this is correct way........... Thanks in...... UNIX for Advanced & Expert Users
UNIX for Advanced & Expert Users
How to pass variable to SQLPLUS in a ksh script?
Hi, I am writing a ksh script which will use sqlplus to run a sql and pass 2 variables as the SQL request. In the ksh script, I have 2 variables which are $min_snap and $max_snap holding 2 different numbers. Inside the same script, I am using SQLPLUS to run an Oracle SQL script,...... Shell Programming and Scripting
Shell Programming and Scripting

Featured Tech Videos