8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Consuming Web Services in Oracle
I would avoid using the SOAP_API
package discussed here and instead now use the APEX_WEB_SERVICE
package.
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 Introduction.
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://oracle-base.com/webservices/server?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://oracle-base.com/webservices/server.php'; l_namespace := 'xmlns="http://oracle-base.com/webservices/"'; l_method := 'ws_add'; l_soap_action := 'http://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:
- UTL_HTTP
- UTL_DBWS (10g)
- APEX_WEB_SERVICE : Consuming SOAP and REST Web Services
- UTL_HTTP and SSL (HTTPS) using Oracle Wallets
- Fine-Grained Access to Network Services in Oracle Database 11g Release 1
Hope this helps. Regards Tim...