The Magic of Excel + Oracle, powered by REST APIs

Today’s question is about the most successful piece of business software ever created, Excel.

Is there an excel oracle db addon that can do bulk data pull at ease. Not regular odbc based mssql query.

Ms Kay Malcolm aka @iamkaymalcolm

And my answer to Kay was, “Yeah the Visual Builder team built a cool plug-in for Excel, that can read/write data from the database using REST APIs powered by ORDS.”

And they did that way back in 2019, and I talked about it in this post.

It’s been a few years, so I wanted to make sure everything was still as I expected. It’s generally the same, but the plug-in has advanced quite a bit. Nice work Visual Builder team!

Some of the UI elements are different than shown in my old post, so let’s do a 2023 walk-through. THIS is that post.

Let’s fricken go!

The cool kids say something a bit different than that, but let me show you what I ended up with, before we do all the setup.

My NETFLIX viewer data, that I had uploaded to my Always Free Autonomous Database cloud service earlier this year. Instead of using SQL to navigate that, what if I just used Excel as my front end?

Here’s what that looks like –

How I got there

Prerequisites

• An Oracle Database
• Said database, configured with Oracle REST Data Services (ORDS)
• A table
• Said table, REST Enabled
• Excel, on Windows
• Download & Install the Oracle Visual Builder Add-In for Excel

You DO NOT need a Cloud, this will work for ANY ORDS serviced Oracle Database. My example is just going to use the Oracle Autonomous Database in the Oracle Cloud, because…I work for Oracle, and they like me to say ‘Cloud’ at least 3x a day.

Of course, the easiest way to get the first 4 things taken care of is to ALSO setup an Always Free Cloud account with us. Here’s a quick tutorial to getting started.

If you want more help, look up, click the ‘REST APIs” menu item, and follow along with my tutorials on ‘AutoREST.’

Our Oracle TABLE

My table is named NETFLIX, and it looks like this –

Now we need to REST Enable it.

In the worksheet, I simply right-click on the table.

I’m going to say, yes, let’s enable this, here’s what I want it to be known as on the URI, and I WANT IT SECURED.

Table Security

For this example we’re going to use basic (username & password) authentication, and that’s going to be the Oracle USER that owns this table. Now, the Excel plug-in supports OAuth2 (awesome!), but for simplicity I’m going the Basic route.

That means, I’m going to be using my database credentials, in Excel. That means I’m NOT going to share this with anyone else.

If you want/need to do that, head for the OAuth2 route.

For my database username and password to give me access to this ORDS provided web resource, my authenticated user needs the ‘SQL Developer’ web role to be granted the ORDS privilege that’s used to protect our table.

So let’s do that now, navigate to the REST workshop in SQL Developer Web, then head to the Security section, and privileges.

Filter on the name of your table –

One last thing before we leave ORDS/SQL Developer Web

The Visual Builder plug-in wants to work with collections of data described by the OpenAPI spec. This used to be known as ‘Swagger,’ but ORDS endpoints support v3 of OpenAPI, so you just need to verify the URL.

It should look like this –

https://something-something.com/ords/user/open-api-catalog/netflix/

If you pull up that URL it should look like this:

The Excel part, finally!

Make sure Excel is closed, then download and run the VB plug-in installer.

You’ll need to say it’s OK to Add the plugin when you open Excel, basically, you’re saying that you trust us, Oracle.

Once you’ve done that, it’s time to add our new data source, via

the Netflix REST API.

You’re going to add a new Business Catalog, via a Service Description (that’s our OpenAPI endpoint.)

And now we’re going to provide the URL, and then our Oracle Database user credentials.

Once this has finished, you can basically click ‘Done’ on the Object Catalogs manager dialog.

Now it’s time to enter the Designer

Do this –

Ok, now we can start using our data, via the database, via the REST APIs!

There’s a query interface, but I’m just going to hit the ‘Download Data’ button.

When it’s done, you’ll get your worksheet with the data, and you’ll see how much time was being spent to get how many rows.

And now it’s time start getting busy living, as Red would suggest (Shawshank reference).

You can update the source table

I can delete rows, I can add rows, and I can CHANGE rows, via Excel.

Once you make the changes, you can POST the changes back up to the database.

Make my changes, click the ‘Upload Changes’ button.

Can it do more?

Oh yes, much more. But I’m not an Excel wizard, and I already spent more time than I anticipated on this post.

But there’s tons of great information here –