SQL Worksheet for ANY database in the Oracle Cloud

TL/DR: – we switched out the old SQL Worksheet for the one you see in SQL Developer Web (AKA Database Actions).

In November of 2021, we released a new service in the Oracle Cloud (OCI), Database Tools. It allows you to create database connections, and then use those connections in your applications, OR use them to run queries in a OCI Console webpage.

Here’s what it looked like on November 16, 2021:

The features we launched in 2021 were pretty impressive even for a v1, and included:

Define Oracle Database connections
Store the database password in the OCI Vault
Create Bastions to access the DB VCN as needed
Automatically Launch SQLcl in CloudShell with said connection
Open a SQL Worksheet in OCI Console with said connection

The SQL Worksheet was also pretty feature rich, and offered:

the ability to change connections or create new ones
Run SQL, get results to a GRID
Run a script, get script output
Save results to a local file or in the object store
SQL insight assistance
SQL History

You can read my feature-deep-dive demo here.

Footnote: we added support for MySQL connections recently as well. Have you heard about Heatwave?

And THIS is what that looks like as of today.

It can do everything the old interface offered, but also much, much more!

2023 Features:

Browser to navigate your schemas
Object Search
Create/Edit/Open objects from said schemas
Load data from CSV, Excel, JSON, etc to new or existing tables
Visual Explain Plan & AutoTrace
Resizable SQL worksheet editor/results
Data Grid – Single Record View, Column Sorting, Hide/Re-Order Columns

It’s a lot of stuff, and you’ve probably seen me talk about these features via SQL Developer Web in OCI we have hosted for our Autonomous Database Cloud Services.

Or, you’ve probably seen me talk about this for your on-premises Oracle Databases via Oracle REST Data Services.

But this is different

This doesn’t require an ORDS install. This doesn’t require an Autonomous Database (although we support that). This SQL Worksheet can be setup for ANY Oracle Database you have running in OCI.

By the way, it supports our new managed MySQL instances as well!

A Quick Demo

Connecting to my BASE database service

In order to connect to something, I need to create said ‘something,’ and in this case the something is a 19c EE BASE service in our Montreal region, called ‘brouepub.’

How fast can I get connected to start running my SELECT * FROM EMPLOYEES code?

Now, I already created two different database connections (SYS and JEFF), I’m going to show you what using it looks like, first. THEN I’ll show you how I set it up to be used.

Open a worksheet, run a query, change connections, run query.

But Jeff, GUIs are for wimps, give us the mighty command-line!

Ok, you’re in charge. Let’s go!

By the way, this isn’t new, but it’s still very cool!

Gimme a cmd prompt to run my SQL, and make all the ‘yucky networking stuff’…just work!

Defining my connections

The Database Tools service is listed in the OCI Console under the Developer service category –

Developer Services, then Database Tools, Connections

If you haven’t already setup a Vault, you’ll need one. This is the ‘Secret Service’ we use to keep your encrypted database passwords, SAFE.

Do this before you click the ‘Create Connection’ button, or you’ll have to come back.

If you’ve done your homework or already have Vault, then you can get started by clicking the ‘Create Connection’ button.

On the pop-out slider, the first big question is, what are we going to name your connection? This name is VERY important. It’s the only thing you’ll have to distinguish your connection. So probably some combination of a database user and database service name.

After that, you’ll use our menu selection to find the particular Oracle Database you want to connect to.

Do you want to use an Autonomous Database? No, how about the Base service – that’s both the VM and BareMetal varieties? No again? How about our Exadata on OPC (aka ExaCS?)

I can simply use the drop downs to click through my servers, databases, and pdbs. I only have to manually type in the highlighted pieces, the NAME of my connection and the USER.

Or maybe you have a database that our control plane doesn’t know about! Maybe you have a Kubernetes cluster or a compute node that you’ve put an Oracle Database onto.

Just choose the ‘Enter database info’ radio option! If you have a connection string, we’ll know how to get there! Think, EZCONNECT strings.

Kind of like in SQL Developer…but passwords are kept in a Vault.

But what about the networking?

Yeah, that’s the ‘hard’ part, but we do our best to make it easier.

If we were to finish off our connection properties by scrolling down the connection dialog, we’d see this –

‘Create password secret’ would stow a database password in the aforementioned Vault.

Ah, private endpoints (PEs), my ‘nemesis.’ I say nemisis because networking is one of my many IT weakness. More or less, our database is on a particular OCI virtual network (VCN), and the Database Tools service needs a path to this VCN. Creating a PE allows us to do that!

If you click the Create PE button, you’ll see something like this –

Odds are you have ALREADY setup the required Subnet, or your apps couldn’t connect to your database, right?

If there’s a place to ‘screw this up’ so you can’t connect, it’s HERE. The INGRESS Rule on your private subnet, for port 1521.

This is basically saying, hey, allow for TCP activity on port 1521 (the database listener!) from this IP address, which is the CIDR Block for our Subnet.