Tuesday, July 25, 2017

SQLPLUS, login.sql and 12.2 in the cloud or not



Another little hiccup in 12.2 this time with SQLPlus.  It appears that it will not run the login.sql in your current directory as it always has in the past.    

There seems to be a problem with SQLPlus and SQLCL using the SQLPATH setting.  SQLPlus as far as I can tell, still uses SQLPATH as it always has for running a SQL file like:

SQL> @emp

    EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
--------------- -------------------- ------------------------- -------------------------
            105 David                Austin                    DAUSTIN
            151 David                Bernstein                 DBERNSTE
            165 David                Lee                       DLEE
SQL>

In SQLPlus, the GET command as far as I know has never used SQLPATH to find files, it only looks in the current directory, and that seems to be working like normal.  

But SQLCL the GET command doesn’t appear to be working correctly.  You can get it to look in what SQLPATH is set to as long as SQLPATH only has ONE directory in it.  If SQLPATH has multiple entries (which mine does) then it doesn’t use it at all. 

This is a known issue in SQLPlus and you can look it up under this document number in your myoraclesupport account.  Doc ID 2274608.1   By the way, the doc talks about the glogin.sql file not being run, that doesn’t appear to be the case for me.  My fix was to add a call to my login.sql file in the glogin.sql file (in ORACLE_HOME/sqlplus/admin) and it worked just fine. The doc indicates that if the glogin.sql file is in some other directory pointed to by SQLPATH it doesn’t work, that very well may be true.  

I’ve logged an SR about the issue with SQLCL. I presume there is some common code between the two hence they seem to be related.

3 comments:

  1. there's no shared code between SQL*Plus and SQLcl...compiled C binaries vs Java :) We do try to emulate what they do when it makes sense...but we also diverge when it also makes sense.

    We'll run your login.sql files in your CWD, but ONLY the non-'dangerous' stuff like a SET command. This change for both tools was done for security purposes.

    ReplyDelete
  2. My problem was that %SQLPATH%\login.sql wasn't being called, either.

    I found that there is a patch for SQL*Plus, # 25804573

    from Andre Van Winssen:

    https://technology.amis.nl/2017/06/30/27-years-oracle-land-forced-patch-sqlplus/#comment-9381

    Applying it lets me use my (well, Tanel's) login.sql, which resides in my SQLPATH location.

    ReplyDelete