How to pass variable to SQLPLUS in a ksh script?

Shell Programming and Scripting
1

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

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

You sql will reference these as follows:

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

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

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?

4

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

test.sql

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

#! /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
5

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

6

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

7

You have a syntx error.

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