How To Make login.sql Work Again In 12.2 and 11.2.0.4

What happens when things change and all of a sudden don’t work the same as they used to?

Do you get annoyed? Do you start trying to find who’s fault it is? Do you start complaining?
Or
Do you try to figure the root cause and fix it?

I’d say it is all of the above. Of course we get annoyed, duh … we are humans, and by nature humans don’t like change. I wonder if aliens do …

I personally try, and most of the time succeed at being open to change.

Last summer, I published a post, Login.sql – The Answer To DBA Questions Where, And Who Am I?, which basically taught you how to setup the prompt in SQLPlus, in order to know exactly WHO you are connected as (username), and WHERE are you connected (database). This information is extremely useful when you are running multiple databases (and who isn’t) on the same server. But, let me not describe that post here. If you are curious, check it out here.

Why did I bring this up?

Because, Oracle has changed some behavior in 12.2 and in 11.2.0.4 db version after a 2017 PSU patch, which makes the information in that post not work as expected.

So things do change with time, with versions, with patches…

Here’s the story.

You applied the July 2017 PSU to your 11.2.0.4 database. It worked well, until you login through SQLPlus, and notice the prompt has changed.

Instead of this (you set this up last year, after reading my post, and you know it was working)

22:04:21 HRTST.WORLD (DROBETE) >

The prompt reset to this:

SQL>

You disconnect, reset your environment, and try again. No change. Same response.
You try another database that was patched, same behavior. All you see is

SQL>

This time you try a database that was not patched, better said a database in a different ORACLE HOME, that was not patched. And magically the prompt is back to what it used to be.

What happened?

Behavioral change happened in Oracle.

Prior to 12.2 Oracle Database, SQLPlus used to search for the login.sql script in the current directory, and then, if not found, it used to search in the $ORACLE_PATH or $SQLPATH environment variables. I personally always used $SQLPATH in Unix and Windows as well, and it used to work.

Starting with 12.2, and with 11.2.0.4 2017 PSUs, the behavior has changed.

SQLPlus will only look in $ORACLE_PATH environment variable on Unix, and %SQLPATH% on Windows for the login.sql

If you have applied 2017 PSUs to your databases, you are most likely affected, same applies if you are running 12.2.

Just to recap, in order to customize the prompt in SQLPlus, explicitly set the $ORACLE_PATH variable to a preferred location, customize login.sql and place it in the location where $ORACLE_PATH is pointing to.

There are some great posts about this already out there:

Oracle 12cR2: changes for login.sql – by Franck Pachot
Security change in Oracle Database 12.2 with login.sql – by Mike Dietrich
Doc ID 2241021.1 SQL*Plus 12.2.0.1.0 and 11.2.0.4 PSU Change in Behavior for Search Path of Login.sql (SQL*Plus User Profile Script)

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana