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

Gunther Pippèrr

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

thatJeffSmith-Oracle

/nolog - there's no connection, so how could module be set?

On connect, SQLcl is set as module for the session.

Kris Rice-Oracle

I'm confused why you run sqlplus at all  anymore

Craig Elliott

For myself, I am using SQLcl, but sometimes I have to use SQL*Plus to connect using external authentication(RADIUS), since I have not figured out how to get SQLcl to work with RADIUS.  Maybe once InstantClient 12 is released this will work?

Macbook ENV vars used:

SQLPATH=/oracle/common_sql

SQLCL=/oracle/instantclient_11_2/sqlcl/bin

TNS_ADMIN=/oracle/instantclient_11_2/network/admin

ORACLE_HOME=/oracle/instantclient_11_2

JAVA_HOME=/Library/Java/JavaVirtualMachines/jdk1.8.0_45.jdk/Contents/Home

In my $SQLPATH/login.sql file, I have:

--********************

--  COMMON CALLS

--   Add any SQL*Plus commands here that are to be executed when a

--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command

-- Used by Trusted Oracle

COLUMN ROWLABEL FORMAT A15

-- Used for the SHOW ERRORS command

COLUMN LINE/COL FORMAT A8

COLUMN ERROR    FORMAT A65  WORD_WRAPPED

-- Used for the SHOW SGA command

COLUMN name_col_plus_show_sga FORMAT a24

COLUMN units_col_plus_show_sga FORMAT a15

-- Defaults for SHOW PARAMETERS

COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME

COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

-- Defaults for SHOW RECYCLEBIN

COLUMN origname_plus_show_recyc   FORMAT a16 HEADING 'ORIGINAL NAME'

COLUMN objectname_plus_show_recyc FORMAT a30 HEADING 'RECYCLEBIN NAME'

COLUMN objtype_plus_show_recyc    FORMAT a12 HEADING 'OBJECT TYPE'

COLUMN droptime_plus_show_recyc   FORMAT a19 HEADING 'DROP TIME'

-- Defaults for SET AUTOTRACE EXPLAIN report

-- These column definitions are only used when SQL*Plus

-- is connected to Oracle 9.2 or earlier.

COLUMN id_plus_exp FORMAT 990 HEADING i

COLUMN parent_id_plus_exp FORMAT 990 HEADING p

COLUMN plan_plus_exp FORMAT a60

COLUMN object_node_plus_exp FORMAT a8

COLUMN other_tag_plus_exp FORMAT a29

COLUMN other_plus_exp FORMAT a44

-- Default for XQUERY

COLUMN result_plus_xquery HEADING 'Result Sequence'

--

--********************

--CUSTOM from here

--

column bytes format 999,999,999,999

column total_bytes format 999,999,999,999

col segment_name format a30

col table_name format a30

col segment_type format a20

col tablespace_name format a20

col file_name format a60

col db_link format a40

col host format a30

--********************

--SQL*Plus OR SQLcl determination from here

SET VERIFY OFF DEFINE ON ECHO OFF

COL SQLFILE FORMAT a200

COL SQLFILE NEW_VALUE sqllogin;

select decode (sys_context ('USERENV', 'MODULE'), 'SQLcl', 'sqlcl_login.sql', 'sqlplus_login.sql') sqlfile from dual

/

@/oracle/common_sql/&sqllogin

--********************

--  COMMON CALLS

--  spool file and prompts from here

COL SPOOLFILE FORMAT a200

COL SPOOLFILE NEW_VALUE SPOOL;

COL SYSTIMESTAMP FORMAT a38

SELECT '/oracle/spoolfiles/'||USER||'_'||GLOBAL_NAME||'_'||TO_CHAR(SYSDATE,'DD_MON_YYYY_HH24MISS')||'.lst' spoolfile FROM GLOBAL_NAME;

spool &spool

PROMPT ---*************************-------------------

PROMPT -- Note: SQLBLANKLINES is ON

PROMPT -- Note: ECHO is ON

PROMPT -- Note: DEFINE is OFF

PROMPT ---*************************-------------------

COL "TAKE NOTE" FORMAT a80

Select 'Scripts will run on ' || GLOBAL_NAME || ' as ' || USER "TAKE NOTE" FROM GLOBAL_NAME;

SELECT SYSTIMESTAMP "DATE/TIME" FROM DUAL;

PROMPT -----------------------------------------------

SET VERIFY ON DEFINE OFF ECHO ON

---

In my sqlcl_login.sql OR sqlplus_login.sql files, I have the SET commands that are specific to that application.

thatJeffSmith-Oracle

Ahhh, you're on a Mac...This should work on Windows at least.

Brad_the_Dazed

Maybe I'm resurrecting the dead, but SYS_CONTEXT('USERENV','MODULE') doesn't seem to get set for SQLcl initially ... only after set appinfo on

$ $HOME/sqlcl/bin/sql

SQLcl: Release 17.3.0 Production on Thu Oct 19 11:16:48 2017

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

...  user and password connection info ...

SQL> select sys_context ('USERENV', 'MODULE') from dual;

SYS_CONTEXT('USERENV','MODULE')

---------------------------------------------------------------------------------------------------------------------------

[email protected] (TNS V1-V3)

SQL> show java

Java Detail

-----------

java.home= /usr/java/jdk1.8.0_92/jre

java.vendor= Oracle Corporation

java.vendor.url= http://java.oracle.com/

java.version= 1.8.0_92

---------------------------------------------------------------------------------------------------------------------------

os.arch= amd64

os.name= Linux

SQL> set appinfo on

SQL> select sys_context ('USERENV', 'MODULE') from dual;

SYS_CONTEXT('USERENV','MODULE')

---------------------------------------------------------------------------------------------------------------------------

SQLcl

(oh, while researching, another site copied this forum ...

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

Gary Graham-Oracle

This works on Windows...

C:\Tools\sqlcl\r17_3_1\sqlcl\bin>sql scott/[email protected]: Release 17.3.0 Production on Thu Oct 19 11:31:51 2017Copyright (c) 1982, 2017, Oracle.  All rights reserved.Connected to:Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit ProductionSQL> show versionOracle SQLDeveloper Command-Line (SQLcl) version: 17.3.0.256.1818SQL> select sys_context('USERENV','MODULE') from dual;SYS_CONTEXT('USERENV','MODULE')--------------------------------------------------------------------------------SQLclSQL>

And...

SQL> show appinfoappinfo is OFF and set to "SQLcl"SQL>

Brad_the_Dazed

Such sweet o/s love ... or lack thereof ... the unix version needs to be burped ...  :-)

SQL> show appinfo

appinfo is OFF and set to "SQLcl"

SQL> select sys_context('USERENV','MODULE') from dual;

SYS_CONTEXT('USERENV','MODULE')

----------------------------------------------------------------------------------------------------------------------------------------------

[email protected] (TNS V1-V3)

SQL> set appinfo on

SQL> set appinfo off

SQL> select sys_context('USERENV','MODULE') from dual;

SYS_CONTEXT('USERENV','MODULE')

----------------------------------------------------------------------------------------------------------------------------------------------

SQLcl

SQL> show version

Oracle SQLDeveloper Command-Line (SQLcl) version: 17.3.0.256.1818