5 Replies Latest reply on Jan 21, 2020 7:17 PM by EJ-Egyed

    Oracle ORDS version 18.2.0.r1831332  getting ‘500 Internal Server Error’ on the POST of a JSON file

    3253886

      Dear all,

       


      I cannot do a POST of a JSON file with Oracle ORDS version 18.2.0.r1831332.

      I am a getting ‘500 Internal Server Error’.

      The handler method ‘GET’ works fine.

      Table DDL:

      CREATE TABLE DOCS_JSON

      (

        SEQ_ID NUMBER(10, 0) NOT NULL                     (sequence)

      , LOADING_DATE DATE DEFAULT sysdate

      , DOCUMENT CLOB

      ) ;

       

      Source Code:

      declare

        --

        l_blob blob;

        l_clob clob;

        l_clob_length  number;

        l_dest_offsset integer := 1;

        l_src_offsset  integer := 1;

        l_lang_context integer := dbms_lob.default_lang_ctx;

        l_warning      integer;

        l_docId        number  := 7788;

        l_error_num    number  := 9999999999;  

        --

        no_doc_inserted EXCEPTION;

        PRAGMA EXCEPTION_INIT( no_doc_inserted, -06502);

        --

      begin

        --

        l_blob := :body;

        --

        dbms_lob.createtemporary(l_clob, true);

        -- convert binary body to clob --

        dbms_lob.converttoclob

            ( dest_lob     => l_clob

            , src_blob     => l_blob

            , amount       => dbms_lob.lobmaxsize

            , dest_offset  => l_dest_offsset

            , src_offset   => l_src_offsset

            , blob_csid    => dbms_lob.default_csid

            , lang_context => l_lang_context

            , warning      => l_warning

        );

        --   

        insert into DOCS_JSON(WG_DOCUMENT) values (l_clob);

        returning seq_id into l_docId;

        commit;

        --

        :stringout := '{"doc_id" : "'||l_docId||'" }';

        :status := 200;

        --

        exception when no_doc_inserted

        -- exception when others

         :status := 400;

        --

      end;

       

      The out parameters stringoutis  string and the status is interger.

      Any hint or help wil be much appreciated.

      Regards,

      Anibal

        • 1. Re: Oracle ORDS version 18.2.0.r1831332  getting ‘500 Internal Server Error’ on the POST of a JSON file
          thatJeffSmith-Oracle

          What is shown on the server when you hit the 500? There should be a ORA or PLS error there explaining what's going on with your request failing.

           

          Also, in ords you can read in your post request body directly as a clob, use : body_text, then no need to run your conversion. It's a feature we added for 18.3

           

           

          https://www.thatjeffsmith.com/archive/2018/10/x-ords-forward-ords-response-forward-to-another-resource/

          • 2. Re: Oracle ORDS version 18.2.0.r1831332  getting ‘500 Internal Server Error’ on the POST of a JSON file
            EJ-Egyed

            I would strongly recommend upgrading to ORDS 18.3 or later as that would greatly simplify the code.  I am running Oracle 19 and ORDS 19.2 and was able to achieve what you are trying to with the following code:

             

            CREATE TABLE DOCS_JSON
            (
                SEQ_ID         NUMBER (10, 0) GENERATED ALWAYS AS IDENTITY (     START WITH 1 INCREMENT BY 1),
                LOADING_DATE   DATE DEFAULT SYSDATE,
                DOCUMENT       CLOB
            );
            
            
            BEGIN
                ORDS.ENABLE_SCHEMA (p_enabled               => TRUE,
                                    p_url_mapping_type      => 'BASE_PATH',
                                    p_url_mapping_pattern   => 'test',
                                    p_auto_rest_auth        => FALSE);
            
            
                ORDS.DEFINE_MODULE (p_module_name => 'v1', p_base_path => 'v1/');
            
            
                ORDS.define_template (p_module_name => 'v1', p_pattern => 'doc');
            
            
                ORDS.define_handler (p_module_name      => 'v1',
                                     p_pattern          => 'doc',
                                     p_method           => 'POST',
                                     p_source_type      => ORDS.source_type_plsql,
                                     p_source           => '
            DECLARE
                l_id       DOCS_JSON.SEQ_ID%TYPE;
                
                no_doc_inserted EXCEPTION;
                PRAGMA EXCEPTION_INIT( no_doc_inserted, -06502);
            BEGIN
                INSERT INTO docs_json (document)
                     VALUES (:body_text)
                  RETURNING seq_id
                       INTO l_id;
            
                owa_util.mime_header(ccontent_type  => ''application/json'');
                htp.p(''{"doc_id" : "'' || l_id || ''" }'');
                :status_code := 201;
            EXCEPTION
                when no_doc_inserted then
                    :status_code := 400;
            END;'                     ,
                                     p_items_per_page   => 0);
            
            
                COMMIT;
            END;
            

             

            As you can see, no output parameters need to be defined if using 18.3 or later.

            • The :body_text parameter is an implicit parameter introduced in ORDS 18.3 that has the contents of the request already converted to a CLOB so you do not need to worry about that.
            • HTP.P can be used to output the content of your response so no stringout parameter needs to be defined
            • The :status_code parameter is another implicit parameter introduced in ORDS 18.3 that is used to set the HTTP status code of the response.

             

            I would also recommend removing the commit from your pl/sql code as ORDS has an implicit commit when the request is finished.  Also I added a line to set the Content-Type header to JSON.

            • 3. Re: Oracle ORDS version 18.2.0.r1831332  getting ‘500 Internal Server Error’ on the POST of a JSON file
              EJ-Egyed

              I also just noticed that your insert statement looks like this:

               

                insert into DOCS_JSON(WG_DOCUMENT) values (l_clob);

               

              but the table you have defined doesn't have a column named WG_DOCUMENT.  That would also cause an error.

              • 4. Re: Oracle ORDS version 18.2.0.r1831332  getting ‘500 Internal Server Error’ on the POST of a JSON file
                3253886

                Hi,

                 

                I totally agree with your advise.

                Ii is the plan to go to ORDS versio 12.3.

                 

                I have adapted the code to simply get the body blob and do an insert.

                 

                This works now but it is like it is doing a loop and cosuming everytime 3 sequences.

                Where two (2) records are physically created.

                The first one has the blob column popuated and in the created second record the blob column is empty.

                 

                Is there any explanation and solution for this?

                 

                 

                Regards,

                Anibal

                • 5. Re: Oracle ORDS version 18.2.0.r1831332  getting ‘500 Internal Server Error’ on the POST of a JSON file
                  EJ-Egyed

                  What does the source code of your REST API look like? also, what does the DDL of your table and sequence look like?