0

How do we call an asp.net webservice from oracle, can we have a stored procedure for calling an asp.net webservice?

0

1 Answer 1

1

Here is a simple stored procedure that calls a simple ASP.NET addition web service from oracle which gives us the result of addition of two numbers which we supply from Oracle.

CREATE OR REPLACE PROCEDURE sp_WsAdd
(

  P_NUM1 number,
  P_NUM2 number
)
AS
V_INVELOP_TEMP VARCHAR2(32767);
V_INVELOP      VARCHAR2(32767);
V_REQUEST      UTL_HTTP.REQ;
V_RESPONSE UTL_HTTP.RESP;  
V_WSDL_URL VARCHAR2(2000):='Address of your Webservice';
V_XML XMLTYPE; 
BEGIN
    V_INVELOP:='<?xml version="1.0" encoding="utf-8"?>
  <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <soap:Body>
   <add xmlns="http://tempuri.org/">
     <num1>'||P_NUM1||'</num1>
     <num2>'||P_NUM2||'</num2>
   </add>
 </soap:Body>
</soap:Envelope>';

  V_REQUEST:= UTL_HTTP.BEGIN_REQUEST(V_WSDL_URL, 'POST','HTTP/1.1');
              UTL_HTTP.SET_BODY_CHARSET(V_REQUEST, 'UTF-8');

   UTL_HTTP.SET_HEADER(V_REQUEST, 'Content-Type', 'text/xml');
   UTL_HTTP.SET_HEADER(V_REQUEST, 'Content-Length', LENGTH(V_INVELOP));
   UTL_HTTP.SET_HEADER(V_REQUEST, 'SOAPAction', 'http://tempuri.org/add');
   UTL_HTTP.WRITE_TEXT(V_REQUEST, V_INVELOP);
   V_RESPONSE := UTL_HTTP.GET_RESPONSE(V_REQUEST);
            dbms_output.put_line('Response Received');
            dbms_output.put_line('--------------------------');                                                                                                                                                                                                                
            dbms_output.put_line ( 'Status code: ' || V_RESPONSE.status_code );
            dbms_output.put_line ( 'Reason phrase: ' || V_RESPONSE.reason_phrase );

  UTL_HTTP.READ_TEXT(V_RESPONSE, V_INVELOP);
  UTL_HTTP.END_RESPONSE(V_RESPONSE);

                V_INVELOP := REPLACE(V_INVELOP,'xmlns="http://tempuri.org/"','');
                V_XML:=  XMLTYPE(V_INVELOP);
                dbms_output.put_line('Value='||V_XML.extract('//addResult/text()').getStringVal());

   commit;

   END;
    /

The web service for this Stored procedure is

  [WebMethod]
  public string add(int num1, int num2)
  {
    return (num1 + num2).ToString();
  }
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.