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
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.
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 )
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.
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 188.8.131.52.0 - Production gaz@//localhost/xepdb1:SQL> exit Disconnected from Oracle Database 18c Express Edition Release 184.108.40.206.0 - Production C:\>sqlplus gaz/gaz@//localhost/xepdb1 SQL*Plus: Release 220.127.116.11.0 - Production on Tue Aug 4 03:24:23 2020 Version 18.104.22.168.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 22.214.171.124.0 - Production Version 126.96.36.199.0 gaz@//localhost/xepdb1:SQL> exit Disconnected from Oracle Database 18c Express Edition Release 188.8.131.52.0 - Production Version 184.108.40.206.0 C:\>