9 Replies Latest reply: Apr 16, 2012 10:56 PM by user1107506 RSS

    utl_http request and response stops when  > 32k

    user1107506
      Hi

      Can anyone help me on how to solve the issue when utl_http request and response stops when > 32k.

      Thanks in advance

      Ankith
        • 1. Re: utl_http request and response stops when  > 32k
          odie_63
          Hi,

          Please post the code you have so far.

          You have to send the request in chunks of 32k, after setting the following header :
          UTL_HTTP.set_header ( http_request, 'Transfer-Encoding', 'chunked' );
          Similarly, the response must be read using a loop and iterative calls to UTL_HTTP.read_text.
          • 2. Re: utl_http request and response stops when  > 32k
            user1107506
            Hi,

            This is the function that is called.

            create or replace
            FUNCTION utl_http_request_resp(url varchar2, requestbody clob)
            RETURN VARCHAR2 AS
            http_req utl_http.req;
            http_resp utl_http.resp;
            reqlength binary_integer;
            responsebody clob:=null;
            resplength binary_integer;
            buffer varchar2(2000);
            amount pls_integer := 2000;
            offset pls_integer := 1;
            begin
            http_req := utl_http.begin_request(url, 'POST', 'HTTP/1.1');
            utl_http.set_header(http_req, 'Content-Type', 'text/xml');
            utl_http.set_header(http_req, 'SOAPAction', 'http://oracle.com/determinations/server/10.3/rulebase/types/Assess');
            utl_http.set_header(http_req, 'Transfer-Encoding', 'chunked');
            reqlength := dbms_lob.getlength(requestbody);
            while(offset < reqlength) loop
            dbms_lob.read(requestbody, amount, offset, buffer);
            utl_http.write_text(http_req, buffer);
            offset := offset + amount;
            end loop;
            http_resp := utl_http.get_response(http_req);
            utl_http.read_text(http_resp, responsebody, 32767);
            utl_http.end_response(http_resp);
            return responsebody;
            end;
            • 3. Re: utl_http request and response stops when  > 32k
              odie_63
              You probably missed this part?
              odie_63 wrote:
              Similarly, the response must be read using a loop and iterative calls to UTL_HTTP.read_text.
              You must read the response in chunks too, and append them to the final CLOB.
              You'll have to modify the return datatype to CLOB as well.

              For example :
              http_resp := utl_http.get_response(http_req);
              eob := false; -- END-OF-BODY flag (Boolean)
              
              while not(eob)
              loop
                begin
                  utl_http.read_text(http_resp, buffer, 32767); -- buffer = VARCHAR2(32767)
                  if buffer is not null and length(buffer)>0 then
                    dbms_lob.writeappend(responsebody, length(buffer), buffer);
                  end if;
                exception when UTL_HTTP.END_OF_BODY then
                  eob := true;
                end;
              end loop;
              
              utl_http.end_response(http_resp);
              
              return responsebody;
              • 4. Re: utl_http request and response stops when  > 32k
                user1107506
                Hi,

                I made the changes and executed and was getting error

                ORA-20001: RATE_REQUEST:ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275.

                could you pls help me to solve this error.

                create or replace
                FUNCTION post_soap_chuncked(url varchar2, requestbody clob)
                RETURN VARCHAR2 AS
                http_req utl_http.req;
                http_resp utl_http.resp;
                reqlength binary_integer;
                responsebody clob:=null;
                resplength binary_integer;
                buffer varchar2(32767);
                amount pls_integer := 2000;
                offset pls_integer := 1;
                reslength binary_integer;
                eob boolean := false;
                -- buffer1 varchar2(32767);
                begin
                http_req := utl_http.begin_request(url, 'POST', 'HTTP/1.1');
                utl_http.set_header(http_req, 'Content-Type', 'text/xml');
                utl_http.set_header(http_req, 'SOAPAction', 'http://oracle.com/determinations/server/10.3/rulebase/types/Assess');
                utl_http.set_header(http_req, 'Transfer-Encoding', 'chunked');
                reqlength := dbms_lob.getlength(requestbody);
                while(offset < reqlength) loop
                dbms_lob.read(requestbody, amount, offset, buffer);
                utl_http.write_text(http_req, buffer);
                -- dbms_output.put_line('============================================');
                -- dbms_output.put_line(buffer);
                offset := offset + amount;
                end loop;
                http_resp := utl_http.get_response(http_req);
                dbms_output.put_line('After http_resp');
                while not(eob)
                loop
                begin
                utl_http.read_text(http_resp,buffer,32767);
                if buffer is not null and length(buffer) > 0 then
                dbms_lob.writeappend(responsebody, length(buffer), buffer);
                end if;
                exception
                when UTL_HTTP.END_OF_BODY THEN
                eob := true;
                end;
                end loop;
                dbms_output.put_line('After resp loop');
                utl_http.end_response(http_resp);
                return responsebody;

                /*
                utl_http.read_text(http_resp, responsebody, 32767);
                dbms_output.put_line('After http_read');
                utl_http.end_response(http_resp);
                reslength := dbms_lob.getlength(responsebody);
                dbms_output.put_line('length of resp'||reslength);
                return responsebody; */
                end;

                /*create or replace
                FUNCTION post_soap_chuncked(url varchar2, requestbody clob)
                RETURN VARCHAR2 AS
                http_req utl_http.req;
                http_resp utl_http.resp;
                reqlength binary_integer;
                responsebody clob:=null;
                resplength binary_integer;
                buffer varchar2(2000);
                amount pls_integer := 2000;
                offset pls_integer := 1;
                reslength binary_integer;

                v_len number;
                v_txt Varchar2(32767);
                l_clob clob;
                l_text VARCHAR2(32767);

                i integer :=0;

                begin
                http_req := utl_http.begin_request(url, 'POST', 'HTTP/1.1');
                utl_http.set_header(http_req, 'Content-Type', 'text/xml');
                utl_http.set_header(http_req, 'SOAPAction', 'http://oracle.com/determinations/server/10.3/rulebase/types/Assess');
                utl_http.set_header(http_req, 'Transfer-Encoding', 'chunked');
                reqlength := dbms_lob.getlength(requestbody);
                DBMS_LOB.createtemporary(responsebody, FALSE);


                -- utl_http.set_header(http_req, 'Content-Length', length(requestbody));
                -- utl_http.set_header(http_req, 'Download', ''); -- header requirements of particular web service


                while(offset < reqlength) loop
                dbms_lob.read(requestbody, amount, offset, buffer);
                utl_http.write_text(http_req, buffer);
                -- dbms_output.put_line('============================================');
                -- dbms_output.put_line(buffer);
                offset := offset + amount;
                end loop;

                responsebody := http2clob('http://devopacluster.cnanational.com:28080/zseries-ods-server/custom/assess/soap/generic/10.3/Rating_Engine');
                i:=0;
                loop
                dbms_output.put_line(substr(responsebody,1+ i*255,250));
                i:= i+1;
                if i*250> length(responsebody)
                then
                exit;
                end if;
                end loop;


                return responsebody; */
                /* http_resp := utl_http.get_response(http_req);
                BEGIN
                LOOP
                UTL_HTTP.read_text(http_resp, l_text, 32766);
                DBMS_LOB.writeappend (responsebody, LENGTH(l_text), l_text);
                END LOOP;
                EXCEPTION
                WHEN UTL_HTTP.end_of_body THEN
                l_clob :=responsebody;
                -- return responsebody;
                UTL_HTTP.end_response(http_resp);
                return l_clob ;

                --return responsebody;    
                DBMS_LOB.freetemporary(responsebody);


                END;
                EXCEPTION
                WHEN OTHERS THEN
                UTL_HTTP.end_response(http_resp);
                -- return responsebody;
                DBMS_LOB.freetemporary(responsebody);
                RAISE; */




                /* utl_http.get_header_by_name(http_resp, 'Content-Length', v_len, 1); -- Obtain the length of the response
                FOR i in 1..CEIL(v_len/32767) -- obtain response in 32K blocks just in case it is greater than 32K
                LOOP
                utl_http.read_text(http_resp, v_txt, case when i < CEIL(v_len/32767) then 32767 else mod(v_len,32767) end);
                responsebody := responsebody || v_txt; -- build up CLOB
                END LOOP;
                utl_http.end_response(http_resp); */
                --return responsebody;

                /* dbms_output.put_line('After http_resp');

                utl_http.read_text(http_resp, responsebody, 32767);
                dbms_output.put_line('After http_read');
                utl_http.end_response(http_resp);
                reslength := dbms_lob.getlength(responsebody);
                dbms_output.put_line('length of resp'||reslength); */
                -- return responsebody;
                --end;

                Thanks
                Ankith
                • 5. Re: utl_http request and response stops when  > 32k
                  user1107506
                  Hi,

                  I made the changes and executed and was getting error

                  ORA-20001: RATE_REQUEST:ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275.

                  could you pls help me to solve this error.

                  create or replace
                  FUNCTION post_soap_chuncked(url varchar2, requestbody clob)
                  RETURN VARCHAR2 AS
                  http_req utl_http.req;
                  http_resp utl_http.resp;
                  reqlength binary_integer;
                  responsebody clob:=null;
                  resplength binary_integer;
                  buffer varchar2(32767);
                  amount pls_integer := 2000;
                  offset pls_integer := 1;
                  reslength binary_integer;
                  eob boolean := false;
                  -- buffer1 varchar2(32767);
                  begin
                  http_req := utl_http.begin_request(url, 'POST', 'HTTP/1.1');
                  utl_http.set_header(http_req, 'Content-Type', 'text/xml');
                  utl_http.set_header(http_req, 'SOAPAction', 'http://oracle.com/determinations/server/10.3/rulebase/types/Assess');
                  utl_http.set_header(http_req, 'Transfer-Encoding', 'chunked');
                  reqlength := dbms_lob.getlength(requestbody);
                  while(offset < reqlength) loop
                  dbms_lob.read(requestbody, amount, offset, buffer);
                  utl_http.write_text(http_req, buffer);
                  -- dbms_output.put_line('============================================');
                  -- dbms_output.put_line(buffer);
                  offset := offset + amount;
                  end loop;
                  http_resp := utl_http.get_response(http_req);
                  dbms_output.put_line('After http_resp');
                  while not(eob)
                  loop
                  begin
                  utl_http.read_text(http_resp,buffer,32767);
                  if buffer is not null and length(buffer) > 0 then
                  dbms_lob.writeappend(responsebody, length(buffer), buffer);
                  end if;
                  exception
                  when UTL_HTTP.END_OF_BODY THEN
                  eob := true;
                  end;
                  end loop;
                  dbms_output.put_line('After resp loop');
                  utl_http.end_response(http_resp);
                  return responsebody;

                  end;
                  • 6. Re: utl_http request and response stops when  > 32k
                    odie_63
                    Sorry, I forgot to mention that the piece of code works with a temporary CLOB.

                    Use this before reading the response :
                    dbms_lob.createtemporary(responsebody, true);
                    You'll have to free it later, so either copy the temp CLOB in a final return variable and free it in the same function :
                    dbms_lob.freetemporary(responsebody);
                    or, the calling process will have to free the temp CLOB after usage.
                    • 7. Re: utl_http request and response stops when  > 32k
                      user1107506
                      Hi,

                      thanks for helping.

                      Included DBMS_LOB.CREATETEMPORARY(responsebody,true); and DBMS_LOB.freetemporary(responsebody);

                      now giving error ORA-6502 PL/SQL numeric or value error

                      its throwing error on return(responsebody);



                      create or replace

                      FUNCTION post_soap_chuncked(url varchar2, requestbody clob)

                      RETURN VARCHAR2 AS

                      http_req utl_http.req;

                      http_resp utl_http.resp;

                      reqlength binary_integer;

                      responsebody clob:=null;

                      resplength binary_integer;

                      buffer varchar2(32767);

                      amount pls_integer := 2000;

                      offset pls_integer := 1;

                      reslength binary_integer;

                      eob boolean := false;

                      -- buffer1 varchar2(32767);

                      begin

                      http_req := utl_http.begin_request(url, 'POST', 'HTTP/1.1');

                      utl_http.set_header(http_req, 'Content-Type', 'text/xml');

                      utl_http.set_header(http_req, 'SOAPAction', 'http://oracle.com/determinations/server/10.3/rulebase/types/Assess');

                      utl_http.set_header(http_req, 'Transfer-Encoding', 'chunked');

                      reqlength := dbms_lob.getlength(requestbody);

                      while(offset < reqlength) loop

                      dbms_lob.read(requestbody, amount, offset, buffer);

                      utl_http.write_text(http_req, buffer);

                      -- dbms_output.put_line('============================================');

                      -- dbms_output.put_line(buffer);

                      offset := offset + amount;

                      end loop;

                      DBMS_LOB.CREATETEMPORARY(responsebody,true);

                      http_resp := utl_http.get_response(http_req);

                      dbms_output.put_line('After http_resp');

                      while not(eob)

                      loop

                      begin

                      utl_http.read_text(http_resp,buffer,32767);

                      if buffer is not null and length(buffer) > 0 then

                      dbms_lob.writeappend(responsebody, length(buffer), buffer);

                      end if;

                      exception

                      when UTL_HTTP.END_OF_BODY THEN

                      eob := true;

                      end;

                      end loop;

                      dbms_output.put_line('After resp loop');

                      utl_http.end_response(http_resp);

                      return responsebody;

                      DBMS_LOB.freetemporary(responsebody);


                      end;
                      • 8. Re: utl_http request and response stops when  > 32k
                        odie_63
                        FUNCTION post_soap_chuncked(url varchar2, requestbody clob)

                        RETURN VARCHAR2 AS
                        As said earlier, you're returning a CLOB now, not a VARCHAR2.
                        You must change the return datatype.
                        return responsebody;

                        DBMS_LOB.freetemporary(responsebody);
                        DBMS_LOB.freetemporary won't be called if you place it there.
                        Again, as explained above, you have to either free the temp CLOB in the same function (post_soap_chuncked) but that means you must copy it first to another variable and return the latter instead, or (better) let the caller free the CLOB itself.
                        • 9. Re: utl_http request and response stops when  > 32k
                          user1107506
                          Hi

                          Thanks a lot. that workded fine. I have one more issue. Extracting of data from xml takes almost an hour for 2.5M data.

                          Ankith.