Pass parameter to sqlplus

[kasparov]

I want to run a script with a parameter & then pass the parameter to some sql code in the script. I've spent a long time looking on the web & none of the suggestions I can find works:

Here's a simplified version of the script called test_sql:

#!/bin/ksh
##
##

## set oracle variables up
ORACLE_SID=cflive
ORA_NLS10=/cfldbap/oracle/product/10.2.0/nls/data
ORACLE_HOME=/cfldbap/oracle/product/10.2.0
LD_LIBRARY_PATH=/cfldbap/oracle/product/10.2.0/lib

echo ++++++
echo $1
echo ++++++

sqlplus '/ as sysdba' <<EOF
   set define off

   select '&1' from dual;
EOF

When I run this I get:

$ ./test_sql param1
++++++
param1
++++++

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 9 16:25:38 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> SQL>
'&
--
&1

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
(/export/home/oracle/scripts/backups)
oracle cflive >

I have tried one quote, 2 quotes, no quotes, double quotes around &1, I have tried $1 etc etc & you will appreciate that I am now guessing.

Can anyone help me out?

Thanks, Chris

 

[taupirho]

Try changing this line

select '&1' from dual;

to this

select '&&1' from dual;



In order to understand recursion, you must first understand recursion.

 

[kasparov]

Sorry - I should have added that I'd already tried that:

$ ./test_sql param1
++++++
param1
++++++

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Sep 10 09:08:35 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> SQL>
'&&
---
&&1

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

[Annihilannic]

Don't you mean $1? I'm assuming you want to insert the first parameter passed to the script into the SQL code, right? If not, what is your desired output?

Annihilannic.

 

[kasparov]

It works! Thank you annihilannic. I had tried $1 but had not previously surrounded it by single quotes. When the script looks like this:

#!/bin/ksh
##
##

## set oracle variables up
ORACLE_SID=cflive
ORA_NLS10=/cfldbap/oracle/product/10.2.0/nls/data
ORACLE_HOME=/cfldbap/oracle/product/10.2.0
LD_LIBRARY_PATH=/cfldbap/oracle/product/10.2.0/lib

echo ++++++
echo $1
echo ++++++

sqlplus '/ as sysdba' <<EOF
   set define off

   select '$1' from dual;
EOF

it works fine:

oracle cflive > ./test_sql param1
++++++
param1
++++++

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Sep 11 10:23:30 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> SQL>
'PARAM
------
param1

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

My fault - I had tried so many things I missed the right one.

Thank you