3 Replies Latest reply on Nov 26, 2016 10:38 AM by Gekko

    Restful services Get handler omitting column if null

    Gekko

      I would like some insight into a problem I'm facing - developing application that exchanges json data. Get Handlers return json OK, the thing I cannot solve is that in case of table that has some columns with no values, those columns are omitted in json output.

       

      I searched the internet and found someone complaining about that, but that was in ords 2.0 and I'm using ords 3.0.1. The resolution for his problem was to include header Accept-Formatting with value json-nulls=include header - when I tried that i got the following errror: An unexpected error with the following message occurred: No enum constant oracle.dbtools.json.JSONOptions.NullValues.INCLUDE HEADER. I tried different values - include header, include-header, include_header and none worked.

       

      I also searched for api documentation of that enum but no luck, and ords configuration guide also doesn't have anything about that setting.

        • 1. Re: Restful services Get handler omitting column if null
          Jon Dixon

          Hi,

           

          In my experience it is not usual to not include the filed just because it has a NULL value so I suspect this is just the way it is.  I am assuming you are using the 'Collection' or 'Query' Source Type.

           

          Another option is to 'manually' build the JSON by using the PL/SQL Source Type. Then you can create a PL/SQL block and build the JSON manually.

          • 2. Re: Restful services Get handler omitting column if null
            Jon Dixon

            Just spotted, if you are building the JSON manually and using APEX_JSON.WRITE, there is a signature that gives you control over whether you write NULL fields and it defaults too not writing NULL.

             

            APEX_JSON.WRITE (

              p_name IN VARCHAR2,

              p_value IN VARCHAR2,

            p_write_null IN BOOLEAN DEFAULT FALSE );

            • 3. Re: Restful services Get handler omitting column if null
              Gekko

              Thank you for the hint. I developed server side using Apex Restful Services with source type Query as you assumed correctly and the problem was when I was using DataAdapter in my code to update client table - which didn't work as columns were missing in server response.

               

              I solved it temporarily with a sort of a hack - filling null values with some other value and replacing that value on the other side with null again. But when time permits I'll look into building json using PL/SQL and APEX_JSON.Write procedure.

               

              Though I'm curious about this feature of setting request header I wrote I found for older version of ORDS - since in version 3 there obviously still exists this undocumented feature and would perhaps come handy as in my case.