2 Replies Latest reply on Dec 24, 2015 10:14 AM by Puneet Singh-Oracle

    Questions related to query parameters

    Puneet Singh-Oracle

      Our team is trying to use ORDS to develop rest services in Oracle database. After a month of experiments, a lot of questions came up regarding functionality and capability of ORDS. It would be great if we can get response on following questions:-

       

      • When we define multiple query parameters in a template for a GET request, we found following restrictions
        • all parameters declared are mandatory and should be supplied with a value [is there any workaround for this ?].
          • what if one of query parameter value was null ? 
        • parameters should be supplied in the same order in which they are declared[undesirable, is there any workaround available ?].
      • Is their any way to provide multiple values for a parameter, like a collection from url for a GET request and utilize them in sql query. [Ex: where status in :status_list].
        • This is a very essential requirement for us.

      • Is their any way to translate a sql query obtained after some manipulations in PL/SQL into JSON where a request is being handled by PL/SQL block. Right now we do it using htp.p to print strings in json format.

       

      Regards,

      Puneet

        • 1. Re: Questions related to query parameters
          Kiran Pawar

          Hi Puneet Singh-Oracle,

          Puneet Singh-Oracle wrote:

           

          Our team is trying to use ORDS to develop rest services in Oracle database. After a month of experiments, a lot of questions came up regarding functionality and capability of ORDS. It would be great if we can get response on following questions:-

           

          • When we define multiple query parameters in a template for a GET request, we found following restrictions
            • all parameters declared are mandatory and should be supplied with a value [is there any workaround for this ?].
              • what if one of query parameter value was null ?
            • parameters should be supplied in the same order in which they are declared[undesirable, is there any workaround available ?].

               Need to investigate this. But, Colm Divilly-Oracle or Kris Rice-Oracle can give more correct answer to this.

           

          • Is their any way to provide multiple values for a parameter, like a collection from url for a GET request and utilize them in sql query. [Ex: where status in :status_list].
            • This is a very essential requirement for us.

               You can use JSON payload to transfer your "in list" for your query. ORDS 3.0 supports query filtering through RESTful Services.

               Refer :

          • Is their any way to translate a sql query obtained after some manipulations in PL/SQL into JSON where a request is being handled by PL/SQL block. Right now we do it using htp.p to print strings in json format.

               You can write PL/SQL function to generate the JSON as you want.

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

               This is the main blog post by Dan McGhan, where he has explained various techniques to generate JSON from relational data in Oracle Database.

               Refer : https://jsao.io/2015/07/relational-to-json-in-oracle-database/

               If you are using APEX 5/ Oracle Database 12c you refer the following thread :

               Refer : Re: APEX5, get feet wet with json?

           

          Regards,

          Kiran

          • 2. Re: Questions related to query parameters
            Puneet Singh-Oracle

            Hi Kiran,

             

            Thanks for prompt reply.

             

            Regarding your answer corresponding to this question:-

            • Is their any way to provide multiple values for a parameter, like a collection from url for a GET request and utilize them in sql query. [Ex: where status in :status_list].
              • This is a very essential requirement for us.

             

            The links you mentioned has some good examples of how to add JSON payload directly in the query, but we are specifically looking to add a collection type parameter in the URL. Something like this:-

            {"PROGRAMMER": "PUPUSING", "STATUS": {"<$in>" : [11, 15, 66, 80, 81]}}


            Not sure if there is any operator in the Filter Object or may be i'm not able to locate. There are options for range like "between", but they don't fulfill the requirement. A work around for this problem is to use OR clause like this:-

            {"PROGRAMMER": "PUPUSING", "STATUS": {"$or" : [{"$eq": 11}, {"$eq": 15}]}}

             

            but it won't scale well in our case since there is a possibility of many parameters with collection type values.

             

            If there is any such operator exist, can you please point out the exact location/reference along with a JSON payload example.

             

            Thanks,

            Puneet