1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Uttl_http to post clob request

Discussion in 'SQL PL/SQL' started by Arunraj, Feb 16, 2017.

  1. Arunraj

    Arunraj Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    INDIA
    I have been trying to POST CLOB (JSON script) into an API , though the datatype clob did not cause problem value with VARCHAR2 limit POSTS happening as it should be whereas when the value exceeds the varchar2 limit , I am getting "numeric or value error".
    Any Help to succed to post the CLOB value without CHUNKING would be thankful.
    Below is the code for reference.
    Code (SQL):
    PROCEDURE http_call IS
                  req utl_http.req;
                  res utl_http.resp;
                  url VARCHAR2(1900) := 'http://XX.XX.XX.XX:YYYY/_POSTCLOB';
                  v_value varchar2(4000);
                  vchunkdata varchar2(2000);
                  v_req_length NUMBER;
                  buffer varchar2(32000);
                  offset NUMBER := 1;
                  amount NUMBER :=32000;
                 
                  utl_err varchar2(1000);
                 
                  BEGIN
                 
                          IF v_doc_fin IS NOT NULL THEN --v_doc_fin is JSON DOC of CLOB data type from a procedure
                              v_req_length := DBMS_LOB.getlength (v_doc_fin) ;
                              dbms_output.put_line (v_req_length);
                             
                              req := utl_http.begin_request(url, 'POST',' HTTP/1.1');
                              utl_http.set_header(req, 'user-agent', 'mozilla/4.0');
                              utl_http.set_header(req, 'content-type', 'application/json;charset=UTF-8');
                              UTL_HTTP.set_header (req, 'Transfer-Encoding', 'chunked' );
                              UTL_HTTP.SET_BODY_CHARSET('UTF-8');

                              --UTL_HTTP.WRITE_RAW (r    => req, data => UTL_RAW.CAST_TO_RAW(v_doc_fin));
                             
                              res := utl_http.get_response(req);
                                        BEGIN
                                          LOOP
                                            utl_http.read_line(res, v_value);
                                            dbms_output.put_line(v_value);
                                          END LOOP;
                                          utl_http.end_response(res);
                                       
                                        EXCEPTION
                                        WHEN utl_http.end_of_body THEN
                                          utl_http.end_response(res);
                                        WHEN UTL_HTTP.TOO_MANY_REQUESTS THEN
                                        UTL_HTTP.END_RESPONSE(res);
                                        WHEN OTHERS THEN
                                        dbms_output.put_line(Utl_Http.Get_Detailed_Sqlerrm);
                                        dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK);
                                        dbms_output.put_line(DBMS_UTILITY.format_error_backtrace);
                                        dbms_output.put_line(DBMS_UTILITY.format_call_stack);
                                        END;
                            END IF;
                 
                      EXCEPTION
                      WHEN OTHERS THEN
                      UTL_HTTP.END_RESPONSE(res);
                      utl_err:= Utl_Http.Get_Detailed_Sqlerrm;
                 


                  END;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    681
    Likes Received:
    137
    Trophy Points:
    830
    Location:
    Russian Federation
    You have to send the request in chunks of 32k,
    The response must be read using a loop and iterative calls to UTL_HTTP.read_text.
    N.B.
    Use this before reading the response : dbms_lob.createtemporary(responsebody, true)
     
    Last edited: Feb 16, 2017
    Arunraj likes this.
  3. Arunraj

    Arunraj Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    INDIA
    Thanks krasnoslobodtsev_si.
    I have handled the split of POSTING syntax in procedure level, since chunking splits are uneven and api throws error.