conditional login.sql in the SQLPATH

 

Further to my post on Self-inflicted unable to startup or shutdown database from OEM, I wondered if there might be a way to make the login.sql conditional. 

Background: 

The windows registry HKey_Local_Machine\ORACLE\Key_OraDb11g_home1 key has a Value field named SQLPATH 

When you run a file from insied sqlplus using @ or “start”, sqlplus will search any folders listed in sqlpath for the file. 

Also on starting sqlplus, it will check folders listed in sqlplus for any file named “login.sql”. If it finds a file, then this gets run before a prompt is presented. Also if any “connect /” is done in sqlplus, then a login.sql is again run. 

Problem: 

If my pc does a sqlplus/nolog then I do not want my login.sql to do anything (because there is no connection) 

Likewise once someone is connected, if they do a “connect sys/password as sysdba”, I do not want my login.sql to do anything (namely because this blocks OEM from starting/shutting down my database) 

Otherwise for any non-sys/non-system connect, I do want a login.sql to be run. 

Solution: 

In sqlplus there are some predefined variables. 

You can see these by typing “define”. The following is what you get after a sqlplus/nolog 

SQL> define
DEFINE _DATE        = "15-MAR-10" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "" (CHAR)
DEFINE _USER        = "" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000700" (CHAR)
DEFINE _EDITOR        = "Notepad" (CHAR) 

 

After connect as test, you get

SQL> define _user
DEFINE _USER           = "TEST" (CHAR)  

So I created the following files: 

“login.sql” with one line:

@start_&_user..sql

“start_.sql”  and “start_SYS.sql” and “start_SYSTEM.sql” are empty files 

start_TEST.sql with any number of lines to do something useful such as

@@init.sql

This works as planned for “sqlplus/nolog”, “connect sys…” and “connect test/…”
Of course, a big downside here is that for each username you need a file named “start_username.sql”

Danger: 

With any login.sql script you need to worry about someone adding lines to your start_SYS.sql such as

grant dba to bad_guy;