2 Replies Latest reply on Oct 10, 2018 3:49 PM by Olafur T

    Future support for value arrays?

    Olafur T

      Hi,

       

      Will ORDS get support for value arrays in the future? If I use the cursor(select id from table) feature I will get a JSON that has an array of objects.

       

      Example:

      SQL:

      select cursor (select object_id from user_objects fetch next 10 rows only) as objects from dual
      

       

      Will return data like:

      {"objects":[{"object_id":970583},{"object_id":339982},{"object_id":955123},{"object_id":955124},{"object_id":955125},{"object_id":955126},{"object_id":955127},{"object_id":955128},{"object_id":955129},{"object_id":955135}]}
      

       

      But I would really like to get a response like:

      {"objects": [970583, 339982, 955123, 955124, 955125, 955126, 955127, 955128, 955129, 955135]}
      

       

      Right now I'm using PL/SQL type of GET to produce this results, but it would be nice if the GET handler type Collection Query could know how to do this.

       

      Regards

      Oli

        • 1. Re: Future support for value arrays?
          Olafur T

          Hi,

           

          Been trying different stuff.

          I see that it's partially possible now.

           

          Collection Query Item. GET handler

          Source

          select apex_string.split('1,2,3,4,5', ',') as col1, apex_string.split_numbers('1,2,3,4,5', ',') as col2 from dual
          

           

          will result in a nice output:

           

          {"col1":["1","2","3","4","5"],"col2":[1,2,3,4,5],"links":[{"rel":"collection","href":"xxxx"}]}
          

           

          I can then use listagg to produce the effect I was looking for:

          GET source:

          with uo as
          (select object_id from user_objects fetch next 5 rows only)
          select apex_string.split_numbers((select listagg(object_id, ',') within group(order by object_id) from uo), ',') as objects
            from dual
          

          (Had to move user_objects select into with list to prevent listagg using all rows)

           

          Produces a nice:

          {"objects":[99334,99335,99336,99337,2429201],"links":[{"rel":"collection","href":"xxxxxx"}]}
          

           

          Guess I could easily throw this into an overloaded function with input sys_refcursor and output apex_t_numbers or apex_t_varchar2 depending on source column.

           

          Gonna try that.

           

          Regards

          Oli

          • 2. Re: Future support for value arrays?
            Olafur T

            Ok,

             

            Found a nice way to use this cleanly in SQL type GET handlers. Using CAST MULTISET into apex_t_*.

             

            This works nicely for me:

             

            select cast(multiset (select object_id from user_objects fetch next 5 rows only) as apex_t_number) as objects,
                   cast(multiset (select object_name from user_objects fetch next 5 rows only) as apex_t_varchar2) as object_names
              from dual
            

             

            Produces nice value arrays that can be used f.i. with apex_json.get_t_number and apex_json.get_t_varchar2

            {  
               "objects":[  
                  2429201,
                  99334,
                  99335,
                  99336,
                  99337
               ],
               "object_names":[  
                  "TABLE1",
                  "TABLE2",
                  "TABLE3",
                  "TABLE4",
                  "TABLE5"
               ],
               "links":[  
                  {  
                     "rel":"collection",
                     "href":"xxxxx"
                  }
               ]
            }
            

             

            Regards

            Oli