Duplicate stream parameter

Oracle REST Data Services has a useful Implicit Parameter feature which conveniently are automatically available to use in Resource Module Handlers without declaring them. More information about Resource Module Handlers is available in the Developing Oracle REST Data Services Applications section of the ORDS documentation.

Amongst other things, these implicit parameters provide access to the request body for a POST or PUT either as a BLOB through :body or CLOB through :body_text. For both parameters the documentation says they should only be referred to once.

parameter must be dereferenced only once in a PL/SQL block.

ORDS Implicit Parameter documentation

The documentation for the :body implicit parameter explains why. If it is dereferenced more than once, then the second and subsequent dereferences will appear to be empty. This is because the client sends the request body only once. So the first :body reference will return a BLOB but the second reference will return null. The same applies to :body_text which is a CLOB representation of the same request body.

That is in the Resource Module Handler PL/SQL block if the source references :body and then :body_text the :body_text will return null.

 insert into tab1(content) values (:body); -- request body will be inserted
 dbms_output.put_line('Request body ' || :body_text); -- request body will be null

However, a second call to :body_text will result in an error at runtime – SQL Error Code: 17270, Error Message: Duplicate stream parameter: 2.

 dbms_output.put_line('Request body ' || :body_text); -- request body as CLOB
 dbms_output.put_line('Request body ' || :body_text); -- error occurs

There is an inconsistency here in how a second call to get the request body content is handled but if you’re getting this Duplicate steam parameter error message do review the source for duplicate references to :body_text implicit parameter.

Automation with OSB and ORDS

Oracle REST Data Services implements an Open Service Broker API for each connection pool. More information about the services and plans available through the service broker can be found in the ORDS User Guide – Configuration of Database API with Open Service Broker API Compatible Platforms. This post provides an overview of why an Open Service Broker provided by ORDS could change how you automate the provision of databases and users.

Most software systems require a database for data storage & retrieval and database users to perform operations on that data. Applications can interact with the database through an API specific to the programming language ( such as JDBC ) or REST.

These ORDS OSB services open up new automation possibilities for provisioning databases and users using tools and scripting platforms that developers and system operators will already be familiar with. Scripts to run Oracle DBCA or specific SQL commands can now be replaced with more platform specific artifacts that are used with the tools for that platform. For example, to creat a new User Acceptance Test pluggable database instance by cloning:

kubectl create -f clone-user-acceptance-test-instance.yaml

Where the clone-user-acceptance-test-instance.yaml might look like this:

apiVersion: servicecatalog.k8s.io/v1beta1
   kind: ServiceInstance
     name: pdbtest1-instance
     namespace: ords-osb
     clusterServiceClassExternalName: create-pluggable-database
     clusterServicePlanExternalName: clone-database
       pdb_name: pdbtest1
       src_pdb_name: db0918_pdb1

In the above case, the create-pluggable-database service has been defined in the ‘ords-osb’ namespace and the administrator has not had to share the credentials . Sensitive information can be managed in a manner that is more suitable for the platform. The Kubernetes Service Catalog topics for operators provides more information on best practice for this.

Cloning existing pluggable databases is likely to be the fastest way to provision new test environments because the source database can have all the necessary object structures, seed data and users for the application to be tested. Depending on the application there might be more database users required too. ORDS can not only provision pluggable databases through it’s open service broker interface, but also database users and those database users can be REST enabled too!

If you’re already using kubernetes or cloud foundry as part of your development or production infrastructure then take a look at how you can do more with your existing Oracle databases through Oracle REST Database Services.

So what version of ORDS am I actually running?

A little while ago ORDS version 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

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.


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.