Updated SQLcl: 2 New Commands, Bug Fixes, and 12cR2 Oh My!

thatjeffsmith SQL Developer 11 Comments

Tell Others About This Story:

You have a new version of SQLcl to download this morning.

Lots and lots of bug fixes – again, thanks everyone in the community for providing feedback so we can make continuous improvements.

A few things of note:

Two New Commands

They do pretty much what you’d expect.

Oracle SQLcl new commands, find and which

FIND and WHICH – hopefully will save you even more time now.

You’ll also notice something new on startup/connects if you’re using a ‘local’ LOGIN.SQL

oracle sqlcl login.sql warning

A security measure – SQLcl will ignore any SQL to be executed in a ‘local’ login.sql file.

What’s a ‘local’ LOGIN.SQL?

It’s a LOGIN.SQL file that we find in your current working directory when starting SQLcl and doing a CONNECT.

If we find one, we won’t run any SQL. But, we will let you run SET commands so you can get your prompt and DDL shaping settings all just the way you need for your script. You can override this security precaution by setting your SQL_PATH env var to include the ‘.’ directory – not something I’d recommend.

12cR2 Stuff

Yes, we support 12cR2. More than that, SQLcl is using a 12.2 JDBC driver by default.

12.2 AND Cloud ready. Go build stuff!

And one of the things you can do in 12.2 is of course get more descriptive TABLE and COLUMN names.

Our insight feature supports that, as well.

I hope you can come up with better names than I.

Just Installed Oracle Database 12c Release 2?

Good news – SQLcl is already there!

Of course, it’s already out of date with today’s update.

We’re able to update SQLcl and SQL Developer MUCH more frequently on OTN…not so much with core database and client distributions. It’ll be there if you need it, but you might want to check OTN for the latest updates and bug fixes.

Tell Others About This Story:

Comments 11

  1. Danger Will Robinson! Danger! (add waving-arms.gif here)
    We recently installed the ‘full’ 12.2.0 client and noticed that the $ORACLE_HOME/bin/sql file is a script that CHANGES DIRECTORY to $ORACLE_HOME/sqldeveloper/sqlcl/bin and then executes sql!
    so …
    $ pwd
    /home/oracle
    $ which sql
    /u01/app/oracle/product/12.2.0/client_1/bin/sql
    $ sql ….login credentials…
    SQL> version
    SQLcl version: 12.2.0.1.0
    SQL> pwd
    /u01/app/oracle/product/12.2.0/client_1/sqldeveloper/sqlcl/bin/
    SQL> !pwd
    /u01/app/oracle/product/12.2.0/client_1/sqldeveloper/sqlcl/bin

    …. yikes! … This is not the pwd you’re looking for. (add jedi-mind-trick.gif here)

  2. 1. How do I get rid of this message:
    log4j:WARN No appenders could be found for logger (org.apache.http.client.protocol.RequestAddCookies).
    log4j:WARN Please initialize the log4j system properly.
    log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.

    2. How do I set SQL_PATH (or is it SQLPATH) – none of these worked:
    set sqlpath=.
    set sqlpath=.\
    set sqlpath=./
    set sql_path=.
    set sql_path=.\
    set sql_path=./
    set sqlpath=%CD%
    set sqlpath=%CD%\
    set sqlpath=%CD%/
    set sql_path=%CD%
    set sql_path=%CD%\
    set sql_path=%CD%/

    After all of these, I still get this:
    login.sql found in the CWD. DB access is restricted for login.sql.
    Adjust the SQLPATH to include the path to enable full functionality.

    Thx

  3. using SQLCI 4.2 we are unable to connect using TNSNAMES, but connection URL works.

    it that connect using TNSNAMES got depreciated.

    C:\Users\179818>sql /nolog
    Mar 17, 2017 10:56:14 AM oracle.dbtools.raptor.console.MultiLineHistory load
    SEVERE: Start of root element expected.

    SQLcl: Release 4.2.0 Production on Fri Mar 17 10:56:14 2017

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

    idle> conn demo/[email protected]
    USER = demo
    URL = jdbc:oracle:oci8:@ora11g
    Error Message = Incompatible version of libocijdbc[Jdbc:122010, Jdbc-OCI:121020
    USER = demo
    URL = jdbc:oracle:thin:@ora11g
    Error Message = IO Error: Unknown host specified
    USER = demo
    URL = jdbc:oracle:oci8:@ora11g
    Error Message = Incompatible version of libocijdbc[Jdbc:122010, Jdbc-OCI:121020
    USER = demo
    URL = jdbc:oracle:thin:@ora11g
    Error Message = IO Error: Unknown host specified
    USER = demo
    URL = jdbc:oracle:thin:@ora11g:1521/ora11g
    Error Message = IO Error: Unknown host specified

    Warning: You are no longer connected to ORACLE.
    idle> conn demo/[email protected]
    USER = demo
    URL = jdbc:oracle:oci8:@ora12c
    Error Message = Incompatible version of libocijdbc[Jdbc:122010, Jdbc-OCI:121020
    USER = demo
    URL = jdbc:oracle:thin:@ora12c
    Error Message = IO Error: Unknown host specified
    USER = demo
    URL = jdbc:oracle:oci8:@ora12c
    Error Message = Incompatible version of libocijdbc[Jdbc:122010, Jdbc-OCI:121020
    USER = demo
    URL = jdbc:oracle:thin:@ora12c
    Error Message = IO Error: Unknown host specified
    USER = demo
    URL = jdbc:oracle:thin:@ora12c:1521/ora12c
    Error Message = IO Error: Unknown host specified

    Warning: You are no longer connected to ORACLE.
    idle> conn demo/[email protected]/ORA11g
    Connected.
    login.sql found in the CWD. DB access is restricted for login.sql.
    Adjust the SQL_PATH to include the path to enable full functionality.

    idle> conn demo/[email protected]/ORA12c
    Connected.
    login.sql found in the CWD. DB access is restricted for login.sql.
    Adjust the SQL_PATH to include the path to enable full functionality.

    idle>

    1. thatjeffsmith Post
      Author
      1. SQL> show tns
        TNS Lookup locations
        ——————–
        1. USER Home dir
        C:\Users\179818

        Location used:
        ————-
        D:\app\Vnameit\product\11.2.0\dbhome_1\network\admin

        Available TNS Entries
        ———————
        SQL>

        1. thatjeffsmith Post
          Author
          1. When i did TNSPING – it refers to 12.2 home

            C:\Users\179818>tnsping ora12c

            TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 – Production on 21-MAR-2017 19:50:35

            Copyright (c) 1997, 2016, Oracle. All rights reserved.

            Used parameter files:
            D:\app\Vnameit\virtual\product\12.2.0\dbhome_1\network\admin\sqlnet.ora

            Used TNSNAMES adapter to resolve the alias
            Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LT035221)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA12C)))
            OK (0 msec)

            when i connect using SQLCL it refers to my 11g (11.2) home

            C:\Users\179818>sql /nolog

            SQLcl: Release 4.2.0 Production on Tue Mar 21 19:50:43 2017

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

            Username? (”?) demo/[email protected]
            Connected.
            login.sql found in the CWD. DB access is restricted for login.sql.
            Adjust the SQL_PATH to include the path to enable full functionality.

            idle> show tns
            TNS Lookup locations
            ——————–
            1. USER Home dir
            C:\Users\179818

            Location used:
            ————-
            D:\app\Vnameit\product\11.2.0\dbhome_1\network\admin

            Available TNS Entries
            ———————

            not sure why it is.

          2. thatjeffsmith Post
            Author
  4. I love the idea of where/which commands for SQLCL

    i have my own version that use a HOST command to run a perl script to generate similar results.

    one thing mine does is allow me to put usage/description information in my .sql script files (as pod comments) that will then also be displayed to screen. (think NAME and SYNONPSIS sections of a man page)

    if something like that was included (maybe via options or a new command) i would be thoroughly impressed and thankful.

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *