3 Replies Latest reply on Aug 3, 2020 5:29 PM by Gaz in Oz

    SQLcl SQLPROMPT _USER value in lowercase




      sqlcl - 19.4

      DB -


      I have recently started using sqlcl along with SQL*Plus, good tool with easy to use.


      Objective: Have single login.sql file for both SQL*Plus and SQLcl


      In sqlcl, for few hours trying to convert _USER value to display as lower case but not successful.


      I have below questions if somebody could provide some input than it will be of great help.


      1. How can I display _USER value in lower case?

         Using below in login.sql displays username in uppercase. (TESTUSER@testdb.dev)



      2. Is it possible to have single login.sql file which can be use for both SQL*Plus and SQLcl?


      My current file is below, can I directly add above #1 set sqlprompt... in my current file without any impact or there is a better way?




      column COL_USR new_value VAR_USR
      column COL_PRMT new_value VAR_PRMT
      SELECT lower(user_users.username) COL_USR
       FROM users_users;
      SELECT value COL_PRMT
       FROM parameter
       WHERE parameter.name = 'sql_prompt';
      -- SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> " // testline for sqlcl
      undefine VAR_USR
      undefine VAR_PRMT


      I have login prompt as testuser@testdb.dev>


      Thanks and appreciate all input.

        • 1. Re: SQLcl SQLPROMPT _USER value in lowercase
          Erik van Roon

          Regarding the single login.sql

          Yes, SQLPlus and SQLcl use the same SQLPATH environment variable to look for a login.sql.

          So, in fact it's hard(er) to *not* use the same login.sql than it is to use the same one.


          But the problem is of course: how about stuff in your login.sql that is specific for just one of the tools?

          Well, then you will have an errormessage at that point every time you start the other tool.


          I (too??) have quite a lot of stuff going on on my login.sql.

          Most of which is common for both tools, but some isn't.

          Obviously I don't want to have two nearly identical login.sql scripts to avoid a maintenance nightmare, but neither do I want error messages on startup.


          So, How do I solve that?

          (Note: I'm not saying "how should everybody solve this?")

          (Below is for windows, but if you're on Linux, I guess you know how to port this)


          First of all:

          I have all the 'common' stuff both tools understand and support in a single file called "login_common.sql"

          You can place this anywhere you want, let's say I have it in "D:\Scripts\"


          Then I create two login.sql files in *separate* directories:




          These contain the stuff that is specific for that tool *and* they call the common script.

          Example of the login.sql for sqlcl

          -- Set some sqlcl specific stuff
          set sqlformat ansiconsole
          -- Run the common login script that can be found one level higher
          @@ ..\login_common.sql
          -- Set some other sqlcl specific stuff
          set classic on


          Now, the important stuff

          I don't run either sqlplus.exe or sql.exe directly.

          For both I have a batch-script: sp.bat for sqlplus and sc.bat for sqlcl.

          There's a lot of 'fancy' stuff I do in these batch scripts, but for this problem the essence is that the bat-file adds the location of the login.sql for that tool to the SQLPATH environment variable *before* starting the tool.

          Since the environment variable is changed within the bat-script the changes will only be available within that bat-script.

          So when the bat script is done, you won't be left with a cluttered SQLPATH variable.

          But by adding the login.sql location the tool is now able to find it's own login.sql and not the other one.


          Example SQLPATH environment variable

          SET SQLPATH=D:\Scripts;D:\OtherScripts


          Example of batch script for sqlcl

          @echo off
          :: Add the location of the tool-specific login.sql to the front of the SQLPATH variable
          set sqlpath=D:\Scripts\sqlcl;%sqlpath%
          :: run sqlcl with all parameters passed to the batchfile
          e:\sqlcl\bin\sql.exe %*


          And you should be on your way.



          Regarding Lowercase-problem

          I don't have a solution for your lowercase problem, other than:

          Don't do it this way.

          The things you use to set your sqlprompt (if this is what you do, and not just a simplified version of it) can all be retrieved using sys_context.


          so if you do, for example:


          set termout off
          column current_user noprint new_value var_usr
          column con_name    noprint new_value var_prmt
          select lower(sys_context('userenv', 'current_user'))  current_user
          ,      lower(sys_context('userenv', 'con_name')    )  con_name
          from  dual
          set termout on
          set sqlprompt '&var_usr@&var_prmt> '


          Then it should work, and you can do whatever you want to the strings inside the sql.


          Don't forget to turn termout off when doing the sql statement.

          In sqlplus the noprint will prevent the result of the query to be put on screen.

          But although sqlcl knows that "noprint" is correct syntax (so it doen't show an error) it will just ignore it and put the query result to screen ( Grrrrr )


          • 2. Re: SQLcl SQLPROMPT _USER value in lowercase

            Hello Erik,

            Wow very good explanation and thank you for all the details.

            I'm trying to test variable values outside sys_context but it somehow wasn't working.


            I like your idea to use sys_context value and keep it simple, will surely test this further and try to implement it.


            1. Is there a difference in how we get variable value for SQL&Plus and SQLcl?

            whatever I did with my login.sql for SQLcl was not displaying variable values.


            Marking your answer as correct without any doubt.


            Thank you and much appreciated.

            • 3. Re: SQLcl SQLPROMPT _USER value in lowercase
              Gaz in Oz
              C:\>set SQLPATH=c:\users\Gaz
              C:\>type %SQLPATH%\login.sql
              col _user new_value _user
              set termout off feedback off verify off
              select lower('&_user') "_USER" from dual;
              set sqlprompt '&_user.@&_connect_identifier.:SQL> '
              C:\>sql gaz/gaz@//localhost/xepdb1
              SQLcl: Release 18.1 Production on Tue Aug 04 03:24:08 2020
              Copyright (c) 1982, 2020, Oracle.  All rights reserved.
              Last Successful login time: Tue Aug 04 2020 03:24:11 +10:00
              Connected to:
              Oracle Database 18c Express Edition Release - Production
              gaz@//localhost/xepdb1:SQL> exit
              Disconnected from Oracle Database 18c Express Edition Release - Production
              C:\>sqlplus gaz/gaz@//localhost/xepdb1
              SQL*Plus: Release - Production on Tue Aug 4 03:24:23 2020
              Copyright (c) 1982, 2019, Oracle.  All rights reserved.
              Last Successful login time: Tue Aug 04 2020 03:24:11 +10:00
              Connected to:
              Oracle Database 18c Express Edition Release - Production
              gaz@//localhost/xepdb1:SQL> exit
              Disconnected from Oracle Database 18c Express Edition Release - Production