Using environment variables in sqlcl

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

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

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   )

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>

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