Oracle SQLcl supports your Oracle Cloud (OCI) APIs

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

20.3 Highlights

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.

SQLcl – show me what’s in my file.

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!

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 –

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.

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.

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.

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

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.

What else don’t you know about SQLcl?

Or see the rest of my SQLcl posts.