5 Replies Latest reply on Sep 13, 2016 2:42 PM by odie_63

    GET Resource handler, method SQL, and simple typed arrays (subcursors)

    Olafur T
    Olafur T Sep 12, 2016 11:01 AM

      Hi,

      Is this possible without doing it by hand with pl/sql?

       

      i.e. what I want is to output:

       

      {
  "department": 10,
  "department_name": "Acme explosives",
  "personnel_ids": [1, 2, 3, 4, 5]
}

       

      Instead of 

      {
  "department": 10,
  "department_name": "Acme explosives",
  "personnel_ids": [{
  "personnel_id": 1
  }, {
  "personnel_id": 2
  }, {
  "personnel_id": 3
  }, {
  "personnel_id": 4
  }, {
  "personnel_id": 5
  }]
}

       

      Which is what happens when you wrap a CURSOR around a subquery,

       

      Thank you

      Oli

      • 261 Views
      • Tags:
        • 1. Re: GET Resource handler, method SQL, and simple typed arrays (subcursors)
          Mike Kutz
          Mike Kutz Sep 12, 2016 12:58 PM (in response to Olafur T)

          Have you tried using an SQL collection instead?

          (I don't have access to ORDS to test it out at this second)

           

          MK

           

          create type personal_id_nt is table of integer;
/

select deptno, cast( collect( empno ) as personal_id_nt ) personal_ids
from emp
group by deptno;
          • 2. Re: GET Resource handler, method SQL, and simple typed arrays (subcursors)
            Olafur T
            Olafur T Sep 12, 2016 1:33 PM (in response to Mike Kutz)

            Very nice guess...

             

            But it results in an ORDS error message: 

            • An unexpected error with the following message occurred: null

             

            I was really hopeful for that implementation since it's easy to use and would have solved my problem.  I have tried the documentation but can't find any mention on how to create simple typed arrays.

             

            Regards

            • 3. Re: GET Resource handler, method SQL, and simple typed arrays (subcursors)
              Mike Kutz
              Mike Kutz Sep 12, 2016 1:55 PM (in response to Olafur T)

              Hmm...

              This makes me wonder: Does ORDS support UDTs?

               

              Also, what do the ORDS logs say? (check tomcat/glassfish/wls logs also)

               

              What DB Version are you on?  (12.1.0.2 has the JSON functions built in)

               

              MK

              • 4. Re: GET Resource handler, method SQL, and simple typed arrays (subcursors)
                Olafur T
                Olafur T Sep 13, 2016 10:04 AM (in response to Mike Kutz)

                Hi,

                 

                the database version is 12.1.0.2 (Oracle supercluster, upgrades managed by Oracle),

                 

                The glassfish error doesn't say anything useful:

                 

                java.lang.IllegalStateException

                        at oracle.dbtools.rt.json.query.JSONQueryStream$DataType.dataType(JSONQueryStream.java:214)

                        at oracle.dbtools.rt.json.query.JSONQueryStream$JSONChunker.renderValue(JSONQueryStream.java:406)

                        at oracle.dbtools.rt.json.query.JSONQueryStream$JSONChunker.render(JSONQueryStream.java:328)

                        at oracle.dbtools.rt.json.query.JSONQueryStream$JSONChunker.advance(JSONQueryStream.java:255)

                        at oracle.dbtools.rt.json.query.JSONQueryStream$JSONChunker.advance(JSONQueryStream.java:221)

                        at oracle.dbtools.common.util.AbstractIterator._advance(AbstractIterator.java:140)

                (plus many more lines)

                 

                I can easily do this with pl/sql and htp.prn, but being able to do this natively is preferred,.

                I have read a lot of documentation (and blogs) about working with and querying json in 12c, but not about casting to json in sql (such as xmlforest for xml). I initially tried to find a native way to do this, but couldn't find any.

                • 5. Re: GET Resource handler, method SQL, and simple typed arrays (subcursors)
                  odie_63
                  odie_63 Sep 13, 2016 2:42 PM (in response to Olafur T)

                  I have read a lot of documentation (and blogs) about working with and  querying json in 12c, but not about casting to json in sql (such as  xmlforest for xml).

                  The next release *might* introduce SQL/JSON publishing functions to generate JSON from relational data.