Essential Guide

Guide: When and how to use REST

A comprehensive collection of articles, videos and more, hand-picked by our editors

REST vs. SOAP: How to choose the best Web service

Here is a breakdown of when it's best to use REST or SOAP Web services.


Guide: When and how to use REST

+ Show More

"I need to update the local inventory database with the inventory information from multiple suppliers. The suppliers...

provide Web service-based interface. As the application does not have any server side component (the application is a fat client talking directly to the database), is it possible to consume these Web services directly from my application database?"

Do similar questions trouble you? Ever wondered what could be the solution to such problems? To date, we have been familiar with consuming Web services within the application that fetches the data from various databases.

There is a different perspective, where the databases act as the service consumer in contrast to the normal norm of acting as a service provider. Here is how you can invoke a Web service via database stored procedures.

Web services overview

A Web service, in very broad terms, is a method of communication between two applications or electronic devices over the World Wide Web (WWW). Web services are of two kinds: Simple Object Access Protocol (SOAP) and Representational State Transfer (REST).

SOAP defines a standard communication protocol (set of rules) specification for XML-based message exchange. SOAP uses different transport protocols, such as HTTP and SMTP. The standard protocol HTTP makes it easier for SOAP model to tunnel across firewalls and proxies without any modifications to the SOAP protocol. SOAP can sometimes be slower than middleware technologies like CORBA or ICE due to its verbose XML format.

REST describes a set of architectural principles by which data can be transmitted over a standardized interface (such as HTTP). REST does not contain an additional messaging layer and focuses on design rules for creating stateless services. A client can access the resource using the unique URI and a representation of the resource is returned. With each new resource representation, the client is said to transfer state. While accessing RESTful resources with HTTP protocol, the URL of the resource serves as the resource identifier and GET, PUT, DELETE, POST and HEAD are the standard HTTP operations to be performed on that resource.


Multiple factors need to be considered when choosing a particular type of Web service, that is between REST and SOAP. The table below breaks down the features of each Web service based on personal experience.


  • The RESTful Web services are completely stateless. This can be tested by restarting the server and checking if the interactions are able to survive.
  • Restful services provide a good caching infrastructure over HTTP GET method (for most servers). This can improve the performance, if the data the Web service returns is not altered frequently and not dynamic in nature.
  • The service producer and service consumer need to have a common understanding of the context as well as the content being passed along as there is no standard set of rules to describe the REST Web services interface.
  • REST is particularly useful for restricted-profile devices such as mobile and PDAs for which the overhead of additional parameters like headers and other SOAP elements are less.
  • REST services are easy to integrate with the existing websites and are exposed with XML so the HTML pages can consume the same with ease. There is hardly any need to refactor the existing website architecture. This makes developers more productive and comfortable as they will not have to rewrite everything from scratch and just need to add on the existing functionality.
  • REST-based implementation is simple compared to SOAP.


  • The Web Services Description Language (WSDL) contains and describes the common set of rules to define the messages, bindings, operations and location of the Web service. WSDL is a sort of formal contract to define the interface that the Web service offers.
  • SOAP requires less plumbing code than REST services design, (i.e., transactions, security, coordination, addressing, trust, etc.) Most real-world applications are not simple and support complex operations, which require conversational state and contextual information to be maintained. With the SOAP approach, developers need not worry about writing this plumbing code into the application layer themselves.
  • SOAP Web services (such as JAX-WS) are useful in handling asynchronous processing and invocation.
  • SOAP supports several protocols and technologies, including WSDL, XSDs, SOAP, WS-Addressing

In a nutshell, when you're publishing a complex application program interface (API) to the outside world, SOAP will be more useful. But when something with a lower learning curve, and with lightweight and faster results and simple transactions (i.e., CRUD operations) is needed, my vote goes to REST.

Invoking Web service via Oracle database stored procedure

Consuming a Web service via a database stored procedure allows users to straight away update a database with information from different sources. Users can also schedule a job at regular intervals to get data updated periodically in the database. 

Oracle provides a "utl_http" utility to help achieve this. Below is sample code for the Oracle package for a customer where the Web service call is made from the database.

Common problems faced when invoking Web services and solutions

Sometimes, even after doing everything as expected in the stored procedure to call the Web service, the procedure doesn't get compiled. The following is a compilation of runtime errors faced during stored procedure execution to invoke a Web service and their solutions.

Problem 1: Getting "ORA-25293 : HTTP request failed" error during procedure compilation.

Solution: Follow the steps below to rectify this.

      • Login through sys user as sysdba.
      • View the privileges to the selected schema to use the utl_HTTP package by using the command as follows:
        • select grantee, table_name, privilege
          from dba_tab_privs
          where table_name = 'UTL_HTTP';

      • The grant will be provided to all the public users by default.
      • Revoke execute grant on utl_http from public and provide it explicitly to the specific schema from which the Web service needs to be invoked.
        • revoke execute on utl_http from public;

        • grant execute on utl_http to BTFT2;
        • select grantee, table_name, privilege
          from dba_tab_privs
          where table_name =  'UTL_HTTP';

Problem 2: If the stored procedure calling the Web service gives "Network access denied" during the call of Web service.

Solution: Add the Web service url to the access control list by following the steps below.

      • Login through sys as sysdba.
      • Execute the following procedure to create ACL.
        • BEGIN
               acl          => '<name of the acl file>.xml',
               description  => 'Permissions to access the web service url',
               principal    => '<Schema name>',
               is_grant    => TRUE,
               privilege    =>

      • Create a role and then grant connect to this role on the ACL by using the steps below:
        • create role role1;

        • BEGIN

                                    acl          => '<name of the acl file>.xml',               
                                    principal   => 'role1',
                                    is_grant     => TRUE,
             => 'connect',
                                    position     => null);

      • Assign the host names to the ACL to open all the related links in the host.
        • BEGIN
               acl          => '<name of the acl file>.xml',               
               host         => '*.<host name of the webservice url>');


        • BEGIN
                acl          => '<name of the acl file>.xml',               
                host         => '<ip>’);

      • Confirm whether the domain has been added in the ACL using ACL_UTILITY package.
        • SELECT * FROM
        • select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
        • select acl , principal , privilege , is_grant from BA_NETWORK_ACL_PRIVILEGES;

There are many more ways to invoke the services, such as using JDeveloper or the Oracle UTL_DWBS package. There will be more options and features available with the upcoming versions of Oracle, so the database can be enabled to act both as a consumer and a provider. This approach in its full potential will ultimately globalize database schemas and their operations.


Next Steps

Understand how network capacity management is still a challenge for apps.


This was last published in April 2013



Find more PRO+ content and other member only offers, here.

Essential Guide

Guide: When and how to use REST
Related Discussions

Swati Dhingra asks:

Do you more frequently use REST or SOAP Web services?

67  Responses So Far

Join the Discussion



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: