Accessing web service in oracle 10g pl/sql procedures

Question

I’m using soap_api method for accessing web services in oracle. When I create add_numbers function and I execute add_numbers function then Function doesn’t execute. fires following error when calling web service in select statement

select add_numbers(2,5) from dual

error is

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 FROM!

I’m using this function and soap_api methods from this link. taken example from
http://www.oracle-base.com/articles/9i/consuming-web-services-9i.php#Top

Function for calling web services.

CREATE OR REPLACE FUNCTION add_numbers (p_int_1 IN  NUMBER,  p_int_2  IN  NUMBER)  RETURN NUMBER AS
  l_request   soap_api.t_request;
  l_response  soap_api.t_response;
  l_return    VARCHAR2(32767);  
  l_url          VARCHAR2(32767);
  l_namespace    VARCHAR2(32767);
  l_method       VARCHAR2(32767);
  l_soap_action  VARCHAR2(32767);
  l_result_name  VARCHAR2(32767);
BEGIN
  l_url         := 'http://192.168.0.75:9001/LicWebService.asmx';
  l_namespace   := 'xmlns="http://192.168.0.75:9001/"';
  l_method      := 'AddNum';
  l_soap_action := 'http://192.168.0.75:9001/AddNum';
  l_result_name := 'return';  
  l_request := soap_api.new_request(p_method => l_method,   p_namespace=> l_namespace);
  soap_api.add_parameter(p_request => l_request,p_name => 'int1',p_type => 'xsd:integer',p_value   => p_int_1);
  soap_api.add_parameter(p_request => l_request,p_name=> 'int2', p_type=> 'xsd:integer',p_value   => p_int_2);
  l_response := soap_api.invoke(p_request => l_request, p_url=> l_url,  p_action => l_soap_action);
  l_return := soap_api.get_return_value(p_response  => l_response,p_name=> l_result_name, p_namespace => NULL);
END;

Kindly suggest me where i am making mistake.

I am pretty sure that your problem was that Oracle did not process the port 9001 correctly. Did you figure out what was the solution? — Arturo Hernandez

AnBisw AnBisw · Accepted Answer · 2012-09-20T17:01:36

Either your TNS is not set up properly or the TNS service is not running. This is the reason why the UTL_HTTP api isn't working (which is used by SOAP_API).

Follow this link to troubleshoot and resolve this.

Accessing web service in oracle 10g pl/sql procedures

1 Answers