Apache Giving HTTP 505 Error While Contacting it through Oracle database

[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.



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;

/