This forum is currently locked. You can't register or post questions at this time. (read more)

UTL_HTTP PROBLEM

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

charlibraun
Member
Posts: 11
Joined: Tue Apr 30, 2013 3:11 pm

UTL_HTTP PROBLEM

Postby charlibraun » Thu May 09, 2013 11:40 am

I have tried an example from

http://www.oracle-base.com/articles/9i/ ... ces-9i.php

soap_api and add_numbers

But doesnt works

In invoke function from soap_api code I include the proxy

utl_http.set_proxy( '172.16.0.8:8080' );

and then continue soap_api code...

l_http_request := UTL_HTTP.begin_request(p_url, 'POST','HTTP/1.1');

When i execute add_numbers i have this errors

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP",line 1029
ORA-12541: TNS:no listener
ORA-06512: at "dbtest.SOAP_API",line 144
ORA-06512: at "dbtest.ADD_NUMBERS", line 34


but i dont unsertand why i can execute

select utl_http.request('http://www.google.com/','172.16.0.8:8080') from dual;

if i try to execute

select utl_http.request('http://www.google.com/') from dual;

it return the same error
I have tried an example from

http://www.oracle-base.com/articles/9i/ ... ces-9i.php

soap_api and add_numbers

But doesnt works

In invoke function from soap_api code I include the proxy

utl_http.set_proxy( '172.16.0.8:8080' );

and then continue soap_api code...

l_http_request := UTL_HTTP.begin_request(p_url, 'POST','HTTP/1.1');

When i execute add_numbers i have this errors

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP",line
ORA-12541: TNS:no listener


it seems that utl_http doesnt set the proxy... ¿?


INVOKE FUNCTION OF SOAP API with proxy set
-------------------------------------
-- ---------------------------------------------------------------------
FUNCTION invoke(p_request IN OUT NOCOPY t_request,
p_url IN VARCHAR2,
p_action IN VARCHAR2)
RETURN t_response AS
-- ---------------------------------------------------------------------
l_envelope VARCHAR2(32767);
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_response t_response;

--// these variables need to be set if web access
--// is via a proxy server
proxyServer varchar2(50) := '172.16.0.8:8080';
--proxyUser varchar2(20) default null;
--proxyPass varchar2(20) default null;
proxyURL varchar2(4000);

BEGIN
generate_envelope(p_request, l_envelope);
show_envelope(l_envelope, 'Request');


dbms_output.put_line (utl_http.get_proxy();

--// configure for web proxy access if applicable
if proxyServer is not null then
/*proxyURL := 'http://'||proxyServer;
if (proxyUser is not null) and (proxyPass is not null) then
proxyURL := Replace( proxyURL, 'http://', 'http://'||proxyUser||':'||proxyPass||'@' );
end if;*/
utl_http.set_proxy( proxyURL );
end if;


l_http_request := UTL_HTTP.begin_request(p_url, 'POST','HTTP/1.1');
UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml');
UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_envelope));
UTL_HTTP.set_header(l_http_request, 'SOAPAction', p_action);
UTL_HTTP.write_text(l_http_request, l_envelope);
l_http_response := UTL_HTTP.get_response(l_http_request);
UTL_HTTP.read_text(l_http_response, l_envelope);
UTL_HTTP.end_response(l_http_response);
show_envelope(l_envelope, 'Response');
l_response.doc := XMLTYPE.createxml(l_envelope);
l_response.envelope_tag := p_request.envelope_tag;
l_response.doc := l_response.doc.extract('/'||l_response.envelope_tag||':Envelope/'||l_response.envelope_tag||':Body/child::node()',
'xmlns:'||l_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/"');
check_fault(l_response);
RETURN l_response;
END;

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: UTL_HTTP PROBLEM

Postby Tim... » Thu May 09, 2013 1:18 pm

Hi.

Sorry. I've never considered the proxy because I don't work behind one, so I have no idea if this is a limitation or whether there is a way to make it work.

I think your best bet is ignore all the SOAP stuff and ask the question of Oracle support. They will be able to tell you if it is a limitation or not. This problem isn't after all about my code. It's about how UTL_HTTP works with proxies. If you just describe something lie this.

Code: Select all

Works:
select utl_http.request('http://www.google.com/','172.16.0.8:8080') from dual;

Fails:
EXEC utl_http.set_proxy( '172.16.0.8:8080' );
select utl_http.request('http://www.google.com/') from dual;


That should be enough to get the ball rolling.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests

cron