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
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.
- 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.’
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.
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!
Defining my connections
The Database Tools service is listed in the OCI Console under the Developer service category –
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.
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?)
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.
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 –
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 –
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.
Your prize for making it this far – more info and pictures!
The SQL worksheet is FULL FEATURED.
So for example, we can use it to –
1. Load Data
2. Execution Plans
Well, it looks like THIS: