Take the 2-minute tour ×

I have set variables in my .sh file (ex. ENV=$1 RELEASE_ID=$2) within my.sh file. I sqlplus into my sql file like

sqlplus -S $username/password@destination @/path/copysetup/insert.sql

I want to pass variables ENV=$1 RELEASE_ID=$2 calling from a unix prompt into my sql file as

copysetup.sh env01 release 1.0

from my sql file I want to pass the same variable into an oracle table

insert into table...

Can someone assist on how to pass variable from bash shell script to sql file and ultimately insert the into my oracle table?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Pass the parameters on the command line:

sqlplus -S $username/password@destination @/path/copysetup/insert.sql $ENV $RELEASE_ID

then in the SQL script they become &1 and &2 (ensure you have set scan on at the top of the SQL script)


set scan on
insert into foo (env, release) values ('&1', '&2');
share|improve this answer
I tried your suggestion and &1 and &2 are inserted into my oracle table vice ENVXX release 1.XX. It doesn't seem to be picking up the variables from my .sh file. I am defining the variable though my unix prompt. So I call to my .sh file like such (ex. copysetup.sh ENV01 release 1.00). My hopes is that when I specifiy what I want passed in unix it will pass those variables from my .sh file to the sql file and ultimately insert them into my oracle table. Any other suggestions? –  user1813261 Jan 3 '13 at 18:39
@user1813261 if you start sql plus manually and execute your sql file as @/path/copysetup/insert.sql test test does test and test get inserted or &1 &2 still? if &1 still whats the output of show define and show scan when run in sqlplus? –  DazzaL Jan 3 '13 at 21:39
I got it to work. Thanks for your help!!! :-) –  user1813261 Jan 3 '13 at 23:29

Assuming your insert.sql file is the one which you'd like to insert an environment variable (or any bash variable), you can use sed to find/replace a some placeholder text with your desired value. For instance, if your insert.sql file looked something like

INSERT INTO @mytable VALUES (1,'sometext');

You could use sed to find/replace the @mytable string with the data stored in $MYVAR using the following syntax in a bash script

sed "s/@mytable/$MYVAR" insert.sql > modifiedinsert.sql

Now this would generate a new file, modifiedinsert.sql which you'd then pass into sqlplus.

share|improve this answer

Your Answer


