Querying the Oracle REST Data Services for your Oracle Database

thatjeffsmith ORDS 0 Comments

Tell Others About This Story:

So you’ve installed ORDS, you’ve REST enabled a schema, and you’ve started publishing some RESTful Services.

Now, how do we go about seeing what’s what?

You could browse…

All the info is there, you just have to mouse through it.

You could search

ORDS integration is one of my favorite new features in SQLDev.

Or you could query the data dictionary!

I’m going to look at USER_ORDS…stuff.

We have _MODULES, _TEMPLATES, and _HANDLERS – most of what you’ll want is there.

SELECT
    name,
    uri_prefix,
    uri_template,
    method,
    source_type,
    SOURCE
FROM
    user_ords_modules a,
    user_ords_templates b,
    user_ords_handlers c
WHERE
    a.id = b.module_id
    AND   b.id = c.template_id
ORDER BY 1,2,3,4;

Like I said, MOST of what you need.

What all do I have access to?

Let’s ask the worksheet what it can see for USER_ORDS_

And yes, there are ALL_ and DBA_ equivalents.

Want a data model? If we go RE the ORDS_METADATA schema to a design, we get something like this:

Not super helpful…

Ok, let’s re-investigate a Modeling trick.

We’re going to:

  1. Find a driving table
  2. Select it
  3. Select it’s ‘neighbors’
  4. Send those to a SubView

And then we get something a bit easier to see/understand.

Trivia: the ORDS developer used SQL Developer Data Modeler to design the ORDS repository. His boxes were much prettier than mine though.

I’m showing you around the ORDS_METADATA schema – but stay out of it, just like you’d stay out of the SYS.X$ tables. Use the data dictionary views we give you to query stuff. And use the PL/SQL API to CHANGE your services, don’t live-edit the tables.

And Don’t Forget about the RESTful Way of Getting Your Services

Just hit the /metadata-catalog/ end point for your REST enabled schema.

You can also get the OpenAPI json for Swagger doc…

Wait, what about my REST Enabled Tables, Views, and PL/SQL?

Oh yeah, those.

SELECT
    parsing_schema "Schema",
    parsing_object "Object",
    object_alias "Alias",
    auto_rest_auth "Authorization",
    TYPE "Type"
FROM
    user_ords_enabled_objects
ORDER BY
    parsing_schema,
    parsing_object

FYI, Authorization = ‘DISABLED’ is a HUGE, RED flag. Don’t do that.

Tell Others About This Story:

Leave a Reply

Your email address will not be published. Required fields are marked *