ORDS and APEX – Common REST definitions at last

Doug Gault APEX, ORDS 9 Comments

Since re-joining Oracle, my first major project has been to work on better integration of APEX and ORDS when it comes to the definition of RESTful Services.

APEX has long had the ability to define and serve RESTful Services on its own. These have been managed via a section of the SQL Workshop known as the RESTful Services Console. These services being separate from those defined as part of ORDS has caused some confusion over the years.

If you define a service in APEX, why can’t you see it in ORDS, and likewise if you define a service in ORDS (using either SQL-Developer or the PL/SQL APIs) why can’t you see it in APEX. The answer is very simple – their definitions are stored in two separate repositories, neither knowing about the other.

So, the decision has been made that ORDS shall be the “way forward” in terms of RESTful service definitions. What this means for those who have created services in APEX is that you’ll need to migrate those definitions into the ORDS metadata repository, as the use of APEX RESTful Service will eventually be deprecated . Fear not, this is easier than you might think and actually gives you better control over security in the long run.

The rest of this post will give you an overview of the upcoming changes to the REST Services Console in forthcoming version of APEX. Please note that the screenshots I’m sharing are of pre-production software and the final release may change from what you’re seeing here.

What you see will depend on what you have

There are basically three scenarios that will alter what you see when you navigate to the RESTful Services section of APEX, and they depend on a couple of things:

  • What version of ORDS are you running?
  • Does your workspace already have APEX based RESTful Services defined?

The scenarios are as follows:

 – You’re running an old (pre 17.4.1) version of ORDS

In this scenario you’ll be taken directly to the current APEX based RESTful services console just as you were before. You’ll be shown a warning message stating you have not met the minimum ORDS version that allows you to use the ORDS RESTful Services console. You’ll be able to continue creating and maintaining APEX Based RESTful services, but should start thinking about upgrading ORDS as soon as appropriate.

– You’re running ORDS 17.4.1 or above and have APEX Based REST services defined in your workspace

In this scenario you’ll be taken to an options menu that will allow you to navigate to a read-only version of the APEX RESTful Services console or to the new ORDS RESTful Services console.

Clicking on the APEX Based RESTful Services option will take you to the familiar console you’re used to seeing with two major differences. First, all service definitions will be read-only and there will be a message at the top of the screen notifying you of such.  Second, there is a new button that allows you to migrate all of your APEX Based RESTful service definitions from the current workspace into the ORDS repository.

Clicking the ORDS Based RESTful Services option from the previous screen will take you to the full ORDS console where you will be able to create an maintain service definitions that will be stored in the ORDS Metadata tables.

– You’re running ORDS 17.4.1 or above and have no APEX Based REST services defined in your workspace

Under this scenario you will be taken directly to the ORDS RESTful Services console and will no longer have access to APEX Based RESTful Services.

The ORDS RESTful Services Console

When accessing the ORDS RESTful Services console for the first time, it is likely that your schema won’t be registered with ORDS. If this is true, you’ll be presented with a warning and a button that will allow registration.

Clicking the button will display a dialog with options for enabling ORDS REST for the schema.

Enabling the schema will then allow you full access to create and manage ORDS Based RESTful Services from within APEX.  In the dashboard example below you can see that I chose to install the Sample HR service, set my schema alias to dag_ords and chose not to require authorization for access to the REST Metadata for the schema.

From here you’ll be able to create and maintain RESTful services, the definitions of which will be stored fully within the ORDS metadata repository. What this means is that you’ll be able to see those services in SQL-Developer and in the ORDS metadata views from the underlying schema.  As an example, below are some screenshots of one small section of the sample HR service.

ORDS Module Definition Page

ORDS Template Definition Page

ORDS Handler Definition Page

New things to look out for

By moving to the new ORDS Based RESTful Services you get many benefits out of the box including the following:

  • REST Services in all associated schemas – When using APEX Based REST, services were created against the first schema assigned to the workspace and only that schema. There was no way to create services for other schemas assigned to the workspace. When using ORDS Based REST, you’re able to manage services in any schema assigned to the workspace. Each is treated separately and each has it’s own Schema Alias that forms part of the URL used to access RESTful Services within that schema.

  • Swagger Doc – ORDS provides the ability to generate Swagger documentation for any module defined within its metadata repository and now this functionality has now been integrated in to APEX. There is a new instance level setting allowing administrators to specify the URL of a Swagger UI server. If the URL is filled in, the Generate Swagger Doc button on the Module Definition page will open a new window and display the output from the Swagger UI server. If the URL is left blank, clicking the button will open a new window and generate the raw Swagger JSON that can be cut and pasted into a Swagger UI server.
  • Easily see which services are protected – On the Module report page and in the Templates report on the Module Definition page you can see a visual indication of whether an all or part of a module, or an individual template is protected by an ORDS Privilege. This helps make sure services aren’t accidentally left open to the world.

Things Coming in Future Releases

No matter how hard you try, you can’t get everything you want into the first release of anything. There were a few major things that we wanted to get in but that will have to wait until a future release.

  1. OAUTH Client Management – Currently you must either use the screens built in to the ORDS engine, or the PL/SQL APIs to create OAUTH Clients. The plan is to create a set of screens within APEX to allow OAUTH client management as well. This way you will be able to manage both Services and their Security in one place.
  2. REST Enabled Object Management – Right now there are two ways to REST Enable database objects; Via SQL-Developer or via the PL/SQL APIs. The goal is to provide an interface to do this within APEX as well. One obvious place to REST enable a single object would be in the Object Browser, but I can also see that it would be useful to have a wizard that allowed you to pick multiple database objects to REST enable.
  3. Guided Tour – Knowing that not everyone is familiar with creating REST services, is very probably that some may not know where to start. Therefore we’d love to use something like Hopscotch to provide guided tours of the interface and walk you through how to create and protect your first web services. This is a larger undertaking than it may seem as bundling Hopscotch into APEX will mean quite a bit of work. Not to mention writing and testing the tour scripts.

I know this has been a pretty high level overview, but to go into more detail would basically be to repeat what’s going to be in the documentation.

Again, don’t take any of this as a promise of things to come. Until the software hits GA, it’s all smoke and mirrors.

Happy RESTing!

Comments 9

  1. Hi Doug, do you think it’s possible to see the rest consumption in real time? To be able to see what calls are being used, performance, errors, what ips are using the rest service, how often etc etc.


  2. Post


    Right now I don’t believe that there is any REAL TIME way to access information about consumption, performance, errors, IPs etc. However, depending on how you have ORDS deployed, you should be able to get a fair amount of that information out the WebServer logs.

    If you’re running ORDS Stand alone, Kris Rice has a blog post about setting up the logging here:


    Adding these kinds of metrics has been discussed before. I’ll bring it up again and see where it’s at. I wouldn’t hold my breath as providing an interface for these metrics in any form other than a log file woudl potentially add weight to (and slow response time of) ORDS itself.

    But you don’t get if you don’t ask!


  3. Thanks Doug,
    1- What is the best way to monitor the APIs calls?

    2- Is there any plan to make a built-in Google authentication for the APIs? — Currently we authenticate our APIs via Google OAuth, and we noticed that it is faster.

    1. Post


      As mentioned in my previous comment, the Web Server logs are currently the best (only) way to monitor what’s going on with the calls to web services. There is nothing that you can attach to real-time, and there is no log data stored in the ORDS metadata. Have a look at the blog post from Kris Rice (Mentioned above) if you’re using ORDS in standalone mode. Otherwise you’ll have to look at the docs for which ever web server you’re using to see how to get the best out of the logs.

      As for building in Google Authentication – No, no plans that I know of. You’ve obviously found something that works well for you, so I’d suggest you keep using it.

      Building in 3rd party Authentication is troublesome. Sure Google OAUTH might be great and “flavor of the month” but what happens if the response time degrades or Google stops supporting it. It’s better to leave this type of things for the individual implementer to decide what is best for them.


  4. Hi Doug,

    Do you know how can I protect a REST Module or Handler with a privilege using the APEX-ORDS REST UI? I can’t seem to find it (I’m working on apex.oracle.com)

    1. Post


      The ORDS REST Workshop is not yet to the point where they can do 100% of the work of protecting a REST service solely using the APEX Screens. There are future plans to extend the screens to include all the steps necessary in the UI, but in the mean time, you’ll have to use the PL/SQL API’s to do much of the work.

      Your best bet is to go to the ORDS documentation and read section 3.3 Configuring Secure Access to RESTful Services in its entirety.

      This will walk you through all the different authentication options and the API calls you will need to make. Once you’ve read that, if you have any specific questions I’d be glad to help

  5. Dear Doug,
    in APEX 5.1.2 to create a GET we use:

    in RESTful Services –> RESTful Service Module –> Resource Template

    we put in URI Template a string similar to this

    ex: getMessage?name={name}&age={age}&city={city}

    In this way when calling this rest one can pass “name= jhon&age=89&city=LasVegas”

    we use this parameters in input.

    In apex 18.1
    we can’t use this notation? We have seen that the notation use “:nameParameter” but when we have more parameters?

    Thanks in advance,


    1. Post


      First let me apologize for being late answering your question. For some reason i didn’t get notified that there was a comment waiting.

      In 5.1.2, you were using APEX to define RESTful services, whereas version 18.1+ uses ORDS to define and deliver RESTful services.

      The syntax (URI Patterns) that you are using to define your the patterns of URLs is really not supported by ORDS. Instead you should switch to using Route Patterns [1] instead of the old deprecated hard to use URI Template syntax as part of your migration to ORDS based REST Services. The types of thing you are trying to do, (i.e use query parameters) are much easier using the newer Route Pattern syntax. [2] The effort will be well worth it.

      [1]: http://download.oracle.com/otndocs/java/javadocs/3.0/plugin-api/route-patterns.html
      [2]: https://blog.cdivilly.com/2015/03/10/route-patterns/


  6. Thanks for a great article Doug. Saved me hours. Moving to OCI and made the mistake of manually creating the workspace (as I wanted to clean up names, get rid of old users, etc) and then tried to import the APEX rest definitions separately into 19.1. Deleted the workspace, re-imported a full workspace export with the rest definitions, converted to ORDS definitions and the world is good again! Thanks my friend!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.