Oracle SQLcl, your modern command line interface for Oracle Database, was released yesterday.

Lots of bug fixes, and a few new commands!

SQLcl Highlights

SQLcl – simple 30mb download and go!

I want to again, thank again the community for helping us catch bugs promptly, AND politely!

One example, there are no longer extra line breaks for statement feedback. This was very obvious when you had creating login.sql scripts. But enough about the bugs, let’s get onto the new stuff!

The primary focus for 20.3 was support for the Oracle Cloud (OCI) and specifically, the Object Storage (OSS). OSS allows you to stow files in a storage ‘bucket’ – you can then refer to these files using the OCI REST APIs, or when using DBMS_CLOUD to load tables in your Autonomous Database.

In SQLcl version 20.3, you can set a ‘cs’ session parameter to define a OSS namespace, bucket, or object. Namespaces allow you to group buckets, which you can think of as directories, and in those buckets you can have objects, which you can think of as files.

Namespaces and buckets allow you to define access permissions across many different objects to different users in your tenancy. (Oracle Docs)

Think of ‘cd’ when you want SQLcl to read or write to files in a specific directory. Now you can use ‘cs’ when you want to read from a specific place in OSS.

SQLcl – show me what’s in my bucket.

The ‘list’ command is either listo (Objects), listb (Buckets), or listc (Compartments).

SQLcl – show me what’s in my file.

Since ‘cs’ is defined down to the bucket level, I can use the ‘peek’ command, and point it to an object (/o/) in my transfer bucket (/b/)

Someone alerted me that our Help text is completely missing the ‘peek’ stuff – thanks Michael!

Here’s what the ‘help cs’ should show for the PEEK bits –

CS PEEK  [ <qualified-name> ] [ { POS | POSITION}  <position> ] [ SIZE <size> ]:     Display the contents of the object.
             <qualified-name> : Optional name of the object, optionally qualified by the namespace and the bucket.
                                 The qualified name concatenated to the url specified must fully identify the object url.
             <position> : Start location for display
             <size> :  Number of characters to display.  If omitted, 2000 characters are displayed.

SQLcl – get that data and use it to load a local table!

What’s my delimiter, what’s my end-of-record ‘terminator’ – load the file!

I’m connected to a local Oracle database, so I’m pulling the records from the Cloud to load into on my on-premises table, but I could load that data really to any Oracle Database I could manage to connect to from SQLcl.

What’s this mean?

I can now very easily take advantage of things I have in my Oracle Object Store from within SQLcl. To take advantage of this feature, we need to have a OCI Profile defined.

You’re going to want to CAREFULLY follow these Docs, but basically…

  1. Create a RSA key pair in PEM format on your machine where SQLcl will be running
  2. Upload that key to your OCI User’s profile, and get the fingerprint
  3. Get your tenancy and user OCID, create a config file

My config file (located in your $HOME/.oci directory) looks like this –

When generating your key, don’t include a pass phrase.

Make sure your key files are locked down – ONLY your OS user should have RW rights on it. If you are too promiscuous with your key files, it will NOT work.

Once you have this done, you only need to configure your SQLcl instance to use the correct config [profile] – as you can have multiple Cloud Accounts, it’s quite likely you’ll need to create and manage multiple profiles in your config file.

The ‘oci’ command isn’t new for 20.3, but the ‘cs’ command is.

To make all of this work, and work nicely, a TON of work was done to the LOAD command, and some SET parameters.

set loadformat - control how the data will be read/interpreted
set load - control how the data will be loaded

You can use the HELP command to get very nice descriptions of these settings, plus examples.

More on the CS command.

What you can do with the the CS command all depends on what the URL has been configured to. Hint, you can use ‘show cs’ to see what the active cloud storage URL is.

If the URL includes the bucket, you can just ask for objects. If it just includes a namespace, you have to include the bucket. If it includes the object, you can ONLY interact with that file.

OCI command isn’t new…call OCI REST APIs!

Let’s say I want to spool a file, and copy it up to OSS. And then when it’s up there, I want to ‘cat’ it back out.

I PUT the file to my CS URL (which id defined out to the bucket), as a locations.txt object.

The peak command automatically grabs the first 2,000 characters. If you only want the first 500…

You can additionally provide an offset, but you’ll get the first 2,000 characters in the file by default

Just one more thing…

Yes Columbo, I know this has been a long post already.

There’s so much more to talk about here, but I did mention a Data Modeler update. Real quick, in SQLcl still – there’s a new modeler command.

You can now from the CLI generate DDL scripts and reports from your existing Data Modeler Designs.

There are a TON of parameters you can feed these commands. No worries, the help includes examples!
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.

2 Comments

  1. Sorry to be a pain but I had this same issue (running SQLcl on Windows) 2 years ago and this was the reply:

    The developer says one of the libraries we use in the EXE (to make starting this easier in Windows) is going into the Registry to look for Java.

    Easiest fix would be to run Oracle JRE installer on that machine.

    We’ll file a bug – it should just work if a Java Home is available.

    Thanks
    Graeme

  2. I don’t know what I’m doing wrong here:

    D:\DBA\SQLcl\bin>CD ..

    D:\DBA\SQLcl>DIR
    Volume in drive D is Data Drive
    Volume Serial Number is C89E-3F2C

    Directory of D:\DBA\SQLcl

    10/31/2020 12:02 PM .
    10/31/2020 12:02 PM ..
    10/31/2020 11:54 AM 44 20.3.0.274.1916
    10/31/2020 12:01 PM bin
    06/24/2020 09:34 PM jdk
    10/31/2020 12:01 PM lib
    10/31/2020 11:54 AM 934 README.md
    2 File(s) 978 bytes
    5 Dir(s) 254,086,332,416 bytes free

    D:\DBA\SQLcl>CD BIN

    D:\DBA\SQLcl\bin>DIR
    Volume in drive D is Data Drive
    Volume Serial Number is C89E-3F2C

    Directory of D:\DBA\SQLcl\bin

    10/31/2020 12:01 PM .
    10/31/2020 12:01 PM ..
    10/31/2020 11:54 AM 978 dependencies.txt
    10/31/2020 11:55 AM 419 README.md
    10/31/2020 11:54 AM 20,031 sql
    10/31/2020 11:55 AM 129,536 sql.exe
    10/31/2020 11:54 AM 119 version.txt
    5 File(s) 151,083 bytes
    2 Dir(s) 254,086,193,152 bytes free

    D:\DBA\SQLcl\bin>echo %JAVA_HOME%
    D:\DBA\SQLcl\jdk\jre

    D:\DBA\SQLcl\bin>java -version
    java version “1.8.0_221”
    Java(TM) SE Runtime Environment (build 1.8.0_221-b11)
    Java HotSpot(TM) 64-Bit Server VM (build 25.221-b11, mixed mode)

    D:\DBA\SQLcl\bin>sql
    This application requires a Java Runtime Environment 1.8.0_220

Write A Comment