Oracle SQLcl 22.1 is now available & featuring Data Guard!

SQLcl gets quarterly updates and it’s again that time of year!

DOWNLOAD NOW — this is the ‘latest’ link, you can permanently bookmark this.

Don’t pass go, don’t use Java 8.

Java 1.8 or Java 8 has been around since 2014. We now have long term support releases 11 & 17 as well. Starting with the 22.1 release cycle, Oracle Database Tools (ORDS, SQLcl, SQL Developer, Data Modeler) will no longer support Java 8.

Officially we will support Oracle Java 11 and 17. That means we build and test for those versions of the Oracle Java runtime and development kits.

It’s easier to tell SQLcl where to find Java now on Windows.

On Windows we’ll inspect the JAVA_HOME OS environment variable.

Java error SQLcl we require Java 11 or 17, Java 8 won't work and you will get A JNI error has occurred if you try. — If you’re using JAVA_HOME, make sure it’s set to at least Java 11.

If you continue to try to run with Java 8, you’ll see a variation of:

Error: A JNI error has occurred, please check your installation and try again
Exception in thread "main" java.lang.UnsupportedClassVersionError: oracle/dbtools/raptor/scriptrunner ... Java Runtime only recognizes class file versions up to 52.0

If you don’t have JAVA_HOME set, we’ll default to the Registry (on Windows) to tell us where it is.

New Feature: Data Guard commands for the Broker

If you’re on 19c or higher, the Data Guard Broker is now available to be managed via it’s PL/SQL API. And since there’s a PL/SQL API, that means we (Oracle, in this case, the Data Guard team), can build a SQLcl command for it.

dg help

Yes, you can initiate a Switchover. The database does all the work, we just kick things off via the PL/SQL API.

New Feature: SQL Performance Health Checks

You may remember from a previous episode that we added the ability to scan your code for SQL Injection Vulnerabilities.

With 22.1, we now also check for some problematic SQL techniques that could negatively impact performance.

You can turn both of these checks on by simply:

set codescan on

The rules are documented in the SQLcl help.

You can find the dozen or so rules/checks in the SQLcl docs, in the SQL Performance Troubleshooting Appendix.

Don’t like the rules? Don’t want to see them? Simply don’t enable this SQLcl setting. Curious if you’re doing something that’s less than optimal? Turn it on and see what happens.

Enhancement: Data Pump (parallel, encryption, filters)

Security, performance, and flexibility. That’s what’s been enhanced for 22.1

 dp export -en DATA_ONLY -excludeexpr "IN ('GRANT','INDEX','TRIGGER')" -ne

So we’re going to:

so a schema export for my session user (HR)
automatically write the dmp to the default data pump directory
encrypt the data
exclude GRANTS, INDEXES, and TRIGGERs in my schema
just generate the PL/SQL vs actually creating the job (-ne)

Let’s see what happens when I ask for encryption and a filter.

If we scroll down the code block, I can see things like –

BEGIN
    dbms_datapump.set_parameter(handle => h1,
                                  name => 'ENCRYPTION',
                                 value => :ENCRYPTION);
    EXCEPTION when success_with_info THEN
              errorlog:=errorlog||success_with_info_warning_text;
END;

The one command basically turns into 135 lines of PL/SQL to do my schema export.

Now, as we add flexibility and features…we add complexity to the commands. Remember, if you need help in the form of