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 next 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;
Re: UTTL_HTTP to POST CLOB request [message #660471 is a reply to message #660428] Fri, 17 February 2017 13:54 Go to previous messageGo to next message
Caffeine+
Messages: 14
Registered: February 2017
Junior Member
32767 is the PL/SQL max string limit so it applies to UTL_HTTP. Chunking it is a must:
DECLARE
              v_doc_fin CLOB := '[' || RPAD('X',32766,'X') || RPAD('X',32767,'X') ||']';
              req utl_http.req;
              res utl_http.resp;
              url VARCHAR2(1900) := 'http://127.0.0.1:19255/twitter/tweet/1';
              v_value varchar2(4000); 
              vchunkdata varchar2(2000);
              v_req_length number;
              buffer varchar2(32767);
              offset number := 1;
              amount number :=32767;
              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);
                          [b]req := utl_http.begin_request(url, 'POST','HTTP/1.1');
                          utl_http.set_header(req, 'Content-Length', v_req_length);[/b]
                          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');
                          while(offset < v_req_length) 
                          loop
                             dbms_lob.read(v_doc_fin, amount, offset, buffer);
                             UTL_HTTP.WRITE_TEXT(r    => req, data => buffer);
                             offset := offset + amount;
                          end loop;
                          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;


It looks like you tried a chunking loop as you have the BUFFER, OFFSET and AMOUNT variables ready to go. Are you just curious if it could be done without chunking?

FYI that you'll need to set the Content-Length header to chuck the results (you may already now that) and you have a space before the "HTTP/1.1" protocol setting.
Re: UTTL_HTTP to POST CLOB request [message #660473 is a reply to message #660471] Fri, 17 February 2017 23:04 Go to previous messageGo to next message
arunrajv@yahoo.com
Messages: 8
Registered: December 2016
Junior Member
Thank you very much For the reply Caffiene+,
Yes, I have tried with the block,
"
while(offset < v_req_length)
loop
dbms_lob.read(v_doc_fin, amount, offset, buffer);
UTL_HTTP.WRITE_TEXT(r => req, data => buffer);
offset := offset + amount;
end loop;
"
The problem I faced is that, the JSON which I am posting should be complete syntax (length vary for each complete syntax)whereas this chunking cuts syntax makes it incomplete. So the requests getting failed.

Though I have handled the split in the procedure itself which increases the number of http call.
Is there any package that we can look for to POST CLOB value from Oracle DB.
Re: UTTL_HTTP to POST CLOB request [message #660544 is a reply to message #660473] Mon, 20 February 2017 15:54 Go to previous messageGo to next message
Caffeine+
Messages: 14
Registered: February 2017
Junior Member
I'm interested in which back-end HTTP server is being used if you are free to divulge. Most standard HTTP servers obey the Chunked and Content-Length header attributes. It sounds like the one in use is ignoring the explicit length and proceeding to process the partial transmission. You initial example didn't include setting the Content-Length header attribute so I would ask you to see if it's has been left out in the latest test.
Re: UTTL_HTTP to POST CLOB request [message #660550 is a reply to message #660544] Tue, 21 February 2017 01:04 Go to previous messageGo to next message
arunrajv@yahoo.com
Messages: 8
Registered: December 2016
Junior Member
The http server is Apache. I have included the Content-length header as well, the code I have tested as below. Now getting

"ORA-29273: HTTP request failed
ORA-29270: too many open HTTP requests
"
"ORA-06512: at "SYS.UTL_HTTP", line 368
ORA-06512: at "SYS.UTL_HTTP", line 1118
ORA-06512: at "COMPLIANCE.PROC_ES_JSON_GEN_V2", line 49 "
line 49 is 'req := utl_http.begin_request(url, 'POST','HTTP/1.1');'

PROCEDURE http_call is
              req utl_http.req;
              res utl_http.resp;
              url VARCHAR2(4000) := 'http://127.0.0.1:19255/twitter/tweet';
              v_value varchar2(4000); 
              v_req_length number;
              v_resp CLOB;
              buffer varchar2(2000);
              offset number := 1;
              amount number :=1024;           
              utl_err varchar2(1000);
              
              BEGIN
              
                      IF v_doc_fin IS NOT NULL THEN
                          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, 'content-type', 'application/json;charset=UTF-8');
                         -- UTL_HTTP.SET_HEADER ( req, 'Content-Length', LENGTH ( v_doc_fin ) );
                          utl_http.set_header(req, 'Content-Length', v_req_length);
                          UTL_HTTP.set_header (req, 'Transfer-Encoding', 'chunked' );
                          UTL_HTTP.SET_BODY_CHARSET('UTF-8');
 
                          while(offset < v_req_length) 
                          loop
                             dbms_lob.read(v_doc_fin, amount, offset, buffer);
                             UTL_HTTP.WRITE_TEXT(r    => req, data => buffer);
                             offset := offset + amount;
                          end loop;
                          
                          
                          --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);
                                        
                                        if v_value is null then
                                        v_resp:=v_value;
                                        else
                                        v_resp := v_resp||v_value;
                                        end if;
                                        
                                      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);  
                                    END;
                        END IF;
                        p_doc_fin := v_doc_fin;
                        p_resp_out := v_resp;
               --DBMS_OUTPUT.PUT_LINE (v_doc_fin);
               --DBMS_OUTPUT.PUT_LINE (v_resp);
               
                  EXCEPTION
                  WHEN OTHERS THEN
                  UTL_HTTP.END_RESPONSE(res); 
                  utl_err:= Utl_Http.Get_Detailed_Sqlerrm;
              END;
Re: UTTL_HTTP to POST CLOB request [message #660845 is a reply to message #660550] Mon, 27 February 2017 12:40 Go to previous messageGo to next message
Caffeine+
Messages: 14
Registered: February 2017
Junior Member
I'll still try to help but it looks like you have resolved the original problem, right?

Re: UTTL_HTTP to POST CLOB request [message #660932 is a reply to message #660845] Thu, 02 March 2017 00:25 Go to previous message
arunrajv@yahoo.com
Messages: 8
Registered: December 2016
Junior Member
Yeah Caffiene++ I have handled the split logicaly in procedure itself. And thanks
Previous Topic: PL/SQL Developer vs. Oracle Discoverer
Next Topic: Oracle connection pool issue in Devart
Goto Forum:
  


Current Time: Tue Nov 21 20:02:19 CST 2017

Total time taken to generate the page: 0.08366 seconds