Passing parameters to Oracle SQL file from Batch f

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?

Answer 1

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

Answer 2

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>