Forum Stats

  • 3,750,549 Users
  • 2,250,192 Discussions
  • 7,867,004 Comments

Discussions

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

Gunther Pippèrr
Gunther Pippèrr Member Posts: 35 Blue Ribbon
edited Oct 19, 2017 4:48PM in SQLcl

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

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,906 Employee
    edited May 4, 2016 11:06AM

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

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

  • Kris Rice-Oracle
    Kris Rice-Oracle Posts: 1,393 Employee
    edited May 4, 2016 7:49PM

    I'm confused why you run sqlplus at all  anymore

  • Craig Elliott
    Craig Elliott Member Posts: 21 Blue Ribbon
    edited May 5, 2016 2:20PM

    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
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,906 Employee
    edited May 5, 2016 2:26PM

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

  • Brad_the_Dazed
    Brad_the_Dazed Member Posts: 54
    edited Oct 19, 2017 11:40AM

    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
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Oct 19, 2017 2:52PM

    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
    Brad_the_Dazed Member Posts: 54
    edited Oct 19, 2017 4:48PM

    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