SQLcl Updated: Bug Fixes

thatjeffsmith SQL Developer 40 Comments

Tell Others About This Story:

There’s an update available for SQLcl on OTN, go get it now.

We’re closing bugs left & right, and we’re super close to 100% SQL*Plus support now.

As an example, we now support SET PAGESIZE 0…which comes in handy apparently if you want to spool a file w/o a leading blank line as described here.

Ta-da...

Ta-da…

Keep testing, keep sharing your feedback, and thanks!

Keep testing, keep sharing your feedback, and thanks!

Tell Others About This Story:

Comments 40

  1. Hi again

    Lokking at the options, I see this one:

    -noupdates Do not check update site for newer versions available.

    What if we are behind a proxy?

    what if this proxy is a windows authenticated (NTLM) one?

    I would advise the automatic update to be replaced by a -checkupdates switch that is simply opening a browser window and passing the current version to the checkupdates URL which would report whether an update us available or not.

    Thanks

  2. Hi
    There is little issue when running SQLcl as a non-admin user in Windows. I get this warning when running it:

    WARNING: Could not open/create prefs root node Software\JavaSoft\Prefs at root 0x80000002. Windows RegCreateKeyEx(…) returned error code 5.

    I traced its execution using sysinternals procmon and I found it’s trying to write to “HKLM\SOFTWARE\JavaSoft\Prefs” which is denied.

    It should rather write to HKCU\SOFTWARE\JavaSoft\Prefs.

    1. thatjeffsmith Post
      Author
  3. DESC is not working with overloaded functions. (It’s slow too, sorry!)

    E:\>REM @E:\sqlcl\bin\sql.bat [email protected] as sysdba

    SQLcl: Release 4.2.0.16.049.0842 RC on Wed Apr 13 11:59:00 2016

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

    Password? (**********?) **************
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
    With the Partitioning and Real Application Testing options

    SQL>
    SQL> desc dbms_workload_replay
    FUNCTION ADD_CAPTURE RETURNS NUMBER
    Argument Name Type In/Out Default?
    —————————— ———————– —— ——–
    CAPTURE_DIR_NAME VARCHAR2 IN
    CAPTURE_DIR_NAME VARCHAR2 IN
    START_DELAY_SECONDS NUMBER IN
    START_DELAY_SECONDS NUMBER IN
    STOP_REPLAY PL/SQL BOOLEAN IN
    STOP_REPLAY VARCHAR2 IN
    TAKE_BEGIN_SNAPSHOT VARCHAR2 IN
    TAKE_BEGIN_SNAPSHOT PL/SQL BOOLEAN IN

    E:\>sqlplus [email protected] as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 13 12:00:35 2016

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

    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
    With the Partitioning and Real Application Testing options

    [email protected](node:zzz):9009,435> desc dbms_workload_replay
    FUNCTION ADD_CAPTURE RETURNS NUMBER
    Argument Name Type In/Out Default?
    —————————— ———————– —— ——–
    CAPTURE_DIR_NAME VARCHAR2 IN
    START_DELAY_SECONDS NUMBER IN DEFAULT
    STOP_REPLAY BOOLEAN IN DEFAULT
    TAKE_BEGIN_SNAPSHOT BOOLEAN IN DEFAULT
    TAKE_END_SNAPSHOT BOOLEAN IN DEFAULT
    QUERY_ONLY BOOLEAN IN DEFAULT
    FUNCTION ADD_CAPTURE RETURNS NUMBER
    Argument Name Type In/Out Default?
    —————————— ———————– —— ——–
    CAPTURE_DIR_NAME VARCHAR2 IN
    START_DELAY_SECONDS NUMBER IN
    STOP_REPLAY VARCHAR2 IN
    TAKE_BEGIN_SNAPSHOT VARCHAR2 IN DEFAULT
    TAKE_END_SNAPSHOT VARCHAR2 IN DEFAULT
    QUERY_ONLY VARCHAR2 IN DEFAULT

    1. thatjeffsmith Post
      Author
      1. Hi Jeff, there’s only one CAPTURE_DIR_NAME, START_DELAY_SECONDS etc parameter per version of the function. In this case there are two overloaded versions, but it seems DESC in SQLcl is not breaking on the function name.

        Regards, JT

  4. I have used a more elaborate SQL prompt than the standard for a while. But it doesn’t work in the latest release – 4.2.0.16.049.0843.

    SET sqlprompt “&USERNAME’@'(&HOST_NAME:&INSTANCE_NAME):&SID,&SERIALno> ”

    Seems likely it’s the NEW_VALUE issue above.

  5. SQLcl is also distributed with Oracle SQL Developer (OSD). As OSD was not released when SQLcl got a new release, I’m assuming that OSD contains an older version of SQLcl. How come the “sql.exe” binary in OSD has a higher version number in it than the one contained in SQLcl release?

    1. thatjeffsmith Post
      Author

      the 3 digit dot number is the julian day of the calendar year, so since 413 came out in december, it’s higher than one that came out in February…not sure about other numbers w/o looking

  6. Jeff-
    Is there somewhere to look for a roadmap of what sqlplus features are planned to be supported in sqlcl and what features will never be supported.

    I’m wondering if sqlcl is ever going to behave exactly like sqlplus in regard to quitky things like not creating PL/SQL objects if the slash to run what’s in the buffer is forgotten.

  7. Hello,

    I have the following issue: first time I try repeat feature it throws and exception, second try in the same session it says “Nested REPEAT Commands will be skipped”

    $ sql [email protected]

    SQLcl: Release 4.2.0.16.049.0842 RC on ╤З╤В ╨▒╨╡╤А. 10 15:35:25 2016

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

    Password? (**********?) *************
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

    SQL> SET sqlformat ansiconsole
    SQL> SELECT systimestamp FROM dual;
    SYSTIMESTAMP
    10.03.2016 15:35:38,354232000 +02:00

    SQL> repeat 15 0.55
    схЁ. 10, 2016 3:35:42 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
    SEVERE: oracle.dbtools.raptor.newscriptrunner.ScriptParser.findEndOfSQLAndPLSQL(ScriptParser.java:952)
    java.lang.NullPointerException
    at oracle.dbtools.raptor.newscriptrunner.ScriptParser.findEndOfSQLAndPLSQL(ScriptParser.java:952)
    at oracle.dbtools.raptor.newscriptrunner.ScriptParser.identifyEndOfStatement(ScriptParser.java:1092)
    at oracle.dbtools.raptor.newscriptrunner.ScriptParser.next(ScriptParser.java:251)
    at oracle.dbtools.raptor.newscriptrunner.ScriptParser.parse(ScriptParser.java:134)
    at oracle.dbtools.raptor.scriptrunner.commands.RepeatSQLBufferCmd.printTop(RepeatSQLBufferCmd.java:112)
    at oracle.dbtools.raptor.scriptrunner.commands.RepeatSQLBufferCmd.handleEvent(RepeatSQLBufferCmd.java:71)
    at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:387)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:197)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:295)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:199)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:293)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:303)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:880)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:352)

    SQL>

    SQL> SET sqlformat ansiconsole

    SQL> SELECT systimestamp FROM dual
    SYSTIMESTAMP
    10.03.2016 15:35:56,826355000 +02:00

    SQL> repeat 15 0.55
    Nested REPEAT Commands will be skipped
    SQL>

    1. thatjeffsmith Post
      Author
  8. I tried to resolve my connection issue several different ways but what ended up resolving the problem was changing the order of the directories in my $PATH environment variable.

    I put $ORACLE_HOME/bin first – I have the SQLcl bin directory under the instant client path and the lib directory is under the instant client path as well. I set my ORACLE_HOME to

    /Users//oracle/product/instantclient_64/11.2.0.4.0 and my path now has $ORACLE_HOME/bin:$ORACLE_HOME/lib:$ORACLE_HOME:$PATH:.

    and with that order my connectivity issues have been resolved. My apologies if you spent any time on this. What is interesting that with the previous version of the SQLcl the order wasn’t an issue. In any case I’m happy to have my access to SQLcl utility again. I love this tool and the fact that it works under OS X.

    I look forward to the official release of the product. I enjoy reading your blog too. Great hints and tips and I hope the support of OS X continues.

    1. I figured out what why my connection wasn’t working. When I am in the office on the local network I would get the jdbc error. When I was out of the office connected to the VPN sqlcl would work. I figured out the problem was the Proxy Server was allowing the jdbc connection over the VPN when not on the local network but wasn’t when I attempted to connect on the local network.
      I added the subnets that the Oracle Databases are located on to the list of hosts/networks to exclude from the Proxy server and all is well now.

      My apologies for posting the previous messages and not figuring out that the Proxy server was to blame. When I saw the connection requests in the output from netted -m tcp on my machine I realized the problem right away.

  9. Jeff after I installed the latest SQLcl client I am unable to connect to any of my Oracle 12c databases. Before installing the latest update the client was working fine. I’m using a Macbook Pro running 10.11.4 and I’m getting the no ocijdbc12 in java.library.path error.

    Do you have any suggestions for I might do to fix this issue. Being able to use the command line utility in OS X was great. I tried to find an older version of the software but haven’t had any luck.

  10. Hi Jeff,
    Question about /*html*/ hint….
    Is it possible to modify the css style output, like modify the default css style to include my company’s style sheet?.

    Thanks

    1. thatjeffsmith Post
      Author
  11. A feature I rely on is explained on my site http://plsql.me/afiedt-buf-whats-with-that

    I set my editor and the name of the file I edit so I can have syntax highlighting and other features known to the editor based on file extension. And not overwriting code I’ve written in other sessions.

    The editor as set with
    define _editor=’vim -c colorscheme evening’
    throw the java exception

    java.io.IOException: Cannot run program “vi -c ‘colorschema eveing'” (in directory “/home/trudkin”): error=2, No such file or directory
    at java.lang.ProcessBuilder.start(ProcessBuilder.java:1041)
    at oracle.dbtools.raptor.scriptrunner.cmdline.editor.EditCommand.processExternalEditor(EditCommand.java:152)

    I can fix this with a shell script and calling that script.

    But I have not found a way to set the name of the file being edited.
    set editfile ‘somefile.sql’

    If there is a way to do this and dynamically it would go a long way to moving to SQLcl!

    1. thatjeffsmith Post
      Author
      1. my login.sql has

        define efile=sqltmp1.SQL

        -- now we make it unique to the session and user in /tmp for system cleanup
        column file_name new_value efile
        SELECT '/tmp/' || SYS_CONTEXT ('USERENV', 'SESSION_USER' )
        || SYS_CONTEXT ('USERENV', 'SESSIONID' )
        || '.sql' file_name from dual;
        set editf '&efile'

        I now look at the help HELP COLUMN and see

        COLUMN
        ——
        (Currently only NEW_V[ALUE] variable syntax is supported)

        So I read that as NEW_VALUE works. But I think this means NEW_VALUE won’t throw an error . And a post below with similar issues and a bug filed. So that is likely it.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
      1. Yes, that’s what I’m saying. I still see the query output on my screen.
        This is how I run the query
        /home/oracle/sqlcl/bin/sql -s harriague/[email protected] @/home/oracle/CYR/Scripts/service_level.sql 25.02.2016 25.02.2016 OSSRC3 22388 23018

        Thanks

        1. thatjeffsmith Post
          Author

          what do you see?

          -S Sets silent mode which suppresses the display of
          the SQLcl banner, prompts, and echoing of
          commands.

          running sql*plus in silent mode still shows query results.

          1. Commands are being echoed per my example below. It would be great if that could be fixed.

          2. thatjeffsmith Post
            Author
  12. Hi Jeff,

    Look. Using sqlplus:

    SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 29 08:32:23 2016

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

    Last Successful login time: Thu Feb 25 2016 07:46:27 +01:00

    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

    SQL>

    col xxx new_value xxx
    SQL>

    select dummy as xxx from dual;
    X

    X

    1 row selected.

    SQL>

    select ‘&xxx’ from dual;


    X

    1 row selected.

    SQL>

    SQL>

    SQL>

    def xxx
    DEFINE XXX = “X” (CHAR)
    SQL>

    Variable xxx is defined with ‘X’ value. And now Using sqlc:

    SQLcl: Release 4.2.0.16.049.0842 RC on lun feb 29 08:33:32 2016

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

    SQL> connect j/[email protected]_pdb1
    Conectado.
    SQL> col xxx new_value xxx
    SQL> select dummy as xxx from dual;

    X

    X

    SQL> select ‘&xxx’ from dual;
    Introduzca un valor para xxx:
    Antiguo:select ‘&xxx’ from dual
    Nuevo:select ” from dual


    SQL> def xxx
    SP2-0135: El símbolo XXX NO ESTÁ DEFINIDO
    SQL>

    So using sqlc no variable xxx is denfined.

    1. thatjeffsmith Post
      Author
  13. It appears that set echo off does not work. I would love this tool to work, but I need a clean csv file strictly with the header row and data. Please let us omit the sql statement and spool off commands.

    sql -s …/…

    set pagesize 0
    set echo off
    set feedback off
    set term off
    set sqlformat csv
    spool test2.csv
    select * from env_auth_user;
    “AUTH_AUID”,”AUTH_ROLE”,”DATE_CREATED”
    “…”,”HAZMAN”,30-NOV-11
    ….
    spool off
    exit

    cat test2.csv
    SQL> select * from env_auth_user;
    “AUTH_AUID”,”AUTH_ROLE”,”DATE_CREATED”
    “…”,”HAZMAN”,30-NOV-11

    SQL> spool off

    1. thatjeffsmith Post
      Author

      it works for me

      vi test_echo.sql
      set echo off
      spool echo_test.log
      select 1 from dual;
      select 2 from dual;
      spool off

      ./sql hr/oracle

      @echo_text.sql

      cat echo_test.log
      └─>more echo_test2.txt

      1
      ———-
      1

      2
      ———-
      2

      no queries…

      SET ECHO {ON | OFF}
      Controls whether or not to echo commands in a script that is executed with @, @@ or START. ON displays the commands on screen. OFF suppresses the display. ECHO does not affect the display of commands you enter interactively or redirect to SQL*Plus from the operating system.

      1. I am looking forward to the completion of this product as it is much needed for jobs and quick access things. I am having the sql statement “echo” problem from a shell script that is executed by cron (interactively executed for this demo). The SQL echo is not a issue with SQL*Plus (it is just a pain for data format reasons). Perhaps you have a newer version than I downloaded on Thursday (2/25 – 4.2.0.16.049.0842)?

        Here is sql(cl) vs sqlplus (one has csv format but extra stuff before and after, other has whitespace even with colsep “,”):

        sql -s /nolog <<EOF
        connect —/$1
        set linesize 500;
        set echo off;
        set termout off;
        set trimspool on;
        set pagesize 0;
        set wrap off;
        set feedback off;
        set sqlformat csv;
        set sqlprompt "";

        /* Gap Report #8 */
        spool equip_summary_sc.csv
        select bldg_num "MAXIMO_BLDG_NUM",
        round( 100 * (select count(*)
        from sde_larc.maximo_equipment eq1, maximo_equipment me2
        where eq1.asset_num = me2.asset_num
        and me2.bldg_num = me1.bldg_num) /
        (select count(*)
        from maximo_equipment me2
        where me2.bldg_num = me1.bldg_num)) "Filtered Completed %"
        from maximo_equipment me1
        where bldg_num is not null
        group by bldg_num
        order by 1;
        spool off;

        exit
        EOF
        #
        sqlplus -s /nolog <<EOF
        connect —/$1
        set linesize 500;
        set echo off;
        set termout off;
        set trimspool on;
        set pagesize 0;
        set colsep ",";
        set wrap off;
        set feedback off;
        set sqlprompt "";

        /* Gap Report #8 */
        spool equip_summary_sp.csv
        select bldg_num "MAXIMO_BLDG_NUM",
        round( 100 * (select count(*)
        from sde_larc.maximo_equipment eq1, maximo_equipment me2
        where eq1.asset_num = me2.asset_num
        and me2.bldg_num = me1.bldg_num) /
        (select count(*)
        from maximo_equipment me2
        where me2.bldg_num = me1.bldg_num)) "Filtered Completed %"
        from maximo_equipment me1
        where bldg_num is not null
        group by bldg_num
        order by 1;
        spool off;

        exit
        EOF

        more equip_summary_sc.csv
        select bldg_num "MAXIMO_BLDG_NUM",
        round( 100 * (select count(*)
        from sde_larc.maximo_equipment eq1, maximo_equipment me2
        where eq1.asset_num = me2.asset_num
        and me2.bldg_num = me1.bldg_num) /
        (select count(*)
        from maximo_equipment me2
        where me2.bldg_num = me1.bldg_num)) "Filtered Completed %"
        from maximo_equipment me1
        where bldg_num is not null
        group by bldg_num
        order by 1;
        "MAXIMO_BLDG_NUM","Filtered Completed %"
        "1236",64
        "1236A",89
        "1236B",93
        "1236C",71
        "1236D",75
        "2101",96
        spool off

        more equip_summary_sp.csv
        1236 , 64
        1236A , 89
        1236B , 93
        1236C , 71
        1236D , 75
        2101 , 96

  14. That’s correct as far as it goes but then after that if you try to use the variable it prompts you for the ampersand variable as below.

    SQL> select ‘&xxx’ from dual;
    Enter value for xxx

    You should get:

    SQL> select ‘&xxx’ from dual;

    old:select ‘&xxx’ from dual
    new:select ‘X’ from dual



    X
    SQL>

  15. Hi Jeff,

    It seems like I might be having problems similar to what Joaquin encountered after updating to the most recent version of SQLcl.

    Before the update (as well as in SQL*Plus), I would get:

    SQL> col xxx new_value xxx
    SQL> select dummy as xxx from dual;

    X

    X

    SQL> select ‘&xxx’ from dual;
    原值 1: select ‘&xxx’ from dual
    新值 1: select ‘X’ from dual



    X

    In the updated version of SQLcl (Release 4.2.0.16.049.0842 RC), however, I get the following:

    SQL> col xxx new_value xxx
    SQL> select dummy as xxx from dual;

    X

    X

    SQL> select ‘&xxx’ from dual;
    Enter value for xxx

    Does this have something to do with the SQLcl update or is it because of my setup?

  16. Hi Jeff,

    It seems that setting sqlplus variables with column values no longer works. Look:

    SQLcl: Release 4.2.0.16.049.0842 RC on mi├® feb 24 15:04:33 2016

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

    SQL> connect j/**@db12_pdb1
    Conectado.
    SQL> def
    DEFINE _DATE = “24/02/16” (CHAR)
    DEFINE _CONNECT_IDENTIFIER = “db12_pdb1” (CHAR)
    DEFINE _USER = “J” (CHAR)
    DEFINE _PRIVILEGE = “” (CHAR)
    DEFINE _SQLPLUS_RELEASE = 0402001649842 (NUMBER)
    DEFINE _EDITOR = “notepad” (CHAR)
    DEFINE _O_VERSION = “Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options” (CHAR)
    DEFINE _O_RELEASE = 1201000000 (NUMBER)
    DEFINE _PWD = “c:\sql” (CHAR)
    SQL> col xxx new_value xxx
    SQL> select dummy as xxx from dual;

    X

    X

    SQL> def
    DEFINE _DATE = “24/02/16” (CHAR)
    DEFINE _CONNECT_IDENTIFIER = “db12_pdb1” (CHAR)
    DEFINE _USER = “J” (CHAR)
    DEFINE _PRIVILEGE = “” (CHAR)
    DEFINE _SQLPLUS_RELEASE = 0402001649842 (NUMBER)
    DEFINE _EDITOR = “notepad” (CHAR)
    DEFINE _O_VERSION = “Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options” (CHAR)
    DEFINE _O_RELEASE = 1201000000 (NUMBER)
    DEFINE _PWD = “c:\sql” (CHAR)
    SQL>

    1. thatjeffsmith Post
      Author
      1. SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 09:00:45 2016

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

        Enter password:
        — SECURITY ALERT
        — This system is monitored for access by unauthorized users.

        — AUDIT ALERT:
        — Access and use of this system is audited.

        Connected to:
        Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
        With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
        Data Mining and Real Application Testing options

        > define x
        SP2-0135: symbol x is UNDEFINED
        > column x_column new_value x
        > select ‘test’ x_column from dual;

        X_CO
        —-
        test

        1 row selected.

        > define x
        DEFINE X = “test” (CHAR)

        SQLcl

        SQLcl: Release 4.2.0.16.049.0842 RC on Wed Mar 02 09:02:55 2016

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

        Username? (”?)
        Password? (**********?) *******
        Connected to:
        Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
        With the Partitioning, Real Application Clusters, Automatic Storage Management,
        OLAP,
        Data Mining and Real Application Testing options

        > define x
        SP2-0135: symbol X is UNDEFINED
        > column x_column new_value x
        > select ‘test’ x_column from dual;
        X_CO
        —-
        test

        1 rows selected.

        > define x
        SP2-0135: symbol X is UNDEFINED

Leave a Reply

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