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.
/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
-- 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
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
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@xeSQLcl: 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>
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
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;
--------------------------------------------------------------------------------
SQL>
And...
SQL> show appinfoappinfo is OFF and set to "SQLcl"SQL>
SQL> show appinfo
appinfo is OFF and set to "SQLcl"
Such sweet o/s love ... or lack thereof ... the unix version needs to be burped ... :-)
----------------------------------------------------------------------------------------------------------------------------------------------
java@oquin2 (TNS V1-V3)
SQL> set appinfo off