Tags: call, calling, database, error, function, hii, mysql, oracle, point, sql, url, via, webservice

Calling a Webservice via a Function in PL/SQL

On Database » Oracle

23,137 words with 14 Comments; publish: Wed, 13 Feb 2008 13:58:00 GMT; (25078.13, « »)

Hi

I am trying to call a webservice from a function, but i am getting an error, i am not sure that i am using the correct url for the end point or actions

Below is the class i am calling..

Please could some assist.

FUNCTION add_new_user (xmlstruct IN VARCHAR2)

RETURN VARCHAR2

IS

req xx_demo_soap.request;

resp xx_demo_soap.response;

BEGIN

req :=

xx_demo_soap.new_request

('processRequest',

'xmlns="http://extServices"'

);

xx_demo_soap.ADD_PARAMETER (req, 'processReq', 'xsd:string', xmlstruct);

resp :=

xx_demo_soap.invoke

(req,

'http://wbrbap92/LDAP_Self-Service/services/LdapProcWS',

'http://wbrbap92/LDAP_Self-Service/services/LdapProcWS/'

);

RETURN xx_demo_soap.get_return_value

(resp,

'processRequestResponse',

'xmlns="http://extServices"'

);

END;

Also here is the WSDL file

\<?xml version="1.0" encoding="UTF-8"?>

<wsdl:definitions targetNamespace="http://extServices" xmlns:impl="http://extServices" xmlns:intf="http://extServices" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:wsdlsoap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:wsi="http://ws-i.org/profiles/basic/1.1/xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<wsdl:types>

<schema targetNamespace="http://extServices" xmlns="http://www.w3.org/2001/XMLSchema" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<element name="firstTestResponse">

<complexType>

<sequence>

<element name="firstTestReturn" nillable="true" type="xsd:string"/>

</sequence>

</complexType>

</element>

<element name="processDelete">

<complexType>

<sequence>

<element name="processDel" nillable="true" type="xsd:string"/>

</sequence>

</complexType>

</element>

<element name="processDeleteResponse">

<complexType>

<sequence>

<element name="processDeleteReturn" nillable="true" type="xsd:string"/>

</sequence>

</complexType>

</element>

<element name="processRequest">

<complexType>

<sequence>

<element name="processReq" nillable="true" type="xsd:string"/>

</sequence>

</complexType>

</element>

<element name="processRequestResponse">

<complexType>

<sequence>

<element name="processRequestReturn" nillable="true" type="xsd:string"/>

</sequence>

</complexType>

</element>

<element name="processModify">

<complexType>

<sequence>

<element name="processMod" nillable="true" type="xsd:string"/>

</sequence>

</complexType>

</element>

<element name="processModifyResponse">

<complexType>

<sequence>

<element name="processModifyReturn" nillable="true" type="xsd:string"/>

</sequence>

</complexType>

</element>

<element name="firstTest">

<complexType>

<sequence/>

</complexType>

</element>

</schema>

</wsdl:types>

<wsdl:message name="processRequestResponse">

<wsdl:part element="impl:processRequestResponse" name="parameters"/>

</wsdl:message>

<wsdl:message name="processDeleteRequest">

<wsdl:part element="impl:processDelete" name="parameters"/>

</wsdl:message>

<wsdl:message name="firstTestResponse">

<wsdl:part element="impl:firstTestResponse" name="parameters"/>

</wsdl:message>

<wsdl:message name="processRequestRequest">

<wsdl:part element="impl:processRequest" name="parameters"/>

</wsdl:message>

<wsdl:message name="processModifyRequest">

<wsdl:part element="impl:processModify" name="parameters"/>

</wsdl:message>

<wsdl:message name="processModifyResponse">

<wsdl:part element="impl:processModifyResponse" name="parameters"/>

</wsdl:message>

<wsdl:message name="processDeleteResponse">

<wsdl:part element="impl:processDeleteResponse" name="parameters"/>

</wsdl:message>

<wsdl:message name="firstTestRequest">

<wsdl:part element="impl:firstTest" name="parameters"/>

</wsdl:message>

<wsdl:portType name="LdapProcWS">

<wsdl:operation name="firstTest">

<wsdl:input message="impl:firstTestRequest" name="firstTestRequest"/>

<wsdl:output message="impl:firstTestResponse" name="firstTestResponse"/>

</wsdl:operation>

<wsdl:operation name="processDelete">

<wsdl:input message="impl:processDeleteRequest" name="processDeleteRequest"/>

<wsdl:output message="impl:processDeleteResponse" name="processDeleteResponse"/>

</wsdl:operation>

<wsdl:operation name="processRequest">

<wsdl:input message="impl:processRequestRequest" name="processRequestRequest"/>

<wsdl:output message="impl:processRequestResponse" name="processRequestResponse"/>

</wsdl:operation>

<wsdl:operation name="processModify">

<wsdl:input message="impl:processModifyRequest" name="processModifyRequest"/>

<wsdl:output message="impl:processModifyResponse" name="processModifyResponse"/>

</wsdl:operation>

</wsdl:portType>

<wsdl:binding name="LdapProcWSSoapBinding" type="impl:LdapProcWS">

<wsdlsoap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>

<wsdl:operation name="firstTest">

<wsdlsoap:operation soapAction=""/>

<wsdl:input name="firstTestRequest">

<wsdlsoap:body use="literal"/>

</wsdl:input>

<wsdl:output name="firstTestResponse">

<wsdlsoap:body use="literal"/>

</wsdl:output>

</wsdl:operation>

<wsdl:operation name="processDelete">

<wsdlsoap:operation soapAction=""/>

<wsdl:input name="processDeleteRequest">

<wsdlsoap:body use="literal"/>

</wsdl:input>

<wsdl:output name="processDeleteResponse">

<wsdlsoap:body use="literal"/>

</wsdl:output>

</wsdl:operation>

<wsdl:operation name="processRequest">

<wsdlsoap:operation soapAction=""/>

<wsdl:input name="processRequestRequest">

<wsdlsoap:body use="literal"/>

</wsdl:input>

<wsdl:output name="processRequestResponse">

<wsdlsoap:body use="literal"/>

</wsdl:output>

</wsdl:operation>

<wsdl:operation name="processModify">

<wsdlsoap:operation soapAction=""/>

<wsdl:input name="processModifyRequest">

<wsdlsoap:body use="literal"/>

</wsdl:input>

<wsdl:output name="processModifyResponse">

<wsdlsoap:body use="literal"/>

</wsdl:output>

</wsdl:operation>

</wsdl:binding>

<wsdl:service name="LdapProcWSService">

<wsdl:port binding="impl:LdapProcWSSoapBinding" name="LdapProcWS">

<wsdlsoap:address location="http://wbrbap92/LDAP_Self-Service/services/LdapProcWS"/>

</wsdl:port>

</wsdl:service>

</wsdl:definitions>

Thanks

All Comments

Leave a comment...

  • 14 Comments
    • Could you post the soap request that needs to be send to the webservice?

      Regards,

      Bhagat

      #2; Sat, 23 Feb 2008 14:21:00 GMT
    • <?xml version="1.0"?>

      <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">

      <SOAP-ENV:Body>

      <impl:processRequest xmlns:impl="http://extServices">

      <impl:processReq><?xml version="1.0"?><users><user></user></users></impl:processReq>

      </impl:processRequest>

      </SOAP-ENV:Body>

      </SOAP-ENV:Envelope>

      #3; Sat, 23 Feb 2008 14:22:00 GMT
    • <?xml version="1.0"?><users><user></user></users>

      <?xml version="1.0"?>

      <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">

      <SOAP-ENV:Body>

      <impl:processRequest xmlns:impl="http://extServices">

      <impl:processReq><?xml version="1.0"?><users><user></user></users></impl:processReq>

      </impl:processRequest>

      </SOAP-ENV:Body>

      </SOAP-ENV:Envelope>

      #4; Sat, 23 Feb 2008 14:23:00 GMT
    • Try this..

      CREATE OR REPLACE PACKAGE L_WEBSERVICE_INVOKE
      AS
      /*********************************************************************************************
      **  Name: L_WEBSERVICE_INVOKE
      **
      **
      *****************************************************************************************/
      PROCEDURE P_CREATE_XML(envolope IN OUT NOCOPY varchar2);
      PROCEDURE P_INVOKE_WEBSERVICE(rs_error_msg  IN OUT VARCHAR2,
          on_result  IN OUT NUMBER);
      PROCEDURE SHOW_ENVELOPE(env IN VARCHAR2);
      END L_WEBSERVICE_INVOKE;
      /
      show errors;
      create or replace PACKAGE BODY L_WEBSERVICE_INVOKE
      AS
      PROCEDURE P_CREATE_XML(envolope IN OUT NOCOPY varchar2)
      AS
      BEGIN
       envolope := '<
      ?xml version="1.0" encoding="UTF-8" ?><
      SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:q0="http://extServices" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><
      SOAP-ENV:Body><
      q0:processRequest><
      processReq><
      ?xml version='1.0'?><
      users><
      user><
      uid>W0001<
      /uid><
      surname>Bruce<
      /surname><
      firstName>Jacob<
      /firstName><
      idNumber>6611254858081<
      /idNumber><
      idType>P<
      /idType><
      email>Jacob.oracle.itags.org.wesbank.co.za<
      /email><
      employer>FNB<
      /employer><
      empNumber>W3546333<
      /empNumber><
      department>Collections<
      /department><
      region><
      /region><
      branch>Southside<
      /branch><
      division>Corporate<
      /division><
      /user><
      user><
      uid>W0034<
      /uid><
      surname>Brian<
      /surname><
      firstName>Sutters<
      /firstName><
      idNumber>3445554858081<
      /idNumber><
      idType>V<
      /idType><
      email>bsutters.oracle.itags.org.wesbank.co.za<
      /email><
      employer>Wesbank<
      /employer><
      empNumber>W3455455<
      /empNumber><
      department>Fraud<
      /department><
      region>PTA West<
      /region><
      branch>Southside<
      /branch><
      division>Corporate<
      /division><
      /user><
      /users><
      /processReq><
      /q0:processRequest><
      /SOAP-ENV:Body><
      /SOAP-ENV:Envelope>';
      END P_CREATE_XML;
      PROCEDURE P_INVOKE_WEBSERVICE(rs_error_msg  IN OUT VARCHAR2,
          on_result  IN OUT NUMBER)
      AS
      ls_out_resp varchar2(32767);
      http_req utl_http.req;
      http_resp utl_http.resp;
      ls_http_req_url varchar2(4000);
      ls_result VARCHAR2(2000);
      name VARCHAR2(256);
       -- Response header name
      value VARCHAR2(1024);
       -- Response header value
      line VARCHAR2(4000);
       -- Response body
      ls_webservice_url varchar2(1024);
      ls_in_request varchar2(4000);
      is_webservice_err varchar2(2000);
      BEGIN
       on_result:=0;
       ls_http_req_url := 'http://wbrbap92/LDAP_Self-Service/services/LdapProcWS';
       --Construct the Input Soap Request
       P_CREATE_XML(ls_in_request);
       --show_envelope(ls_in_request);
       -- Establish the connection
        http_req := utl_http.begin_request(ls_http_req_url, 'POST' ,'HTTP/1.1');
        -- The Content-Length header has to indicate the amount of data in the XML document
         utl_http.set_header(http_req, 'Content-Type', 'text/xml');
         utl_http.set_header(http_req, 'Content-Length', to_char(length(ls_in_request)) );
         -- POST the XML document
        utl_http.write_text(http_req,ls_in_request);
        -- Now process the HTTP server's response
        http_resp := utl_http.get_response(http_req);
        dbms_output.put_line('HTTP response status code: ' || http_resp.status_code);
        dbms_output.put_line('HTTP response reason phrase: ' || http_resp.reason_phrase);
        utl_http.read_text(http_resp, ls_out_resp);
        show_envelope(ls_out_resp);
        utl_http.end_response(http_resp);
      EXCEPTION
      WHEN Utl_Http.request_failed THEN
       on_result:=-1;
       rs_error_msg:='L_WEBSERVICE_INVOKE.p_invoke_webservice-request_failed: ' ||UTL_HTTP.GET_DETAILED_SQLERRM;
       Utl_Http.end_request (http_req);
      WHEN Utl_Http.http_server_error THEN
       on_result:=-1;
       rs_error_msg:='L_WEBSERVICE_INVOKE.p_invoke_webservice-Http_Server_Error: ' ||UTL_HTTP.GET_DETAILED_SQLERRM;
       Utl_Http.end_request (http_req);
      WHEN Utl_Http.http_client_error THEN
       on_result:=-1;
       rs_error_msg:='L_WEBSERVICE_INVOKE.p_invoke_webservice-Http_Client_Error: ' ||UTL_HTTP.GET_DETAILED_SQLERRM;
       Utl_Http.end_request (http_req);
      when others then
       on_result:=-1;
       rs_error_msg := 'L_WEBSERVICE_INVOKE.P_INVOKE_WEBSERVICE: ' || SQLERRM;
       Utl_Http.end_request (http_req);
      END P_INVOKE_WEBSERVICE;
      PROCEDURE SHOW_ENVELOPE(env IN VARCHAR2)
      AS
      i pls_integer;
      len pls_integer;
      BEGIN
       i := 1;
       len := length(env);
       WHILE (i <
      = len)
       LOOP
       dbms_output.put_line(substr(env, i, 100));
       i:=i+100;
       END LOOP;
      END SHOW_ENVELOPE;
      END L_WEBSERVICE_INVOKE;
      ***********************************************************************************************
      Now execute the anonymous block
      set serverout on size 1000000;
      DECLARE
      RS_ERROR_MSG VARCHAR2(2000);
      ON_RESULT NUMBER;
      BEGIN
      L_WEBSERVICE_INVOKE.P_INVOKE_WEBSERVICE(RS_ERROR_MSG,ON_RESULT);
      END;
      Good luck!!!
      Bhagat
      Message was edited by:
       Bugs
      Message was edited by:
       Bugs
      #5; Sat, 23 Feb 2008 14:24:00 GMT
    • Hi

      Response was

      ORA-29273: HTTP request failed

      ORA-06512: at "SYS.UTL_HTTP", line 1194

      ORA-29261: bad argument

      ORA-06512: at "APPS.L_WEBSERVICE_INVOKE", line 60

      ORA-29273: HTTP request failed

      ORA-06512: at "SYS.UTL_HTTP", line 1022

      ORA-12545: Connect failed because target host or object does not exist

      ORA-06512: at line 5

      could this be due to proxy settings?

      the url for the webservice is accessable from within the network and without going out oof the proxy? so i doubt it

      #6; Sat, 23 Feb 2008 14:25:00 GMT
    • Hi i changed the service url to

      ls_http_req_url := 'http://172.16.245.92/LDAP_Self-Service/services/LdapProcWS';

      and it ran with no result shown

      Message was edited by:

      George

      #7; Sat, 23 Feb 2008 14:26:00 GMT
    • Ok mine is giving another error...

      So i gave yours a go again..

      This is the result in DBMS

      HTTP response status code: 500

      HTTP response reason phrase: Internal Server Error

      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soapenc="http://sc

      hemas.xmlsoap.org/soap/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www

      .w3.org/2001/XMLSchema-instance"><soapenv:Header/><soapenv:Body><soapenv:Fault><faultcode>Client.NoS

      OAPAction</faultcode><faultstring><![CDATA[WSWS3147E: Error: no SOAPAction header!]]></faultstring><

      /soapenv:Fault></soapenv:Body></soapenv:Envelope>

      ?

      Thanks in advance

      #8; Sat, 23 Feb 2008 14:27:00 GMT
    • If you are able to invoke this url from browser,the only other thing which could claim responsible for this error would be the input soap request.

      Check that out..

      Good luck!!

      Bhagat

      #10; Sat, 23 Feb 2008 14:29:00 GMT
    • Hi

      I am able to call the service both in the code and in IE.. But as i said in my previous posts i am gettin an error once it has called the webservice..

      HTTP response status code: 500

      HTTP response reason phrase: Internal Server Error

      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soapenc="http://sc

      hemas.xmlsoap.org/soap/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www

      .w3.org/2001/XMLSchema-instance"><soapenv:Header/><soapenv:Body><soapenv:Fault><faultcode>Client.NoS

      OAPAction</faultcode><faultstring><![CDATA[WSWS3147E: Error: no SOAPAction header!]]></faultstring><

      /soapenv:Fault></soapenv:Body></soapenv:Envelope>

      that is the error that i get..

      i got the systems ppl to send me their log and this is what what shown on thier side

      this is what the result was.

      [06/11/02 16:15:12:032 SAST] 0000002b WebServicesSe E com.ibm.ws.webservices.engine.transport.http.WebServicesServlet getSoapAction WSWS3112E: Error: Generating WebServicesFault due to missing SOAPAction.

      WebServicesFault

      faultCode: Client.NoSOAPAction

      faultString: WSWS3147E: Error: no SOAPAction header!

      faultActor: null

      faultDetail:

      WSWS3147E: Error: no SOAPAction header!

      at com.ibm.ws.webservices.engine.transport.http.WebServicesServlet.getSoapAction(WebServicesServlet.java:1251)

      at com.ibm.ws.webservices.engine.transport.http.WebServicesServlet.doPost(WebServicesServlet.java:698)

      at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)

      at com.ibm.ws.webservices.engine.transport.http.WebServicesServletBase.service(WebServicesServletBase.java:341)

      at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)

      at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1284)

      at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1241)

      at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:113)

      at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterChain.java:82)

      at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:671)

      at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:89)

      at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1924)

      at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:89)

      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:472)

      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:411)

      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:288)

      at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminaters(NewConnectionInitialReadCallback.java:207)

      at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:109)

      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.requestComplete(WorkQueueManager.java:566)

      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.attemptIO(WorkQueueManager.java:619)

      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.workerRun(WorkQueueManager.java:952)

      at com.ibm.ws.tcp.channel.impl.WorkQueueManager$Worker.run(WorkQueueManager.java:1039)

      at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1471)

      [06/11/02 16:15:12:037 SAST] 0000002b UserException E WSWS3228E: Error: Exception: WebServicesFault

      faultCode: Client.NoSOAPAction

      faultString: WSWS3147E: Error: no SOAPAction header!

      faultActor: null

      faultDetail:

      WSWS3147E: Error: no SOAPAction header

      #11; Sat, 23 Feb 2008 14:30:00 GMT
    • I have been sent an example of what a correct message looks like, below is the example

      <?xml version="1.0" encoding="UTF-8" ?>

      - <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:q0="http://extServices" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

      - <SOAP-ENV:Body>

      - <q0:processRequest>

      <processReq><?xml version='1.0'?><users><user><uid>W0001</uid><surname>Bruce</surname><firstName>Jacob</firstName><idNumber>6611254858081</idNumber><idType>P</idType><email>Jacob.oracle.itags.org.wesbank.co.za</email><employer>FNB</employer><empNumber>W3546333</empNumber><department>Collections</department><region></region><branch>Southside</branch><division>Corporate</division></user><user><uid>W0034</uid><surname>Brian</surname><firstName>Sutters</firstName><idNumber>3445554858081</idNumber><idType>V</idType><email>bsutters.oracle.itags.org.wesbank.co.za</email><employer>Wesbank</employer><empNumber>W3455455</empNumber><department>Fraud</department><region>PTA West</region><branch>Southside</branch><division>Corporate</division></user></users></processReq>

      </q0:processRequest>

      </SOAP-ENV:Body>

      </SOAP-ENV:Envelope>

      #12; Sat, 23 Feb 2008 14:31:00 GMT
    • Anybody have an idea??
      #13; Sat, 23 Feb 2008 14:32:00 GMT
    • George,

      Well..I don't understand one thing...

      Is the one below the soap request or the reponse from the webservice?

      <?xml version="1.0" encoding="UTF-8" ?>

      - <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:q0="http://extServices" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

      - <SOAP-ENV:Body>

      - <q0:processRequest>

      <processReq><?xml version='1.0'?><users><user><uid>W0001</uid><surname>Bruce</surname><firstName>Jacob</firstName><idNumber>6611254858081</idNumber><idType>P</idType><email>Jacob.oracle.itags.org.wesbank.co.za</email><employer>FNB</employer><empNumber>W3546333</empNumber><department>Collections</department><region></region><branch>Southside</branch><division>Corporate</division></user><user><uid>W0034</uid><surname>Brian</surname><firstName>Sutters</firstName><idNumber>3445554858081</idNumber><idType>V</idType><email>bsutters.oracle.itags.org.wesbank.co.za</email><employer>Wesbank</employer><empNumber>W3455455</empNumber><department>Fraud</department><region>PTA West</region><branch>Southside</branch><division>Corporate</division></user></users></processReq>

      </q0:processRequest>

      </SOAP-ENV:Body>

      </SOAP-ENV:Envelope>

      Lemme know.

      Bhagat

      #14; Sat, 23 Feb 2008 14:33:00 GMT