Mostly Making oracle easy, mostly:
Search for:
In ORDS

Querying the Oracle REST Data Services for your Oracle Database

3 Mins Read

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?

If you’re using ORDS to build ORDS REST APIs with Database Actions, AKA SQL Developer Web, simply navigate to the REST page under development.

Yes, I’m running ORDS on my machine, I’ve pulled up http://localhost:8080/ords/sql-developer

There you can find a modules page, and select your REST Module.

Browsing ORDS REST Module Templates…

Browse, click, inspect…

You could search using SQL Developer Desktop’s Find DB Object panel…

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;
The highlights of your APIs, in a pretty simple query.

What all do I have access to?

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

ctrl+spacebar to invoke insight

And ‘yes!’ there are DBA_ dictionary view options as well.

You’ll need the ORDS_ADMINISTRATOR role to see these…

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

You’ll need to zoom and pan to get something useful…

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

We’re going to:

  1. Find a driving table (ORDS_MODULES)
  2. Select it in the Navigator
  3. Right click and select ‘Add with dependencies’
  4. Send those to a SubView

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

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

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 (Swagger) JSON for each individual module or REST Enabled Object.

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

Related Posts

9 Comments

  1. Dean Miller Reply

    Creating a call to a oracle package (owned by the ORDS enabled schema). I have a table ar.ra_customer_trx_lines_all that is causing me to not get a response . I can run the same query as the schema owner in sqlplus and get data to return.

    Is there some security issue I need to resolve with ORDS? The schema owner of the package has view access of this underlying table. I apologize in advance if I’m posting this in the wrong forum.

    • thatjeffsmith Post Author

      you’re calling a package (via ords POST or GET) which then works with this table?

      Sounds like maybe it’s invoker rights issue…the person making the ords call (the rest enabled schema), is the person that the code runs as in the database

  2. Billy Ahern Reply

    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 Post Author

      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.

  3. greg Braswell Reply

    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.

    • greg Braswell

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

  4. greg Braswell Reply

    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 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;

Write A Comment