sqlplus, command line parameters and ORACLE_HOME with spaces

Question

I have noticed strange interaction between ORACLE_HOME and parameter substitution. Here is my test.sql

prompt &1
prompt &2
prompt &3
prompt &4
exit

The expected behavior:

C:\cygwin64\home\rejap\subst>sqlplus /nolog @test.sql one two three four five
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 16 15:19:16 2019
Copyright (c) 1982, 2017, Oracle. All rights reserved.
one
two
three
four
C:\cygwin64\home\rejap\subst>

however, when ORACLE_HOME is set, things get interesting

C:\cygwin64\home\rejap\subst>set ORACLE_HOME=c:\Program Files (x86)\Oracle\product12円.2.0\client_1
C:\cygwin64\home\rejap\subst>sqlplus /nolog @test.sql one two three four five
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 16 15:21:25 2019
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Files
(x86)\Oracle\product12円.2.0\client_1\sqlplus\admin\glogin.sql
three
four
C:\cygwin64\home\rejap\subst>

Am I missing something fundamental here?

Are there any negative effects of not setting ORACLE_HOME at all?

Are there any negative effects of pointing ORACLE_HOME to symlink?

c:
cd \
mklink /D oracle c:\Program Files (x86)\Oracle
set ORACLE_HOME=c:\oracle\product12円.2.0\client_1

And of course I am interested in explanation of what is going on.

• Philᵀᴹ: Looks like it’s messing up because you’re not escaping the spaces in the path. I think (but i’m unsure) that you’re supposed to escape the brackets too. I have little experience of Oracle on Windows, but I thought that ORACLE_HOME was usually a registry key.
• Wernfried Domscheit: How did you manage to install Oracle client in c:\Program Files (x86)\Oracle? Usually the Oracle installer does not permit it. Otherwise you may use c:\PROGRA~2\Oracle
• Pawel Jasinski: @PhilTM, how should I escape it? I think the sqlplus is happy with the value. If it is not set right, I get Error 6 initializing SQL*Plus SP2-0667: Message file sp1<lang>.msb not found SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
• Pawel Jasinski: @WernfriedDomscheit, I am not the one who installed it. I also believe the "no space" case is enforced by server but not client installer. At the same time, the 8.3 file name works perfectly.
• Pawel Jasinski: @WernfriedDomscheit, I asked some questions and installing oracle client into c:\Program Files (x86)\... is a creation of someone from IT department. You are right, the universal installer does not permit it by default.

Answer 1

sqlplus and many other programs do not require ORACLE_HOME to be set.

In this documentation 9.2 Oracle states:

Oracle Corporation recommends that you never set the ORACLE_HOME environment variable, because it is not required for Oracle products to function properly. If you set the ORACLE_HOME environment variable, then Oracle Universal Installer will unset it for you.

I did not find similar statement for never releases of Oracle, so it might be outdated. Newer installation guides for Windows do not mention ORACLE_HOME environment variable at all, so most likely it is really not needed.

• EdStevens: I think the cited document is misleading. It does not distinguish between setting ORACLE_HOME in a specific os process/session vs. setting it system-wide for all os processes/sessions. Plus your citation is a bit out of context, because it is discussing setting variables on a system with multiple ORACLE_HOMEs, and avoiding confusion between them.
• Pawel Jasinski: I think in my case setting ORACLE_HOME is cargo cult. I have it set, because I believe it should be done, but I can not find any documentation which tells me "do it or else". I have only one oracle client installed and even if I had multiple, it is sqlplus which deducts ORACLE_HOME to match its own location.

Answer 2

The fundamental that you are missing is that the command line ('sqlplus ......') is processed by the OS command processor BEFORE sqlplus gets anything. And the command processor sees every space as a delimiter for the next command line parm to be passed to whatever executable it is setting up.

• Pawel Jasinski: How should I escape it? I tried ,円 " and ^, all trigger Error 6 initializing SQL*Plus SP2-0667 .... Or better where in command line I execute do you see %ORACLE_HOME?
• EdStevens: You are correct, you did not include ORACLE_HOME in your sqlplus command line. I conflated the two statements (set ORACLE_HOME and sqlplus)statements in my own mind.