Mostly Making oracle easy, mostly:
Search for:
In ORDS

ORDS & SQLcl 22.4 are now available for download

3 Mins Read
oracle sqlcl argument command for defining default values in sql scripts

“ORDS and SQLcl 22.4 now available” – some of my favorite words to utter as a product manager!

The actual download links –

Download SQLcl
Download ORDS

We do quarterly releases for our products, and it’s time to end the year with our 22.4 updates! ORDS 22.4 has already been submitted to our Oracle Cloud DevOps team, so you’ll see that show up for Oracle Autonomous Shared subscribers over the next few days as well.

So what’s new?

SQLcl 22.4

We have a new command, ARGUMENT.

Do you have SQL scripts that take in one or more inputs? What happens when you forget to supply one? Wouldn’t it be nice if you could code into your scripts either a PROMPT for the user to supply said argument OR supply default(s)?

That’s what we have now. Simply use the ARGUMENT command IN your SQL scripts.

Example: default argument values

If I call the script w/o supplying values for &1 or &2, then SQLcl will supply the defaults.

Example: prompting for value

This time my script knows to ask for a value on the 5th argument if none is supplied.

Other SQLcl items of note

Bug fixes, lots of them. Some insight/SQL completion bugs were addressed, but the vast majority were fixes for our LIQUIBASE support. Additionally, we updated the Liqubase library from 4.15 to 4.17.

See the release notes for bugs fixes of interest.

ORDS 22.4

There was a lot of work put into this release that end users/system administrators won’t necessarily see directly. And some more work that WILL BECOME evident later in 2023. That being said, most of what you’re getting in 22.4 are bug fixes.

Pools and routing patterns at start-up

I can see here that ORDS is servicing two different databases on / and orcl/

Some previous 404 responses will now result in more accurate 403 and 429s, which hopefully will be much less frustrating for API consumers.

SQL-based APIs and AutoREST Filtering & Sorting

One of my favorite ORDS features, just got a little bit of a boost. You can now SORT your responses, and provide input if you want the NULLs first or last.

So, give me a list of my employees, but sort them on COMMISSION_PCT descending, and put the NULLs on the last pages.

?q={“$orderby”: {“COMMISSION_PCT”:[“DESC”, “NULLS LAST”]}}

I “lied” about SQL Developer & Data Modeler 22.4

I previously mentioned we would have maintenance updates for these products this quarter. That was our intention and goal, but that’s evolved to 23.1. We’re forecasting those will be made available closer to the end of January.

In March we’ll have SQLcl and ORDS 23.1. And hopefully we’ll also have v1 of “SQL Developer Next!”

SQL Developer Next – VS Codium + Electron w/all the Oracle support you’ve come to expect.
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.

Related Posts

8 Comments

  1. Rene Jeruschkat Reply

    Hi Jeff,

    having trouble with # in filenames here.
    @dwh#acme#emp.syn

    sqlcl 22.4 works just fine.
    sql developer 22.2.1 does not.

    Its most likely due to the url feature and # being interpreted as anchor.
    It is possible to partially work around it by replacing # with %23.
    But that stops working as soon as there are multiple #.

    a) Is there another workaround that works for multiple #?
    b) Will the fix be included in sql developer 23.1

  2. ilanado Reply

    Hey Jeff.

    Quick question, I updated to sqlcl 22.4, for the moment I’m basically using it for Liquibase. Since this update every time I run lb genschema on the same folder I previously ran it I im getting a whole new set of files with a suffix 1 (EMP1.xls, controller1.xml, etc…)

    Is this a new functionality or how can I make work replacing the old files with the new changes?

    Thank you for you time!

    • thatjeffsmith Post Author

      The Liquibase framework is implemented in such a way we’re not really left with a choice here. It makes rollbacks very tenuous if files mentioned in controllers are no longer there…more of less. We’ve filed an issue with them so that we can achieve what you (and several others have mentioned). So it should get ‘fixed’ sometime this year.

  3. andreml Reply

    Hi Jeff,
    I need your help urgently…
    I have asked our IT to install a JAVA11/ 17 on the ORDS server in preparation for the upgrade to the current ORDS version, using Oracle’s sources because OpenJDK is not supported by Oracle.
    Now IT claims that this will incur (further) licensing costs …
    I am very sure that this is not true, because we have valid database license agreements.
    Can you give me a reliable source which disproves these false claims?
    Thanks in advance!
    Andre

    • thatjeffsmith Post Author

      Any Oracle product requiring Java does not require a Java license. That’s written down/policy.

      So they can use OpenJDK, but know that if they run into problems, we’ll ask them to switch, and won’t be in a supported configuration.

      Also, Java 17 removed the license requirement in general, so it’s not even a relevant issue now.
      https://blogs.oracle.com/java/post/free-java-license

Write A Comment