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