Sometimes there are tiny little changes in a new release – and sometimes they are not documented very well yet. One of these changes is the new behavior of login.sql for SQL*Plus in Oracle Database 12.2.0.1.
Behavior before Oracle Database 12.2
In all releases until Oracle Database 12.1.0.2 by default SQL*Plus searches for the user profile (login.sql) in your current directory first, and then in the directories you specify with the ORACLE_PATH environment variable.
Please be aware that this change can be introduced with a PSU or a BP from 2017 as well.
Behavior since Oracle Database 12.2
Since Oracle Database 12.2.0.1 SQL*Plus will only search for the user profile (login.sql) in the directories you specify with the ORACLE_PATH environment variable on Linux (or SQLPATH on Windows). That means, you have a login.sql in your working directory – but it will be silently ignored unless you set explicitly ORACLE_PATH or SQLPATH to point to this directory. I’d just blindly guess that the same behavior change applies to SQLcl as well.
Workaround/Solution
Users currently using a login.sql to customize a session will need to set the ORACLE_PATH variable accordingly, and move login.sql to the directory defined in ORACLE_PATH.
If that doesn’t work for you maybe using a prologue for scripts, including it explicitly with @@ calls would be a doable workaround.
On Windows please be aware of (credits to Tim Hall):
- Bug 25804573
SQL PLUS 12.2 NOT OBSERVING SQLPATH IN REGISTRY OR ENV VARIABLE FOR LOGIN.SQL
Related information:
- MOS Note 2241021.1
SQL*Plus 12.2.0.1.0 Change in Behavior for Search Path of Login.sql - Franck Pachot (dbi services):
https://blog.dbi-services.com/oracle-12cr2-changes-for-login-sql/
–Mike
earlier it was easy for us to remember the urls like this.
blogs.oracle.com/optimizer
blogs.oracle.com/developer
blogs.oracle.com/upgrade.
but now looks like most of you are getting move out, ( like Maria colgan got moved into sqlmaria.com and now you into this new site ) – it is hard to remember the website name these days.
I apologize – and I see your point. Hopefully you’ll remember MikeDietrichDE.com as well as it is my twitter handle 🙂 MikeDietrich.com was open for an offer at roughly $3000 😉 But the blogs.oracle.com will undergo a migration to a more marketing look&feel which is not appropriate for a tech blog. Therefore the move – and I’m just guessing that others will follow.
Cheers
Mike
Mike,
Is it possible for you to add the notification about a new blog post from this blog to appear under the “Blogs” section of this link. ( like how you do previously with blogs.oracle.com/upgrade, when a new post got published it get reflected in “Blogs” of this OTN link )
http://www.oracle.com/technetwork/dbadev/index.html
that would help us to keep track of new things coming up in this blog.
Hope you would help us on this.
Rajeshwaran,
thanks for this hint – but unfortunately I have no influence on this page – and the people owning it will strictly enforce only Oracle-hosted content to be displayed. Furthermore I checked some links and they are pretty outdated. For instance, my “Hitchhiker’s Guide” presentation dates from April 2014 😉
But you can subscribe on the blog directly – see on the main page at https://MikeDietrichDE.com and scroll down a bit on the right side to SUBSCRIBE. That should work pretty straight forward – and you will ONLY notified when a new post gets published.
Cheers
Mike
We just completed an upgrade to 12.2 (from 12.1) and ran into a strange problem.
With sec_case_sensitive_logon=TRUE (the default), the password sensitivity must match. I created a test user naveen with password Naveen, and it gives an error when I login as naveen/naveen. Which is expected.
Next I changed sec_case_sensitive_logon=FALSE and now I cannot login as user naveen. I tried both naveen/Naveen and naveen/naveen and in both cases I get ORA-01017: invalid username/password; logon denied.
Not sure what is going on here??
Hi Naveen,
the secret is first the SQLNET.ALLOWED_LOGON_VERSION_SERVER in your sqlnet.ora – it defaults to 12, and in order to have this working you will have to lower it to 11 and potentially restart the listener. Furthermore you will have to ALTER the user, e.g. ALTER USER naveen identified by Naveen;. Once you do that you can connect now with naveen/naveen or naveen/NAVEEN etc.
Cheers
Mike
Is it applicable in 19c Env as well? I have a customer who has this issue 19c client: SQL*PLUS does not consistently load the login.sql file from the SQLPATH. They are on 19.10.0.0.0
As far as I know, yes.
Cheers,
Mike