Forum Stats

  • 3,751,804 Users
  • 2,250,415 Discussions
  • 7,867,596 Comments

Discussions

Using environment variables in sqlcl

John Li Yim [VND]
John Li Yim [VND] Member Posts: 4
edited Jan 14, 2020 12:14PM in SQLcl

How can we reference environment variables in sqlcl?

We have a bunch of sql scripts that call other sql scripts using a environment variables

eg (Windows OS).

@%sql_path%/dba.sql

Using sqlcl, this does not work.

Any idea?

John

Erik van Roon
«1

Answers

  • Erik van Roon
    Erik van Roon Member Posts: 58 Blue Ribbon
    edited Jan 7, 2020 7:55AM

    It works for me on all sqlcl versions from current 19.4 all the way back to 4.2 (at least, but stopped testing there).

    [email protected]>version

    Oracle SQLDeveloper Command-Line (SQLcl) version: 19.4.0.0 build: {1}

    [email protected]>@%temp%/test_env_var.sql

    ######################################################################

    running script from %TEMP%

    ######################################################################

    [email protected]>

    sqlcl_env_var.jpg

    Please explain "this does not work".

    What errors do you get?

    Is your environment variable set correctly?

    Please do a

    host echo %sql_path%

    just before using it, so you can see what it actually contains when you use it.

    I wouldn't be surprised if it contains a backslash at the end, rendering the constructed filepath invaild.

  • John Li Yim [VND]
    John Li Yim [VND] Member Posts: 4
    edited Jan 7, 2020 8:11AM

    Below is the SQL output (I'm using the sqlcl version that came with SQL Developer 19.2 ... will try the standalone version when I get some time later):

    SQL> show version

    Oracle SQLDeveloper Command-Line (SQLcl) version: 19.2.1.0

    SQL> host dir %sql_path%\dba.sql

    Volume in drive Q is Data

    Volume Serial Number is BC4E-6865

    Directory of Q:\5_T5\2_sql

    24/12/2018  13:40            20,271 dba.sql

                   1 File(s)         20,271 bytes

                   0 Dir(s)  45,566,144,512 bytes free

    SQL> @%sql_path%\dba.sql

    Error starting at line : 1 in command -

    @%sql_path%\dba.sql

    Error report -

    SP2-0310: Unable to open file: "\dba.sql"

    SQL>

  • Erik van Roon
    Erik van Roon Member Posts: 58 Blue Ribbon
    edited Jan 7, 2020 8:46AM

    Hmmmm.

    If I set my own variable testvar I see the same behaviour.

    Strange.

  • Erik van Roon
    Erik van Roon Member Posts: 58 Blue Ribbon
    edited Jan 7, 2020 10:33AM

    As a workaround for now you could put the value of the environment variable in a substitution variable.

    I have some environment variables that I want available in my sqlcl/sqlplus scripts.

    So I have a script for putting an environment variable in a substitution variable.

    I run this script for a couple of variables in my login.sql, but obviously you can run it anytime, anywhere.

    Below is the contents of my get_env_var.sql script.

    If you call this script for example this way:

    @get_env_var sql_path

    It will create a substitution variable env_sql_path containing the value of the environment variable (if it exists of course).

    After that you can call your script as follows:

    @&env_sql_path\dba.sql

    To prevent extra output on screen by this script, you may want to run it with termout off (once you've verified that it works correctly).

    Script get_env_var.sql

    -- Put name of desired environment variable (parameter 1) in a substitution variable

    define env_var_name=&1

    -- put sessionid in a substitution variable

    column ThisSessionId       noprint new_value ThisSessionId

    select sys_context('userenv','sessionid')    ThisSessionId

    from   dual

    ;

    -- Construct the pathname for a temporary sql script as "GetEnvVar_" + sessionID + ".sql" placed in the %temp% directory

    define GetEnvVarSqlScript=%temp%\GetEnvVar_&ThisSessionId..sql

    -- Use the host command to echo a define statement to the temporary sql script

    host echo define env_&env_var_name=%&env_var_name%  > "&GetEnvVarSqlScript"

    -- execute the temporary sql script

    @ "&GetEnvVarSqlScript"

    -- delete the temporary sql script

    host del /f "&GetEnvVarSqlScript" > nul

    -- cleanup variables

    undef env_var_name

    undef GetEnvVarSqlScript

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jan 7, 2020 9:42PM

    A much simpler "workaround" would be to use windows command line utility setx or the windows control panel to define the variable.

    Using setx, the var will only be visible from subsequent incarnations of cmd.exe.

    To OP I am guessing sqlcl doesn't read local env vars, but uses different registry key/s to locate env var values

  • Erik van Roon
    Erik van Roon Member Posts: 58 Blue Ribbon
    edited Jan 8, 2020 4:23AM

    Sorry Gaz, but the issue is not about setting the environment variable, and neither is the workaround.

    This issue is aboit *using* the environment variable within sqlcl, which does not always seem te work properly.

    The workaround is only a way to get an environment variable into a substitution variable so it *can* be used in these cases.

    Your workaround only defines environment variables.

    Which then still can't be used in the reported case.

    After being able to reproduce the problem I also tested if it would work if I used setx instead of set, but no luck, same behavior

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jan 8, 2020 4:56AM

    Apologies, you are right, after  further testing I see setx doesn't work.

  • Erik van Roon
    Erik van Roon Member Posts: 58 Blue Ribbon
    edited Jan 8, 2020 6:07AM

    No problem.

    The fact that setx doesn't work either surprised me.

    Since sqlcl can use the %temp% variable this way, but has problems with the custom variable I thought, maybe in this case sqlcl gets the value from the registry rather than straight from the variable. But if that would be the case setx should solve this. (And yes, it would seem ridiculous if sqlcl would do this, but *something* must be causing this   )

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jan 8, 2020 9:41AM

    After even more frigging around, it looks like it is a case issue. Can you confirm whether your env var is defined as uppercase or lowercase?

    My testing show a user or system reg var of lowercase cannot be found. Uppercase how ever can.

    For example in the initial cmd.exe:

    F:\Oracle>setx lowercase f:\oracle\a_directorySUCCESS: Specified value was saved.F:\Oracle>setx UPPERCASE f:\oracle\a_directorySUCCESS: Specified value was saved.F:\Oracle>

    In a new cmd.exe started AFTER using setx in the previous cmd.exe:

    F:\Oracle>sql /nologSQLcl: Release 18.1 Production on Thu Jan 09 00:25:40 2020Copyright (c) 1982, 2020, Oracle.  All rights reserved.SQL> @%lowercase%\msg.sqlError starting at line : 1 in command -@%lowercase%\msg.sqlError report -SP2-0310: Unable to open file: "\msg.sql"SQL> @%uppercase%\msg.sqlfound the fileSQL>

    Notice the %lowercase% reference resolves to null.

    Also note that declaring the variable via setx, it MUST BE declared in UPPERCASE, but referencing it does not.

    So basically, use UPPERCASE to define the variable with setx or use

    Control Panel -> System -> Advanced System Settings

    and click on the [Environment Variables...] button and convert your variable name to uppercase. Then click on ok, ok, ok etc and close control panel window and then start a NEW cmd.exe.

    Erik van Roon
  • Erik van Roon
    Erik van Roon Member Posts: 58 Blue Ribbon
    edited Jan 8, 2020 10:41AM

    I can confirm on my machine.

    Not only with setx, but also with 'regular' set.
    If environment variable is set with an uppercase name, no problem, if set with lower- or mixed case name it won't work.

    Thanks Gaz. You're brilliant.

    [email protected] E:\sqlcl\bin> set testvar_lower=C:\Users\erikv\AppData\Local\[email protected] E:\sqlcl\bin> set TESTVAR_UPPER=C:\Users\erikv\AppData\Local\[email protected] E:\sqlcl\bin> sql ###/###########@evrocsSQLcl: Release 19.4 Production on wo jan 08 16:27:45 2020Copyright (c) 1982, 2020, Oracle.  All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0SQL> @ %testvar_lower%\test_env_var.sqlError starting at line : 1 in command [email protected] %testvar_lower%\test_env_var.sqlError report -SP2-0310: Unable to open file: "\test_env_var.sql"SQL> @ %testvar_upper%\test_env_var.sql######################################################################running script from %TEMP%######################################################################SQL>