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.

If you’re using JAVA_HOME, make sure it’s set to at least Java 11.

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
Switchover, really? YES.

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

Image
Whiz bang, and proceed with caution.

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

dp help examples
You can do a LOT of damage with just a few keystrokes in SQLcl.

What about ORDS & SQLDev/SQLDev Data Modeler?

ORDS 22.1 will be released shortly.

The next scheduled updates for SQL Developer and SQL Developer Data Modeler will be version 22.2, due end of June.

thatjeffsmith
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.

Write A Comment

RSS
Follow by Email
LinkedIn
Share