Passing parameters to Oracle SQL file from Batch file

Question

I have a SQL file that updates certain table. However, the table name depend on the machine name on which the software is installed. (Something like: TableFooBar-MyMachine). I have to write a batch file that calls an Oracle SQL script which will update this table.

So, BatchFile --> Update. Sql --> TableFooBar-MyMachine

The Update. Sql will have statement like:

Update TableFooBar-<Machine-Name> where fooid = -99;

This batch file needs to run on many machines. There are actually many update statements on such tables. I do not want people to edit the sql files. Instead if I set the machine name in the batch file and pass it to the sql, I'm pretty much done! How do I pass the machine name parameter to the .sql file to achieve this?

schurik · Accepted Answer · 2011-05-26 15:04:58Z

6

you can use substitution variables

update.sql

--
Update TableFooBar-&1 set column_foo='bar' where fooid = -99;
--

and then call

sqlplus foo/bar@db @update.sql <Machine-Name>

edited May 26, 2011 at 15:04

answered May 26, 2011 at 14:58

schurik

7,9582 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DCookie Over a year ago

+1, was going to say the same. However, you left off the parameter on the sqlplus call: sqlplus foo/bar@db @update.sql <machine_name>

schurik Over a year ago

@DCookie: <Machine-Name> - is the call Parameter :)

DCookie Over a year ago

Oopps, didn't see it for some reason. Formatting helped :-)

Daniel Hilgarth · Accepted Answer · 2011-05-26 16:27:06Z

2

Yes, you can do this, by creating the SQL file from the BATCH file.

It would look like this:

@echo off
set SQL = update.sql
ECHO connect username/password@database
ECHO Prompt Updating tables > %SQL%
ECHO Update TableFooBar-%1 where fooid = -99; >> %SQL%

sqlplus @update.sql

edited May 26, 2011 at 16:27

answered May 26, 2011 at 14:53

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

Collectives™ on Stack Overflow

Passing parameters to Oracle SQL file from Batch file

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related