1 Reply Latest reply on May 30, 2016 5:10 PM by Kiran Pawar

    Retrieving table metadata via ORDS and ORDS plugin

    lmcardle-Oracle

      Hello,

       

      I am using ORDS 3.0.5 deployed on my local WebLogic instance. I am writing JavaScript that, among other things, needs to get a JSON representation of the table metamodel via an HTTP request, I have two questions.

       

      Is there a way of retrieving the metamodel of all ORDS-enabled tables for a connection in a single hit? The URL http://<host>:<port>/ords/<user>/metadata-catalog returns the table names but I want their metadata (columns and so on) as I would find in http://<host>:<port>/ords/<user>/metadata-catalog/<table-name>. I could get the table names then fire off seperate URLs for each one but getting all this information in a single HTTP request would be preferable. I couldn't find documentation for supported URLs to access information through ORDS.

       

      One option I explored while researching this leads to my second question. Is it sensible for me to write this as a java plugin for the deployed ords.war file? If so, is there are way of querying for ORDS-enabled tables only? What marks a table as being ORDS enabled? Is there something in the plugin API that shows this?

       

      Many thanks for any help.

       

      Leon

        • 1. Re: Retrieving table metadata via ORDS and ORDS plugin
          Kiran Pawar

          Hi Imcardle-Oracle,

          lmcardle-Oracle wrote:

           

          Hello,

           

          I am using ORDS 3.0.5 deployed on my local WebLogic instance. I am writing JavaScript that, among other things, needs to get a JSON representation of the table metamodel via an HTTP request, I have two questions.

           

          Is there a way of retrieving the metamodel of all ORDS-enabled tables for a connection in a single hit? The URL http://<host>:<port>/ords/<user>/metadata-catalog returns the table names but I want their metadata (columns and so on) as I would find in http://<host>:<port>/ords/<user>/metadata-catalog/<table-name>. I could get the table names then fire off seperate URLs for each one but getting all this information in a single HTTP request would be preferable. I couldn't find documentation for supported URLs to access information through ORDS.

           

          One option I explored while researching this leads to my second question. Is it sensible for me to write this as a java plugin for the deployed ords.war file? If so, is there are way of querying for ORDS-enabled tables only? What marks a table as being ORDS enabled? Is there something in the plugin API that shows this?

           

          Many thanks for any help.

          Leon

               The metadata-catalog RESTful service works only for RESTful enabled tables and it has a particular behavior as defined in ORDS product documentation.

               Refer: https://docs.oracle.com/cd/E56351_01/doc.30/e56293/develop.htm#GUID-4CE630AA-2F06-41D9-96F6-DA77AB1E6395

               NOTE: This feature of ORDS 3.0 is known as AutoREST and is documented as above.

           

               But, if you want to retrieve object metadata in a particular JSON format/structure which you intend to use in "JavaScript", why don't you create your own RESTful web service that puts together whatever/however you want and returns through a RESTful call?

               Refer: https://jsao.io/2015/07/relational-to-json-with-ords/

               NOTE: This blog post demonstrates how to create JSON with relational data in Oracle DB. You could query table metadata dictionary viz. ALL_TABLES/ALL_TAB_COLUMNS to return what you want. You can use APEX based RESTful Web Service / ORDS based RESTful Web Service.

           

          Regards,

          Kiran