You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Ankur Gupta <gu...@gmail.com> on 2015/10/08 10:38:13 UTC
Apache Giving HTTP 505 Error While Contacting it through Oracle database
This is my SMS sending procedure where mobile number and body of msg is
passed.
Then concatenating all these values a URL is formed .
URL hit to apache server where .jsp page is made to send SMS via third
vendor.
This apache server is just a mediator between database and third vendor
Problem is that when the same URL is accessed through browser then page
runs but when same thing is accessed by the procedure then
*DBMS_OUTPUT.PUT_LINE*('HTTP response status CODE: ' || resp.status_code);
gives ‘505’ error and UTL_HTTP.READ_TEXT(resp,v_msg,null);
Gives end of body reached.
Ex of url :-
http://10.xxx.xx.xx:xxxx/examples/trysms.jsp?mobileNumber=99xxxxxxxx&message=HELLO
<http://10.192.11.99:8080/examples/trysms.jsp?mobileNumber=99xxxxxxxx&message=HELLO>
WORLD
CREATE OR REPLACE Procedure CHANNELG.SMS_headers_V1
(
mob in varchar2,
msgg in varchar2
)
AS
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
name varchar2(256);
value varchar2(5000);
value1 varchar2(1024);
v_msg varchar2(32767);
output_table *DBMS_OUTPUT.CHARARR*;
num_lines number := 500;
URL varchar2(500):='http://10.xxx.xx.xx:xxxx
/examples/xxxx.jsp?mobileNumber='||mob||'&message='||msgg;
l_clob clob;
id number;
BEGIN
select nvl(max(ID),0)+1 into id from SMS_STATUS;
insert into SMS_STATUS(ID,MOBILE_NUMBER,SENT_STATUS) values(id,mob,'Y'
);
*DBMS_LOB.CREATETEMPORARY*(l_clob,false);
UTL_HTTP.SET_PROXY('http://10.xxx.xx.xx:xxxx);
req := UTL_HTTP.BEGIN_REQUEST(URL,'POST','HTTP/1.1');
*--UTL_HTTP.SET_HEADER(req,'Content-Type','text/html;charset=ISO-8859-1');*
UTL_HTTP.SET_HEADER(req,'User-Agent', 'Mozilla/4.76');
UTL_HTTP.SET_HEADER(req,'Content-Length','0');
resp:= UTL_HTTP.GET_RESPONSE(req);
*DBMS_OUTPUT.PUT_LINE*('HTTP response status CODE: ' || resp.status_code
);
*DBMS_OUTPUT.PUT_LINE*('HTTP RESPONSE reason Pharse: ' || resp.
reason_phrase);
for i in 1..UTL_HTTP.GET_HEADER_COUNT(resp)
LOOP
UTL_HTTP.GET_HEADER(resp,i,name,value);
*DBMS_OUTPUT.PUT_LINE*(name || ':' || value);
END LOOP;
UTL_HTTP.READ_TEXT(resp,v_msg,null);
*DBMS_OUTPUT.PUT_LINE*('v_msg-' || v_msg);
*DBMS_LOB.WRITEAPPEND*(l_clob,length(v_msg),v_msg);
*DBMS_OUTPUT.GET_LINES*(output_table,num_lines);
DELETE FROM CLOB_TEST;
insert into clob_test values(l_clob);
select regexp_substr(substr(data,108,35),'[^,]+',1,3) INTO value1 from
clob_test;
update SMS_STATUS set DELIVERY_STATUS= value1 where ID=id and mobile_number
= mob ;
*--DBMS_OUTPUT.PUT_LINE('LENGTH OF MSG ' || length(v_msg));*
*--value1 := UTL_HTTP.REQUEST(URL);*
*--DBMS_OUTPUT.PUT_LINE('value' || value1);*
UTL_HTTP.END_RESPONSE(resp);
END;
/
Re: Apache Giving HTTP 505 Error While Contacting it through Oracle
database
Posted by Felix Schumacher <fe...@internetallee.de>.
Am 08.10.2015 um 10:38 schrieb Ankur Gupta:
> This is my SMS sending procedure where mobile number and body of msg is
> passed.
>
> Then concatenating all these values a URL is formed .
>
> URL hit to apache server where .jsp page is made to send SMS via third
> vendor.
>
> This apache server is just a mediator between database and third vendor
>
> Problem is that when the same URL is accessed through browser then page
> runs but when same thing is accessed by the procedure then
> *DBMS_OUTPUT.PUT_LINE*('HTTP response status CODE: ' || resp.status_code);
> gives ‘505’ error and UTL_HTTP.READ_TEXT(resp,v_msg,null);
>
> Gives end of body reached.
So you are basically asking, how to connect from your pl/sql procedure
within oracle to tomcat via a proxy?
I have never done such a thing, but I would look at a few things.
Do the requests reach tomcat at all?
If so, are there any errors logged?
Can you find the requests in your proxy logs?
And since http status 505 points to a http-version mismatch, I would try
to omit it from BEGIN_REQUEST (defaults to null).
Regards,
Felix
>
>
>
> Ex of url :-
> http://10.xxx.xx.xx:xxxx/examples/trysms.jsp?mobileNumber=99xxxxxxxx&message=HELLO
> <http://10.192.11.99:8080/examples/trysms.jsp?mobileNumber=99xxxxxxxx&message=HELLO>
> WORLD
>
>
>
> CREATE OR REPLACE Procedure CHANNELG.SMS_headers_V1
>
> (
>
> mob in varchar2,
>
> msgg in varchar2
>
> )
>
> AS
>
> req UTL_HTTP.REQ;
>
> resp UTL_HTTP.RESP;
>
> name varchar2(256);
>
> value varchar2(5000);
>
> value1 varchar2(1024);
>
> v_msg varchar2(32767);
>
> output_table *DBMS_OUTPUT.CHARARR*;
>
> num_lines number := 500;
>
> URL varchar2(500):='http://10.xxx.xx.xx:xxxx
> /examples/xxxx.jsp?mobileNumber='||mob||'&message='||msgg;
>
> l_clob clob;
>
> id number;
>
> BEGIN
>
> select nvl(max(ID),0)+1 into id from SMS_STATUS;
>
> insert into SMS_STATUS(ID,MOBILE_NUMBER,SENT_STATUS) values(id,mob,'Y'
> );
>
> *DBMS_LOB.CREATETEMPORARY*(l_clob,false);
>
> UTL_HTTP.SET_PROXY('http://10.xxx.xx.xx:xxxx);
>
> req := UTL_HTTP.BEGIN_REQUEST(URL,'POST','HTTP/1.1');
>
>
> *--UTL_HTTP.SET_HEADER(req,'Content-Type','text/html;charset=ISO-8859-1');*
>
> UTL_HTTP.SET_HEADER(req,'User-Agent', 'Mozilla/4.76');
>
> UTL_HTTP.SET_HEADER(req,'Content-Length','0');
>
> resp:= UTL_HTTP.GET_RESPONSE(req);
>
> *DBMS_OUTPUT.PUT_LINE*('HTTP response status CODE: ' || resp.status_code
> );
>
> *DBMS_OUTPUT.PUT_LINE*('HTTP RESPONSE reason Pharse: ' || resp.
> reason_phrase);
>
> for i in 1..UTL_HTTP.GET_HEADER_COUNT(resp)
>
> LOOP
>
> UTL_HTTP.GET_HEADER(resp,i,name,value);
>
> *DBMS_OUTPUT.PUT_LINE*(name || ':' || value);
>
> END LOOP;
>
> UTL_HTTP.READ_TEXT(resp,v_msg,null);
>
> *DBMS_OUTPUT.PUT_LINE*('v_msg-' || v_msg);
>
> *DBMS_LOB.WRITEAPPEND*(l_clob,length(v_msg),v_msg);
>
> *DBMS_OUTPUT.GET_LINES*(output_table,num_lines);
>
> DELETE FROM CLOB_TEST;
>
> insert into clob_test values(l_clob);
>
> select regexp_substr(substr(data,108,35),'[^,]+',1,3) INTO value1 from
> clob_test;
>
> update SMS_STATUS set DELIVERY_STATUS= value1 where ID=id and mobile_number
> = mob ;
>
> *--DBMS_OUTPUT.PUT_LINE('LENGTH OF MSG ' || length(v_msg));*
>
> *--value1 := UTL_HTTP.REQUEST(URL);*
>
> *--DBMS_OUTPUT.PUT_LINE('value' || value1);*
>
> UTL_HTTP.END_RESPONSE(resp);
>
> END;
>
> /
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org