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:

Code:
#!/bin/bash
export ORACLE_SID=plgc1
export ORACLE_HOME=/opt/oracle/product/11.2.0.2/db_1
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

do

/opt/oracle/product/11.2.0.2/db_1/bin/sqlplus -s /nolog << EOF

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

EOF

done

exit

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:

Code:
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
@insert_count_${VARIABLE}.sql

Example output would be;
Code:
insert into table_count_${VARIABLE}
select 'DSMOWNERPLI.ADMINUSERS', count(*) from DSMOWNERPLI.ADMINUSERS;
commit;

insert into table_count_${VARIABLE}
select 'DSMOWNERPLI.DSM_CAS', count(*) from DSMOWNERPLI.DSM_CAS;
commit;

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

Thanks for any help,

jd

Last edited by vbe; 4 Weeks Ago at 12:22 PM.. Reason: extra code tags
# 2  
How to Pass Arguments to a Bash Script

Quote:
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.

Thanks.
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?
aroragaurav.84
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
7
Shell Programming and Scripting
How to pass value of pwd as variable in SED to replace variable in a script file
cielle
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
6
Red Hat
How to pass parameter from sqlplus(procedure completed) to your shell script
sanora600
if then # mail -s "Import failed file does not exist" sanjay.jaiswal@xyz.com 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
1
Shell Programming and Scripting
How to pass unix variable to SQLPLUS
chiru
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
1
UNIX for Advanced & Expert Users
How to pass variable to SQLPLUS in a ksh script?
rwunwla
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
6
Shell Programming and Scripting
UNIX and Linux

Unix (trademarked as the UNIX certification mark) is a very mature family of multitasking, multiuser computer operating systems that evolved from the original AT&T Unix. Unix development starting in the 1970s by legendary Bell Labs programmers Ken Thompson, Dennis Ritchie, and others. Unix was first targeted for the Bell System and AT&T licensed Unix to outside parties. In the 1970s time period, this lead to a variety of for-profit as well as not-for-profit Unix variants.
In the early days, this included the University of California, Berkeley (BSD), Microsoft (Xenix), IBM (AIX), and Sun Microsystems (Solaris). In the early 1990s, AT&T sold their Unix rights to Novell. In 1995 Novell sold their Unix business to the Santa Cruz Operation (SCO). The UNIX trademark was passed to The Open Group, a "neutral" industry consortium. The Open Group promoted the use of the UNIX trademark for certified operating systems that comply with the Single UNIX Specification (SUS). In 2014 Apple's macOS became the Unix version with the largest global install base and macOS remains the largest Unix-user base today.
Linux is a family of open source Unix-like operating systems based on the Linux kernel. The Linux kernel is an operating system kernel first released by Linus Torvalds on 17 September 1991. Linux distributions include the Linux kernel, system software and libraries. Popular free open source Linux distributions include Debian, Fedora, and Ubuntu. Commercial Linux distributions include Red Hat Enterprise Linux and SUSE Linux Enterprise Server. Linux may be freely modified and redistributed. Anyone on the planet may create a Linux distribution for any purpose.
Please enjoy and help our forum community by "showing your work" and posting your code, even when your code is not working as expected. To help others help you, please wrap your code blocks, sample input, sample output, error messages, and other data in CODE tags and wrap your short commands and short data objects in ICODE tags. We were all beginners in the beginning. If you have any questions about how to register or how to post, please contact us in Live Chat. Thank you and enjoy this "forever free" technical support community for UNIX, Linux and computer information technology in general.