6 Replies Latest reply on May 20, 2019 3:04 AM by dmarinov87

    Problem with value length when sending JSON over ORDS

    dmarinov87

      hi to everyone!

       

      I'm facing a problem with sending JSON over the ORDS.

       

      I'm sending body payload in structure as shown:

      {
          "DOCUMENT":{
              "CREATED_BY":"test",
              "SHORT_NOTE":"Test document",
              "FILENAME":"test.jpeg",
              "FILETYPE":"application/jpeg",
              "BASE64_DOC":"around 80k base64 encoded string",
          }
      }
      

       

      And I have a problem with BASE64_DOC because it has a large value. For now, I found out that if I send BASE64_DOC in 4k length it works fine.

       

      Does anyone know what is a problem and how I can send value which is larger than 4k over ORDS?

       

      We are using:

      Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

      PL/SQL Release 12.2.0.1.0 - Production

      "CORE    12.2.0.1.0    Production"

      TNS for Linux: Version 12.2.0.1.0 - Production

      NLSRTL Version 12.2.0.1.0 - Production

      ORDS v18.4

       

      Any help would be appreciated!

       

      Best regards to everyone

      Dragan

        • 1. Re: Problem with value length when sending JSON over ORDS
          Olafur T

          Hi,

           

          What exactly is your error?

           

          I tried this with a 360kb base64 and it worked just fine.

           

          POST handler:

          declare
            l_clob clob;
          begin
            apex_json.parse(:body_text);
            l_clob := apex_json.get_clob('DOCUMENT.BASE64_DOC');
            htp.prn('Size: ' || length(l_clob) || '<br>');
            htp.prn('Snippet: ' || substr(l_clob,1,20));
          end;
          

           

          I got a 200 OK and the output:

          Size: 368384

          Snippet: PD94bWwgdmVyc2lvbj0i

           

          Regards

          Oli

          1 person found this helpful
          • 2. Re: Problem with value length when sending JSON over ORDS
            dmarinov87

            Hi Oli,

             

            we don't get any error message, just the BASE64_DOC is stored as null value so we can't use it in further operations and that problem occurred only if JSON is sended over ORDS if I take body payload and run POST procedure on DB it works fine with any length of value from BASE64_DOC key.

             

            Best regards,

            Dragan

            • 3. Re: Problem with value length when sending JSON over ORDS
              Olafur T

              Try to debug this by saving the POST body in a table to see what you are getting.

               

              i.e. create table post_test(d date, data clob); -- or blob if you are in an older version of ords and don't have :body_text

               

              I'm guessing that ORDS is getting an empty or malformed POST request.

               

              begin
                ORDS.DEFINE_HANDLER(p_module_name    => 'post_test',
                                    p_pattern        => 'test2',
                                    p_method         => 'POST',
                                    p_source_type    => 'plsql/block',
                                    p_items_per_page => 0,
                                    p_mimes_allowed  => '',
                                    p_comments       => null,
                                    p_source         => 'begin
               insert into post_test values (sysdate, :body_text);
               commit;
               owa_util.status_line(200, '''', false);
               owa_util.mime_header(''text/plain'', true);
              end;');
                commit;
              end;
              

               

              The thing is to debug the correct location. i.e. Is the client not sending correct data, or is ORDS not parsing it correctly or is apex_json/json_table/etc. messing up..

               

              for multilevel json strings you can not use automatic parameter casting and need to use apex_json or json_table to parse it.

               

              Oli

              • 4. Re: Problem with value length when sending JSON over ORDS
                dmarinov87

                I tried that:

                 

                insert into table json_temp (id, json) values (temp_seq.nextval, :body);
                

                 

                and also

                 

                insert into table json_tmep_c values (temp_seq.nextval, :body_text)
                

                 

                and result is the same, if length of value of BASE64_DOC is lower than 4001 characters it insert value into table (I can select it out with

                 

                select jt.id jtid, length(jt.json.BASE64_DOC) len, jt.json.BASE64_DOC base
                from json_temp jt
                order by id desc
                

                 

                ) but if length is higher than 4k characters then I get null as the result from select above (also if I run json_temp_c instead of json_temp table).

                 

                But it is interesting that if I parse a payload directly (not to send it as a parameter to one procedure from a package I get value for BASE64_DOC and it does not matter if the length is less or higher than 4k characters).

                 

                So if I have

                 

                declare
                    lcBase64Doc clob;
                    
                begin 
                    insert into api_schema.json_temp(json) values (:body);
                    commit;
                    apex_json.parse(:body_text);  
                    lcBase64Doc := apex_json.get_clob('DOCUMENT.BASE64_DOC');  
                    htp.prn('Size: ' || length(lcBase64Doc)');
                end;
                

                 

                as code into my POST service I get:

                - if I'm sending less than 4001 characters (in base64_doc) all from a payload is inserted and also parsing is working (in package or direct as in code upper)

                - if I'm sending more than 4k characters (in base64_doc) it inserts all from a payload except value for base64_doc and parsing for BASE64_DOC is working only if I parse it directly (not to send it into a package procedure as

                 

                api_pkg.p_post_doc(:body, :body_text, :status, :result); 
                

                 

                all other values I can parse from a package procedure).

                 

                Regards,

                Dragan

                • 5. Re: Problem with value length when sending JSON over ORDS
                  Olafur T

                  Wow,

                   

                  That is really weird, can't remember that was a problem when I was running 18.4, it definitely isn't in 19.1. ORDS decouples the :body/:body_text as soon as you reference it. (can only reference it once).

                   

                  In your POST handler can you try to move the :body_text to the declaration section? Just to make sure that isn't happening.

                   

                  declare
                  l_body clob := :body_text;
                  begin
                    .... use l_body
                  end;
                  

                   

                  Regards

                  Oli

                  • 6. Re: Problem with value length when sending JSON over ORDS
                    dmarinov87

                    Olafur T wrote:

                     

                    Wow,

                     

                    That is really weird, can't remember that was a problem when I was running 18.4, it definitely isn't in 19.1. ORDS decouples the :body/:body_text as soon as you reference it. (can only reference it once).

                     

                    In your POST handler can you try to move the :body_text to the declaration section? Just to make sure that isn't happening.

                     

                    1. declare
                    2. l_bodyclob:=:body_text;
                    3. begin
                    4. ....usel_body
                    5. end;

                     

                    Regards

                    Oli

                    Hi Oli,

                    I have solved the problem. You helped me with writings (I bolded up the part which was really helping in your previous post).

                     

                    Best regards,

                    Dragan