How to stop sqlcl from inserting $Path and other environment variables

User_F2J4D

Executing "select '$Path' from dual" on macOS inserts the PATH environment variable into the statement. How can this be turned off?

User_2DKLA

Hello,

That issue was also discussed in that thread: https://community.oracle.com/tech/developers/discussion/4492570/bug-with-the-string-temp-in-21-4-at-least-in-windows

Unfortunately, so far it seems it can't be disabled—at least, how to do that has apparently not been publicly disclosed. 😕

You might try to work around it, as unset parameters are not substituted, e.g.:

select '$'||'Path' from dual

Regards,

User_H3J7U

SQL> prompt $OS
Windows_NT
SQL> script
  2  var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");
  3  var CommandListener = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener");
  4
  5  var cmd = {};
  6  cmd.handle = function (conn,ctx,cmd) {return false;}
  7  cmd.begin  = function (conn,ctx,cmd) {
  8    if (cmd.getProperty("prop_post_env_var_expansion")) {
  9      s0 = cmd.getProperty("prop_post_substitution");
 10      if (!s0) s0 = cmd.getSQLOrig();
 11      cmd.setSql(s0);
 12    }
 13  }
 14  cmd.end    = function (conn,ctx,cmd) { }
 15  var FixEnvSubst =
 16    Java.extend(CommandListener,
 17                { handleEvent:cmd.handle, beginEvent:cmd.begin, endEvent:cmd.end}
 18                );
 19
 20  CommandRegistry.addForAllStmtsListener(FixEnvSubst.class);
 21  ctx.write("FixEnvSubst activated\n");
 22* /
FixEnvSubst activated
SQL> prompt $OS
$OS
SQL> 

User_2DKLA

@User_H3J7U: impressive and enlightening! And of course that also works in SQL Developer.

Thanks!

Reminder: as of SQLcl 21.4 and SQL Developer 21.4.1, Javascript is practically broken if the Java 8 JDK is being used (details here: https://community.oracle.com/tech/developers/discussion/4492701/error-js-language-engine-not-found-in-sql-dev-sqlcl-21-4). Therefore the Java 11 JDK should be used, possibly with -Dnashorn.args=--no-deprecation-warning in JVM arguments, in order to inhibit the Nashorn engine deprecation message that comes with that release of the JDK.

Regards,

User_F2J4D

I would have hoped for some kind of set ENVSUBSTITUTE OFF but thanks for the solution. I wanted to investigate the javascript capabilities anyways.

User_H3J7U

21.4.1 Release Notes

Bugs Fixed

Here is a limited list of some high priority bugs fixed for 21.4.1

• 33734264 SET LOAD TEMPORAL FORMATS NOT HONORED ON DDL
• 33677012 WORKSHEET INCORRECTLY EXPANDS ENVIRONMENT VARIABLE VALUES INTO SQL CODE THAT REFERENCES VARIABLE NAMES
• 33676971 REMOVE ENV VARIABLE SUBTITUTION FROM COMMON LIBRARY

User_F2J4D

Ok, that works, too 🚀