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

Question

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.

+1 for well formed question! This looks OK, my guess is sqlplus is reading the keyboard (or something equally daft) instead of stdin. But, just to be sure, did you try echo 7788 | sqlplus ... ? Good luck. — shellter
– shellter, Commented Nov 3, 2014 at 2:44
I've copy/pasted your code and it runs fine on my end. I've also turned on the verify to double check and the parameter is passed on fine: [oracle@dbsgoel1 gvenzl]$ ./wrapping_shell_script.sh tiger Mon Nov 3 10:50:00 GMT 2014 Enter an empno: ENAME ---------- SCOTT With verify on: [oracle@dbsgoel1 gvenzl]$ ./wrapping_shell_script.sh tiger Mon Nov 3 10:50:27 GMT 2014 Enter an empno:old 3: WHERE TO_CHAR(e.empno) = '&abc' new 3: WHERE TO_CHAR(e.empno) = '7788' ENAME ---------- SCOTT — gvenzl
– gvenzl, Commented Nov 3, 2014 at 9:07
I'd suggest to set verify on and check whether the parameter is passed on successfully. Perhaps by copy/pasting the sql script if you are not allowed to modify it. — gvenzl
– gvenzl, Commented Nov 3, 2014 at 9:11

tvCa · Accepted Answer · 2014-11-25 20:56:30Z

1

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.

answered Nov 25, 2014 at 20:56

tvCa

8267 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tebbe · Accepted Answer · 2014-11-04 04:42:53Z

1

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!

answered Nov 4, 2014 at 4:42

Tebbe

1,37210 silver badges13 bronze badges

Comments

JohnFL · Accepted Answer · 2014-11-03 19:49:17Z

0

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

answered Nov 3, 2014 at 19:49

JohnFL

523 bronze badges

Comments

gstanden · Accepted Answer · 2017-07-13 20:07:12Z

@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

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related