4 Replies Latest reply on Oct 5, 2018 6:22 PM by Mark W Wiseman

    New Pre-Hook Feature in ORDS 18.3

    Mark W Wiseman

      In the ORDS 18.3 Release notes you'll find this paragraph:

      RESTful Services Pre-Hook Function

      ORDS 18.3.0 introduces the ability for a stored function to be invoked prior to the dispatching of an ORDS based RESTful Service. This facility enables customers to perform additional request validation and authorization and/or configure the database session as required. In addition this facility provides a means for the pre-hook to assert the identity and roles of the user making the request, thus facilitating integration with custom authentication mechanisms.

      You can learn more about this feature in the tutorial located here.


      I would like to use this feature but can find no mention of this Pre-Hook feature anywhere except the 18.3 release notes.  It is not in the 18.3 Documentation that I could find.

      The link "tutorial located here"  mentioned in the preceding paragraph doesn't exist.


      I have googled and found nothing except the 18.3 release notes page.


      I have steered away from enabled objects because we have a multi-tenant database.  Therefore, I either needed to alter all the procedures and functions I plan to rest-enable to take more parameters and add pre-processing to validate user and restrict access before accessing code that normally is only available for a user's db session for which she/he has logged into.  I've resorted to generating my own module/template/handler/parameters to wrap the stored packages/procedures/functions I publish as REST through ORDS.  Yes, I have this working, but I would much rather use the AUTO PL/.SQL if I can inject a stored procedure that will do my session validation and setup without having to modify the objects I am exposing as REST endpoints.


      So, my question is:

      1. Can anyone offer information on how to use the new PRE-HOOK feature?



      Mark Wiseman

        • 1. Re: New Pre-Hook Feature in ORDS 18.3

          Oh man....we have TONS of great doc/examples on this. The page just didn't get uploaded. Will fix first thing tomorrow.


          A bit of a preview - too much to post here:



          This document provides an overview of using PL/SQL based 'Pre-Hook' functions that are invoked prior to dispatching every ORDS based REST call.

          A pre-hook is typically used to implement application logic that needs to be applied across all REST endpoints of an application. For example a pre-hook enables the following types of requirements to be met:

          • Configure application specific database session state, such as configuring the session to support a VPD policy.
          • Custom authentication and authorization. As the pre-hook is invoked prior to dispatching the REST service it has the opportunity to inspect the request headers and make determinations about: who the user that is making the request, and if that user is authorized or not to make the request.
          • Perform auditing/metrics gathering, customers may need to track how/when/by whom REST APIs are invoked.

          Enabling a Pre-Hook

          A pre-hook is enabled by configuring the procedure.rest.preHook setting. The value of this setting must be the name of a stored PL/SQL function.

          Authoring a Pre-Hook

          A pre-hook must be a PL/SQL function that takes no arguments and returns a BOOLEAN. The function must be executable by the database user that the request is mapped to. For example if the request is mapped to an ORDS enabled schema, then execute privilege on the pre-hook must be granted to that schema (or to PUBLIC).

          If the function returns true the function is indicating that normal processing of the request should continue. If the function returns false it is indicating that further processing of the request must be aborted.

          ORDS invokes the pre-hook in an OWA (Oracle Web Agent, aka PL/SQL Gateway Toolkit) environment. This means the function can introspect the request headers and the OWA CGI environment variables, and use that information to drive it's logic. The function may also use the OWA PL/SQL APIs to generate a response for the request (in the case where the pre-hook wishes to abort further processing of the request, and provide it's own response)....

          1 person found this helpful
          • 2. Re: New Pre-Hook Feature in ORDS 18.3
            Colm Divilly-Oracle

            Hi Mark,

            the example (and all the other examples for ORDS) are included in the actual product distribution download, essentially this is a typo in the OTN edition of the release notes and the link should not be there. Please download the distribution and look at the release notes in the unzipped distribution folder itself. The pre-hook example is located at the following path within the distribution: examples/pre_hook/index.html


            Apologies for the confusion.

            1 person found this helpful
            • 3. Re: New Pre-Hook Feature in ORDS 18.3
              Mark W Wiseman

              Thank you very much.  I see in the download that the link on the release page works locally.  Thank for your help.  Glad to know that the answer wasn't "Sorry, that actually didn't make it in the release.".

              I will be looking at the examples and am very glad to see this Pre-Hook feature added.  It makes the whole Auto PL/SQL feature the absolute way to go for us.

              We have decided to create new packages that we will ordify whose procedures and functions will be wrappers that call the original code instead of enabling the original procedures directly.  In some cases there are package variables that are expected to already be setup and so we'll be able to setup any of these variables before calling the original code.  It also makes it easier to know what code is expected to be stateless.


              Again, thank you.

              Mark Wiseman

              • 4. Re: New Pre-Hook Feature in ORDS 18.3
                Mark W Wiseman

                Thanks Jeff for your midnight response!


                ORDS is coming to save the day and speed up our ability to deliver restful apis for solid working legacy code.  You mentioned in one of your articles (sorry, don't remember which one) that while Auto PL/SQL is great, we should still write our real code manually using the modules/templates/handlers/parameters process.  I hope I'm not missing anything because from what I see, this Pre-hook feature (which I have yet to use) makes it a no-brainer decision to use Auto PL./SQL for production code.  We use Oracle database so we are limited in our json abilities to go back and forth easily between custom oracle type objects and json using the manual process.  But, with Auto PL/SQL this is all done nicely for us.

                So... Thank you, Thank you, Thank you.


                Mark Wiseman