CODEWITHSUNDEEP
oracle-databaseshellsqlplus
Tebbe
Tebbe

Reputation: 1372

Passing Parameter to SQL*Plus Script Containing ACCEPT From Shell Script

I am trying to call a SQL*Plus (10g) script from a Korn shell script.

I'm not allowed to modify the SQL*Plus script, and it has a SQL*Plus ACCEPT command in it.

I'm trying to pass my specific argument for this ACCEPT-command variable (7788 further below) in to the SQL*Plus script via the Korn Shell script.

Here is a whittled-down version of the SQL*Plus script, oracle_code.sql:

SET VERIFY OFF
ACCEPT abc PROMPT "Enter an empno:"
SELECT e.ename
FROM   emp       e
WHERE  TO_CHAR(e.empno) = '&abc'
;

And here is my attempt at the Korn shell script (wrapping_shell_script.sh) to make the call:

#!/usr/bin/ksh
# wrapping_shell_script.sh
# Expecting one and exactly one parameter to be passed, the password for SCOTT
if [ $# -ne 1 ]
then
    echo "USAGE: wrapping_shell_script.sh <SCOTT_Password>"
    exit 1
fi

passwd="$1"

echo `date`

sqlplus -s scott/${passwd} @oracle_code.sql << EOF1
7788
EOF1

Here's what it looks like when I run the shell script from the Korn shell command line:

$ wrapping_shell_script.sh tiger
Fri Oct 31 16:23:20 CDT 2014
Enter an empno:
no rows selected

$

The 7788 value I'm trying to pass isn't making its way into the SQL*Plus script's abc lexical parameter.

How can I change my Korn shell script such that the value I'm specifying is passed in, and the invoked SQL*Plus script executes?

Thanks.

Upvotes: 4

Views: 4311

Answers (4)

gstanden
gstanden

Reputation: 16

@Tebbe Worked like a charm, thanks!

I know this thread is old but just in case it helps someone else:

Here is a modified version I created from Tebbe's scripts to accept variables and pass them to a pair of ACCEPT statements. Note, I learned the hard way don't put any spaces, tabs, etc between the EOF1 open and the variable assignments or you'll get spaces/tabs etc introduced into the variable values. That is

this is ok:

<< EOF1
$tbl

but this is not:

<< EOF1
    $tbl

# Begin Script

#!/usr/bin/ksh
# gfvbsaddpol_wrapper.sh
# Expecting tbl and owner to be passed 

tables='SCBCRKY SCBCRSE SCBDESC SCBSUPP SCRATTR SCRCLBD SCRCORQ SCRCPRT SCRCRDF SCREQIV SCRFEES SCRGMOD SCRINTG SCRLEVL SCRMEXC SCRRARE SCRRATT SCRRCAM SCRRCHR SCRRCLS SCRRCMP SCRRCOL SCRRDEG SCRRDEP SCRRLVL SCRRMAJ SCRRPRG SCRRTRM SCRRTST SCRSBGI SCRSCHD SCRSYLN SCRSYLO SCRSYRM SCRSYTR SCRTEXT SIRASGN SSBWLSC SSRCLBD SSRRATT SSRRCHR SSRRDEP SHRGCOM SHRSCOM SSBDESC SSBFSEC SSBOVRR SSBSECT SSBSSEC SSRATTR SSRBLCK SSREXTN SSRCORQ SSREVAL SSRFEES SSRLINK SSRMEET SSRRMAJ SSRMPRT SSRMRDF SSRRARE SSRRCLS SSRRCMP SSRRDEG SSRRCOL SSRRESV SSRRFND SSRRLVL SSRRPRG SSRRSTS SSRRTST SSRSCCD SSRSPRT SSRSRDF SSRSYLN SSRSYLO SSRSYRM SSRSYTR SSRTEXT SSRXLST SSTSCHW'

owner='SATURN'

# if [ $# -ne 1 ]
# then
#   echo "USAGE: wrapping_shell_script.sh <UrPassw0rd>"
#   exit 1
# fi

for tbl in $tables
do
passwd="UrPassw0rd"

echo `date`

sqlplus -s username/${passwd}@SID @gfvbsaddpol.sql << EOF1
$tbl
$owner
EOF1
done

Upvotes: 0

tvCa
tvCa

Reputation: 816

It's always easy once the issue has been found, but the VERIFY option was created to debug variable setting. Given the fact you could not change the calling SQL file, but you could change the (g)login.sql, you can set :

set verify on

But, to only find other options were set unexpectingly.

Upvotes: 1

Tebbe
Tebbe

Reputation: 1372

Original poster here....

Thanks, everybody, for confirming that my approach should have worked.

I did some digging, and, thanks to info at this link, I discovered that the login.sql that was in effect at the time my code executed contained a SET DEFINE OFF.

By adding a SET DEFINE ON to my shell script before calling the Oracle script, my code completed successfully.

Thanks again for the hint!

Upvotes: 1

JohnFL
JohnFL

Reputation: 52

It worked fine for me, too. I'm wondering, did you check to see if the query actually will return a row for 7781?

Upvotes: 0

Related Questions