Versions 20.4 of SQLcl, ORDS, and Data Modeler are now available! SQL Developer v20.4 (desktop) will be available SHORTLY. SQL Developer Web is provided as a feature of ORDS, so go here to download and get started if you want to use the features discussed in this post.

Need help getting started? Here’s a quick video tutorial on installing & configuring ORDS, and building your first RESTful Web Service.

We ended 2020 by providing some 20.4 product updates that predominately features BUG FIXES. However, SQL Developer Web (via ORDS 20.4) is the major exception.

There are two primary updates to discuss, the REST and JSON Workshops, but today we’re going to focus on the REST.

REST Workshop now includes AutoREST

In addition to your RESTful Web Services, privs, roles, and OAUTH2 clients, you can now also manage your AutoREST enabled tables, views, and Auto PLSQL endpoints.

REST workshop home page, available under the Development category in SQL Developer Web

So assuming you’ve already enabled some objects, you’ll see them here.

If you need to start from NOT having anything enabled for REST, then you can fall back to the SQL Worksheet.

Enabling an Object from the Worksheet

Find your table, view, or PL/SQL object (function, procedure, package) and right-click.

This menu is sure to grow as we continue to add features in 2021!

This action will cause a Slider to open, where we can define the particulars of our REST Enabled table, in this case, HR.EMPLOYEES. Note, that I’ve already enabled the HR schema, and I’m also logged in as HR.

As I mess with the object alias, the resulting URL is updated to reflect that.

For our Slider controls, if you invoke the Help, an additional slider on the opposite side of the page will appear so you can learn more about what’s being asked of you in the dialog.

Clicking the ‘Enable’ button will execute the ORDS PL/SQL API to enable the object, and we’ll see a notification that it’s succeeded in the upper right hand corner.

If we go back and look at the table in the worksheet browser, we’ll see a new indicator by the object that tells us it’s been REST enabled, and our context menu item for REST looks different now.

We’ll come back to the cURL command feature in a bit!

Back to the REST Workshop

Now that we have a few things enabled for AUTO in ORDS, let’s look at the drill-down page for the AUTO category in the REST workshop.

The boxes across the top serve as ‘filters’, by default I see everything.

If you have more than a page’s worth of objects enabled in your schema, you can filter by type of object and/or you can use the Search mechanism to narrow down the list further.

We can also see via the card indicators (yellow/green) that only 1 of our 2 objects are protected by a privilege – that means that ANYONE who can reach the /abc endpoint can exercise it, whereas to access /peeps they’ll need to be authenticated/authorized.

Let’s look at the TABLE first.

Clicking the vertical ellipsis looking button in the top right corner of the card, we’ll see similar to what we saw back in the SQL worksheet –

Edit and Disable do what you would think, turn it off, or change the alias/privilege properties.

Get cURL

This item will guide you in making use or exercising the endpoints available for the REST enabled object.

Assuming you have cURL available on your machine, this makes testing the feature pretty easy.

Maybe in Day0 of dev/hello world you don’t want to require Authorization, but just be sure you remember to take care of security before you push anything to UAT/Production!

Anyways, you can see quite a few items listed. In the first one, it’s a pretty boring one, show me ALL of the EMPLOYEES, so it’s just a GET on /ords/hr/peeps/

Let’s look at a more interesting one, PUT –

We’re presented with a FORM to provide the required values.

Once I input everything that’s required, I can go with ‘Next’ –

And we even give you an easy-peasy Copy-to-Clipboard button in the top right corner.

Now, we’re not going to build you a REST client, but we have given you the APIs, and some help in building your cURL commands to exercise/use those APIs.

Let’s INSERT/PUT a record into our table, here’s a demo –

Trying to make it as easy as possible!

A Quick Look at our ORDS Enabled Package

For PL/SQL we only publish a POST endpoint for each program. For packages, that’ll be one POST endpoint per procedure and function.

My package has two subprograms so there are two endpoints to choose from.

What about the Export OpenAPI?

This does what it sounds like, we’ll bring up the OpenAPI {Swagger} endpoint for the REST/ORDS Enabled Object.

So for the table, EMPLOYEES –

We give you the {json} definition of the endpoints so you can use something like editor.swagger.io

And it’s not just for the AUTO objects now either. You can also see this for your RESTFul Web Service Modules!

PL/SQL will give you what you’ll want to Source Control/backup.

Summary

ORDS provides database professionals a few different ways to define RESTful Web Services. You can write your own with SQL and PL/SQL, and you can enable existing database objects for REST.

Now with SQL Developer Web version 20.4, you are provided completely development and management interfaces for both methods, directly in your browser! In addition, you’re able to manage the security so they are PROTECTED.

You’re still going to want to have cURL installed and your favorite (Insomnia) REST client handy to work with those endpoints – but there should be a lot less alt-tabbing between your browser and your other application IDEs.

One last thing…

You should see much less frequent prompts to log back into SQL Developer Web. We updated the security model such that your ‘1 hour’ expiration period extends itself as you continue to work in your authenticated ‘session’ – vs expiring an hour from when you logged in!

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