Forum Stats

  • 3,783,670 Users
  • 2,254,818 Discussions
  • 7,880,514 Comments

Discussions

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

User_F2J4D
User_F2J4D Member Posts: 3 Green Ribbon

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

Answers

  • User_2DKLA
    User_2DKLA Member Posts: 21 Green Ribbon

    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
    User_H3J7U Member Posts: 833 Gold Trophy
    edited Jan 18, 2022 11:36AM
    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_2DKLA
    User_2DKLA Member Posts: 21 Green Ribbon

    @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
    User_F2J4D Member Posts: 3 Green Ribbon

    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
    User_H3J7U Member Posts: 833 Gold Trophy

    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
    User_F2J4D Member Posts: 3 Green Ribbon

    Ok, that works, too 🚀