SQLcl

This post shows how to craft a login.sql script, and have it work nicely regardless of whether a database session is started immediately or not (/nolog).

It might come as a surprise that SQLcl runs the login.sql script upon start, even when /nolog is being used. But actually, this is 100% expected behaviour (though it's not clearly stated in the documentation), given that SQL*Plus behaves in exactly the same way—and that is documented: see section 3.3 Starting SQL*Plus in SQL*Plus User's Guide.

The problem is, some session-initialization actions, e.g. alter session set nls_date_format="..." obviously cannot run successfully until a database session is started, hence running these without a database session, as happens when SQLcl is started with the /nolog option, can only result in annoying error messages.

Therefore, we have to turn to easy SQL*Plus tricks in order to split initialization actions (from our original login.sql script) into 2 separate parts:

• One which will always be run, and contains only commands which can run without a database session
• One which will be run only when a database session is being initialized.

There's more than 1 way to do that, but here's one possible solution.

1/ login.sql

The login.sql script contains the following:

@@login-common-noconnect

@@login-session-init

So it does nothing but run, in turn, the next 2 scripts: login-common-noconnect.sql, and login-session-init.sql, respectively.

Remark: both scripts are called using the @@ (double "at") syntax, hence they are supposed to be in the same directory as the login.sql script.

2/ login-common-noconnect.sql

This script, which must be placed in the same directory as login.sql, contains whatever SQLcl initialization commands which are deemed necessary, and which may safely be run without a database session. E.g:

set arraysize 200
set linesize 200
set pagesize 50
set trimspool on
set trimout on
set tab off

set long 10000000
set longchunksize 10000000

set history nofails
set history filter none

define _EDITOR="C:\path_to\favorite_text_editor.exe"

set sqlprompt "@|bg_black,fg_green,bold SQL>|@ "

Etc.

3/ login-session-init.sql

This script, which must be put in the same directory as login.sql, will check whether a database session has been started or not. If we have a database session, it will proceed by running the login-session.sql script; if not (/nolog) it will run the login-nolog.sql script.

define def_script_suffix = "nolog"

set termout off
set feedback off

column is_session noprint new_value def_script_suffix

whenever sqlerror continue none

select 'session' as is_session
  from dual;

set feedback on
set termout on

column is_session clear

@@login-&def_script_suffix

undefine def_script_suffix

4/ login-session.sql

This script, to be put in the same directory as login.sql, is where database session-initialization commands and statements will be put. E.g.:

alter session set
    nls_date_format='YYYY-MM-DD HH24:MI:SS'
    nls_timestamp_format='YYYY-MM-DD HH24:MI:SSXFF4'
    nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SSXFF4 TZH:TZM'
    nls_time_format='HH24:MI:SSXFF4'
    nls_time_tz_format='HH24:MI:SSXFF4 TZH:TZM'
    nls_territory='AMERICA'
    nls_numeric_characters='. '
;

set appinfo on

alter session set plsql_code_type = 'INTERPRETED';
alter session set plsql_optimize_level = 2;
alter session set plsql_debug = false; -- Deprecated: use plsql_optimize_level=1 to compile in debug mode
alter session set plsql_warnings = 'ENABLE:ALL,DISABLE:6009';
alter session set plscope_settings = "IDENTIFIERS:ALL, STATEMENTS:ALL";

Etc.

5/ login-nolog.sql

This script, to be put in the same directory as login.sql, contains the following:

prompt
prompt *** Not logged in so far.
prompt

Voilà !

Now you have basically 2 scripts for initialization commands/statements, either the login-common-noconnect.sql script or the login-session.sql script; the rest is pure SQL*Plus technicality.

(Remark: another way to distinguish between /nolog and the common case when we have a database session relies on the _USER substitution variable: it is set to "" (empty) when /nolog is used, and to the session username upon starting a database session; therefore, if we run the following:

@@login-&_USER

we'll call login-<username>.sql if a database session has been started, and login-.sql otherwise. The obvious inconvenient here is that a per-user login script must be created and maintained.

And further, due to this regression, at the time being this solution does not work very gracefully anymore in SQLcl 23.1.)

Best regards,