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