41

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?

djangofan
  • 28,471
  • 61
  • 196
  • 289

2 Answers2

65

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';
mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • Thanks. That was it. It sounds like the '@' character is an alias for "START". – djangofan Sep 04 '13 at 20:51
  • 2
    @ChadD see `sqlplus` [`spool`](http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve043.htm#i2683777)-command. – user272735 Jan 16 '15 at 06:21
  • Not sure but can't you "replace" the `&` with another delimiter by setting `set define` ? – Ben Jul 05 '19 at 08:19
  • 1
    I also want to add that do not set this in your SQL "SET DEFINE OFF" if you do that then this wont work. – Sam B Apr 03 '23 at 15:54
5

If you want log the run of sqlplus, you can use this syntax:

sqlplus.exe MYUSER/mypassword@HOST030 @refreshDataOnOracle.sql foo bar  >> log.log
Devon_C_Miller
  • 16,248
  • 3
  • 45
  • 71
PolyMorph
  • 83
  • 2
  • 7