Oracle: Consuming a .NET Web Service

UTL_HTTP allows your PL/SQL program to consume a web service in a simplest way.

UTL_HTTP.request:The simplest way to call UTL_HTTP.request is to send a URL and get back the page.
As long as the page was 2,000 bytes or less, this would work well.

Syntax : UTL_HTTP.request(url,proxy,wallet_path,wallet_password);

DECLARE
v_pagecontent LONG;l_return LONG;_url VARCHAR2(2000);
p_param1 VARCHAR2(2000);
p_param2 VARCHAR2(2000);
iCnt NUMBER := 1;
BEGIN

v_url := 'http://YourWebsite.com/YourWebservice.asmx/WebserviceMethod';
--Add the parametersv_url := v_url || '?param1='|| p_param1 ||'&param2='|| p_param2 ||'';
--Call the WebServicev_pagecontent := UTL_HTTP.request( v_url );
LOOP l_return := SUBSTR( v_pagecontent, iCnt, 255 )
DBMS_OUTPUT.put_line( l_return);
iCnt = iCnt + 255
EXIT WHEN l_return IS NULL OR l_return = ' ' ;
END LOOP ; 
EXCEPTION
WHEN Utl_Http.request_failed THEN
DBMS_OUTPUT.put_line ('Request_Failed: ' || Utl_Http.get_detailed_sqlerrm);
WHEN Utl_Http.http_server_error THEN
DBMS_OUTPUT.put_line ('Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm);
END ;

UTL_HTTP.request_pieces
For larger pages, we need to use REQUEST_PIECES as follows which returns Array of UTL_HTTP.HTML_PIECES.

Syntax : UTL_HTTP.request_pieces(url,max_pieces,proxy,wallet_path,wallet_password)

DECLARE
pieces UTL_HTTP.HTML_PIECES;
v_url VARCHAR2(2000);
p_param1 VARCHAR2(2000);
p_param2 VARCHAR2(2000);

 BEGIN
v_url := 'http://YourWebsite.com/YourWebservice.asmx/WebserviceMethod';--Add the parameters
v_url := v_url || '?param1='|| p_param1 ||'&param2='|| p_param2 ||'';

--Call the WebService
pieces := UTL_HTTP.REQUEST_PIECES (v_url);

FOR I IN 1 .. pieces.COUNT
LOOPDBMS_OUTPUT.put_line(SUBSTR ('Value of pieces=' || pieces (I), 1, 255));END LOOP;
EXCEPTION
WHEN Utl_Http.request_failed THEN
DBMS_OUTPUT.put_line ('Request_Failed: ' || Utl_Http.get_detailed_sqlerrm);
WHEN Utl_Http.http_server_error THEN
DBMS_OUTPUT.put_line ('Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm);
END ;

UTL_HTTP.begin_request

DECLARE
v_url VARCHAR2(2000);
p_param1 VARCHAR2(2000);
p_param2 VARCHAR2(2000);
http_req UTL_HTTP.req;
http_resp UTL_HTTP.resp;
BEGIN
v_url := 'http://YourWebsite.com/YourWebservice.asmx/WebserviceMethod';
--Add the parameters
v_url := v_url || '?param1='|| p_param1 ||'&param2='|| p_param2 ||'';

--Call the WebService
http_req := UTL_HTTP.begin_request(v_url, 'POST', UTL_HTTP.HTTP_VERSION_1_1);
UTL_HTTP.end_request(http_req);

--Response from WebService
http_resp := UTL_HTTP.get_response(http_req);
DBMS_OUTPUT.put_line('Response Received');
DBMS_OUTPUT.put_line('--------------------------');
DBMS_OUTPUT.put_line ( 'Status code: ' || http_resp.status_code );
DBMS_OUTPUT.put_line ( 'Reason phrase: ' || http_resp.reason_phrase );
UTL_HTTP.end_response(http_resp);

EXCEPTION
WHEN Utl_Http.request_failed THEN
DBMS_OUTPUT.put_line ('Request_Failed: ' || Utl_Http.get_detailed_sqlerrm);
WHEN Utl_Http.http_server_error THEN
DBMS_OUTPUT.put_line ('Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm);
END ;

---
Otherway of Consuming a Web Service in Oracle :

Source : http://www.oracle-base.com/articles/10g/utl_dbws10g.php

In Oracle 10g the UTL_DBWS package is loaded by default. In Oracle9i the package must be loaded using the specification and body provided in the zip file.

The function below uses the UTL_DBWS package to access a web services from PL/SQL. The URL of the WDSL file describing the web service is shown here (http://www.oracle-base.com/webservices/server.php?wsdl). The web service accepts two number parameters and returns the sum of those values.

CREATE OR REPLACE FUNCTION add_numbers (p_int_1 IN NUMBER,

                                        p_int_2 IN NUMBER)

  RETURN NUMBER

AS

  l_service          UTL_DBWS.service;

  l_call             UTL_DBWS.call;

  l_wsdl_url         VARCHAR2(32767);

  l_namespace        VARCHAR2(32767);

  l_service_qname    UTL_DBWS.qname;

  l_port_qname       UTL_DBWS.qname;

  l_operation_qname  UTL_DBWS.qname;

  l_xmltype_in       SYS.XMLTYPE;

  l_xmltype_out      SYS.XMLTYPE;

  l_return           NUMBER;

BEGIN

  l_wsdl_url        := 'http://www.oracle-base.com/webservices/server.php?wsdl';

  l_namespace       := 'http://www.oracle-base.com/webservices/';

  l_service_qname   := UTL_DBWS.to_qname(l_namespace, 'Calculator');

  l_port_qname      := UTL_DBWS.to_qname(l_namespace, 'CalculatorPort');

  l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'ws_add');

  l_service := UTL_DBWS.create_service (

    wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),

    service_name           => l_service_qname);

  l_call := UTL_DBWS.create_call (

    service_handle => l_service,

    port_name      => l_port_qname,

    operation_name => l_operation_qname);

  l_xmltype_in := SYS.XMLTYPE('<?xml version="1.0" encoding="utf-8"?>

    <ws_add xmlns="' || l_namespace || '">

      <int1>' || p_int_1 || '</int1>

      <int2>' || p_int_2 || '</int2>

    </ws_add>');

  l_xmltype_out := UTL_DBWS.invoke(call_Handle => l_call,

                                   request     => l_xmltype_in);

  UTL_DBWS.release_call (call_handle => l_call);

  UTL_DBWS.release_service (service_handle => l_service);

  l_return := l_xmltype_out.extract('//return/text()').getNumberVal();

  RETURN l_return;

END;

/

The output below shows the function in action.

SELECT add_numbers(1, 5) FROM dual;

ADD_NUMBERS(1,5)

----------------

               6

SQL>

SELECT add_numbers(10, 15) FROM dual;

ADD_NUMBERS(10,15)

------------------

                25

SQL>