By Franck Pachot

.
If you use a login.sql script to set the SQL*Plus environment from your current working directory, you will see that it will not run anymore in 12.2. This is a security feature, and a good occasion to explain how sqlplus finds the scripts to run, on Linux.

For my test, I have login.sql, LOGIN.SQL, and script.sql in the following directories

$ tree /tmp/mytest/
/tmp/mytest/
├── a
│   ├── login.sql
│   ├── LOGIN.SQL
│   └── script.sqlL
├── b
│   ├── login.sql
│   ├── LOGIN.SQL
│   └── script.sql
├── login.sql
├── LOGIN.SQL
└── script.sql

I’m going to the parent directory

cd /tmp/mytest

The scripts display their name:

+ head login.sql LOGIN.SQL script.sql
==> login.sql  LOGIN.SQL  script.sql <==
prompt Hello from /tmp/mytest/script.sql

I’ll run commands from bash -x so that they are displayed, and environment variables are set only for the command to be run.

login.sql

+ sqlplus -s /nolog

Nothing displayed here. This is what has changed in 12.2 for security reasons the login.sql in the current working directory is not run anymore.

+ sqlplus -s /nolog @ login.sql

This is probably a side effect of the implementation of this new security feature: even when I explicitly want to run the login.sql script it is ignored

+ sqlplus -s /nolog @ login
Hello from /tmp/mytest/login.sql

Here, I rely on the implicit ‘.sql’ added and the script is run. Probably the implementation of the security feature is done before this implicit extension.

+ sqlplus -s /nolog @ /tmp/mytest/login.sql
Hello from /tmp/mytest/login.sql

With full path, the script is run.

Actually, the only way to get the current directory login.sql run implicitly when starting sqlplus or connecting is to set the current directory in ORACLE_PATH:

+ ORACLE_PATH=.
+ sqlplus -s /nolog
Hello from /tmp/mytest/login.sql

Note that this defeats the security feature, in the same way it is not recommended to add ‘.’ to your shell PATH. It is better to put only absolute paths in the PATH, with directories where you know nobody can add a trojan script.

LOGIN.SQL

+ sqlplus -s /nolog @ LOGIN.SQL

The implementation of this new feature is case insensitive. LOGIN.SQL is ignored even when specified in the command line.

+ sqlplus -s /nolog @ ./LOGIN.SQL
Hello from /tmp/mytest/LOGIN.SQL

Only when using less or more characters to specify it is is used.

Note that the implicit login.sql is case sensitive on Linux:

+ rm login.sql
+ ORACLE_PATH=.
+ sqlplus -s /nolog

Even with ORACLE_PATH it is not found.

SQLPATH

+ SQLPATH=/tmp/mytest
+ sqlplus -s /nolog

SQLPATH is not a solution to find login.sql unlike ORACLE_PATH

Note that the documentation tells very different things in http://docs.oracle.com/database/122/SQPUG/configuring-SQL-Plus.htm#SQPUG012

Update 28-AUG-17 – see Edward Rusu about the bug and patch.

script.sql

Now, because SQLPATH and ORACLE_PATH was already a mess, I’ll try with a script that is not login.sql

+ sqlplus -s /nolog @ script.sql
Hello from /tmp/mytest/script.sql

Current directory is still searched for non-login scripts

+ sqlplus -s /nolog @ /tmp/mytest/script.sql
Hello from /tmp/mytest/script.sql

Absolute path can be used, or we can sete a PATH to search:

+ SQLPATH=/tmp/mytest
+ sqlplus -s /nolog @ script
Hello from /tmp/mytest/script.sql

Unlike login.sql, SQLPATH can be used to find a script in another directory

+ cd /tmp/mytest/a
+ SQLPATH=/tmp/mytest
+ sqlplus -s /nolog @ script
Hello from /tmp/mytest/a/script.sql

But current directory is still the first one that is searched

+ rm script.sql
+ SQLPATH=/tmp/mytest
+ sqlplus -s /nolog @ script
Hello from /tmp/mytest/script.sql

Only when the script is not in the current directory it is searched in SQLPATH

+ rm /tmp/mytest/script.sql
+ SQLPATH=/tmp/mytest
+ sqlplus -s /nolog @ script
SP2-0310: unable to open file "script.sql"

Again, the documentation is wrong. Only specified directories are looked-up, not sub-directories. But if I specify the subdirectory relative to SQLPATH (I am still in /tmp/mytest/a where there is no script.sql)

+ SQLPATH=/tmp/mytest
+ sqlplus -s /nolog @ b/script
Hello from /tmp/mytest/b/script.sql

The b/script was resolved from the SQLPATH=/tmp/mytest

In SQLPATH, we can add multiple paths

+ SQLPATH=/tmp/mytest:/tmp/mytest/a:/tmp/mytest/b
+ sqlplus -s /nolog @ script
Hello from /tmp/mytest/b/script.sql

Here I have a script.sql only in the ‘b’ sub-directory and sqlplus finds it when this directory is listed in SQLPATH

ORACLE_PATH

Running the same with ORACLE_PATH instead of SQLPATH

+ ORACLE_PATH=/tmp/mytest:/tmp/mytest/a:/tmp/mytest/b
+ sqlplus -s /nolog @ script
Hello from /tmp/mytest/a/login.sql
Hello from /tmp/mytest/b/script.sql

We can have also multiple paths for ORACLE_PATH (this is not in the documentation) and it acts as with SQLPATH but there are a few differences.

First, you have seen that the login.sql script is run.

Then, if I have the script in my current directory, but not in ORACLE_PATH

+ cd /tmp
+ echo 'prompt Hello from /tmp' > script.sql
+ ORACLE_PATH=/tmp/mytest:/tmp/mytest/a:/tmp/mytest/b
+ sqlplus -s /nolog @ script
Hello from /tmp/mytest/a/login.sql
Hello from /tmp/mytest/b/script.sql

The ORACLE_PATH one is used first

+ ORACLE_PATH=/tmp/mytest:/tmp/mytest/a
+ sqlplus -s /nolog @ script
Hello from /tmp/mytest/a/login.sql
Hello from /tmp

The current directory is considered only when not found in ORACLE_PATH.

+ ORACLE_PATH=/tmp/mytest:/tmp/mytest/a
+ sqlplus -s /nolog @ b/script
Hello from /tmp/mytest/a/login.sql
Hello from /tmp/mytest/b/script.sql

As with SQLPATH, subdirectory is accessible if mentioned.

Both, in order

If you don’t want to keep it simple, you can specify both ORACLE_PATH and SQLPATH

+ cd mytest
+ ORACLE_PATH=/tmp
+ SQLPATH=/tmp/mytest/b
+ sqlplus -s /nolog @ script
Hello from /tmp

and ORACLE_PATH is read first. So the order is:

  1. ORACLE_PATH, in order of paths specified
  2. Current directory (except for login.sq)
  3. SQLPATH (except for login.sql) in order of paths specified

strace

Better than documentation or testing all combinations, in Linux we can trace the system calls when sqlplus is looking for the script.

I’ve set non-existing paths /ORACLE_PATH1 and /ORACLE_PATH2 for ORACLE_PATH, and /SQLPATH1 and /SQLPATH2 for SQLPATH and run ‘script’ without the extension

ORACLE_PATH=/ORACLE_PATH1:/ORACLE_PATH2 SQLPATH=/SQLPATH1:/SQLPATH2 strace -e trace=file sqlplus -s /nolog @ script

This traces all system calls with a file name:


access("/ORACLE_PATH1/script", F_OK)    = -1 ENOENT (No such file or directory)
access("/ORACLE_PATH2/script", F_OK)    = -1 ENOENT (No such file or directory)
access("/ORACLE_PATH1/script.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/ORACLE_PATH2/script.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/ORACLE_PATH1/script", F_OK)    = -1 ENOENT (No such file or directory)
access("/ORACLE_PATH2/script", F_OK)    = -1 ENOENT (No such file or directory)
access("/ORACLE_PATH1/script.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/ORACLE_PATH2/script.sql", F_OK) = -1 ENOENT (No such file or directory)
stat("script.sql", 0x7fff01921400)      = -1 ENOENT (No such file or directory)
access("/ORACLE_PATH1/script", F_OK)    = -1 ENOENT (No such file or directory)
access("/ORACLE_PATH2/script", F_OK)    = -1 ENOENT (No such file or directory)
access("/ORACLE_PATH1/script.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/ORACLE_PATH2/script.sql", F_OK) = -1 ENOENT (No such file or directory)
stat("/SQLPATH1/script.sql", 0x7fff0191b430) = -1 ENOENT (No such file or directory)
stat("/SQLPATH2/script.sql", 0x7fff0191b430) = -1 ENOENT (No such file or directory)
SP2-0310: unable to open file "script.sql"
access("/ORACLE_PATH1/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/ORACLE_PATH2/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/ORACLE_PATH1/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/ORACLE_PATH2/login.sql", F_OK) = -1 ENOENT (No such file or directory)
stat("/u01/app/oracle/product/12.2.0/dbhome_1/sqlplus/admin/glogin.sql", {st_mode=S_IFREG|0644, st_size=342, ...}) = 0
access("/u01/app/oracle/product/12.2.0/dbhome_1/sqlplus/admin/glogin.sql", F_OK) = 0
statfs("/u01/app/oracle/product/12.2.0/dbhome_1/sqlplus/admin/glogin.sql", {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=6676009, f_bfree=2866104, f_bavail=2521221, f_files=1703936, f_ffree=1663469, f_fsid={-1731931108, 1057261682}, f_namelen=255, f_frsize=4096}) = 0
open("/u01/app/oracle/product/12.2.0/dbhome_1/sqlplus/admin/glogin.sql", O_RDONLY) = 9

This is very interesting. First, we see that the paths are searched multiple time, and I don’t know why. Second, when passing a name without extension (i.e without dot in the name) the exact name is used first for ORACLE_PATH, but lookup in the current directory and in SQLPATH automatically adds ‘.sql’. The system calls are also different: ORACLE_PATH has no stat() call before access(), which is different with current directory and SQLPATH. Finally, login.sql is read from ORACLE_PATH only and glogin.sql from ORACLE_HOME/sqlplus/admin.

Change occurred between 12.2.0.1 and 12.2.0.1

As a comparison, sqlplus 12.1.0.2 and even 12.2.0.1 DBaaS version (built in October) has the following additional calls to look for login.sql in current path and in SQLPATH:

stat("login.sql", 0x7fffc14d5490)       = -1 ENOENT (No such file or directory)
stat("/SQLPATH1/login.sql", 0x7fffc14cf4c0) = -1 ENOENT (No such file or directory)
stat("/SQLPATH2/login.sql", 0x7fffc14cf4c0) = -1 ENOENT (No such file or directory)

This has disappeared in 12.2.0.1 on-premises version (build in January).

So what?

Big thanks to the SQL Developer team who gave me the solution approximately 3 seconds after my tweet.

This behavior changed and, as far as I know, is not documented and the MOS note about it is not published. It makes sense, for security reason, to prevent running scripts in the current directory without explicitly allowing it. However, login.sql is often used for formatting only. It seems that SQLcl will implement this in a finer way, running only the formatting commands when it comes implicitly.

Be careful when moving to/from the Oracle Cloud and your premises because you don’t run exactly the same version…

Update 8th March, 2017

The MOS note has been published today: https://support.oracle.com/epmos/faces/DocContentDisplay?id=2241021.1 and mentions that this behavior can come in earlier releases through PSU.