Very Computer

by **Mike** » Wed, 23 Oct 2002 00:47:48

I gather parameter values in a korn shell script and want to use them in a
sqlplus script that is called from the shell script. How do you pass a value
into the sqlplus script?

by **isa** » Wed, 23 Oct 2002 01:02:34

Hi:

In the sql: where field1 = '&1' and field2 = '&2' and field3 = &3

where parameters 1 and 2 are character and the 3 is numeric.

Regards,
Isa

Quote:> I gather parameter values in a korn shell script and want to use them in a
> sqlplus script that is called from the shell script. How do you pass a
value
> into the sqlplus script?

by **Alan Mill** » Wed, 23 Oct 2002 00:49:41

It's been quite a while (about 5 years) since I've done this but if memory
serves you simply reference command line parameters as &1, &2 ... from
within SQL*Plus.

Quote:> I gather parameter values in a korn shell script and want to use them in a
> sqlplus script that is called from the shell script. How do you pass a
value
> into the sqlplus script?

by **Martin Dohert** » Wed, 23 Oct 2002 03:10:46

True, &1 &2 &3 etc. is the way to get the command line parameters into
the sqlplus script, but if your script is anything other than trivial
I'd recommend defining substitution variables with MEANINGFUL names,
copy the parameters into them, and then only use the named variables
throughout your script. Very difficult to work on a long script which is
littered with numeric parameter names.
e.g.
DEFINE P_USER_NAME = "&1"
DEFINE P_START_DATE = "&2"
DEFINE P_DATE_FORMAT = "&3"
...
SELECT *
FROM PN_LEASE_MILESTONES_ALL
WHERE CREATED_BY = '&P_USER_NAME'
AND COMMENCEMENT_DATE = TO_DATE('&P_START_DATE', '&P_DATE_FORMAT')

Those who follow in your footsteps will call upon all known gods to
bless your name for eternity.

Martin

>Hi:

>In the sql: where field1 = '&1' and field2 = '&2' and field3 = &3

>where parameters 1 and 2 are character and the 3 is numeric.

>Regards,
> Isa

>>I gather parameter values in a korn shell script and want to use them in a
>>sqlplus script that is called from the shell script. How do you pass a

>value

>>into the sqlplus script?

by **Joel Gar** » Wed, 23 Oct 2002 09:11:20

> I gather parameter values in a korn shell script and want to use them in a
> sqlplus script that is called from the shell script. How do you pass a value
> into the sqlplus script?

schema=$1
password=$2
echo "running my program" >myprog$$.log
date >>myprog$$.log
sqlplus -s <<eof > myprog$$.log 2>&1
$schema/$password
-- Do whatcha want, might hafta escape dollar signs sometimes.
eof

or

echo "Enter year \c"
read year
echo "Enter period \c"
read per

echo "$schema/$password
insert into your_table (year, period, created) values ($year,$per,sysdate);
" | sqlplus > /dev/null

Or pass parameters the way the other posters do.

jg

see http://www.orafaq.com for other faqqy stuff.

Very Computer

How do you pass a value from a shell script into a sqlplus script?

How do you pass a value from a shell script into a sqlplus script?

How do you pass a value from a shell script into a sqlplus script?

How do you pass a value from a shell script into a sqlplus script?

How do you pass a value from a shell script into a sqlplus script?

How do you pass a value from a shell script into a sqlplus script?