Forum Stats

  • 3,741,772 Users
  • 2,248,475 Discussions
  • 7,861,984 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

Answers

  • Erik van Roon
    Erik van Roon Member Posts: 58 Red 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 Red 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 Red 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 Red 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 Red 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 Red 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>
  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jan 8, 2020 10:59AM

    You're welcome.

    "regular" set sets the var for the CURRENT cmd.exe only, whereas setx sets the var for FUTURE cmd.exe's and also persists across reboots...

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

    Yep, I can confirm the same behaviour over here. Even the pre-existing system variables like %SystemRoot%, %SystemDrive% and %windir% are affected. I wonder whether there is a JAVA option for this since sqlcl is java-based

    John

  • Erik van Roon
    Erik van Roon Member Posts: 58 Red Ribbon
    edited Jan 8, 2020 11:19AM

    I know.

    But sometimes SET is exactly what you need exactly because it doesn't persist.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jan 8, 2020 6:55PM

    Indeed, I thought it would help to state the differences.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jan 12, 2020 6:57AM
    Yep, I can confirm the same behaviour over here. Even the pre-existing system variables like %SystemRoot%, %SystemDrive% and %windir% are affected. I wonder whether there is a JAVA option for this since sqlcl is java-based

    No need spending  time "wondering", just use uppercase declared windows OS variables. That resolves the issue.

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

    It is an acceptable workaround if we are writing new codes but my initial objective was to evaluate whether sqlplus can be replaced by sqlcl to run our existing codes with minimal impact. Given that we have quite a large number of scripts and various teams writing them, I would say the sqlcl still has some way to go before we can use it instead of sqlplus.

    Anyway, many thanks for you help in identifying the issue ... much appreciated.

    John

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jan 14, 2020 12:14PM
    Anyway, many thanks for you help in identifying the issue ... much appreciated.

    You are most welcome.

    It is an acceptable workaround if we are writing new codes but my initial objective was to evaluate whether sqlplus can be replaced by sqlcl to run our existing codes with minimal impact. Given that we have quite a large number of scripts and various teams writing them, I would say the sqlcl still has some way to go before we can use it instead of sqlplus.

    Indeed... the phrase "use the right tool for the job" applies here I believe. sqlcl is a feature rich tool that does most of what sqlplus does plus extra stuff. I would never replace sqlplus with sqlcl myself however.

Sign In or Register to comment.