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.
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.
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 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.
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.
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.
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 –
Get cURL
This item will guide you in making use or exercising the endpoints available for the REST enabled object.
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 –
Once I input everything that’s required, I can go with ‘Next’ –
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 –
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.
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 –
And it’s not just for the AUTO objects now either. You can also see this for your RESTFul Web Service Modules!
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!
