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:

Write SQL, run SQL!

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.

Savvy? I sure hope so, because if you need more help, I have to punt you to an OCI/Networking guy or gal.

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

Do you think I actually took the time to properly model and create table and load it? Oh heck no, I pointed to a CSV/Excel file and said – PUT IT IN MY DB, NOW!

2. Execution Plans

I’ve talked about the SSB schema before, and it’s accompanying execution plans. Well, what’s that look like with our new plan viewer?

Well, it looks like THIS:

Most of the work is happening on the HASH JOIN.

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.

Write A Comment