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?

mustaccio · Accepted Answer · 2013-09-04 18:24:53Z

69

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';

answered Sep 4, 2013 at 18:24

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

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

4 Comments

djangofan Over a year ago

Thanks. That was it. It sounds like the '@' character is an alias for "START".

user272735 Over a year ago

@ChadD see sqlplus spool-command.

Ben Over a year ago

Not sure but can't you "replace" the & with another delimiter by setting set define ?

Sam B Over a year ago

I also want to add that do not set this in your SQL "SET DEFINE OFF" if you do that then this wont work.

Devon_C_Miller · Accepted Answer · 2016-09-20 15:46:43Z

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

edited Sep 20, 2016 at 15:46

Devon_C_Miller

16.5k3 gold badges48 silver badges75 bronze badges

answered May 17, 2016 at 9:06

PolyMorph

832 silver badges7 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related