Home » Other » 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 messageGo to next message
arunrajv@yahoo.com
Messages: 8
Registered: December 2016
Junior Member
Yeah Caffiene++ I have handled the split logicaly in procedure itself. And thanks
Re: UTTL_HTTP to POST CLOB request [message #669592 is a reply to message #660932] Tue, 01 May 2018 22:47 Go to previous messageGo to next message
bman1978
Messages: 1
Registered: May 2018
Junior Member
Hi,
I'm curious to know how you handled the splitting.

I have a large XML body that I'm sending to a remote server and with the chunked encoding carriage returns are added, breaking XML tags.

utl_http.set_header (request, 'Transfer-Encoding', 'Chunked');

WHILE (offset < req_length)
  LOOP
    dbms_lob.read(q_data,amount,offset,buffer);
    dbms_output.put_line(buffer);
    utl_http.write_line(request,buffer);
    offset := offset + amount;
  END LOOP;
Re: UTTL_HTTP to POST CLOB request [message #670824 is a reply to message #669592] Sat, 28 July 2018 17:19 Go to previous messageGo to next message
sarahjones
Messages: 1
Registered: July 2018
Junior Member
Thank you
Re: UTTL_HTTP to POST CLOB request [message #673954 is a reply to message #660428] Wed, 19 December 2018 09:26 Go to previous messageGo to next message
vasanthanand
Messages: 4
Registered: February 2008
Junior Member
Hello,
Greetings,

I am working on a Cloud Application, which requires Data to be sent over REST API as their preferred data API method.
currently, the Cloud application vendor has set a Limit of 500 Records per HTTP REST Call.
We have a CLOB as similar to yours and it contains well-formed JSON more than 500 whenever this happens the Cloud application rejects and results in Failure.
I am curious How did Split the JSON into Wellformed JSON in the CLOB with Finite Number in our case say

IF Well-formed JSON Count <= 499 THEN
Start Splitting at the Correct WEll formed JSON in Chunks

END IF

Thanking you in advance for your Response.

Thanks
vasanthanand

[Updated on: Wed, 19 December 2018 09:37]

Report message to a moderator

Re: UTTL_HTTP to POST CLOB request [message #673955 is a reply to message #673954] Wed, 19 December 2018 09:53 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why do you need to split JSON when COUNT <= 499?
Previous Topic: SQL Developer Database Diff...
Next Topic: SP2-0606: Cannot create SPOOL file "password/hr_main.log"
Goto Forum:
  


Current Time: Thu Mar 28 16:57:56 CDT 2024