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.
thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

7 Comments

  1. Hi Jeff,

    This article is the closest I’ve found on ORDS tracking. We are doing a POC on Oracle ORDS and have done the basic install example using SQLDEVELOPER. We’ve created a few GET APIs and added some oAuth Security. Our two next steps are to deploy to Tomcat and to follow API calls (tracking). I have scoured the internet for an example of someone logging and tracking ORDS api calls and can’t find anything.

    Our dream situation would be to have a dashboard or at least logs of dates, times etc of API calls. I assume we could log these ourselves but I also assume Oracle has a table that manages this. Please point me in the right direction any help would be appreciated.

    Thanks,

    Billy

    • thatjeffsmith

      There’s two levels of tracking:

      1. mid tier – you said Tomcat, so you just need to mine the Apache access logs…something like this…or if you’re sticking with ORDS standalone, you can enable Jetty logging
      2. database – the datatabase provides auditing – you’ll be tracking users as they do their SELECTS, INSERTS, PL/SQL Executions via your ORDS APIs, but you’ll be catching the database perspective, not the mid tier

      ORDS doesn’t track things in a table, because the above should give everyone what they want…for free.

  2. 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.

    • 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.

    • thatjeffsmith

      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

  3. 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

    • thatjeffsmith

      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;

Write A Comment