Hi there,

I have a simple script that pipes a query to SQL*Plus like this:

ne=`<< END_SQL
SELECT
Names
FROM
MyTable
;
EXIT
END_SQL`

nq=`echo "$ne" |sqlplus -s usr/pwd`

now I'm trying to modify the script in order to use a parameter in the query but I haven't found out a way to do it. Is it possible? Basically the problem is how to pipe into sqlplus a query like the following:

ne=`<< END_SQL
SELECT
Names
FROM
MyTable
WHERE
Names = '&1'
;
EXIT
END_SQL`

Thanks in advance.
Giggi

Giggi, try this:

Code:

#!/bin/ksh
cat - << END_SQL >ne.sql
set ver off
SELECT EName
FROM EMP
WHERE Deptno = '&1'
;
EXIT
END_SQL

nq=`sqlplus -s scott/tiger @ne.sql 10`

echo "$nq"

Hi LKBrwn_DBA,

thanks for the reply, that works but I'd like to avoid saving the script to a file. Is it possible to have the sql statement stored in a variable and pass it to sqlplus together with the parameter?

Giggi

Can't you do it like this:

This will also work:

Code:

#!/bin/ksh
sqlplus scott/tiger <<END_SQL
def p1=$1
set ver off
SELECT EName
FROM EMP
WHERE Deptno = '&&p1'
;
EXIT
END_SQL

Thanks for the hints! Now it's ok.

Giggi