8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Web Services and the Oracle Database

The Oracle database includes lots of functionality to consume and publish web services directly from the database, most of which have been covered by articles on this site. This article serves to link all those articles together to give a consistent view of web service technology in the Oracle database.

Consuming Web Services
Publishing Web Services
Miscellaneous

Consuming Web Services

Consuming web services from PL/SQL will invariably involve making HTTP/HTTPS calls to web services and processing the resulting data. There are three variations on this theme described below. In all cases, you will need to consider the following points.

From Oracle 11g onward, access to external network services is restricted using Access Control Lists (ACLs). You will need to create the appropriate ACLs before attempting to access web services. The management of ACLs is different between 11g and 12c, but you can see examples here (11g, 12c).
If you are making calls to HTTPS resources, you will need to use an Oracle wallet to handle the trusted certificates. You can see an example of this here.

Consuming SOAP Web Services

There are four basic approaches to handling SOAP web services.

Do It Yourself (DIY): Oracle 8i introduced the building blocks for processing HTTP requests and XML, allowing you to manually build the appropriate SOAP request in a SOAP envelope, send it as a HTTP request to the SOAP web service, then manually parse the resulting SOAP envelope containing the response.
SOAP API: Use one of the APIs that has been written to simplify the DIY method. You can see an example of this here.
UTL_DBWS: Oracle 10g introduced the UTL_DBWS package to access SOAP web services. You can see an example of this here. I find this API rather inflexible and often revert to using one of the previous two manual methods.
APEX_WEB_SERVICE: APEX includes a number of APIs that can be used directly from PL/SQL, even if you are not using APEX itself. The APEX_WEB_SERVICE package provides a simple API for consuming SOAP and REST web services. You can see an example of this here.

Depending on the the type of web service and the complexity of the response, you may still need to do some manual parsing of the XML, which is described below.

Consuming REST XML Web Services

There are a number of ways to deal with REST-style XML web services. In all cases, this will start with a HTTP/HTTPS request to the web service to retrieve the XML data. Once you have the XML response, there are several ways to extract data from it.

SQL: Oracle 10g Release 2 introduced the XMLTABLE operator, allowing you to query the contents of XML using SQL. This is a really convenient method for processing small XML documents. You can see an example of this here.
Manual Parse: This method has been available since Oracle 8i. Use the DBMS_XMLPARSER and DBMS_XSLPROCESSOR packages to manually parse the XML and extract the data. This works well when dealing with medium-large XML documents. You can see an example of this here.
XML DB: Since Oracle 9i Release 2 it has been possible to use the XML DB functionality in the database to automatically shred XML documents into relational tables by associating XML with an XML Schema. For very large XML documents, this method is probably the most efficient. You can see an example of XML DB here.
APEX_WEB_SERVICE: APEX includes a number of APIs that can be used directly from PL/SQL, even if you are not using APEX itself. The APEX_WEB_SERVICE package provides a simple API for consuming SOAP and REST web services. You can see an example of this here.

Consuming REST JSON Web Services

Prior to Oracle 12c (12.1.0.2), the only way to consume JSON web services in the database was to make a HTTP request to the web service, then use PL/SQL string handling to pull the data apart. Oracle 12c (12.1.0.2) now includes server side JSON support, allowing you to store JSON in the databases and query it as if it were relational data. This functionality is described here.

It would also be possible to load Java-based JSON APIs into the database and call them from PL/SQL.

The APEX_WEB_SERVICE package provides a simple API for consuming REST web services, but it doesn't include any JSON-specific functionality, so it will simplify the process of getting the JSON document, but once you have it you will have to process it manually or with the 12c functionality mentioned previously. You can see an example of using the APEX_WEB_SERVICE package here.

Publishing Web Services

Publishing SOAP Web Services

The simplest way to publish SOAP web services from the database is to use XML DB Native Web Services, available from Oracle 11g onward. Amongst other things, this functionality allows you to expose any PL/SQL stored procedures, functions and packages as SOAP web services at the "flick of a switch".

It is also possible to construct SOAP web services manually using the PL/SQL web toolkit, but you will need to manually construct a WSDL file that describes the service and will probably need to do some URL rewrites in you HTTP server to make it all appear like a consistent SOAP experience.

Publishing REST XML Web Services

Oracle 8i introduced the PL/SQL Web Toolkit, which allows web applications to be published directly from the database. The functionality includes the ability to send text and binary data in response to HTTP/HTTPS requests. This can be fronted by the Oracle HTTP Server, Oracle REST Data Services (the APEX listener) or the PL/SQL Embedded Gateway. It is this foundation that Oracle Application Express (APEX) is built upon.

Pushing out REST web services using the web toolkit is really simple, as described here. The article also describes an alternative method using the XML DB repository.

The XML published by the web service can be constructed manually, or produced using SQL/XML.

Publishing REST JSON Web Services

User ORDS!

Since JSON is text-based, the PL/SQL Web Toolkit mentioned previously is also perfectly capable of publishing manually constructed JSON web services.

Miscellaneous

The database is also capable of retrieving and publishing binary data, as described here (retrieve, publish).

For more information see:

Hope this helps. Regards Tim...

Back to the Top.