Skip to Main Content

SQLcl

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQLcl - login.sql - dedect if login.sql is called from SQLcL or from SQL*Plus

Gunther PippèrrMay 3 2016 — edited Oct 19 2017

Hello together,

How I can write a login.sql that can be used in both worlds, SQLcl and SQL*Plus?

For example a 100% compatible script with SQL*Plus that calls a SQLcl script only if it runs realy in a SQLcl  to load all the other nice new settings.

My first idea was to check in v$session the connected program, but you have not always the rights to read this information and /nolog will not work.

Something like this:

-- but the test should not throw error in SQL*Plus!

var INTERPRETER varchar2(10)

-- now check  if this is SQLcl over the MODULE Info

declare

v_version varchar2(20);

begin

    select sys_context ('USERENV', 'MODULE') into v_version from dual;

    if v_version not like 'java%' then

        :INTERPRETER :='SQLCL';

    else

        :INTERPRETER :='SQLPLUS';

    end if;

end;

/

define SCRIPTPART_CALL='call_no_script.sql'

col SCRIPTPART_COL new_val SCRIPTPART_CALL

select decode (:INTERPRETER, 'SQLCL', 'set_sqllc_login.sql', 'call_no_script.sql') as SCRIPTPART_COL from dual

/

undefine INTERPRETER

-- call

@@&&SCRIPTPART_CALL

The "/nolog" should work and no special rights should be needed.

It will be a nice feature if SQLcl set something more meaning full like "java.exe" in the MODULE information, SQLcl will be nice .-) !


Something like => "dbms_application_info.set_module ('SQLcl Connection', 'SQLcl')"

Any other ideas?

Thanks

Best Regards

Gunther

Comments

Post Details

Added on May 3 2016
7 comments
2,825 views