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

      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

        • 1. Re: GET Resource handler, method SQL, and simple typed arrays (subcursors)
          Mike Kutz

          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

            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

              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

                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

                  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.