Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: How to pass variable to SQLPLUS in a ksh script?
Top Forums Shell Programming and Scripting How to pass variable to SQLPLUS in a ksh script? Post 302074740 by rwunwla on Thursday 25th of May 2006 02:09:33 PM
Old 05-25-2006
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, which request to enter 2 set of numbers, one at a time. Normally running situation, I can enter the number from the keyboard, but since I am writing a script, I want to pass those numbers by passing variables in the script? How can I do it?

Please help!

here is my ksh script,

-----------------------------------

#!/bin/ksh

if [ "$1" = "" ]
then
echo "Usage : delete_snapshot.sh <db_name>"
exit
fi

export ORACLE_BASE=/oracle1/app/oracle
export ORACLE_PATH=.:/oracle1/app/oracle/product/8.1.7/tune
export ORACLE_HOME=/oracle1/app/oracle/product/8.1.7
export ORACLE_RDBMS=$ORACLE_HOME/rdbms/admin
export ORACLE_SID=$1

curr_time=`date +"%m/%d/%y %H:%M:%S"`
log_file=/home/mdbtuner/stats/sppurge/log/{$ORACLE_SID}_`date +"%Y%m%d"`.log

#echo $log_file
echo "****** Delete Snapshot for $ORACLE_SID at $curr_time ******" >> $log_file

#echo "****** Delete Snapshot for $ORACLE_SID at $curr_time ******"
#echo $ORACLE_HOME/bin/sqlplus -S perfstat/perf$ORACLE_SID@$ORACLE_SID

cd /home/mdbtuner/stats/sppurge
$ORACLE_HOME/bin/sqlplus -S perfstat/perf$ORACLE_SID@$ORACLE_SID << EOF > $min_snap
@min_snapid.sql
exit;
EOF
$ORACLE_HOME/bin/sqlplus -S perfstat/perf$ORACLE_SID@$ORACLE_SID << EOF > $max_snap
@max_snapid.sql
exit;
EOF

echo $min_snap $max_snap

cd $ORACLE_RDBMS
#$ORACLE_HOME/bin/sqlplus -S perfstat/perf$ORACLE_SID@$ORACLE_SID << EOF >> $log_file
$ORACLE_HOME/bin/sqlplus -S perfstat/perf$ORACLE_SID@$ORACLE_SID << !
@sppurge
$min_snap
$max_snap
exit;
!
#EOF

--------------------------------------------


Thanks in advance!

Robert
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need help with ksh script that uses sqlplus, called from PHP

I have a ksh script that connects to sqlplus and dumps the query results into a file. The script works file when I run it from the command line, however, when I call it from PHP using system(), exec(), or shell_exec() commands, the script doesn't seem to run the query. It will create the text file... (7 Replies)
Discussion started by: j2owilson
7 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

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

4. Shell Programming and Scripting

ksh variable pass to awk

I'm trying to store the response from a nawk command inside of a ksh script. The command is: text=$(nawk -F: '$1 ~ /${imgArray}/ {print $2}' ${etcDir}/captions.txt) From what I can tell, the imgArray variable is not being expanding when it is inside the single quote ('). Is there something I... (4 Replies)
Discussion started by: meman1188
4 Replies

5. Shell Programming and Scripting

AIX ksh: how to pass variable to host shell

I have a script that "runs" a script. For example: ./runscript.ksh pcnmc01.ksh runscript puts pcnmc01.ksh into the background with log output going to the logfile. After executing the command, I get this output: Running script in the background: pcnmc01.ksh Logfile:... (2 Replies)
Discussion started by: Eben Yong
2 Replies

6. 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

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

8. Shell Programming and Scripting

ksh script trying to pass a variable to edit a file

I'm trying to create a ksh script that will ask the user for the port number. $PORT1 is the variable I want to use that will contain whatever numbers the user inputs. The script would edit ports.txt file, search and delete "./serv 110.1.0.1.$PORT1 200;=3" . So if the user types 50243 then the... (5 Replies)
Discussion started by: seekryts15
5 Replies

9. Shell Programming and Scripting

Line break in sqlplus output through ksh script

Hi, I am new to shell script programming. I have written a ksh script to run the sql File placed in server directory and spool the output in destination directory. Below Command: $ORACLE_HOME/bin/sqlplus -s $ora_uid @$sqlfile_loc$testquery.sql > $opfiledirectory It is generating the output... (6 Replies)
Discussion started by: Sumit Arora
6 Replies

10. Shell Programming and Scripting

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: #!/bin/bash export ORACLE_SID=plgc1 export ORACLE_HOME=/opt/oracle/product/11.2.0.2/db_1 export... (1 Reply)
Discussion started by: jonnyd
1 Replies

LEARN ABOUT DEBIAN

tapset::perf

TAPSET::PERF(3stap)													       TAPSET::PERF(3stap)

NAME

       tapset::perf - systemtap perf probe points

DESCRIPTION

       This  family  of probe points is used to probe "perf events" on suitably configured kernels (2.6.33+).  It contains a number of aliases for
       the ABI-specified event type/config tuples.

       The default sampling interval of the translator(1000000) is not overridden, so some of these probe points may fire very infrequently.  Use
       the lower level perf.type(AA).config(BB).sample(CC) probe point if this is a problem.

       perf.hw.*
	      A  variety of hardware events, as generalized by the kernel.  These generally require running on bare hardware with selected proces-
	      sors.
	      probe perf.hw.cpu_cycles
	      probe perf.hw.instructions
	      probe perf.hw.cache_references
	      probe perf.hw.cache_misses
	      probe perf.hw.branch_instructions
	      probe perf.hw.branch_misses
	      probe perf.hw.bus_cycles

       perf.sw.*
	      Special "software" events provided by the kernel.  These sometimes work in virtualized environments and with more  processor  archi-
	      tectures.
	      probe perf.sw.cpu_clock
	      probe perf.sw.task_clock
	      probe perf.sw.page_faults
	      probe perf.sw.context_switches
	      probe perf.sw.cpu_migrations
	      probe perf.sw.page_faults_min
	      probe perf.sw.page_faults_maj
	      probe perf.sw.alignment_faults
	      probe perf.sw.emulation_faults

       perf.hw_cache.TYPE.ACCESS.RESULT
	      Hardware cache events, where available.  A subset of the following cartesian product may be available:
	      probe perf.hw_cache.l1d.*.*
	      probe perf.hw_cache.l1i.*.*
	      probe perf.hw_cache.ll.*.*
	      probe perf.hw_cache.dtlb.*.*
	      probe perf.hw_cache.itlb.*.*
	      probe perf.hw_cache.bpu.*.*

	      probe perf.hw_cache.*.read.*
	      probe perf.hw_cache.*.write.*
	      probe perf.hw_cache.*.prefetch.*

	      probe perf.hw_cache.*.*.access
	      probe perf.hw_cache.*.*.miss

SEE ALSO

       stap(1), stapprobes(3stap)

IBM
															       TAPSET::PERF(3stap)
All times are GMT -4. The time now is 02:07 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy