SQL*plus does not tokenize its command-line arguments properly when the program path contains spaces

Question

I am using SQL*Plus with the following command line:

sqlplus user/pw@TNS @test.sql foo

The contents of test.sql follow:

SET VERIFY ON
DEFINE argone='&&1'
SELECT '&argone' FROM dual;
EXIT SQL.sqlcode

Results:

• When SQL*Plus executable is in C:\Program Files\Oracle Client\whatever\sqlplus.exe then &&1 evaluates to Files\Oracle.
• When SQL*Plus executable is in C:\Oracle\Client\10.2.xx\bin then &&1 evaluates to foo.

Did anyone encounter this problem and had a way to circumvent it?

Answer 1

You'll need to use double quotes at both the command line and the define statement to properly capture the arguments with spaces.

Script:

SET VERIFY ON
DEFINE argone="&&1"
SELECT '&argone' FROM dual;
EXIT SQL.sqlcode

Command line:

sqlplus user/pw@TNS @test.sql "foo bar"