Oracle REST Data Services on OTN | Downloads | Forum
- Documentation for Oracle REST Data Services is provided here.
- Documentation for SQL Developer Web is provided here.
- Documentation on using SODA for REST is provided on here.
- A tutorial on getting started with developing RESTful Services is included in the product documentation , in the book titled 'Oracle® REST Data Services Quick Start Guide'.
You can discuss issues on the forums.
- Be sure to use clear subject lines to initiate a thread. Provide a complete and clear description of the issue, including steps to reproduce the issue.
- Try to avoid using old, unrelated threads for a new issue.
Important Changes to Note
Removal of PDF Generation Support
As previously advised in the ORDS 18.4.0 Release Notes, the Apache FOP based functionality to produce PDF Reports from Oracle Application Express has been removed in this release. This feature has also been removed from Oracle Application Express 19.2. Customers using older versions of Oracle Application Express are recommended to upgrade to newest version.
SQL Developer Web
This release sees the introduction of Oracle SQL Developer Web.
Important Concepts to Keep In Mind
SQL Developer Web
ORDS 19.4.0 saw the introduction of Oracle SQL Developer Web. SQL Developer Web is an ORDS hosted web application giving Oracle Database users an interface for executing queries and scripts, creating and altering database objects, building data models, accessing Performance Hub, and viewing database activity. Please consult the ORDS documentation library for instructions on how to enable this feature, which is disabled by default. Please access the SQL Developer Web documentation library for an overview of features and capabilities.
For consistent representation of date values in SQL Developer Web, ORDS should be run in the UTC timezone. This can be done as follows
java -Duser.timezone=UTC -jar ords.war standalone
Performance of REST APIs
Performance of ORDS based REST APIs was significantly improved in ORDS 19.4.0 by changing how ORDS handles proxied database connections. In ORDS 19.2.0 and earlier, for each HTTP request ORDS proxies from
ORDS_PUBLIC_USER to the relevant ORDS enabled schema, performs the relevant database calls and then closes the proxy session. We term this behavior 'disposing' a connection.
In ORDS 19.4.0 and later ORDS changes its default behavior to keep proxied connections in the pool, ameliorating away the cost of opening and closing the proxy sessions. This gives a very substantial performance improvement. We term this behavior 'recycling' a connection.
Reverting to Previous behavior
ORDS Administrators may revert to the old behavior of 'disposing' connections by performing the following command:
java -jar ords.war set-property jdbc.cleanup.mode dispose
To switch to recycling connections use:
java -jar ords.war set-property jdbc.cleanup.mode recycle
Managing Connection Re-use
To mitigate against database code that is leaking resources, ORDS has a configuration setting:
jdbc.MaxConnectionReuseCount which controls how many times ORDS will use a database session before terminating it (and releasing the leaked resources) and replacing it with a new database session. The default value for this setting is 1000. If you are experiencing database resource exhaustion you should:
- Determine the database code that is leaking resources, and fix the database code not to leak.
- If that is not feasible then reduce the value of
jdbc.MaxConnectionReuseCount in increments until a point is found where the database is no longer experiencing resource exhaustion.
Installer Behavior Using the
In ORDS 19.4.0, the installer does not automatically update the ORDS parameter file (default ords parameter file or user-specified ords parameter file) when you use the following commands:
java -jar ords.war
java -jar ords.war install <OPTIONS> simple
java -jar ords.war install <OPTIONS> advanced
java -jar ords.war setup <OPTIONS>
If you want the same behavior to update the ords parameter file, you will need to use the --saveParameters option.
java -jar ords.war install --parameterFile <PATH TO PARAMS>/ords_params.properties --saveParameters simple
java -jar ords.war install --parameterFile <PATH TO PARAMS>/ords_params.properties --saveParameters advanced
java -jar ords.war setup --parameterFile <PATH TO PARAMS>/ords_params.properties --saveParameters
Supported Java Version
Oracle REST Data Services requires Java 8 or later. Java 7 is no longer supported. Please consult the documentation for the minimum supported Application Server versions for ORDS.
Changes in 19.4.0
The following changes and enhancements have been made since 19.2.0:
Issues Fixed in 19.4.0
- BUG:30656342 - Remove Apache FOP Support
- BUG:30589833 - Uptake Javassist 3.26.0-GA
- BUG:30589826 - Uptake Jetty 9.4.24
- BUG:30589815 - Uptake Guava 28.1
- BUG:30589806 - Uptake Apache XML Graphics Commons 2.4
- BUG:30589782 - Uptake Jackson Annotations and Jackson Databind 2.10.1
- BUG:30574360 - StackOverflowError possible when db.invalidpooltimeout property is less than zero
- BUG:30516590 - ORDS Standalone parameter to have Jetty bind to specific addresses
- BUG:30514855 - SDW does not have a help page for Create Synonym dialog
- BUG:30443935 - SDW 'move up' and 'move down' buttons have no effect in create table panel
- BUG:30430085 - Starting ORDS Standalone prompts for APEX static images location even after saying 'NO' to APEX during install
- BUG:30376626 - SDW Worksheet does not display execution time
- BUG:28487031 - ORDS metadata catalog does not generate valid Swagger V2 API document
- BUG:26326540 - SDW Worksheet does not prompt for a variable value when one is defined in a script
- BUG:22472978 - Filter query does not support IN for comparison of an attribute with a list of values
- BUG:30494480 - CDB-PDB migration reports ORA-00600 error when upgrading the common ORDS_METADATA schema
- BUG:30505126 - CDB-PDB migration reports ORA-20148 error when upgrading releases earlier than 3.0.6
- BUG:30294697 - Make static HTTP resources in ords.war cacheable
- BUG:30288094 - Uptake JDBC Patch 30269428: Add Oracle binary JSON format support to SODA
- BUG:30287115 - Uptake UCP Patch 30076604: Labelling Callback being passed invalid connection
- BUG:30287001 - Uptake JDBC Patch 29010517 - Parameter missing when too many parameters are submitted
- BUG:30238917 - Improve performance of dispatching requests
- BUG:30215276 - Include timestamp and ECID in error page
- BUG:30193380 - Uptake Javassist 3.25.0-GA
- BUG:30193375 - Uptake Jetty 9.4.20
- BUG:30193361 - Uptake Guava 28.0
- BUG:30193344 - Uptake Batik 1.11
- BUG:30193331 - Uptake PDFBox 2.0.16
- BUG:30193314 - Uptake Jackson Databind 220.127.116.11
- BUG:30095934 - Provide
/ords/sql-developer landing page
- BUG:29844552 - Provide option to keep connection proxied when returned to pool
- BUG:29772200 - Address issue with upgrading ORDS configurations that use TNS connection
- BUG:29693492 - Provide installer option to use TNS connection
- BUG:26809645 - Handle overflow of offset and limit query parameter
- BUG:30072487 - Do not assume non Oracle Database when using db.customURL
- BUG:30062366 - Pools defined with a Custom URL cause 404 response
New Features in 19.4.0
- BUG:30265178 - SDW: Data load to a new table
- BUG:30419710 - SDW: Activity now includes a logins section
- BUG:30429500 - SDW: Create and REST Enable users
- BUG:30282368 - Incorporate SQL Developer Web into Oracle REST Data Services
Changes in 19.2.0
The following changes and enhancements have been made since 19.1.0:
Issues Fixed in 19.2.0
- BUG:29884541 - Force newline characters in
Error-Reason headers to be replaced by space characters to avoid error on WebLogic
- BUG:29942773 - Force use of
APEX_PUBLIC_USER when dispatching APEX Static Resources service
- BUG:24941023 - Handle pound character in URL path values
- BUG:28871609 - Improve Open-API documentation for date time fields
- BUG:29028746 - Dispatch APEX static resources using built in ORDS service
- BUG:29422705 - Enumerate exact set of OWA packages that access is restricted to
- BUG:29536670 - Uptake Oracle JDBC Driver PSE 29010517 to address loss of data in PL/SQL Gateway calls
- BUG:29550323 - The root cause for this issue was addressed by uptaking 19.3.0 Oracle JDBC Drivers, see BUG:29803162
- BUG:29642673 - Fix problem with output being truncated for multi-byte characters in PL/SQL Gateway responses
- BUG:29693752 - Fix problem with detection of SQL Administrator role on 12+ non container database
- BUG:29809280 - Make installer more resilient to error conditions in customer database
- BUG:29843652 - Improve performance of built in Java based REST endpoints
- BUG:29719737 - Fix problem with detection of APEX when APEX is installed in a CDB using Application Containers
- BUG:29711406 - Fix problem with ICAP integration in some environments
- BUG:29803162 - Uptake Oracle JDBC Drivers 19.3.0
- BUG:29617151 - Uptake Eclipse Jetty 9.4.18
- BUG:29778333 - Uptake PDFBox 2.0.15
- BUG:29872040 - Uptake Jackson 2.9.9
- BUG:29615441 - Uptake Guava 27.1
- BUG:29191117 - Uptake Javassist 3.24.1-GA
New Features in 19.2.0
- BUG:29778399 - Provide script to provision a database user to be able to install ORDS database schema
- BUG:29629522 - Propagate authenticated user to database as
Changes in 19.1.0
The following changes and enhancements have been made since 18.4.0:
Issues Fixed in 19.1.0
- BUG:29544918 - Fix problem with Standalone Mode producing WARN level messages during startup
- BUG:29532796 - Fix problem with WebLogic raising 'Cannot contain CRLF Charcters' (sic) error
- BUG:29524572 - Remove Apache Avalon third party dependency
- BUG:29193867 - Fix problem with invocation of ORDS_ADMIN.DEFINE_SERVICE and ORDS_ADMIN.DEFINE_HANDLER APIs
- BUG:29290410 - Add DOC_SIZE, DAD_CHARSET and CONTENT_TYPE to file upload in document table
- BUG:29303722 - Improve how string arrays are transferred from ORDS to the database on 12C and later
- BUG:29210421 - ORDS corrupting timestamps of ords.war contents when running configdir command
- BUG:29197220 - Dispatch PL/SQL Gateway calls via ORDS_PUBLIC_USER to minimize number of pools required
- BUG:29191117 - Uptake Javassist 3.24.1-GA
- BUG:29191097 - Uptake Eclipse Jetty 9.4.14
- BUG:29191084 - Uptake Google Guava 27.0.1
- BUG:29190987 - Uptake Jackson 2.9.8
- BUG:29190934 - Uptake Apache Commons File Upload 1.4
- BUG:29190890 - Uptake Apache PDFBox 2.0.13
- BUG:29128000 - Connect to database using Wallet Zip archive as found Oracle Database Cloud ATP/ADW environments
- BUG:28570782 - Improve integration with McAfee ICAP Server
- BUG:27639517 - Provide mechanism to enable Plugin servlets function as an administrative database user
New Features in 19.1.0
- ENH:29303772 - Enable ORDS to install in a PDB without requiring SYS user or SYSDBA role
Changes in 18.4.0
The following changes and enhancements have been made since 18.3.0:
Issues Fixed in 18.4.0
- BUG:29053557 - Determine administrative database users based on granted database role
- BUG:29049176 - Show 403 Forbidden status when REST Service fails due to database user lacking privilege to access objects referenced in the SQL statement
- BUG:28520359 - Show Oracle Logo in UI
- BUG:29011184 - Suppress unsupported and undocumented
X-DB-Content-Length response header produced by OWA
- BUG:28877175 - Fix resolution of
url-mapping.xml based mapping that uses
- BUG:28997641 - Gracefully cope with database password rotation
- BUG:28964132 - ORDS installer won't accept passwords more than 28 bytes long
- BUG:28808094 - Uptake Apache Commons Logging 1.2
- BUG:28787846 - Uptake Apache PDFBox 2.0.12
- BUG:28719460 - Uptake Javassist 3.23.1-GA
- BUG:28719440 - Uptake Jetty 9.4.12
- BUG:28719424 - Uptake Jackson 2.9.7
- BUG:28561298 - Address problem with upgrading from ORDS 17.4.1
- BUG:28518849 - Fix problem with OAuth token lifetimes not being calculated correctly
- BUG:28466581 - Fix 500 Error status on open-api-catalog resources
- BUG:27992525 - Fix access to metadata-catalog when no authorization required
- BUG:27933884 - Fix parsing of Resource Handler content to recognize parameters more precisely
- BUG:27808357 - Enhance performance of AutoREST tables/views
New Features in 18.4.0
- ENH:23666046 - Make
security.requestValidationFunction setting configurable per database pool
- ENH:28028432 - Echo p_comments value into generated Swagger documentation
For information on Issues Fixed and New Features introduced for earlier versions please refer to the release notes for those versions.
Oracle Database DATE and TIMESTAMP values
ORDS interprets Oracle Database DATE and TIMESTAMP values (which do not possess a time zone) into the time zone of ORDS. These values are then represented in the RFC3339 UTC format.
In a future release Oracle Database DATE and TIMESTAMP values will not by default be interpreted into any time zone. These values will be represented in the ISO 8601 "yyyy-mm-ddThh:mm:ss[.sss]" format. Oracle Database TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE values will remain represented in the RFC3339 UTC format.
SQL Developer Web
- DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE values downloaded from the Worksheet Query Result in the JSON format are not presented in the Internet Datetime format.
- ORDS will run and is supported on JDK 9 however the only supported way to use ORDS on JDK 9 is by running it in standalone mode, as currently neither Oracle WebLogic or Glassfish are certified for use with JDK 9, and ORDS has not yet been certified on Apache Tomcat on JDK 9.
From ORDS 18.1.0 and later you can launch ORDS on JDK 9 by just typing:
java -jar ords.war <command-name>
- The requirement in 17.4 to use the
--add-modules java.xml.bind command line argument has been eliminated
From JDK 11 Nashorn is deprecated and a warning will be displayed. To disable this warning use the following JVM property
When using a TNS connection type the JDBC driver needs to know the location of the folder containing
tnsnames.ora. To configure this when running ORDS in standalone mode do:
java -Doracle.net.tns_admin=$ORACLE_HOME/network/admin -jar ords.war
$ORACLE_HOME points to the folder where Oracle Database/Oracle Instant Client is installed)
If using Tomcat or WebLogic, then the startup script used to start the app server would need to be modified to pass the
-Doracle.net.tns_admin=$ORACLE_HOME/network/admin argument to the java command that starts Tomcat/WebLogic
JDBC Array values
ORDS passes array values to the Oracle Database in a number of cases, for example arrays are used to pass the request headers and form fields during PL/SQL Gateway calls. Oracle JDBC Driver 18.3 and later supports an improved mechanism for passing array values, however this feature is only supported on Oracle Database 12c and later. Thus ORDS uses existing deprecated and less efficient APIs for passing array values when connecting to 11G databases, and the improved mechanism when connected to 12c and later. Customers experiencing problems with passing large array sets (for example OutOfMemory exceptions) are encouraged to upgrade from 11G to a newer database version to mitigate these issues.
Autogenerated REST Endpoints
- AutoRest resources support the OAuth 2.0 Client Credentials flow only.
- Application Express workspaces do not support first party authentication, and therefore do not support the
/sign-in/ interactive sign in form. Accessing
/sign-in/ in APEX workspaces will produce a 404 status.
Support For mod_plsql logmeoff
- The mod_plsql logmeoff mechanism is not supported reliably by modern browsers and it is not provided by ORDS. The only way to end a HTTP Basic Authentication session is to close the Browser.
Starting Standalone Mode when connected to CDB
The typical manner to start ORDS in standalone mode, once ORDS has been configured is:
java -jar ords.war
ORDS will detect that it is fully configured and proceed to launching standalone mode. Since 18.2 the
ORDS_METADATA schema is not installed in the CDB, which means that when the default pool is connected to a CDB there is no way for ORDS to automatically verify the version of the ORDS schema installed in the database, thus ORDS prompts for the
SYS AS SYSDBA password so it can connect to each PDB and verify the ORDS version installed in each PDB. This means that when connected to a CDB the above command is not sufficient to start standalone mode automatically. To work around this issue, use the following command to start ORDS (only after the ORDS instance has been configured) in standalone mode automatically:
java -jar ords.war standalone
Support for apex.docTable
- apex.docTable (now depreciated) and owa.docTable should not be used for APEX 4.x and above as APEX provides its own document table.