Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Consuming Web Services in Oracle

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>

If you are working on Oracle 11g onward, you may see the following error.

SQL> SELECT add_numbers(1, 5) FROM dual;
SELECT add_numbers(1, 5) FROM dual
       *
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "TEST.SOAP_API", line 144
ORA-06512: at "TEST.ADD_NUMBERS", line 34

SQL>
Oracle 11g introduced fine-grained access to network services. The error indicates you need to create an access control list of the service you are attempting to connect to.

For further information see:

Hope this helps. Regards Tim...

Back to the Top.