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

    SQLcl SQLPROMPT _USER value in lowercase

    Mike301

      Friends,

       

      sqlcl - 19.4

      DB - 12.1.0.2

       

      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)

      SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "
      

       

      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?

       

      login.sql

       

      SET HEADING OFF
      
      column COL_USR new_value VAR_USR
      column COL_PRMT new_value VAR_PRMT
      
      SET TERMOUT OFF
      
      SELECT lower(user_users.username) COL_USR
       FROM users_users;
      
      SELECT value COL_PRMT
       FROM parameter
       WHERE parameter.name = 'sql_prompt';
      
      SET TERMOUT ON
      
      -- SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> " // testline for sqlcl
      SET SQLPROMPT '&VAR_USR@&VAR_PRMT'
      
      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:

          "D:\Scripts\sqlplus\login.sql"

          "D:\Scripts\sqlcl\login.sql"

           

          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.

          NOTE!!!

          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
            Mike301

            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 18.0.0.0.0 - Production
              
              
              gaz@//localhost/xepdb1:SQL> exit
              
              Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
              
              C:\>sqlplus gaz/gaz@//localhost/xepdb1
              
              SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 4 03:24:23 2020
              Version 19.6.0.0.0
              
              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 18.0.0.0.0 - Production
              Version 18.4.0.0.0
              
              gaz@//localhost/xepdb1:SQL> exit
              Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
              Version 18.4.0.0.0
              
              C:\>