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 |
|
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 |
|
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 #660550 is a reply to message #660544] |
Tue, 21 February 2017 01:04 |
|
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 #673954 is a reply to message #660428] |
Wed, 19 December 2018 09:26 |
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
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:29:03 CDT 2024
|