Home » SQL & PL/SQL » Client Tools » UTTL_HTTP to POST CLOB request (ORACLE VERSION 12.1.0.2)
UTTL_HTTP to POST CLOB request [message #660428] Thu, 16 February 2017 04:48 Go to previous message
arunrajv@yahoo.com
Messages: 8
Registered: December 2016
Junior Member
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.
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;
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: SQL Developer Database Diff...
Next Topic: SP2-0606: Cannot create SPOOL file "password/hr_main.log"
Goto Forum:
  


Current Time: Thu Jul 02 10:53:33 CDT 2020