passing variable from bash shell to sql file to oracle table

Question

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?

Solution 1

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)

eg

set scan on
insert into foo (env, release) values ('&1', '&2');
commit;

Solution 2

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.