Querying the Oracle REST Data Services for your Oracle Database

thatjeffsmith ORDS 5 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.

Related Posts

Tell Others About This Story:

Comments 5

  1. no, this is on a private network (not on the internet) and quite obscure. 99% of the traffic is “GET” publishing information to various web apps.

    I did just test this, on the schema that the metadata-catalog was working, by re-enabling with p_auto_rest_auth => TRUE. then got the 401 on that schema. when I re-enabled the same schema with p_auto_rest_auth => FALSE it still gives the 401. in other words… by having tested this it now doesnt work and there does not seem to be a way to get it working again. bummer.

    1. interesting… the metadata-catalog gets a 401 but ords/[schema]/open-api-catalog/ works. but it doesnt give the same information such as “members” which I had a quick app to automatically build grids from. Oh well. thanks for your help, I’ll have to devise some other tool for this.

      1. thatjeffsmith Post
        Author

        what version of ORDS? can you start a thread on the forums so we can get to the root of the problem on this? sounds like a bug.

        ive seen similar behavior when some of the URI patterns had a {} in it

  2. Jeff,
    I have several schemas enabled for ORDS. when I go to server/ords/schema/metadata-catalog/ in some it works fine but in 1 it’s getting 401-Unauthorized.
    I’ve had this problem before and fixed it and can’t remember what I did, any help would be appreciated.
    thanks!
    Greg

    1. thatjeffsmith Post
      Author

      did you secure the schema, a la

      BEGIN
       
          ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                             p_schema => 'HR',
                             p_url_mapping_type => 'BASE_PATH',
                             p_url_mapping_pattern => 'hr',
                             p_auto_rest_auth => TRUE); -- this line, also it's important you SHOULD be securing your services
       
          COMMIT;

Leave a Reply

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