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://webservices.imacination.com/distance/Distance.jws?wsdl). The web service returns the city associated with the specified zipcode.

CREATE OR REPLACE FUNCTION get_city_from_zipcode (p_zipcode  IN  VARCHAR2)
  RETURN VARCHAR2
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
  -- Set proxy details if no direct net connection.
  --UTL_HTTP.set_proxy('myproxy:4480', NULL);
  --UTL_HTTP.set_persistent_conn_support(TRUE);

  -- Set proxy authentication if necessary.
  --soap_api.set_proxy_authentication(p_username => 'myusername',
  --                                  p_password => 'mypassword');

  l_url         := 'http://webservices.imacination.com/distance/Distance.jws';
  l_namespace   := 'xmlns="' || l_url || '"';
  l_method      := 'getCity';
  l_soap_action := l_url || '#getCity';
  l_result_name := 'getCityReturn';
  
  l_request := soap_api.new_request(p_method       => l_method,
                                    p_namespace    => l_namespace);

  soap_api.add_parameter(p_request => l_request,
                         p_name    => 'zip',
                         p_type    => 'xsd:string',
                         p_value   => p_zipcode);

  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 => l_namespace);

  RETURN l_return;
END;
/

The output below shows the function in action.

SQL> SELECT get_city_from_zipcode('94065') FROM dual;

GET_CITY_FROM_ZIPCODE('94065')
--------------------------------------------------------------------------------
Redwood City

SQL> SELECT get_city_from_zipcode('94066') FROM dual;

GET_CITY_FROM_ZIPCODE('94066')
--------------------------------------------------------------------------------
San Bruno

SQL>

For further information see:

Hope this helps. Regards Tim...

Back to the Top.