📔 🚯 🤩 Web Services in Oracle 🎋 💟 🚒

Web Serviceswidely used for integration between components of one system or between different systems. The popularity of web services as a way to integrate is due to their versatility, as well as ease of implementation and debugging. Versatility is associated with data transfer using the Internet and the HTTP protocol. Web services make it relatively easy to build integrations between components written in different languages that run on different operating systems and platforms. Ease of implementing web services is achieved through built-in tools and components in many IDEs that allow you to quickly develop both the web service (provider side) and the necessary code to call the service on the client side. Debugging services is simplified by the use of human-readable data exchange formats - XML and JSON. Besides,There are many utilities for debugging and testing services, including load.

In this article, we will consider several ways to create web services directly from Oracle DBMS, that is, without using third-party tools.

Content

Native Oracle XML DB Web Services


         Oracle XML DB HTTP server
         HTTP
         -
         Access List (ACL)
     -

Oracle REST Data Service






     1: POST-
         WS


         HTTP-


        CGI-
     2: ORDS

         ORDS





    Java Servlet
    Database Access Descriptor (PL/SQL Servlet)

Background

Given: information business system of a large distribution network (about a thousand retail stores), consisting of many components and subsystems. Inside each store there is a main server - an Oracle database with the main business logic. Cash nodes are managed by separate software from their local database. This software periodically collects data from the main server (via WS SOAP) and gives back the results of the sale (file exchange).

Progress did not stand still, and new equipment appeared in stores. Data from this equipment should periodically be sent to the main server of the store (period - every few minutes), the integration must necessarily go through the web service, the message must have a certain format, authentication is not needed. We looked at the data exchange contracts and found out that the used technology of web services will not allow building integration with such equipment. And the search for a solution began ...

As a result, several options for implementing the necessary web service were considered, up to writing a separate component that would open a window for the Oracle database for the Oracle database: on the one hand, it would provide a web service, on the other, it would interact with the database via JDBC. The difficulty is that the new component, firstly, would have to be installed in a thousand stores, and, secondly, another link would have appeared that would have to be accompanied. Therefore, the priority was still the option of implementing a web service with Oracle built-in tools.

As a result of the searches, we found four ways that we will consider in this article:

Native Oracle XML DB Web Services
Oracle REST Data Service
Java Servlet
Database Access Descriptor (PL / SQL servlet)

The first two options will be considered in more detail. Native Oracle XML DB Web Services was used in our system initially, that is, it was inherited, so to speak. ORDS became a replacement for this outdated technology (despite the fact that I still had to work hard and install ORDS in a thousand stores).

Two other methods - Java Servlet and PL / SQL Servlet - we considered along with ORDS when searching for an alternative to Native Oracle WS, but we did not use it in the project. Therefore, we will not consider these approaches in detail and confine ourselves to a brief reference.

The article will present some practical examples of implementing web services with instructions that will help create a working service.

Native Oracle XML DB Web Services

Capabilities

Allows you to organize access to the Oracle database via HTTP using WS SOAP (version 1.1):

SQL- XQuery- SOAP.
( ).

- : , HTTP-.
- (WSDL), , -.
-. , — , — .
XML-. , , XML, - — Oracle . , WS- Oracle — , - .

WSDL , , - . — WSDL, WSDL , .
( 50 ). , — , . , - — , , .

It is worth noting that authentication is required for all requests to the web service (from the documentation : Basic Authentication: Oracle XML DB supports Basic Authentication, where a client sends the user name and password in clear text in the Authorization header). In Oracle, you can configure anonymous access to server resources via HTTP - using the Oracle XML DB Protocol Server configuration , but in fact it works only for GET requests, and authentication is required for POST requests. Since Native Oracle WS only works through POST requests, there is no way to configure anonymous access for this technology.

Customization

To use Native Oracle WS you will need:

Configure the built-in HTTP server in Oracle.
Configure access inside the Oracle database (open the HTTP port).
Create a servlet.
Configure ACL (Access List).

Configure Oracle XML DB HTTP server

The HTTP server should already work by default, but in some cases an additional listener configuration may be required - adding the following block to DESCRIPTION_LIST:

(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8080))(Presentation=HTTP)(Session=RAW)
)

After that, you need to restart the listener.

Configure HTTP Access

Checking the current port for HTTP.
```
SELECT dbms_xdb.gethttpport() AS http_port FROM dual;
```
A value of "0" means that HTTP access is disabled.

Port setup.

BEGIN
    --    web-services
    dbms_xdb.setHttpPort(8080);
    COMMIT;
END;
/

Creating a servlet for a web service

For the web service to work, servlet registration is required in the database configuration.

Script to create a servlet

--   SYS
DECLARE
    l_servlet_name      VARCHAR2(32)  := 'orawsv';
BEGIN
    --   orawsv   Native Web Services
    DBMS_XDB.deleteServletMapping(l_servlet_name);
    DBMS_XDB.deleteServlet(l_servlet_name);
 
    DBMS_XDB.addServlet( NAME     => l_servlet_name
                       , LANGUAGE => 'C'
                       , DISPNAME => 'Oracle Query Web Service'
                       , DESCRIPT => 'Servlet for issuing queries as a Web Service'
                       , SCHEMA   => 'XDB');
 
    DBMS_XDB.addServletSecRole( SERVNAME => l_servlet_name
                              , ROLENAME => 'XDB_WEBSERVICES'
                              , ROLELINK => 'XDB_WEBSERVICES');
 
    DBMS_XDB.addServletMapping( PATTERN => '/orawsv/*'
                              , NAME    => l_servlet_name);
    COMMIT;
END;
/

Configure Access List

To access Oracle through HTTP, you need to add rules in the DBMS configuration. This is done using the built-in DBMS utilities.

To configure the ACL you will need:

script editing the database configuration (below);
database schema for which the configuration is done.

That is, the database schema for which ACL is being configured must already be created. In the examples below, there will be references to this section, in those places where you need to create new database schemas - for them you will need to configure the ACL.

In the SYS scheme, we execute the ACL configuration script:

--   SYS
DECLARE
    l_ws_user       VARCHAR2(32)  := 'WS_SOAP_TEST'; --    ,        WS
    l_acl           VARCHAR2(250) := 'acl_allow_all.xml';
    l_tmp           VARCHAR2(250);
BEGIN
    EXECUTE IMMEDIATE 'GRANT XDB_WEBSERVICES TO "'||l_ws_user||'"';
    EXECUTE IMMEDIATE 'GRANT XDB_WEBSERVICES_OVER_HTTP TO "'||l_ws_user||'"';
    EXECUTE IMMEDIATE 'GRANT XDB_WEBSERVICES_WITH_PUBLIC TO "'||l_ws_user||'"';
 
    --       PL/SQL 
    BEGIN
        dbms_network_acl_admin.drop_acl(acl => '/sys/acls/'||l_acl);
    EXCEPTION
        WHEN dbms_network_acl_admin.acl_not_found THEN
            NULL;
    END;
 
    --  ACL
    dbms_network_acl_admin.create_acl( acl         => l_acl
                                     , description => 'Allow all connections'
                                     , is_grant    => TRUE
                                     , start_date  => SYSTIMESTAMP
                                     , end_date    => NULL
                                     , principal   => 'SYS'
                                     , privilege   => 'connect');
 
    dbms_network_acl_admin.assign_acl( acl        => l_acl
                                     , host       => '*'
                                     , lower_port => NULL
                                     , upper_port => NULL);
 
    --    (resolve) 
    dbms_network_acl_admin.add_privilege( acl        => l_acl
                                        , principal  => l_ws_user
                                        , is_grant   => TRUE
                                        , privilege  => 'resolve'
                                        , POSITION   => NULL
                                        , start_date => SYSTIMESTAMP
                                        , end_date   => NULL);
 
    COMMIT;
END;
/

It should be noted right away that ACL configuration is required when using not only Native Oracle WS, but also all other methods of creating web services discussed in this article.

Web Service Example Using a Batch Procedure

We will need:

The database schema in which the objects for processing web service requests will be located.
Table for logging.
Package with procedure / function.
Any tool that allows you to send web requests. In this case, the SOAP UI application was used , but you can choose any other tool, up to the command line.

Features:

Procedure parameters must have either simple or object types. Otherwise, the procedure will not be perceived as a web service method and will not be included in the list of service methods.
To implement complex structures of input or output data, you need to use object types (batch types cannot be used).

We create the necessary database objects:

Scheme WS_TEST:

CREATE USER WS_SOAP_TEST IDENTIFIED BY ws_soap_test QUOTA 200M ON USERS;
GRANT CREATE SESSION, RESOURCE TO ws_soap_test;

Immediately add a new scheme to the ACL. The script in the previous section.

In the new scheme, create a table for logging T_WS_REQ_LOG:

CREATE TABLE T_WS_REQ_LOG
(
  id_log      NUMBER GENERATED ALWAYS AS IDENTITY,
  message     VARCHAR2(2000),
  proc        VARCHAR2(128),
  dtm_request TIMESTAMP(6) DEFAULT SYSTIMESTAMP
);
COMMENT ON TABLE T_WS_REQ_LOG IS ' HTTP-';
COMMENT ON COLUMN T_WS_REQ_LOG.id_log IS '';
COMMENT ON COLUMN T_WS_REQ_LOG.message IS '';
COMMENT ON COLUMN T_WS_REQ_LOG.proc IS '';
COMMENT ON COLUMN T_WS_REQ_LOG.dtm_request IS '/ ';

Package with a simple procedure:


CREATE OR REPLACE PACKAGE PK_NATIVE_WS_TEST IS
 
PROCEDURE proc_simple
    ( a_id      INTEGER
    , a_data    VARCHAR2
    , o_result  OUT VARCHAR2
    );
 
END PK_NATIVE_WS_TEST;
/
CREATE OR REPLACE PACKAGE BODY PK_NATIVE_WS_TEST IS
 
PROCEDURE proc_simple
    ( a_id      INTEGER
    , a_data    VARCHAR2
    , o_result  OUT VARCHAR2
    )
AS
BEGIN
    INSERT INTO t_ws_req_log (message, proc)
    VALUES ('ID='||a_id||'; DATA='||a_data, 'proc_simple')
    RETURNING id_log INTO o_result;
END proc_simple;
 
END PK_NATIVE_WS_TEST;
/

The simplest web service is created and ready to go.

To access the service you need to use the URL in the following format:

http://[server]:[port]/[servlet_name]/[DB_SCHEMA]/[WS_OBJ]?wsdl

Where:

[server] - domain name or IP address of the Oracle database server
[port] - port for access via HTTP specified in the section "Configuring access through HTTP"
[servlet_name] - name of the servlet specified in the section "Creating a servlet for web service ”
[DB_SCHEMA] - name of the database schema (in upper case)
[WS_OBJ] - name of the service (in upper case), which is equal to the name of the database object, in our case - the name of the package

Note that the URL is important!

Link example:

http://my.server:8080/orawsv/WS_SOAP_TEST/PK_NATIVE_WS_TEST?wsdl

If we click on this link in the browser, we will get automatically created based on our WSDL package:

Wsdl

<definitions name="PK_NATIVE_WS_TEST"
    targetNamespace="http://xmlns.oracle.com/orawsv/WS_SOAP_TEST/PK_NATIVE_WS_TEST"
    xmlns="http://schemas.xmlsoap.org/wsdl/"
    xmlns:tns="http://xmlns.oracle.com/orawsv/WS_SOAP_TEST/PK_NATIVE_WS_TEST"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/">
  <types>
    <xsd:schema targetNamespace="http://xmlns.oracle.com/orawsv/WS_SOAP_TEST/PK_NATIVE_WS_TEST"
     elementFormDefault="qualified">
      <xsd:element name="PROC_SIMPLEInput">
        <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="A_DATA-VARCHAR2-IN" type="xsd:string"/>
              <xsd:element name="A_ID-NUMBER-IN" type="xsd:integer"/>
            </xsd:sequence>
          </xsd:complexType>
      </xsd:element>
 
      <xsd:element name="PROC_SIMPLEOutput">
        <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="O_RESULT" type="xsd:string"/>
            </xsd:sequence>
          </xsd:complexType>
      </xsd:element>
   </xsd:schema>
  </types>
 
  <message name="PROC_SIMPLEInputMessage">
    <part name="parameters" element="tns:PROC_SIMPLEInput"/>
  </message>
 
  <message name="PROC_SIMPLEOutputMessage">
    <part name="parameters" element="tns:PROC_SIMPLEOutput"/>
  </message>
 
  <portType name="PK_NATIVE_WS_TESTPortType">
  <operation name="PROC_SIMPLE">
      <input message="tns:PROC_SIMPLEInputMessage"/>
      <output message="tns:PROC_SIMPLEOutputMessage"/>
    </operation>
  </portType>
 
  <binding name="PK_NATIVE_WS_TESTBinding"
           type="tns:PK_NATIVE_WS_TESTPortType">
    <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
    <operation name="PROC_SIMPLE">
      <soap:operation
 soapAction="PROC_SIMPLE"/>
      <input>
        <soap:body parts="parameters" use="literal"/>
      </input>
      <output>
        <soap:body parts="parameters" use="literal"/>
      </output>
    </operation>
  </binding>
 
  <service name="PK_NATIVE_WS_TESTService">
    <documentation>Oracle Web Service</documentation>
    <port name="PK_NATIVE_WS_TESTPort" binding="tns:PK_NATIVE_WS_TESTBinding">
       <soap:address 
             location="http://******:8080/orawsv/WS_SOAP_TEST/PK_NATIVE_WS_TEST"/>
     </port>
  </service>
 
</definitions>

To test the service, create a new SOAP project in the SOAP UI. As the URL, specify the address from the example above.

Upon opening, Request 1we see that the request template is already generated automatically by the specified WSDL.

Immediately add Basic authorization for the request - the login and password are the same as when connecting to the database.

Example request and response. Please note that a line has been added to the automatically generated request <pk:O_RESULT-VARCHAR2-OUT/>. The fact is that there is a bug in Oracle 12c: OUT variables are not added to the WSDL for Native Oracle WS).

The result of the call is visible in the log table.

findings

Native Oracle XML DB Web Services technology can be used as an industrial solution provided that there is no heavy load on this web service (one request in a few seconds). This option is suitable when consumers do not have strict requirements for a web service (its attributes, data structures, processing logic) and there is no strict predetermined contract. If you need to create a web service according to a predefined WSDL (as in our case with new equipment), then the Native Oracle WS technology will be unsuitable.

Oracle REST Data Service

Starting with version 11.1, Oracle introduced full RESTful support in the form of a separate module called Oracle REST Data Service (ORDS).

ORDS is a Java application that allows you to create a RESTful API for an Oracle database using SQL and PL / SQL. It is an alternative to using Oracle HTTP Server and mod_plsql. In essence, ORDS is an HTTP interface between the outside world and an Oracle database. This interface allows you to close incoming HTTP requests to any database object - a table or PL / SQL procedure.

All that needs to be done is to install, configure and run ORDS for the required database. The further process of creating REST services comes down to writing code in PL / SQL (or even by clicking in the IDE if the code is too lazy to write).

No additional licenses are required to install ORDS.

Requirements:

Java JDK 8 or higher;
Oracle 11.1 or later (Oracle 11 XE Release 2 is also supported).

Several ORDS deployment options are supported:

standalone mode;
on the application server (Oracle WebLogic Server, Apache Tomcat).

Capabilities

ORDS allows you to:

Organize access to resources in a RESTful style.
Build an interaction in the style of "Remote Procedure Call" (RPC-style interaction).

Simply put, with the help of ORDS, you can open access via HTTP to certain database objects - tables, procedures, functions, packages.

In the first case, we are dealing with resources in the sense that they are understood in the RESTful architectural style. Each resource is defined by a unique URI, and operations with resources (CRUD - create, read, modify, delete) are determined by operations from the HTTP request: PUT, POST, GET, DELETE. For example, if the resource is an entry in the employee table, then this entry will be available by a URI of the form:

GET https://server:port/ords/workspace/hr/employees/7369

Here, the database schema name, table name, and record ID in the table are indicated in the URI. Thus, this HTTP request actually performs a SELECT operation from the specified table. With other operations (adding, changing, deleting), the principle of communication is the same: the path to the resource is indicated in the URI, and additional parameters, for example, the values of the fields for inserting the record into the table, are indicated in the request body.

In the second case, instead of accessing the resource, a procedure call is directly used, which can do everything the same as in the first case (CRUD), as well as execute any other logic that implements the necessary business process. A procedure call from a package can be made using an HTTP request of this format:

http://localhost:8080/ords/my_schema/my_pkg/MY_PROC

Parameters for the procedure are transferred in the request body in the form of JSON format structures {"param" : "value"}. The same approach is used when using the Native Oracle WS, discussed above, but in the case of REST services there are no restrictions imposed by the SOAP protocol.

pros

A flexible mechanism that allows you to implement web services of any complexity.
Simplicity of implementation: to turn a PL / SQL procedure into a web service, you need to execute only a few typical commands. And using SQL Developer, creating a REST API turns into a mouse click with a minimum of code writing.
The ability to implement a web service without authentication.

Minus

ORDS is not included in the standard Oracle package - it must be installed separately for each server (additional licenses, as already mentioned, are not required). This can be a problem if you have thousands of database servers.

Customization

ORDS can be launched in two modes: through the application server or in standalone mode.

This article only discusses the option to run in standalone mode.

For ORDS to work you need:

Configure settings before installation.
Complete the ODRS installation.
Launch ORDS.
Configure ACL (for the desired database schema).

Settings

The ORDS distribution has a file with parameters that looks like this by default:

db.hostname=
db.port=
db.servicename=
db.sid=
db.username=APEX_PUBLIC_USER
migrate.apex.rest=false
rest.services.apex.add=
rest.services.ords.add=true
schema.tablespace.default=SYSAUX
schema.tablespace.temp=TEMP
standalone.http.port=8080
standalone.static.images=
user.tablespace.default=USERS
user.tablespace.temp=TEMP

A description of the parameters can be found in the documentation

Parameters for standalone-mode:

Parameter	Description
`db.hostname`	Name or IP Address of Oracle Database Server
`db.port`	DB port (e.g. 1521)
`db.servicename`	Servicename Database
`db.username`	The username of the PL / SQL gateway. Default = APEX_PUBLIC_USER, but the example does not plan to use APEX, so you can leave this parameter blank
`db.password`	User password (also do not fill out)
`migrate.apex.rest`	Transition of RESTful APEX services to ORDS. Set to false if APEX is not used.
`rest.services.apex.add`	Configure ORDS for use in APEX
`rest.services.ords.add`	Set the database schema for ORDS (ORDS_PUBLIC_USER and ORDS_METADATA). Value must be true
`schema.tablespace.default`	Default table space for ORDS_METADATA schema
`schema.tablespace.temp`	Temporary tablespace for ORDS_METADATA schema
`standalone.http.port`	The port on which ORDS will run. This port will be used in the URI to access WS.
`user.tablespace.default`	Default table space for ORDS_PUBLIC_USER schema
`user.tablespace.temp`	Temporary table space for ORDS_PUBLIC_USER schema

As a result, the contents of the file should look something like this:

db.hostname=your_server_host_name
db.port=1521
db.servicename=your_db_servicename
migrate.apex.rest=false
rest.services.apex.add=false
rest.services.ords.add=true
schema.tablespace.default=SYSAUX
schema.tablespace.temp=TEMP
standalone.http.port=8888
standalone.static.images=
user.tablespace.default=USERS
user.tablespace.temp=TEMP

The necessary settings are completed, you can proceed with the installation.

Installation

We execute the command in the OS:

java -jar ords.war

By default, the configuration file is taken from the directory paramslocated next to ords.war. You can explicitly specify the path to this file using the parameter --parameterFile:

java -jar ords.war --parameterFile /path/to/params/myown_params.properties

In the installation dialog, do the following:

We indicate the path to the directory for saving the configuration (in the example it is indicated conf - as a result, in the same directory where the file is located ords.war, a directory will be created confin which the files with the ORDS configuration will be created).
After the command Enter the database password for ORDS_PUBLIC_USER appears, we enter the password for the scheme ORDS_PUBLIC_USER. Under this user, ORDS will connect to the database.
After the prompt Enter 1 if you want to use PL / SQL Gateway or 2 to skip this step appears, we answer “2”, since we are not going to use APEX and we do not need to migrate from mod_plsql.
After the instruction Enter 1 if you wish to start in standalone mode or 2 to exit [1] appears, we answer “1”.
After the instruction Enter 1 if using HTTP or 2 if using HTTPS [1] appears, we answer 1.

Installation dialog

D:\ords-19.2.0.199.1647>java -jar ords.war install
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts


Enter the location to store configuration data: conf
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
03, 2019 2:47:49 PM oracle.dbtools.rt.config.setup.SchemaSetup getInstallOrUpgrade
WARNING: Failed to connect to user ORDS_PUBLIC_USER jdbc:oracle:thin:@//***YOUR_HOST_NAME.DOMAIN***:1521/***YOUR_SERVICE_NAME.DOMAIN***

Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
03, 2019 2:48:32 PM
INFO: reloaded pools: []
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:
Enter 1 if using HTTP or 2 if using HTTPS [1]:
2019-09-03 14:48:49.754:INFO::main: Logging initialized @4276887ms to org.eclipse.jetty.util.log.StdErrLog
03, 2019 2:48:49 PM
INFO: HTTP and HTTP/2 cleartext listening on port: 8082
03, 2019 2:48:50 PM
INFO: Disabling document root because the specified folder does not exist: D:\ords-19.2.0.199.1647\conf\ords\standalone\doc_root
Exception in thread "main" java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at oracle.dbtools.jarcl.Entrypoint.invoke(Entrypoint.java:66)
at oracle.dbtools.jarcl.Entrypoint.main(Entrypoint.java:89)
Caused by: java.lang.OutOfMemoryError: Java heap space
at java.util.Arrays.copyOf(Unknown Source)
at java.lang.AbstractStringBuilder.ensureCapacityInternal(Unknown Source)
at java.lang.AbstractStringBuilder.append(Unknown Source)
at java.lang.StringBuilder.append(Unknown Source)
at java.lang.StringBuilder.append(Unknown Source)
at java.util.AbstractMap.toString(Unknown Source)
at java.lang.String.valueOf(Unknown Source)
at java.lang.StringBuilder.append(Unknown Source)
at oracle.dbtools.jarcl.zip.ZipIndex.toString(ZipIndex.java:166)
at java.lang.String.valueOf(Unknown Source)
at java.lang.StringBuilder.append(Unknown Source)
at oracle.dbtools.jarcl.JarClassLoader.toString(JarClassLoader.java:51)
at org.eclipse.jetty.server.ClassLoaderDump.dump(ClassLoaderDump.java:67)
at org.eclipse.jetty.util.component.Dumpable.dumpObjects(Dumpable.java:225)
at org.eclipse.jetty.util.component.ContainerLifeCycle.dumpObjects(ContainerLifeCycle.java:746)
at org.eclipse.jetty.server.handler.ContextHandler.dump(ContextHandler.java:259)
at org.eclipse.jetty.util.component.Dumpable.dumpObjects(Dumpable.java:162)
at org.eclipse.jetty.util.component.ContainerLifeCycle.dumpObjects(ContainerLifeCycle.java:746)
at org.eclipse.jetty.util.component.ContainerLifeCycle.dump(ContainerLifeCycle.java:701)
at org.eclipse.jetty.util.component.Dumpable.dump(Dumpable.java:62)
at org.eclipse.jetty.util.component.ContainerLifeCycle.dump(ContainerLifeCycle.java:684)
at oracle.dbtools.standalone.StandaloneConfiguration.start(StandaloneConfiguration.java:241)
at oracle.dbtools.standalone.Standalone.execute(Standalone.java:508)
at oracle.dbtools.cmdline.DefaultCommand.execute(DefaultCommand.java:137)
at oracle.dbtools.cmdline.Commands.execute(Commands.java:207)
at oracle.dbtools.cmdline.Commands.main(Commands.java:163)
at oracle.dbtools.cmdline.Commands.main(Commands.java:368)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at oracle.dbtools.jarcl.Entrypoint.invoke(Entrypoint.java:66)

As a result, a directory with the ORDS configuration will be created, and utility schemes for ORDS will appear in the database.

See also: Install Oracle REST Data Services 3.0.X in under 5 minutes .

Launch

Starting in standalone mode is performed by the command:

java -jar ords.war standalone

Launch dialog

D:\ords-19.2.0.199.1647>java -jar ords.war standalone
2019-09-03 15:52:45.825:INFO::main: Logging initialized @2079ms to org.eclipse.jetty.util.log.StdErrLog
03, 2019 3:52:45 PM
INFO: HTTP and HTTP/2 cleartext listening on port: 8082
03, 2019 3:52:45 PM
INFO: Disabling document root because the specified folder does not exist: D:\ords-19.2.0.199.1647\conf\ords\standalone\doc_root
2019-09-03 15:52:47.124:INFO:oejs.Server:main: jetty-9.4.z-SNAPSHOT; built: 2019-05-02T09:46:34.874Z; git: 14f32d50076f2b706f41a33066eb364d8492e199; jvm 1.8.0_221-b11
2019-09-03 15:52:47.179:INFO:oejs.session:main: DefaultSessionIdManager workerName=node0
2019-09-03 15:52:47.179:INFO:oejs.session:main: No SessionScavenger set, using defaults
2019-09-03 15:52:47.180:INFO:oejs.session:main: node0 Scavenging every 660000ms
03, 2019 3:52:48 PM
INFO: Configuration properties for: |apex|pu|
db.hostname=***YOUR_HOST_NAME.DOMAIN***
db.password=******
db.port=1521
db.servicename=***YOUR_SERVICE_NAME.DOMAIN***
db.username=ORDS_PUBLIC_USER
resource.templates.enabled=true

03, 2019 3:52:48 PM
WARNING: *** jdbc.MaxLimit in configuration |apex|pu| is using a value of 10, this setting may not be sized adequately for a production environment ***
03, 2019 3:52:48 PM
WARNING: *** jdbc.InitialLimit in configuration |apex|pu| is using a value of 3, this setting may not be sized adequately for a production environment ***
03, 2019 3:52:50 PM
INFO: Oracle REST Data Services initialized
Oracle REST Data Services version : 19.2.0.r1991647
Oracle REST Data Services server info: jetty/9.4.z-SNAPSHOT

2019-09-03 15:52:50.484:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@d56d67{/ords,null,AVAILABLE}
2019-09-03 15:52:50.658:INFO:oejs.AbstractConnector:main: Started ServerConnector@12325ad{HTTP/1.1,[http/1.1, h2c]}{0.0.
0.0:8082}
2019-09-03 15:52:50.659:INFO:oejs.Server:main: Started @6914ms

After that, ORDS is up and running. The web service address will be http: // <hostname>: / ords / ..., where <hostname> is the name of the computer running ORDS (it does not have to match the database server name, that is, ORDS can be run on another host ), <port> - the port specified in the ORDS configuration.

If you wish, you can create a script to automatically run ORDS when the OS starts.

Access setting

And the last step is to configure the ACL. The steps are the same as using Native Oracle WS.

You must complete this step after the database schema has been created, so the examples below will separately indicate when you need to perform this configuration. So far, we can assume that all the preliminary steps have been completed, and proceed with the examples.

Example 1: Handler for a POST request

As an example, consider the RPC-style interaction implementation option, that is, we will make a web service method, the handler of which will be a batch procedure.

Actually, it was in this version that the work with ORDS was implemented in our project for the distribution network, and the result met its expectations. It turned out a quick and universal way to create new methods of web services for different business processes - from working with equipment to the API for sites.

As mentioned above, ORDS support is also included in SQL Developer (the database IDE developed by Oracle). In SQL Developer, creating web services is available directly from menu items or the context menu.

In this article, examples are made using SQL Developer (version 18), but the PL / SQL code that is executed in the database as a result of actions in the IDE is also included.

The version of the database on which the experiments were done:

SQL> SELECT v.BANNER FROM v$version v;
 
BANNER
--------------------------------------------------------------------------------
Oracle DATABASE 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE	12.2.0.1.0	Production
TNS FOR Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

Creating a test user for WS

For examples, we again need a new scheme - create it:

CREATE USER WS_TEST IDENTIFIED BY ws_test QUOTA 200M ON USERS;
GRANT CREATE SESSION, RESOURCE TO ws_test;

Setting up a database schema

After the diagram is created, it is necessary to make it accessible through ORDS.

For this:

In SQL Developer, create a connection for WS_TEST.
In the list of connections, call the context menu for the new connection. Choose REST Services → Enable REST Services ... :
In the dialog, check the box next to Enable schema (you can also change the “ Schema alias ” parameter here if you want ws_testother text to be displayed in the URI instead of the schema name ):
By clicking “Next” , we will see the PL / SQL code that will eventually be executed in the database (and which you can write yourself without using the IDE):

Listing:

BEGIN
    ORDS.ENABLE_SCHEMA( p_enabled             => TRUE
                      , p_schema              => 'WS_TEST'
                      , p_url_mapping_type    => 'BASE_PATH'
                      , p_url_mapping_pattern => 'ws_test'
                      , p_auto_rest_auth      => FALSE);
 
    COMMIT;
END;
/

Creating a module and template

Next, you need to create a module and template for the resource.

A module is a structural unit that allows you to group several logically related resource templates.

A template is a specific web service serving a specific set of methods for a resource.

At source :

Resource module: An organizational unit that is used to group related resource templates.

Resource template: An individual RESTful service that is able to service requests for some set of URIs (Universal Resource Identifiers). The set of URIs is defined by the URI Pattern of the Resource Template.

For example, for an online store, a module can be calledshop - in this module all the store’s APIs will be combined. The templates can be specific resources, for example, an order (template order), product catalog (template item), payment (template payment), etc.

To create a module and template, you need to perform the following steps:

In the object tree, open the REST Data Services section, call the context menu in the Modules section , select New module ... :
In the module creation dialog, specify the module name ( shop), prefix URI (also indicate shop - in the example below we immediately see what the web service address template will be), put a daw in front of Publish , click "Next>" :
In the next dialog, specify the name of the template, for example order, and again click "Next>" .

In the last dialog, we see all the entered parameters of the module and template. On the SQL tab, you can see the PL / SQL code that will be executed in the database when you click Finish :

To create a module shop , you can add more patterns - also in the object tree, the context menu and selecting the Add the Template ... .

Code listing to create a module and template

BEGIN
    ORDS.DEFINE_MODULE( p_module_name    => 'shop'
                      , p_base_path      => 'shop'
                      , p_items_per_page =>  25
                      , p_status         => 'PUBLISHED'
                      , p_comments       => NULL);
 
    ORDS.DEFINE_TEMPLATE( p_module_name    => 'shop'
                        , p_pattern        => 'order'
                        , p_priority       => 0
                        , p_etag_type      => 'HASH'
                        , p_etag_query     => NULL
                        , p_comments       => NULL);
 
    COMMIT;
END;
/

Creating an HTTP Request Handler

Now we need to create an HTTP request handler for our service.

First, we need to create database objects that will contain the request processing logic.

We need a table for logging and a package in which there will be a handler code.

Create a table using a script:

CREATE TABLE T_WS_LOG
(
  id_log          NUMBER GENERATED ALWAYS AS IDENTITY,
  message         VARCHAR2(2000),
  request_header  VARCHAR2(2000),
  request_body    CLOB,
  response_header VARCHAR2(2000),
  response_body   CLOB,
  dtz_log         TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP
);
COMMENT ON TABLE T_WS_LOG IS '  HTTP- (ORDS)';
COMMENT ON COLUMN T_WS_LOG.id_log IS '';
COMMENT ON COLUMN T_WS_LOG.message IS ' ';
COMMENT ON COLUMN T_WS_LOG.request_header IS ' ';
COMMENT ON COLUMN T_WS_LOG.request_body IS ' ';
COMMENT ON COLUMN T_WS_LOG.response_header IS ' ';
COMMENT ON COLUMN T_WS_LOG.response_body IS ' ';
COMMENT ON COLUMN T_WS_LOG.dtz_log IS '/  ';
ALTER TABLE T_WS_LOG ADD CONSTRAINT PK_T_WS_LOG PRIMARY KEY (ID_LOG) USING INDEX;

Create a package:

CREATE OR REPLACE PACKAGE PK_ORDS_API IS
 
 
FUNCTION blob2clob
    ( a_blob            BLOB
    , a_from_charset    VARCHAR2  := 'AMERICAN_AMERICA.AL32UTF8'
    , a_to_charset      VARCHAR2  := 'AMERICAN_AMERICA.AL32UTF8'
    ) RETURN            CLOB;
 
 
PROCEDURE process_request
    ( a_request     CLOB
    );
 
 
END PK_ORDS_API;
/
 
CREATE OR REPLACE PACKAGE BODY PK_ORDS_API IS
 
 
FUNCTION blob2clob
    ( a_blob            BLOB
    , a_from_charset    VARCHAR2  := 'AMERICAN_AMERICA.AL32UTF8'
    , a_to_charset      VARCHAR2  := 'AMERICAN_AMERICA.AL32UTF8'
    ) RETURN            CLOB
AS
    l_clob      CLOB;
    l_amount    NUMBER := 2000;
    l_offset    NUMBER := 1;
    l_buffer    VARCHAR2(32767);
    l_length    PLS_INTEGER := dbms_lob.getlength(a_blob);
BEGIN
    dbms_lob.createtemporary(l_clob, TRUE);
    dbms_lob.OPEN(l_clob, dbms_lob.lob_readwrite);
    WHILE l_offset <= l_length LOOP
        l_buffer := UTL_RAW.cast_to_varchar2(UTL_RAW.convert( r            => dbms_lob.substr(a_blob, l_amount, l_offset)
                                                            , from_charset => a_from_charset
                                                            , to_charset   => a_to_charset));
        IF LENGTH(l_buffer) > 0 THEN
            dbms_lob.writeappend(l_clob, LENGTH(l_buffer), l_buffer);
        END IF;
        l_offset := l_offset + l_amount;
        EXIT WHEN l_offset > l_length;
    END LOOP;
    RETURN l_clob;
END blob2clob;
 
 
PROCEDURE process_request
    ( a_request     CLOB
    )
AS
    TYPE TStringHash IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(256);
    lh_hdr              TStringHash;
    l_hdr               VARCHAR2(256);
    l_resp              CLOB;
    l_response_status   INTEGER := 200;
    l_ccontent_type     VARCHAR2(64) := 'application/json';
    l_in_headers        VARCHAR2(32767);
BEGIN
    --      
    lh_hdr('SERVER_SOFTWARE')       := OWA_UTIL.get_cgi_env('SERVER_SOFTWARE');
    lh_hdr('SERVER_NAME')           := OWA_UTIL.get_cgi_env('SERVER_NAME');
    lh_hdr('GATEWAY_INTERFACE')     := OWA_UTIL.get_cgi_env('GATEWAY_INTERFACE');
    lh_hdr('SERVER_PROTOCOL')       := OWA_UTIL.get_cgi_env('SERVER_PROTOCOL');
    lh_hdr('SERVER_PORT')           := OWA_UTIL.get_cgi_env('SERVER_PORT');
    lh_hdr('REQUEST_METHOD')        := OWA_UTIL.get_cgi_env('REQUEST_METHOD');
    lh_hdr('PATH_INFO')             := OWA_UTIL.get_cgi_env('PATH_INFO');
    lh_hdr('PATH_TRANSLATED')       := OWA_UTIL.get_cgi_env('PATH_TRANSLATED');
    lh_hdr('SCRIPT_NAME')           := OWA_UTIL.get_cgi_env('SCRIPT_NAME');
    lh_hdr('QUERY_STRING')          := OWA_UTIL.get_cgi_env('QUERY_STRING');
    lh_hdr('REMOTE_HOST')           := OWA_UTIL.get_cgi_env('REMOTE_HOST');
    lh_hdr('REMOTE_ADDR')           := OWA_UTIL.get_cgi_env('REMOTE_ADDR');
    lh_hdr('AUTH_TYPE')             := OWA_UTIL.get_cgi_env('AUTH_TYPE');
    lh_hdr('REMOTE_USER')           := OWA_UTIL.get_cgi_env('REMOTE_USER');
    lh_hdr('REMOTE_IDENT')          := OWA_UTIL.get_cgi_env('REMOTE_IDENT');
    lh_hdr('CONTENT-TYPE')          := OWA_UTIL.get_cgi_env('CONTENT-TYPE');
    lh_hdr('CONTENT-LENGTH')        := OWA_UTIL.get_cgi_env('CONTENT-LENGTH');
    lh_hdr('HTTP_ACCEPT')           := OWA_UTIL.get_cgi_env('HTTP_ACCEPT');
    lh_hdr('HTTP_ACCEPT_LANGUAGE')  := OWA_UTIL.get_cgi_env('HTTP_ACCEPT_LANGUAGE');
    lh_hdr('HTTP_USER_AGENT')       := OWA_UTIL.get_cgi_env('HTTP_USER_AGENT');
    lh_hdr('HTTP_COOKIE')           := OWA_UTIL.get_cgi_env('HTTP_COOKIE');
 
    l_hdr := lh_hdr.FIRST;
    WHILE l_hdr IS NOT NULL LOOP
        IF lh_hdr(l_hdr) IS NOT NULL THEN
            l_in_headers := l_in_headers||CHR(10)||l_hdr||': '||lh_hdr(l_hdr);
        END IF;
        l_hdr := lh_hdr.NEXT(l_hdr);
    END LOOP;
 
    l_resp := '{ "result" : "success" }';
 
    INSERT INTO t_ws_log
        ( message
        , request_header
        , request_body
        , response_header
        , response_body)
    VALUES
        ( NULL
        , l_in_headers
        , a_request
        , 'Content-Type: '||l_ccontent_type
        , l_resp
        );
 
    OWA_UTIL.STATUS_LINE(nstatus => l_response_status, bclose_header => FALSE);
    OWA_UTIL.MIME_HEADER(ccontent_type => l_ccontent_type, bclose_header => FALSE);
    OWA_UTIL.HTTP_HEADER_CLOSE();
    htp.p(l_resp);
END process_request;
 
END PK_ORDS_API;
/

Now for the created template you need to add a handler. Add a handler for the HTTP method POST.

To do this, perform the following steps:

We call the context menu for the template, select Add Handler and then select the HTTP method:
. SQL Worksheet PL/SQL-, HTTP- POST. , process_request . , bind- :body — ORDS, ( BLOB). . :body_text, CLOB. , , , ORDS . :body_text , «» . :body, CLOB . , :

Save REST Handler — .

POST-:

DECLARE
    l_blob BLOB := :body;
BEGIN
    PK_ORDS_API.process_request(a_request => PK_ORDS_API.blob2clob(l_blob));
EXCEPTION
    WHEN OTHERS THEN
        OWA_UTIL.STATUS_LINE(nstatus => 500, bclose_header => FALSE);
        OWA_UTIL.MIME_HEADER(ccontent_type => 'application/json');
        htp.p('{ "result" : "error", "message" : "'||SQLERRM||'" }');
END;

If necessary, you can get the PL / SQL code to create a handler. To do this, in the context menu of the ORDS module, select REST Definition and then specify where to output the module creation script, for example, to the clipboard:

The resulting code to create the module

-- Generated by Oracle SQL Developer REST Data Services 18.1.0.095.1630
-- Exported REST Definitions from ORDS Schema Version 18.4.0.r3541002
-- Schema: WS_TEST   Date: Wed Oct 23 20:19:54 MSK 2019
--
BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'WS_TEST',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'ws_test',
      p_auto_rest_auth      => TRUE);    
 
  ORDS.DEFINE_MODULE(
      p_module_name    => 'shop',
      p_base_path      => '/shop/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'shop',
      p_pattern        => 'order',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'shop',
      p_pattern        => 'order',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'DECLARE
    l_blob BLOB := :body;
BEGIN
    PK_ORDS_API.process_request(a_request => PK_ORDS_API.blob2clob(l_blob));
EXCEPTION
    WHEN OTHERS THEN
        OWA_UTIL.STATUS_LINE(nstatus => 500, bclose_header => FALSE);
        OWA_UTIL.MIME_HEADER(ccontent_type => ''application/json'');
        htp.p(''{ "result" : "error", "message" : "''||SQLERRM||''" }'');
END;'
      );
 
 
  COMMIT; 
END;

Note: if you perform the module creation procedure ORDS.DEFINE_MODULEagain, then all templates of this module will be automatically deleted, and there will be no warning about this!

Call example

This is where our web service is ready. It remains to check its work.

To check, we execute the request:

POST http://****:8888/ords/ws_test/shop/order HTTP/1.1
Accept-Encoding: gzip,deflate
Content-Type: application/json
Content-Length: 22
Host: ****:8888
Connection: Keep-Alive
User-Agent: Apache-HttpClient/4.1.1 (java 1.5)
 
{ "message" : "test" }

In response, we get:

HTTP/1.1 200 OK
Date: Wed, 23 Oct 2019 16:54:53 GMT
Content-Type: application/json
Transfer-Encoding: chunked
 
{ "result" : "success" }

The result of the method execution is a record with the body of the request and response in the log table:

As you can see, the sent HTTP request was successfully processed by the procedure from the packet.

Parameters from the request

Above, we have already seen the result of a simple web service. Now let's complicate the task a bit by adding additional parameters to the request.

There are two ways to pass parameters:

Via URL. Parameters are set in the URL in the standard form:

http://...URI...?p1=val1&p2=val2

Via HEADER. Parameters are set in the request header as p1: val1

In ORDS in the request handler, parameters are defined as bind variables.

We add two parameters to the previous example: prm1- parameter in the URI, prm2- parameter in the request header.

To process these parameters, PK_ORDS_API.process_request:we add the procedure, add the parameters a_prm_uriand a_prm_hdr, to which the values of our parameters from the request will come.

Request handler procedure with new parameters

PROCEDURE process_request
    ( a_request     CLOB
    , a_prm_uri     VARCHAR2 := NULL
    , a_prm_hdr     VARCHAR2 := NULL
    )
AS
    TYPE TStringHash IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(256);
    lh_hdr              TStringHash;
    l_hdr               VARCHAR2(256);
    l_resp              CLOB;
    l_response_status   INTEGER := 200;
    l_ccontent_type     VARCHAR2(64) := 'application/json';
    l_in_headers        VARCHAR2(32767);
BEGIN
    --      
    lh_hdr('SERVER_SOFTWARE')       := OWA_UTIL.get_cgi_env('SERVER_SOFTWARE');
    lh_hdr('SERVER_NAME')           := OWA_UTIL.get_cgi_env('SERVER_NAME');
    lh_hdr('GATEWAY_INTERFACE')     := OWA_UTIL.get_cgi_env('GATEWAY_INTERFACE');
    lh_hdr('SERVER_PROTOCOL')       := OWA_UTIL.get_cgi_env('SERVER_PROTOCOL');
    lh_hdr('SERVER_PORT')           := OWA_UTIL.get_cgi_env('SERVER_PORT');
    lh_hdr('REQUEST_METHOD')        := OWA_UTIL.get_cgi_env('REQUEST_METHOD');
    lh_hdr('PATH_INFO')             := OWA_UTIL.get_cgi_env('PATH_INFO');
    lh_hdr('PATH_TRANSLATED')       := OWA_UTIL.get_cgi_env('PATH_TRANSLATED');
    lh_hdr('SCRIPT_NAME')           := OWA_UTIL.get_cgi_env('SCRIPT_NAME');
    lh_hdr('QUERY_STRING')          := OWA_UTIL.get_cgi_env('QUERY_STRING');
    lh_hdr('REMOTE_HOST')           := OWA_UTIL.get_cgi_env('REMOTE_HOST');
    lh_hdr('REMOTE_ADDR')           := OWA_UTIL.get_cgi_env('REMOTE_ADDR');
    lh_hdr('AUTH_TYPE')             := OWA_UTIL.get_cgi_env('AUTH_TYPE');
    lh_hdr('REMOTE_USER')           := OWA_UTIL.get_cgi_env('REMOTE_USER');
    lh_hdr('REMOTE_IDENT')          := OWA_UTIL.get_cgi_env('REMOTE_IDENT');
    lh_hdr('CONTENT-TYPE')          := OWA_UTIL.get_cgi_env('CONTENT-TYPE');
    lh_hdr('CONTENT-LENGTH')        := OWA_UTIL.get_cgi_env('CONTENT-LENGTH');
    lh_hdr('HTTP_ACCEPT')           := OWA_UTIL.get_cgi_env('HTTP_ACCEPT');
    lh_hdr('HTTP_ACCEPT_LANGUAGE')  := OWA_UTIL.get_cgi_env('HTTP_ACCEPT_LANGUAGE');
    lh_hdr('HTTP_USER_AGENT')       := OWA_UTIL.get_cgi_env('HTTP_USER_AGENT');
    lh_hdr('HTTP_COOKIE')           := OWA_UTIL.get_cgi_env('HTTP_COOKIE');
    lh_hdr('a_prm_uri')             := a_prm_uri;
    lh_hdr('a_prm_hdr')             := a_prm_hdr;
 
    l_hdr := lh_hdr.FIRST;
    WHILE l_hdr IS NOT NULL LOOP
        IF lh_hdr(l_hdr) IS NOT NULL THEN
            l_in_headers := l_in_headers||CHR(10)||l_hdr||': '||lh_hdr(l_hdr);
        END IF;
        l_hdr := lh_hdr.NEXT(l_hdr);
    END LOOP;
 
    l_resp := '{ "result" : "success" }';
 
    INSERT INTO t_ws_log
        ( message
        , request_header
        , request_body
        , response_header
        , response_body)
    VALUES
        ( NULL
        , l_in_headers
        , a_request
        , 'Content-Type: '||l_ccontent_type
        , l_resp);
 
    OWA_UTIL.STATUS_LINE(nstatus => l_response_status, bclose_header => FALSE);
    OWA_UTIL.MIME_HEADER(ccontent_type => l_ccontent_type, bclose_header => FALSE);
    OWA_UTIL.HTTP_HEADER_CLOSE();
    htp.p(l_resp);
END process_request;

Inside the procedure, we simply record the values of the new parameters in the log.

Add new parameters to the POST request handler - in the form of bind variables :prm_uriand :prm_hdr.

Handler of the POST request request with new parameters:

DECLARE
    l_blob BLOB := :body;
BEGIN
    PK_ORDS_API.process_request(a_request => PK_ORDS_API.blob2clob(l_blob), a_prm_uri => :prm_uri, a_prm_hdr => :prm_hdr);
EXCEPTION
    WHEN OTHERS THEN
        OWA_UTIL.STATUS_LINE(nstatus => 500, bclose_header => FALSE);
        OWA_UTIL.MIME_HEADER(ccontent_type => 'application/json');
        htp.p('{ "result" : "error", "message" : "'||SQLERRM||'" }');
END;

In the handler, on the Parameters tab, declare the variables:

In this form, the first field ( Name ) contains the name of the parameter that is expected in the request, the second field ( Bind Parameter ) - the name of the bind variable that will be specified in the handler of this request.

Let's execute the request with new parameters:

Result - the parameters from the request were saved in the log:

Please note that the parameters from the URI can also be retrieved from the CGI variable QUERY_STRING, that is, to get the parameters it is not necessary to start the bind variables - you can parse them in the handler procedure request.

CGI variables

When working with HTTP in Oracle, it is possible to obtain the values of environment variables that reflect the context of the HTTP request. You can get the values of variables using the procedure OWA_UTIL.get_cgi_env.

List of CGI Variables Available in PL / SQL

APEX_LISTENER_VERSION
GATEWAY_INTERFACE
GATEWAY_IVERSION
HTTP_ACCEPT_ENCODING
HTTP_HOST
HTTP_PORT
HTTP_USER_AGENT
PATH_ALIAS
PATH_INFO
PLSQL_GATEWAY
QUERY_STRING
REMOTE_ADDR
REMOTE_USER
REQUEST_CHARSET
REQUEST_IANA_CHARSET
REQUEST_METHOD
REQUEST_PROTOCOL
REQUEST_SCHEME
SCRIPT_NAME
SERVER_NAME
SERVER_PORT
SERVER_PROTOCOL
SERVER_SOFTWARE
WEB_AUTHENT_PREFIX
host
user-agent
CONTENT-LENGTH
CONTENT-TYPE

Example 2: Accessing a table through ORDS

In this example, we consider the organization of access to a database object (to a table) through ORDS.

As in the previous example, we make access without authorization. See how to make secure access to resources in the documentation .

To make a database object accessible through ORDS, you need to perform only one step - the command ORDS.ENABLE_OBJECT. After that, the object can be accessed by a URI of the form:

http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>

Create a test pattern

For an example we will create the table "Orders".

Table creation script:

CREATE TABLE T_ORDER
(
  id_order   NUMBER NOT NULL,
  NUM        VARCHAR2(32),
  buyer_name VARCHAR2(256),
  dt_order   DATE,
  memo       VARCHAR2(2000)
);
 
COMMENT ON TABLE T_ORDER IS '';
COMMENT ON COLUMN T_ORDER.id_order IS ' ';
COMMENT ON COLUMN T_ORDER.num IS ' ';
COMMENT ON COLUMN T_ORDER.buyer_name IS ' ';
COMMENT ON COLUMN T_ORDER.dt_order IS '  ';
COMMENT ON COLUMN T_ORDER.memo IS '';
ALTER TABLE T_ORDER ADD CONSTRAINT PK_T_ORDER PRIMARY KEY (ID_ORDER) USING INDEX;

Opening table access through ORDS

In SQL Developer invoke the context menu for the required table, select the Enable the Service the REST ... .
In the access settings window, check the Enable object checkbox , uncheck the Authorization required checkbox , click "Finish" (or "Next" to see the received PL / SQL code):

After performing these steps, the table T_ORDERbecomes available through HTTP, the base URI for accessing the resource:

http://<server>:<port>/ords/ws_test/t_order

Table inclusion listing:

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 
    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'WS_TEST',
                       p_object => 'T_ORDER',
                       p_object_type => 'TABLE',
                       p_object_alias => 't_order',
                       p_auto_rest_auth => FALSE);
 
    commit;
 
END;
/

Create or edit a record

Access to the table is open - we check how you can create and edit records in the table through ORDS.

To create a record, we execute the request PUT.

In the ORDS documentation, the following description PUTis specified in the method description :

PUT http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/<KeyValues>

That is, the field KeyValues(record key) must be filled even for a new record being created. The query itself should list all the fields in the table (but the key field can be omitted).

Inquiry

PUT http://<server>:<port>/ords/ws_test/t_order/25 HTTP/1.1
Accept-Encoding: gzip,deflate
Content-Type: application/json;charset=UTF-8
Content-Length: 157
Host: <server>:<port>
Connection: Keep-Alive
User-Agent: Apache-HttpClient/4.1.1 (java 1.5)
 
{
	"num" : "ords-3472634",
	"buyer_name" : "Buyer Name",
	"dt_order" : "2019-10-25T12:00:00Z",
	"memo" : "  1"
}

In response, we get all the fields of the just created record:


HTTP/1.1 200 OK
Content-Type: application/json
Content-Location: http://<server>:<port>/ords/ws_test/t_order/25
ETag: "..."
Transfer-Encoding: chunked
 
{
   "id_order": 25,
   "num": "ords-3472634",
   "buyer_name": "Buyer Name",
   "dt_order": "2019-10-25T12:00:00Z",
   "memo": "  1",
   "links":    [
            {
         "rel": "self",
         "href": "http://<server>:<port>/ords/ws_test/t_order/25"
      },
            {
         "rel": "edit",
         "href": "http://<server>:<port>/ords/ws_test/t_order/25"
      },
            {
         "rel": "describedby",
         "href": "http://<server>:<port>/ords/ws_test/metadata-catalog/t_order/item"
      },
            {
         "rel": "collection",
         "href": "http://<server>:<port>/ords/ws_test/t_order/"
      }
   ]
}

We look at the contents of the table - our new record appeared:

To change the record, we call the same PUT method. Change the note in our order:

PUT http://<server>:<port>/ords/ws_test/t_order/25 HTTP/1.1
Accept-Encoding: gzip,deflate
Content-Type: application/json;charset=UTF-8
Content-Length: 178
Host: <server>:<port>
Connection: Keep-Alive
User-Agent: Apache-HttpClient/4.1.1 (java 1.5)
 
{
	"num" : "ords-3472634",
	"buyer_name" : "Buyer Name",
	"dt_order" : "2019-10-25T12:00:00Z",
	"memo" : "  1.  "
}

In the response we get the same JSON with the parameters of the modified record. In the table, we see that the note has been updated:

Retrieving records from a table

There are three modes of querying data from a table:

Page request:

GET http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/?offset=<Offset>&limit=<Limit>

Request for conditions:

 GET http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/?q=<FilterClause>

Primary Key Request:

GET http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/<KeyValues>

To get all the records, you can run the query without specifying any parameters:

GET http://<server>:<port>/ords/ws_test/t_order/

Answer

HTTP/1.1 200 OK
Date: Fri, 25 Oct 2019 15:39:58 GMT
Content-Type: application/json
ETag: "..."
Transfer-Encoding: chunked
 
{
   "items": [   {
      "id_order": 25,
      "num": "ords-3472634",
      "buyer_name": "Buyer Name",
      "dt_order": "2019-10-25T12:00:00Z",
      "memo": "ўµЃ‚ѕІ‹№ ·°є°· 1.  ·јµЅ‚Њ їЂјµ‡°Ѕµ",
      "links": [      {
         "rel": "self",
         "href": "http://<server>:<port>/ords/ws_test/t_order/25"
      }]
   }],
   "hasMore": false,
   "limit": 25,
   "offset": 0,
   "count": 1,
   "links":    [
            {
         "rel": "self",
         "href": "http://<server>:<port>/ords/ws_test/t_order/"
      },
            {
         "rel": "edit",
         "href": "http://<server>:<port>/ords/ws_test/t_order/"
      },
            {
         "rel": "describedby",
         "href": "http://<server>:<port>/ords/ws_test/metadata-catalog/t_order/"
      },
            {
         "rel": "first",
         "href": "http://<server>:<port>/ords/ws_test/t_order/"
      }
   ]
}

How to get Oracle to add an encoding to the Content-Type header is an open question.

GET http://<server>:<port>/ords/ws_test/t_order/25

Answer

HTTP/1.1 200 OK
Date: Fri, 25 Oct 2019 15:44:35 GMT
Content-Type: application/json
ETag: "..."
Transfer-Encoding: chunked
 
{
   "id_order": 25,
   "num": "ords-3472634",
   "buyer_name": "Buyer Name",
   "dt_order": "2019-10-25T12:00:00Z",
   "memo": "ўµЃ‚ѕІ‹№ ·°є°· 1.  ·јµЅ‚Њ їЂјµ‡°Ѕµ",
   "links":    [
            {
         "rel": "self",
         "href": "http://<server>:<port>/ords/ws_test/t_order/25"
      },
            {
         "rel": "edit",
         "href": "http://<server>:<port>/ords/ws_test/t_order/25"
      },
            {
         "rel": "describedby",
         "href": "http://<server>:<port>/ords/ws_test/metadata-catalog/t_order/item"
      },
            {
         "rel": "collection",
         "href": "http://<server>:<port>/ords/ws_test/t_order/"
      }
   ]
}

Delete record

To delete, use the HTTP method DELETE.

Inquiry:

DELETE http://<server>:<port>/ords/ws_test/t_order/?q={"id_order":25}

The request in its original form:

DELETE http://<server>:<port>/ords/ws_test/t_order/?q=%7B%22id_order%22%3A25%7D

Answer:

HTTP/1.1 200 OK
Date=Fri, 25 Oct 2019 16:23:39 GMT
Content-Type=application/json
Transfer-Encoding=chunked
 
{"rowsDeleted":1}

findings

ORDS is a fairly flexible and versatile mechanism for working with web services, which allows you to implement full-fledged REST. In terms of performance, it is far superior to Native Oracle WS with its heavy internal XML parsing. To implement a highly loaded system, this approach is not suitable: in this case, a different technology stack is needed - a separate application server, proxying requests, using clustered databases, and so on. However, for the implementation of systems with a relatively small number of HTTP requests (up to 10–20 per second), ORDS is the optimal approach in both performance and flexibility. ORDS is inferior to Native Oracle WS only in terms of generating a web service specification: the latter provides a fully finished specification (WSDL) that can be given “as is” to consumers of the service. ORDS also has the abilitygeneration of a description, but for the approach considered in this article with a fully universal service (when there is a common processing procedure for all services) automatic generation of the specification becomes impossible. Oracle will only generate a top-level specification, and parts (data models) will have to be described manually.

Alternative approaches

Java Servlet

This option for creating web services in the configuration method is similar to Native Oracle WS: it also requires the use of Oracle's built-in HTTP server, as well as ACL settings (as, indeed, all other methods). But, unlike Native Oracle WS, this option works with pure HTTP. Request handlers in this case are written in Java and look only at the HTTP-request type ( PUT, GET, POST, and so on, although it can be done and one handler for all types), and processing logic completely remains at the discretion of the developer. You can transfer the request body "as is" to the database logic, and there it can be disassembled and processed, you can leave part of the logic on the side of the Java handler, and from the database you can call the necessary procedure depending on the data that came in the request.

This approach to the implementation of web services is quite universal and also does not require the installation of any additional components. We did not manage to apply it only because of the strict requirements for the service: we needed a web service that did not require authentication. When implementing this approach, authentication is required, and this requirement could not be circumvented.

See the Oracle documentation for more details on this method .

Database Access Descriptor (PL / SQL Servlet)

This option is completely analogous to the previous one, only the PL / SQL stored procedure acts as the request handler.

Example URL - the package name, procedure name, parameters (GET request) are indicated:

GET http://<server>:<port>/servlet_plsql/pi_test.test_serv?p_path=ppp

In the case of a POST request, the parameter names must be entered directly in the request body through the “=” sign, which is rather inconvenient, since the type of the request content (ContentType) in this case can only be text. You can transfer an xml or json structure only in this form:

p_proc_param_name=<xml_data>…</xml_data>

This version of the web service is applicable only in cases when we are dealing with very simple requests - procedure calls with simple data types. Transmit any complex multi-level structure in this option will not work.

This approach is described in detail on the ORACLE-BASE website.

Conclusion

Creating a web service in Oracle is a fairly simple task that does not require writing any kind of super complex code. At the same time, Oracle developers get into their arsenal a fairly powerful mechanism that allows you to integrate heterogeneous systems or parts of systems through HTTP.

In this article, we examined four approaches to creating web services.

Native Oracle WS is an outdated technology that nevertheless has its advantages: automatically generated WSDL, automatic parsing of XML, no need to install additional software. The main drawback is the low performance and limitation of the supported data types.

ORDS- in my opinion, the preferred way to create web services. Flexible enough and versatile. Of the inconveniences of this method, we can only distinguish that it is not included in the standard Oracle package, that is, it requires a separate installation.

Java Servlet is a completely universal way that does not require the installation of additional software. However, everything needs to be done completely manually, since there is no possibility of auto-generating services.

PL / SQL Servlet is the least successful approach. Of the pluses, we can distinguish that in this option we get the ability to call stored procedures via HTTP without the need to install additional software, or without writing additional code in other languages: all code is written only in PL / SQL.

Thank you all for your attention! I hope the article’s material will be useful to those who are somehow connected with Oracle products and are puzzled by the problems of intra-system and intersystem integration.

Additional materials

Native Oracle XML DB Web Services

Using Native Oracle XML DB Web Services

Native Oracle XML DB Web Services in Oracle 11g Release 1

ORDS

: Installation, Configuration, and Development Guide

Install Oracle REST Data Services 3.0.X in under 5 minutes

CGI-: HTTP Headers (OWA_UTIL) and ORDS-Specific Bind Variables

ORDS: Oracle REST Data Services (ORDS) : Authentication

ORDS Open API (Swagger): Oracle REST Data Services (ORDS) : Open API 2.0 (Swagger) Support

ORDS, BLOB: ORDS web services returning BLOBs

Java Servlet

Writing Oracle XML DB HTTP Servlets in Java

Database Access Descriptor (PL/SQL Servlet)

DBMS_EPG — The Embedded PL/SQL Gateway in Oracle 10g Database Release 2

Web Services in Oracle

Background

Native Oracle XML DB Web Services

Capabilities

Customization

Configure Oracle XML DB HTTP server

Configure HTTP Access

Creating a servlet for a web service

Configure Access List

Web Service Example Using a Batch Procedure

findings

Oracle REST Data Service

Capabilities

Customization

Settings

Installation

Launch

Access setting

Example 1: Handler for a POST request

Creating a test user for WS

Setting up a database schema

Creating a module and template

Creating an HTTP Request Handler

Call example

Parameters from the request

CGI variables

Example 2: Accessing a table through ORDS

Create a test pattern

Opening table access through ORDS

Create or edit a record

Retrieving records from a table

Delete record

findings

Alternative approaches

Java Servlet

Database Access Descriptor (PL / SQL Servlet)

Conclusion

More articles: