Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Pass a VARIABLE to sqlplus script
Top Forums Shell Programming and Scripting Pass a VARIABLE to sqlplus script Post 303034061 by jonnyd on Wednesday 17th of April 2019 12:08:21 PM
Old 04-17-2019
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; 04-17-2019 at 01:22 PM.. Reason: extra code tags
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

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,... (6 Replies)
Discussion started by: rwunwla
6 Replies

2. UNIX for Advanced & Expert Users

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... (1 Reply)
Discussion started by: chiru
1 Replies

3. Shell Programming and Scripting

error in passing a variable to sqlplus from a shell script

hi, I am using a shell script from where i will be conecting to sqlplus.. i am having a problem in passing a variable to sqlplus query.. i will be assigning the variable in the unix environment..whenever i am trying to pass a variable having the contents greater than 2500 characters, i am... (3 Replies)
Discussion started by: kripssmart
3 Replies

4. Shell Programming and Scripting

How to pass parameter from sqlplus(procedure completed) to your shell script

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... (1 Reply)
Discussion started by: sanora600
1 Replies

5. Shell Programming and Scripting

In a csh script, can I set a variable to the result of an SQLPLUS select query?

Can someone tell me why I'm getting error when I try to run this? #!/bin/csh -f source ~/.cshrc # set SQLPLUS = ${ORACLE_HOME}/bin/sqlplus # set count=`$SQLPLUS -s ${DB_LOGIN} << END select count(1) from put_groups where group_name='PC' and description='EOD_EVENT' and serial_number=1;... (7 Replies)
Discussion started by: gregrobinsonhd
7 Replies

6. Red Hat

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: ... (6 Replies)
Discussion started by: cielle
6 Replies

7. Shell Programming and Scripting

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... (7 Replies)
Discussion started by: aroragaurav.84
7 Replies

8. Shell Programming and Scripting

Shell Script (ksh) - SQLPlus query filter using a string variable

Using ksh, I am using SQLPlus to execute a query with a filter using a string variable. REPO_DB=DEV1 FOLDER_NM='U_nmalencia' FOLDER_CHECK=$(sqlplus -s /nolog <<EOF CONNECT user/pswd_select@${REPO_DB} set echo off heading off feedback off select subj_name from subject where... (5 Replies)
Discussion started by: nkm0brm
5 Replies

9. Shell Programming and Scripting

Shell script to pass the config file lines as variable on the respective called function on a script

I want to make a config file which contain all the paths. i want to read the config file line by line and pass as an argument on my below function. Replace all the path with reading config path line by line and pass in respective functions. how can i achieve that? Kindly guide. ... (6 Replies)
Discussion started by: sadique.manzar
6 Replies

10. UNIX for Beginners Questions & Answers

Pass value from sqlplus to shell on AIX

hello friend good morning I have a problem, how can I take the value that the PROCEDURE returns to me in the variable "CodError", when the connection to the bbdd is closed I lose the value and I need it in the shell #AIX cat <<EOF | sqlplus -s ${ORA_LOGIN}/${ORA_PASSWORD} > $logftmp set... (6 Replies)
Discussion started by: tricampeon81
6 Replies

LEARN ABOUT OSX

script

SCRIPT(1)						    BSD General Commands Manual 						 SCRIPT(1)

NAME

     script -- make typescript of terminal session

SYNOPSIS

     script [-akq] [-t time] [file [command ...]]

DESCRIPTION

     The script utility makes a typescript of everything printed on your terminal.  It is useful for students who need a hardcopy record of an
     interactive session as proof of an assignment, as the typescript file can be printed out later with lpr(1).

     If the argument file is given, script saves all dialogue in file.	If no file name is given, the typescript is saved in the file typescript.

     If the argument command is given, script will run the specified command with an optional argument vector instead of an interactive shell.

     The following options are available:

     -a      Append the output to file or typescript, retaining the prior contents.

     -k      Log keys sent to program as well as output.

     -q      Run in quiet mode, omit the start and stop status messages.

     -t time
	     Specify time interval between flushing script output file.  A value of 0 causes script to flush for every character I/O event.  The
	     default interval is 30 seconds.

     The script ends when the forked shell (or command) exits (a control-D to exit the Bourne shell (sh(1)), and exit, logout or control-D (if
     ignoreeof is not set) for the C-shell, csh(1)).

     Certain interactive commands, such as vi(1), create garbage in the typescript file.  The script utility works best with commands that do not
     manipulate the screen.  The results are meant to emulate a hardcopy terminal, not an addressable one.

ENVIRONMENT

     The following environment variable is utilized by script:

     SHELL  If the variable SHELL exists, the shell forked by script will be that shell.  If SHELL is not set, the Bourne shell is assumed.  (Most
	    shells set this variable automatically).

SEE ALSO

     csh(1) (for the history mechanism).

HISTORY

     The script command appeared in 3.0BSD.

BUGS

     The script utility places everything in the log file, including linefeeds and backspaces.	This is not what the naive user expects.

     It is not possible to specify a command without also naming the script file because of argument parsing compatibility issues.

     When running in -k mode, echo cancelling is far from ideal.  The slave terminal mode is checked for ECHO mode to check when to avoid manual
     echo logging.  This does not work when in a raw mode where the program being run is doing manual echo.

BSD
								 January 22, 2004							       BSD
All times are GMT -4. The time now is 07:45 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy