login.sql, SQLPROMPT and worksheets

SQLDeveloper has had support for a login.sql for several releases now.  You can set this in the preferences at

Tools -> Prefernces -> Database

You can set your login.sql here.

Now, when SQL*Plus starts up, it looks for a global login script called glogin.sql in the $ORACLE_HOME/sqlplus/admin directory. If found, this script will be executed.
Thereafter, SQL*Plus will try to find a local login script called login.sql in the directory where you start sqlplus from, alternatively the directories listed in the SQLPATH environment variable. When found, sqlplus will execute it.  Here's my login.sql for SQL*Plus

define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name
  from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '

and when I login to sqlplus, I get this.

SQL*Plus: Release 11.2.0.2.0 Beta on Mon Nov 21 11:05:58 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta


GLOBAL_NAME
-----------------------------------------------------------------

barry@XE

barry@XE>

Obviously, in SQLDeveloper, this won't mean anything as prompts are not there, however, the variables you set and the column formats, titles, pagesizes etc, will be preserved.

For example, in our login2.sql  list in the preferences above, we set a couple of column settings and for fun, lets set the prompt variable too.

As shown above, we now have a login.sql defined in the preferences. When we make a connection, the login.sql will be run and any settings will be applied to the database.  We will also hold onto any SQL*Plus variables defined so they can be used in any worksheet that is started using this connection.

When you connect, you get the new worksheet, and the sql that was run produces any output in the messages.log.  The reason for this is you can connect to the database without spawning a worksheet and this lets you know of any output from that login script we put together earlier.

Now, we are connected, any worksheet created on that connection will have the context of the original script.

Lastly, you can also set your worksheet name to be a substitution variable as well

set worksheetname &gname

which will swap you default worksheet name to your connection credentials shown above.