How to pass variable to SQLPLUS in a ksh script? - Shell Programming and Scripting

rwunwla · #1 05-25-2006

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

tmarikle · #2 05-25-2006

Code:

$ORACLE_HOME/bin/sqlplus -S perfstat/perf$ORACLE_SID@$ORACLE_SID @sppurge $min_snap $max_snap

You sql will reference these as follows:

Code:

PROMPT Argument1 (low) : &1
PROMPT Argument2 (high): &2

...
begin
   :snapshots_purged := statspack.purge( i_begin_snap      => &1
                                       , i_end_snap        => &2
                                       ...
                                       );
end;
/

rwunwla · #3 05-25-2006

Thanks. Yes, that is one way to do which I will have to change the original code from Oracle.

If I don't want to change the code, in ksh script, how should I program to force inputing the variables instead of typing on the keyboard?

tmarikle · #4 05-25-2006

One way is to use a co-processor in KSH.

test.sql

Code:

set serveroutput on
set feedback off verify off

variable input_number number;

begin 
    :input_number := &input_number;
    dbms_output.put_line ('your number divided by 5 = ' || :input_number / 5);
end;
/

test.sh

Code:

#! /usr/bin/ksh

# Create coprocessor
sqlplus -s /nolog |&

# Send log on message
print -p connect user/password@sid

# Call SQL
print -p @test.sql

# Answer prompts
print -p 37

# Plant a known message to allow break from loop
print -p PROMPT done

# Loop through message from co-processor
while read -p LINE
do
    # Print them; filter them; test for error message, etc.
    print - "${LINE}"

    # Break from loop when planted message is found
    [[ ${LINE} = done ]] && break
done

# Terminate co-processor
print -p quit

exit 0

rajeeb_d · #5 05-25-2006

If I get you right then this would work.

Create a wrapper shell script for your shell. in which export your vairables with the values before calling your shell script

For example you shell script is shell1.sh

Create a new shell like this:

export min_snap=20
export max_snap=80

shell1.sh

rwunwla · #6 05-25-2006

# Create coprocessor
sqlplus -s /nolog |&

Hi, tmarikle.

Are you sure this will work in KSH? I got error message.

-------------------------
...
...
...
cd /home/mdbtuner/stats/sppurge
$ORACLE_HOME/bin/sqlplus -S perfstat/perf$ORACLE_SID@$ORACLE_SID << ! > $losnapid
@min_snapid.sql
exit;
!

$ORACLE_HOME/bin/sqlplus -S perfstat/perf$ORACLE_SID@$ORACLE_SID << ! > $hisnapid
@max_snapid.sql
exit;
!

echo $losnapid $hisnapid

$ORACLE_HOME/bin/sqlplus -s /nolog |
print -p connect perfstat/perf$ORACLE_SID@$ORACLE_SID
print -p @sppurge.sql
print -p $losnapid
print -p $hisnapid
print -p quit

exit 0

--------------------
Try this new script.

tmarikle · #7 05-25-2006

Quote:

Originally Posted by rwunwla

$ORACLE_HOME/bin/sqlplus -s /nolog |
print -p connect perfstat/perf$ORACLE_SID@$ORACLE_SID
print -p @sppurge.sql
print -p $losnapid
print -p $hisnapid
print -p quit

You have a syntx error.

Code:

$ORACLE_HOME/bin/sqlplus -s /nolog |&<== Notice the ampersand that creates the background process

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Is it possible to pass variable from awk to shell script	user_prady	Shell Programming and Scripting	3	12-18-2007 07:06 AM
How to pass Shell script variable to awk	HIMANI	UNIX for Dummies Questions & Answers	3	07-15-2007 09:23 PM
Pass script variable value to AWK	BearCheese	Shell Programming and Scripting	1	06-28-2007 02:02 AM
How to pass unix variable to SQLPLUS	chiru	UNIX for Advanced & Expert Users	1	06-09-2006 11:11 PM
How to Pass variable to shell Script	sam70	UNIX for Dummies Questions & Answers	5	08-23-2005 04:27 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode