SQLcl 23.1 Is Out! Here’s Everything You Need To Know & More

SQLcl and Liquibase
Written By Zachary Talke

Summary

SQLcl connections have been made easier for things like Radius, Kerberos, SocksProxy. SQLcl Liquibase has been improved to be even better for APEX developers.

 

The Cliff Notes

  • You can get the download HERE

  • The connect command for connecting to your database with SQLcl has been beefed up

    • EZCONNECT has been expanded upon with its connection options

    • The documentation when running help connect has been enriched

  • SQLcl Liquibase

    • liquibase generate-apex-object has 5 new parameters added to it:

      • -fail-on-error | foe [Default:false] {true|false}

      • -runalways | -ra [Default:true] {true|false}

      • -split | -sp {true |false}

      • -contexts | -co

      • -labels | -la

    • More detail has been added to the apex_install.xml changelog file generated by liquibase generate-apex-object

    • The liquibase update command has some new APEX override parameters:

      • -override-app-alias | -ovaa (Override value to be used for the APEX application alias)

      • -override-app-id | ovai (Override value to be used for the APEX application id)

      • -override-app-schema (Override value to be used for the APEX workspace schema)

      • -override-app-workspace | -ovaw (Override value to be used for the APEX application workspace)

  • SQLcl APEX Export

    • Additional apex export commands have been created that break down the original apex export command into components for finer tuned control:

      • export-all-applications | exaa  (Export all applications in a workspace)

      • export-all-workspaces | exaw (Export all workspaces without there applications, structure only)

      • export-application | exa (Export all or the parts of an application)

      • export-components | excom (Export specific components within an application)

      • export-feedback | exf (Export workspace feedback)

      • export-instance | exi (Export all applications in all workspaces that are not hidden)

      • export-static-files | exsf (Export static files in a workspace)

      • export-workspace | exw (Export workspace without the applications, structure only)

  • Bug Fixes

  • Other Improvements

    • The SQLcl load command has been updated to support Gzip, zip, and gz.

The Details

Connect Command

We’ve beefed up the connect command and added new option tweaking for connecting with EZCONNECT.

 

With SQLcl you can connect to your database by doing either:

  • sql /nolog (to connect to SQLcl without connecting to a database) then connect username/password@connect_identifier to connect to your Oracle Database of choice

  • Directly connect to SQLcl and your database at the same time with sql username/password@connect_identifier

 

Traditionally there are 3 ways to go about the connect_identifier component:

  • EZCONNECT

    • A simple and fast way to connect without needing to reference a tnsnames.ora file for connection info

    • Format is simply host_name:port_number/service_name

      • Ex: localhost:1521/orclpdb

  • TNS Alias

    • Think of it like the nickname you set for your TNS connection description (see directly below for an example image)

      • Ex: ORCLPDB

      • Note: you can set the TNS Alias to whatever you want, the fact that I made my name match my EZCONNECT service_name is just a convenience

  • TNS Description

    • The actual information for connecting that you store in tnsnames.ora.

    • When you use TNS Alias instead of TNS Description, the TNS Alias “nickname” is just an identifier for pointing to this information

      • Ex: (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orclpdb)

In this example from a tnsnames.ora file you can see the TNS Alias ORCLPDB and the TNS Description it points to.

The easiest way to get a feel for what’s new with the EZCONNECT option expansion and take a look at the details is the help connect command:

Compare this to the help connect from SQLcl 22.4 and you have a lot more option fine tuning:

As a heads up, if you want to take advantage of these options, log-in through sql /nolog then use the connect command with the options.

  • These options aren’t compatible with the sql log-in command.

    • If there is interest perhaps we can incorporate that in a future update :)

 

Liquibase Enhancements

For our Liquibase feature which automates making changes to your database, we’ve made some updates to working with APEX objects.

  • If you aren’t familiar with SQLcl Liquibase, you can learn about it in 6 minutes with this post HERE

  • Oracle Application Express (APEX) is Oracle’s low code development platform for easily building applications.

    • With SQLcl Liquibase not only can you automatically save to files and deploy your standard database objects (such as tables, procedures, constraints), but you can also do so with APEX objects which are additionally stored in the Oracle Database.

 

For the liquibase generate-apex-object command, which is used to capture an APEX object and save it to a Liquibase changelog file, a couple new parameters have been added:

  • -fail-on-error | foe [Default:false] {true|false}

    • Creates a fail-on-error attribute in the changelog file generated.

    • When running the liquibase update command to recreate or update the APEX object in the changelog, if an error is detected in the changelog the update will fail.

  • -runalways | -ra [Default:true] {true|false}

    • Creates a runalways attribute in the changelog file generated.

    • If a changeset (the actual set of deployment instructions in a changelog) has already been run, the runalways parameter determines whether the liquibase update will run the changeset again (true) or not (false).

    • An example of when you would use this attribute is if you need to repeat a changeset action every update deployment.

    • If there is no runalways attribute or it is set to false, Liquibase will skip the already deployed changesets.

  • -split | -sp {true |false}

    • Neatly organizes your generated files into structured folders rather than dumping them all side by side in the working directory

  • -contexts | -co

    • Creates a contexts attribute in the changelog file generated.

    • Contexts are a way for you to create identifiers to control whether a changeset is applied or not when the liquibase update command is run.

    • Contexts and labels are very similar and can be used identically in simple use cases. Where they differ is what “user” has the power to filter designations made.

      • With contexts you can specify a logical expression within the changeset. You can set this changeset attribute when you create the changelog

        • Ex: liquibase generate-apex-object -applicationid 100 -contexts “test AND prod”

      • Then to make sure the changeset runs with the liquibase update command you would specify liquibase update -changelog-file controller.xml -contexts test,production

  • -labels | -la

    • Creates a labels attribute in the changelog file generated.

    • Labels are a way for you to create identifiers to control whether a changeset is applied or not when the liquibase update command is run.

    • Contexts and labels are very similar and can be used identically in simple use cases. Where they differ is what “user” has the power to filter designations made.

    • With labels the logical expression (if you choose to use one) is specified by the user making deployments.

      • So you set the labels to be in your changeset:

        • Ex: liquibase generate-apex-object -applicationid 100 -labels test,prod

      • Then you can specify the the logical expression in the update command:

        • liquibase update -changelog-file controller.xml -labels “test AND prod”

 

More detail has been added to apex_install.xml which is your APEX changelog file generated when you run the liquibase generate-apex-object command:

  • Additional details have been added to show if overrides were used

  • A new custom runApexScript option has been added

 

The liquibase update command, which is used to run your changelog files and automatically create the database objects specified, has been updated with some new parameters:

  • -override-app-alias | -ovaa (Override value to be used for the APEX application alias)

  • -override-app-id | ovai (Override value to be used for the APEX application id)

  • -override-app-schema (Override value to be used for the APEX workspace schema)

  • -override-app-workspace | -ovaw (Override value to be used for the APEX application workspace)

 

These parameters add extra control to how the APEX application is going to be imported.

 

APEX Export Command Updates

Outside the Liquibase feature, we have additionally updated the APEX command category in SQLcl.

Previously there was only one apex export command:

Along with still providing the original apex export command, these new export commands break down the original command into separate components allowing for greater flexibility and control.

Bug Fixes

Here is a list of the issues fixed in 23.1:

  • 35164605    INDEX OUT OF BOUNDS WHEN HIGHLIGHTING IS ON AND RUNNING JUST A COMMENT

  • 35068715    LB GEO ADDS BLANK LINE TO TRIGGER TEXT

  • 35042293    LIQUIBASE -GRANT AND -FILTER OPTION WHEN USED TOGETHER RETURNS ERROR

  • 35041864    LIQUIBASE FAILS TO PROCESS LARGE VIEWS

  • 34936524    "ORA-00001: UNIQUE CONSTRAINT VIOLATED" ERROR FOR LB GENERATE-SCHEMA -SYNONYMS;

  • 35037479    SEARCH/REPLACE TYPE OF SQL*PLUS COMMAND USING SQLCL DOES NOT WORK

  • 34680656    SQLCL 22.3 HAS \ INSTEAD OF / IN CONTROLLER.XML ON WINDOWS AS COMPARED TO LINUX/UNIX.

  • 34671379    ERROR: "PLS-00201: IDENTIFIER 'IDENTIFIER_NAME' MUST BE DECLARED" WHEN USING "LB UPDATE" COMMAND

  • 35029218    LB GENERATE-APEX-OBJECT COMMAND DOES NOT SUPPORT -SPLIT PARAMETER [BUT THIS WORKED IN 22.2]

  • 34671379    ERROR: "PLS-00201: IDENTIFIER 'IDENTIFIER_NAME' MUST BE DECLARED" WHEN USING "LB UPDATE" COMMAND

 

Other Improvements

The SQLcl load command which allows you to load data from a CSV file into your Oracle Database table, has been updated to support Gzip, zip, and gz.

Zachary Talke
Previous

Desiderata
Next

Why I’m Starting Talke Tech…