5 Replies Latest reply on Oct 24, 2018 3:17 PM by Erik Raetz

    Simplest way to create REST-client inside Oracle Database 12.1+ to call REST-services outside that database

    Alexus67

      Hi All!

       

      Found this article:

      https://oracle-base.com/articles/misc/web-services-and-the-oracle-database#consuming-rest-json-web-services

       

      Citation:

       

      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.

       

      Does that mean that simples answer to question: "Simplest way to create REST-client inside Oracle Database to call REST-services outside that database" is "use APEX_WEB_SERVICE package?"

        • 1. Re: Simplest way to create REST-client inside Oracle Database 12.1+ to call REST-services outside that database
          Kiran Pawar

          Hi Alexus67,

          Alexus67 wrote:

           

          Found this article:

          https://oracle-base.com/articles/misc/web-services-and-the-oracle-database#consuming-rest-json-web-services

          Citation:

          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.

          Does that mean that simples answer to question: "Simplest way to create REST-client inside Oracle Database to call REST-services outside that database" is "use APEX_WEB_SERVICE package?"

          The term "REST-client" is different from "consuming a RESTful Web Service". APEX_WEB_SERVICE API is not only meant to consume RESTful but also SOAP Web Services in Oracle APEX.

          However if you are looking for REST Client (a GUI based utility to test RESTful Web Services) inside Oracle Database, there was one packaged application (built in Oracle APEX 5.1 available with release):

          image01.png

           

           

          Also if you are looking just REST Client only for testing RESTful API, there are many clients available as Postman, Insomnia and so on and so forth.

           

          Regards,

          Kiran

          • 2. Re: Simplest way to create REST-client inside Oracle Database 12.1+ to call REST-services outside that database
            Alexus67

            Thanks, Kiran!

             

            I need to create automatic interactions from Oracle database to REST services outside that database, so "consuming a RESTful Web Service" is right term here.

             

            Is APEX_WEB_SERVICE package the simplest way to call REST-services outside that database?

            I know there is utl_https, but it seems to be more complex to use, is it?

            • 3. Re: Simplest way to create REST-client inside Oracle Database 12.1+ to call REST-services outside that database
              Kiran Pawar

              Hi Alexus67,

              Alexus67 wrote:

               

              Thanks, Kiran!

               

              I need to create automatic interactions from Oracle database to REST services outside that database, so "consuming a RESTful Web Service" is right term here.

               

              Is APEX_WEB_SERVICE package the simplest way to call REST-services outside that database?

              I know there is utl_https, but it seems to be more complex to use, is it?

              Yes APEX_WEB_SERVICE package is simpler way to call REST services outside the database. Also, APEX_WEB_SERVICE is a wrapper package built on top of UTL_HTTP.

              The APEX_WEB_SERVICE API provides a more simpler way to consume Web Services (SOAP/REST) as compared to UTL_HTTP.

               

              NOTE: APEX_WEB_SERVICE API may differ according to the version of Oracle APEX installed in Oracle Database.

               

              Regards,

              Kiran

              • 4. Re: Simplest way to create REST-client inside Oracle Database 12.1+ to call REST-services outside that database
                Kiran Pawar

                Hi Alexus67,

                Alexus67 wrote:

                 

                I need to create automatic interactions from Oracle database to REST services outside that database, so "consuming a RESTful Web Service" is right term here.

                 

                Is APEX_WEB_SERVICE package the simplest way to call REST-services outside that database?

                I know there is utl_https, but it seems to be more complex to use, is it?

                Here is an example how you can use APEX_WEB_SERVICE and APEX_JSON API (if you have Oracle APEX 5+ installed) to consume RESTful Web Service: Re: Re: Web Service API access

                 

                Regards,

                Kiran

                • 5. Re: Simplest way to create REST-client inside Oracle Database 12.1+ to call REST-services outside that database
                  Erik Raetz

                  If you already use APEX it is should be the way to go like Kiran wrote.

                   

                  If you don't have APEX using UTL_HTTP is the solution for all HTTP connections going out of the database.

                   

                  Using UTL_HTTP gives you full access over the request and response (header and body). Ontop you can log all request and response transaction data. For JSON services you can log the response body in a table that has a clob field with the trigger IS_VALID_JSON set to true. That way we can react on all kind of response errors: ACL, firewall, SSL/Wallet, HTTP status codes, semantics like BAD_JSON or  MISSING_JSON_ATTR etc.

                   

                  You can write a generic function that handles all the UTL_HTTP communication. For example based on content type you can handle all kind of different services like: form data, xml or json.

                   

                  In the end the request and response body are just strings interpreted by content type. (Well, unless served binary like for image services)

                   

                  If you are going to use UTL_HTTP here are some tips:

                   

                  When working with JSON services all string are handled in UTF-8. Hopefully your database is UTF-8 already too.

                  Sadly UTL_HTTP defaults to ISO-8859-1 if no charset is found! Make sure to set the correct charset on both request and response bodies using UTL_HTTP.SET_BODY_CHARSET or by using the content type header.

                  Following the HTTP protocol standard specification, if the media type of a request or a response is text, but the character set information is missing in the Content-Type header, the character set of the request or response body should default to ISO-8859-1.

                  Another note is not related to UTL_HTTP but https://www.ietf.org/rfc/rfc2616.txt When working with request headers and you are sending the transfer-encoding you must not send content-length and vise versa. Only one is allowed.

                  Oracle database SSL algorithms are limited by ORACLE DB version. It is possible that when communicating with highly secure financial services the SSL handshake will fail. In that scenario a reverse proxy is needed.