1 2 Previous Next 17 Replies Latest reply on Jan 14, 2020 5:14 PM by Gaz in Oz

    Using environment variables in sqlcl

    John Li Yim [VND]

      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

        • 1. Re: Using environment variables in sqlcl
          Erik van Roon

          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).

           

          ERO@EVROCS>version

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

          ERO@EVROCS>@%temp%/test_env_var.sql

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

          running script from %TEMP%

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

          ERO@EVROCS>

          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.

          • 2. Re: Using environment variables in sqlcl
            John Li Yim [VND]

            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>

            • 3. Re: Using environment variables in sqlcl
              Erik van Roon

              Hmmmm.

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

               

              Strange.

              • 4. Re: Using environment variables in sqlcl
                Erik van Roon

                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

                • 5. Re: Using environment variables in sqlcl
                  Gaz in Oz

                  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

                  • 6. Re: Using environment variables in sqlcl
                    Erik van Roon

                    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

                    • 7. Re: Using environment variables in sqlcl
                      Gaz in Oz

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

                      • 8. Re: Using environment variables in sqlcl
                        Erik van Roon

                        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   )

                        • 9. Re: Using environment variables in sqlcl
                          Gaz in Oz

                          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_directory
                          
                          SUCCESS: Specified value was saved.
                          
                          F:\Oracle>setx UPPERCASE f:\oracle\a_directory
                          
                          SUCCESS: Specified value was saved.
                          
                          F:\Oracle>
                          

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

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

                          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.

                          • 10. Re: Using environment variables in sqlcl
                            Erik van Roon

                            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.

                             

                            erikv@LAPTOP-EVROCS E:\sqlcl\bin
                            > set testvar_lower=C:\Users\erikv\AppData\Local\Temp
                            
                            erikv@LAPTOP-EVROCS E:\sqlcl\bin
                            > set TESTVAR_UPPER=C:\Users\erikv\AppData\Local\Temp
                            
                            erikv@LAPTOP-EVROCS E:\sqlcl\bin
                            > sql ###/###########@evrocs
                            
                            SQLcl: Release 19.4 Production on wo jan 08 16:27:45 2020
                            
                            Copyright (c) 1982, 2020, Oracle.  All rights reserved.
                            
                            Connected to:
                            Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
                            Version 19.3.0.0.0
                            
                            SQL> @ %testvar_lower%\test_env_var.sql
                            
                            Error starting at line : 1 in command -
                            @ %testvar_lower%\test_env_var.sql
                            Error report -
                            SP2-0310: Unable to open file: "\test_env_var.sql"
                            SQL> @ %testvar_upper%\test_env_var.sql
                            ######################################################################
                            running script from %TEMP%
                            ######################################################################
                            SQL>
                            
                            • 11. Re: Using environment variables in sqlcl
                              Gaz in Oz

                              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...

                              • 12. Re: Using environment variables in sqlcl
                                John Li Yim [VND]

                                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

                                • 13. Re: Using environment variables in sqlcl
                                  Erik van Roon

                                  I know.

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

                                  • 14. Re: Using environment variables in sqlcl
                                    Gaz in Oz

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

                                    1 2 Previous Next