How do I pass arguments to a PL/SQL script on command line with SQLPLUS?

Question

How do I pass arguments to a PL/SQL script on command line with SQLPLUS? I can call my PL/SQL script like so, but the script requires arguments in order for it to succeed. How can I run sqlplus.exe so that I can pass arguments to the script?

@ECHO off
// where HOST030 is a tnsnames alias to a machine, port, and instance    
sqlplus.exe MYUSER/mypassword@HOST030 < refreshDataOnOracle.sql    
pause

I tried to search for the answer but couldn't find an "argument example" anywhere for SQLPLUS. I suspect it would be a similar method of using the SQL*Plus "START" command?

Answer 1

Firstly, you will need to invoke your script like so:

sqlplus.exe MYUSER/mypassword@HOST030 @refreshDataOnOracle.sql foo bar  

Instead of the OS redirection you will use the "@" symbol to indicate the file name to execute. You will also supply the script parameters on the command line. In the script you will refer to the parameters using &1, &2 etc.

update mytable set mycol = '&2' where myid = '&1';

which will translate into

update mytable set mycol = 'bar' where myid = 'foo';