Consuming Web Services

Over the last few years web services have increased in popularity to the point where most new application incorporate them to some degree. At the heart of web services is SOAP (Simple Object Access Protocol), a simple XML based protocol to let applications exchange information over HTTP. For more information about SOAP read the SOAP Tutorial.

Oracle9i allows direct access to web services from PL/SQL using the UTL_HTTP package. In Oracle10g it will be possible to publish PL/SQL as web services directly from the database, rather than via Oracle9iAS as is currently the case. In this article I'll present a simple example of accessing a web service from PL/SQL.

First the soap_api.sql code must be loaded into the database.

The function below uses the SOAP_API 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_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://www.oracle-base.com/webservices/server.php';
  l_namespace   := 'xmlns="http://www.oracle-base.com/webservices/"';
  l_method      := 'ws_add';
  l_soap_action := 'http://www.oracle-base.com/webservices/server.php/ws_add';
  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);

  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>

For further information see:

Hope this helps. Regards Tim...

Back to the Top.