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.

There are four basic approaches to handling SOAP web services. 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.

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.

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 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 package here .

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.

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 .

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

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...