Skip to Main Content
  • Questions
  • Windows-Based SQL*Plus Scripting Issue


Question and Answer

Connor McDonald

Thanks for the question, Richard.

Asked: May 26, 2023 - 3:10 pm UTC

Last updated: May 29, 2023 - 3:56 am UTC

Version: 19.x

Viewed 1000+ times

You Asked

I will preface this with not having had tested SQL*Plus scripts on Windows utilizing script arguments in a number of years so I can't categorically state when this stopped working on said platform. That being said, the issue described does not occur on any *nix system. Additionally, this is running the SQL*Plus from the Instant Client not the Full Client.

For Windows-based SQL*Plus scripting if you plan to use script arguments such as (being executed as "sqlplus <username>/<password>@<tns_alias> @test.sql 1"):

select  to_number( '&1' )
from    dual;

You get the following:

SQL> select     to_number( '&1' )
  2  from       dual;
select  to_number( '&1' )
ERROR at line 1:
ORA-01722: invalid number

as it is not translating the '&1' to mean the first script argument.

If you add "set define '&'" to the options setting up the SQL*Plus environment then it runs as expected:

old   1: select  to_number( '&1' )
new   1: select  to_number( '1' )


1 row selected.

My question is why is it only mandatory on Windows to purposefully set DEFINE to its default value ('&')?

I struggled with this for many weeks as there wasn't even a thought in my head I needed to set DEFINE to its default value as it makes no sense to do so. Only after re-reading the SQL*Plus User's Guide for the thousandth time did I say what the heck and as soon as I did it worked. Either this needs to be fixed or the documentation needs to address this because in theory SQL*Plus scripts, short of shelling out with HOST, should be portable.

and Connor said...

I suspect you're picking up a login.sql script from somewhere.

Here's my Windows installation

C:\>sqlplus scott/tiger@pdb21a

SQL*Plus: Release - Production on Mon May 29 11:54:11 2023

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Wed May 24 2023 12:07:44 +08:00

Connected to:
Oracle Database 21c Enterprise Edition Release - Production

SQL> show define
define "&" (hex 26)

Check to see if SQLPATH is set either as an environment variable or in the registry. It might be grabbing a "login.sql" or "glogin.sql" and unsetting it.


  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore


Need more information on Multitenant? Check out the Multitenant docs for the Oracle Database