4 Replies Latest reply on Jan 24, 2019 1:38 PM by Walid KHARRAT

    Modify items word

    Walid KHARRAT

      Hi all,

      is there a way to modify the result of json from ORDS example :

       

      ords1.png

       

      The term items can be changed to depts?

       

      Regards

       

      Walid

        • 1. Re: Modify items term
          Franck N

          Hi,

           

          no you can not.

           

          • it is an element  which is named items and contains a number of child elements. Each child element corresponds to a row in the result set generated by the query.

           

          You can not dynamically change it at run time for that solo purpose.

           

          regards,

          Franck

          • 2. Re: Modify items term
            Walid KHARRAT

            Thanks Franck,

             

            maybe if there was the possibility of overloading this term when defining the service as for the path. it would be clearer for the service consumer.

            • 3. Re: Modify items term
              Olafur T

              Hi,

               

              There are two ways to change the structure of the returned object.

               

              1. Use Collection Query Item and subqueries. You loose the benefits of pagination and the "q" parameter, since limit needs to be 0 with subqueries. But you can create your own.

              Example:

              Collection query item, source SQL:

              select cursor (select object_id, object_name
                               from all_objects
                              where owner = 'SYS'
                              order by object_name offset(:numrows * :offs) - :numrows rows fetch next :numrows rows only) as objects
                from dual
              

               

              Call service /sql-test?numrows=5&offs=2 returns

              {
                  "objects": [{
                      "object_id": 45913,
                      "object_name": "/100c1606_StandardMidiFileRead"
                  }, {
                      "object_id": 327246,
                      "object_name": "/100c72c8_PseudoSchemaBuilder"
                  }, {
                      "object_id": 467946,
                      "object_name": "/100e64d6_HashMapLinkedHashMap"
                  }, {
                      "object_id": 46744,
                      "object_name": "/10128284_OpenMBeanAttributeIn"
                  }, {
                      "object_id": 33359,
                      "object_name": "/101419a4_NormalDataCollector"
                  }],
                  "links": [{
                      "rel": "collection",
                      "href": "http://*****/"
                  }]
              }
              

               

              2. Use source PLSQL and simply print out json using htp.prn:

               

              Source type: PL/SQL:

              declare
                l_comma boolean := false;
              begin
                owa_util.status_line(200, '', false);
                owa_util.mime_header('application/json', true);
                htp.prn('{"myObjects":[');
                for i in (select object_id, object_name from all_objects where owner = 'SYS' order by object_name fetch next 5 rows only) loop
                  if l_comma then
                    htp.prn(',');
                  else
                    l_comma := true;
                  end if;
                  htp.prn('{');
                  htp.prn('"object_id": ' || i.object_id);
                  htp.prn(',"object_name": ' || apex_json.stringify(i.object_name));
                  htp.prn('}');
                end loop;
                htp.prn(']}'); -- myObjects
              end;
              

               

              Produces:

              {
                  "myObjects": [{
                      "object_id": 34433,
                      "object_name": "\/1000323d_DelegateInvocationHa"
                  }, {
                      "object_id": 45331,
                      "object_name": "\/1000e8d1_LinkedHashMapValueIt"
                  }, {
                      "object_id": 23050,
                      "object_name": "\/1005bd30_LnkdConstant"
                  }, {
                      "object_id": 334728,
                      "object_name": "\/10074015_Document1"
                  }, {
                      "object_id": 19996,
                      "object_name": "\/10076b23_OraCustomDatumClosur"
                  }]
              }
              

               

              I use this all the time when migrating older services to ORDS and I need to keep the former structure intact.

               

              Regards

              Oli

              1 person found this helpful
              • 4. Re: Modify items term
                Walid KHARRAT

                Oli,

                 

                it's very clever. Thank you for sharing.

                 

                Regards

                 

                Walid