[Update for ORDS 22+] So what version of ORDS am I actually running?

A little while ago ORDS version 20.2.1.227.0350 became available for download from oracle.com and was also rolled out to Autonomous Database Shared regions around the world. Oracle REST Data Services has been available on Oracle Cloud for quite some time but this is the first time the release notes specifically mention Autonomous Database Shared.

If you are running a customer-managed ORDS for your Autonomous Transaction Processing, Autonomous Data Warehouse, or Autonomous JSON Database, you must upgrade your ORDS to version 20.2.1. Failure to do so could lead to service disruptions.
Oracle REST Data Services 20.2.1.r2270350 Release Note

Why does this matter? Before answering that, some basic concepts to cover first. At a high level, an ORDS installation consists of three parts:
– The Java application that accepts, validates and processes the HTTP requests. This is the ords.war running standalone, in WebLogic Server or Tomcat Server.
– The file system that contains the configuration settings for the ords.war to make and manage database connections, as well as static files and so on.
– The database that ORDS connects to and where users define & run their REST services in. This information and the database procedures for running these services are in the ORDS_METADATA schema.

When a customer first installs and configures ORDS the database(s) which ORDS will provide a RESTful interface for, will also have ORDS specific database objects installed. The version of the ords.war and the version of the ORDS_METADATA should be the same because they are coded, built and tested together before release. While it is possible that ORDS will work just fine if the ords.war and ORDS_METADATA versions are out of synch the expected behaviour is unspecified in this scenario.

At a high level, an ORDS installation consists of the application, file system and database.

ORDS Java application ( ords.war ) version

There are a number of ways to get the application version. From the command line…

java -jar ords.war version

Update for ORDS 22+

When using ORDS 22+ the above command will show you the version but will also show this error message:

java -jar ords.war version
Warning: Support for executing: java -jar ords.war has been deprecated.
Please add ords to your PATH and use the ords command instead.
...
ORDS: Release 22.4 Production 
...

The output does give you the high level version number but the correct command to use with ORDS 22+ is in fact:

ords --version

That’s assuming the ords script is in your path. If not, the correct command is:

$ORDS_INSTALL_DIR/bin/ords --version

Now, back to the other steps to get the ORDS Java Application version number…

From the SQL Developer Web through Preferences > About…

From within APEX through SQL Workshop > RESTful Services > ORDS RESTful Services…

From within APEX through Help (?) > About. In this case it’s called the APEX_LISTENER_VERSION…

ORDS repository ( ORDS_METADATA ) version

The metadata version can be obtained through an SQL query in the database…

select ords.installed_version from dual;

The above query can be executed in SQL Developer Web worksheet, APEX SQL Workshop > SQL Commands, sqlplus, sqlcl, SQL Developer. Effectively, anywhere you can run a query from.

Conclusion

It is possible for an ORDS installation to have the ords.war and ORDS_METADATA versions out of synch. This can happen if one runs a new version of ords.war against a database that already has ORDS installed without going through the installation or upgrade process or the database metadata is upgraded but the runtime ords.war is not. The latter can happen for Customer Managed ORDS on Autonomous Database. When doing so, always regularly check the application and database versions are in synch.

When is a string literal too long?

When it’s a multi-byte encoded string, that’s when.

Recently I came across a runtime problem where an ORA-01704 (string literal too long) error was occuring when inserting some text into a database table. The column had been set to the maximum amount of characters allowed for VARCHAR2, that is 4000 characters. Before inserting any values, the text was put through a truncation method to make sure it did not exceed 4000 characters. That’s what made the issue such a conundrum. If we had code limiting the string to 4000 characters, and the logs for the SQL statement were showing that the truncation to 4000 characters was happening, why was the Oracle database reporting that the string was longer than 4000 characters?

This is where the logs became very important. I noticed that the some of the text contained non-Western European characters, which was perfectly valid. However, while these characters are counted as single characters, depending on the encoding, they could be multi-byte characters. Not only is 4,000 the maximum number of characters for VARCHAR2, it is also the maximum number of bytes. In our case, we had a string of 4,000 characters, but in UTF-8 it required 4,017 bytes to store it. So, the solution was to make a slight change to the truncation logic, by introducing a maximum number of bytes for a specific character encoding. Now the truncation method has the following:


try {
while (truncatedString.getBytes(charSetName).length > maxBytes) {
 // remove the last character and check the bytes lenght again
 truncatedString = truncatedString.substring(truncatedString.length() - 2);
}
} catch (UnsupportedEncodingException e) {
logger.log(Level.SEVERE, "The character set " + charSetName +
" is not supported. Unable to truncated string if it is too long.", e);
}

The variable charSetName is set to the character set used by the database (in our case UTF-8) and the maxBytes variable is the maximum number of bytes allowed (in our case 4000). In the rare cases when there are over 4000 bytes in the string, this code will systematically remove the last character until the number of bytes come down. This code could be improved to be more efficient. For example, the code could check how many characters need to be removed and then remove that chunk, rather than removing a character at a time. It performs in the scenario it is used because the string is already chopped down to 4,000 characters. If the system begins to receive a large amount of multi-byte characters in a single string, then reducing the string to 4,000 characters may still leave you with an 8,000 byte string. Let me know if you determine a more efficient approach.

Differences between BPEL and ESB

Dave Berry (ESB Product Manager @Oracle) recently brought my attention to a discussion on the Oracle SOA Suite forum: Difference between Oracle ESB and BPEL. In short, the differences are in the problems they address. Confusion arises because there are similar capabilities in both. However, BPEL is about orchestrating business logic and ESB is about highly efficient integration.

The forum discussion is worth a read as there are useful contributions on which tool is right for what job, i.e. when to use BPEL and when to use ESB.

Finding your SOA sweet spot

Quocirca is a UK based research and analysis firm that particularly focuses on the business impact of IT. In a recent article ‘SOA – Dead or Alive?‘, Clive Longbottom raises the issue faced by ISVs in selling SOA solutions. Not all SOA initiatives have to be strategic and at the enterprise level, which is where a lot of the bigger software firms are more comfortable making their pitch.

Reflecting this need for tactical SOA solutions to evolve into a strategic game plan, Oracle outlines a SOA Maturity Model which helps IT folks in these businesses to make the move to SOA. It clearly outlines how benefits can be achieved in incremental steps. There is even a useful survey tool available to help you identify the SOA profile of your own organisation. This is a great place to start understanding the business audience and to begin tailoring the messages around the benefits of SOA to the business where it is today.

ESBREQUEST in Oracle ESB Routing Services

Oracle’s Enterprise Service Bus (ESB) is designed to implement service-oriented architecture (SOA) and event-driven architecture (EDA), providing a responsive, low-cost, high-impact framework for matching technology needs to business problems.

One of the powerful capabilities of Oracle’s ESB is the ‘Routing Service’ which caters for service virtualization through content based routing. It supports one way and request response operations. One of the challenges with service virtualization and the request response pattern is matching the response from the implementation service to the response that the routing service should return. There is no easy answer to this one, but the Oracle ESB provides some help. One is the transformation capabilities, but you would expect this from any ESB system. The really useful feature is being able to refer to the request in the response transformation. Oracle ESB does this by declaring a parameter to the XSLT called $ESBREQUEST.

Elements in the request can be referred to through XPATH. For example:

This is great, but there are a few things I need to draw to your attention:

$ESBREQUEST refers to the root element of the request sent to the service implementation and not the request sent to the routing service. So, if your implementation service request structure is DoMethodRequest/MyParam then refer to MyParam as $ESBREQUEST/MyParam not $ESBREQUEST/DoMethodRequest/MyParam.
You need to enter the XPATH manually because the mapper does not know the structure of the request.
You have to specify ‘Include Request in the Reply Payload’ when defining the routing rule. This can not be added to the routing rule or XSL file through the tools afterwards.

The latter point is what I really wanted to cover in this article. At the moment, in version 10.1.3, you can not retrospectively ‘Include Request in the Reply Payload’ using the JDeveloper or the ESB console. When you use JDeveloper the first time it puts
in the XSL file and sets attachRequestPayload=”true” in the transformation element of the routingRule section of the routing service esbsvc file.

So, you can do this manually to retrospectively add ‘Include Request in the Reply Payload’ to your existing routing rules. Unfortunately the only way I have found to update the running ESB routing service routing rules with these changes is to to delete the routing service (through ESB console), shutdown JDeveloper, edit the esbsvc file manually, restart JDeveloper and then register the ESB project with the integration server.

The Oracle Mapper tool in JDeveloper which is used to edit XSL files does understand the parameter concept, so you can test your stylesheet in JDeveloper before deploying it. Just open the XSL file in JDeveloper, right click in the design view and select Test. Be sure to enter the ESBREQUEST parameter as an XML fragment. Don’t forget that ESBREQUEST corresponds to the request structure sent to the service implementation and not the request received by the routing service!

This ESBREQUEST parameter gives you the benefit of manipulating the reply such as returning correlation information or using the data from the request to perform filtering and other manipulation that the service implementation does not provide.

Should you wish to use data from the original request received by the routing service then you have to add elements manually to the request transformation. This will work at runtime if the implentation service doesn’t validate the request for extra elements not part of the schema. However, once you add these elements to the XSL transformation you will not be able to use the JDeveloper mapper as it does validate the target document structure against the schema.