SQLcl 22.3 was released while I was on vacation…how dare they!

Download SQLcl now.

This blog is coming a few days later than normal, you have my sincerest apologies.

The Highlights

For bugs the big ones would be we’re now able to do APEX exports via the APEX command and imports via Liquibase changeSets.

There’s additionally a bunch of parser/insight bugs fixed, which will also go upstream to SQL Developer Web.

New Command Interface

  • Easier to read help pages
  • Standardized parameters/options
  • Easy access to Examples

<command> help <option> -ex

So for say the DataPump command, I can do this –

dp hep import -ex

-ex is shorthand for -example

If I ask for help just on ‘dp’ itself, I get this –

The highlighting is new too.

These changes were implemented on a few command including datapump, migrateadvisor, and liquibase.

Liquibase

This is where most of the work for the last 3 months went. The changes are pretty radical, at least compared to what we offered before.

  • upgraded to Liquibase version 4.15
  • renamed our command and options to match Liquibase’ commands
  • added support for most of the community edition commands and options
  • made it easy/possible to take SQLcl JARs to your existing Liquibase installs/environments
  • fixed a TON of bugs

Yes, I’ll have many more blog posts, videos, and even hands-on labs to discuss Liquibase support in SQLcl, but in the meantime, here’s some slides to chew on.

Before you freak out, yes, we changed up the commands (again), but they’re backwards compatible. We’ll honor the old style, but print a gentle reminder it’s deprecated and you need to update your scripts.

What we have, in a nutshell.

SQLcl’s Liquibase Docs got a major update as well

You probably want to start here, the supported commands.

SQLcl 22.3 Liquibase Docs

It’s a lot.
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

12 Comments

  1. Daniel Gonzalez Reply

    With the new release, I’m also receiving a
    OCI connection specification must start with ‘ocid1.’
    for
    sql.exe -oci /@
    something that used to work on 22, as Laurent Schneider reported

  2. Hi
    I am a bit confused with OCI and OCI. Is it a driver or a Cloud thing?
    In sql cl 22.2 it used to be a driver
    /opt/sqldeveloper/sqldeveloper/bin/sql -oci scott/tiger@orcl

    Now in 23.3 it seems to be cloud-related
    $ sql -oci scott/tiger@orcl
    SQLcl: Release 23.3 Production on Mon Oct 23 10:34:21 2023
    Copyright (c) 1982, 2023, Oracle. All rights reserved.
    OCI connection specification must start with ‘ocid1.’

    Should I open a SR?

    Thanks

    • sql -oci scott/tiger@orcl

      used to work in 22.2
      now in 23.3 it generates an error.

      Does -oci relate to the driver or to the cloud?

    • 22.x : sql -oci -l /@orcl
      SQLcl: Release 22.4 Production on Mon Nov 06 14:19:09 2023
      Copyright (c) 1982, 2023, Oracle. All rights reserved.
      Last Successful login time: Mon Nov 06 2023 14:19:10 +01:00
      Connected to:
      Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
      Version 19.21.0.0.0

      23.x : sql -oci -l /@orcl
      SQLcl: Release 23.3 Production on Mon Nov 06 14:23:16 2023
      Copyright (c) 1982, 2023, Oracle. All rights reserved.
      OCI connection specification must start with ‘ocid1.’

      Without the -oci option, ldap+kerberos don’t work

      MOS: SR 3-34793094981

    • Hi Jeff

      I figured out by reading the doc that -oci has a new meaning.

      I also figured how to force oci connection in 23.3

      sql scott/tiger@jdbc:oracle:oci8:@orcl

      Then, I got a message that my instant client version does not match.
      $ sql scott/tiger@jdbc:oracle:oci8:@orcl
      Error Message = Incompatible version of libocijdbc[Jdbc:211100230512, Jdbc-OCI:2111000

      My instant client version is 21.11.0.0.0. But after installing the full 21.11 client it works.

      Thanks
      Laurent

    • I’m chasing your bug to get a fix for 23.4, you shouldn’t have to use a JDBC URL, but that’s probably your best workaround at the moment.

    • Jeff, do I need to install sqlcl 19 to use instantclient 19? The latest version of instantclient for MacOS right now seems to be v19 (still x86), but sqlcl seems to be looking for ocijdbc21. And just to confirm, do we still need to use OCI if we want to use ldap.ora for sqlcl?

      % sql database_username_here@jdbc:oracle:oci8:@database_name_here

      SQLcl: Release 23.3 Production on Tue Jan 02 11:29:20 2024

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

      Password? (**********?) *********
      USER = database_username_here
      URL = jdbc:oracle:oci8:@database_name_here
      Error Message = no ocijdbc21 in java.library.path: /Users/q713285/Library/Java/Extensions:/Library/Java/Extensions:/Network/Library/Java/Extensions:/System/Library/Java/Extensions:/usr/lib/java:.

    • No, you would need a 21c client, but there isn’t one for a Mac.

      But, you shouldn’t need a thick connection for using ldap in general, you could do this
      jdbc:oracle:thin:@ldap://oid:5000/mydb1,cn=OracleContext,dc=myco,dc=com

  3. Hi Jeff,

    I am exploring the possibilities this new version of SQLcl/Liquibase following the example flow as shown on slide 19 of the ppt.

    My subject database schema contains ref-contraints and I run into ORA-02275 errrors when I load the the controller.xml into the new environment using the LB -UPDATE command when the ref_contraint xml’s are processed. The ref-constraints are already generated as part of the create table xml.

    I have tried to solve this by tweaking the DDL generation parameters, but so far without success.

    I could edit the controller.xml file and remove the ref_constraints references, but I presume there must be a more “sophisticated” solution for avoiding these errors.

    Could you give me some hints how to avoid these errors?

    Thnx in advance for your help!

    • Yeah that is a bug that we fixed in 22.3.1
      Bug 34690393 - LB GENERATES DUPLICATE FOREIGN KEY XML FILE ON GENERATE-SCHEMA AND FAILS TO UPDATE LATER.

Write A Comment