DevOps, CI/CD and Automation

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

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

I'm confused why you run sqlplus at all  anymore

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.

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

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')

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

java@myhostname (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 )

This works on Windows...

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

And...

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

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')

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

java@oquin2 (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