ORDS is a Java application that enables developers with SQL and database skills to develop REST APIs for the Oracle Database, the Oracle Database 12c JSON Document store, and the Oracle NoSQL Database. Any application developer can use these APIs from any language environment, without installing and maintaining client drivers, in the same way they access other external services using the most widely used API technology: REST. For more info see this blog post “Why Use REST and ORDS To Transform Your Oracle Database into a RESTful API Service
Download the Oracle Database Application Development VirtualBox Appliance. Our Hands-on Labs are included on this free resource, which also contais an Oracle Database and Oracle REST Data services already installed, configured, and running. The labs run you through a very useful and complete set of ORDS exercises on your personal machine.
ORDS is included (and for no additional cost) with any Oracle Database license. For ORDS use with Weblogic the following paragraph was added at the end of the Weblogic licensing documentation here : “A license to WebLogic Management Framework includes a restricted-use license to Oracle WebLogic Server Standard Edition. Oracle WebLogic Server Standard Edition may be used solely for hosting the Java servlet that is used by Oracle REST Data Services. The Java servlet is the functional replacement for Oracle HTTP Server mod_plsql” . So within the WebTier license you have a RUL of WL SE for hosting Java servlets that is used by Oracle Rest Data Services.
ORDS runs the same on the Oracle Public Cloud as it does on-premise. To see how to go about creating an account on the Oracle Exadata Express Cloud Service and use ORDS to build a simple demo of a REST-enabled application see the blog post "QUICK DEMO >> A Great Use Case for REST on the Oracle Exadata Express Cloud Service".
How does ORDS pass parameters in and out to SQL and PL/SQL handlers in REST calls like GET and POST?
ORDS enables both in and out parameters to be passed within the body of some REST calls such as POST and PUT, in the URIs as query strings or route patterns, and in HTTP headers. See blog post “ORDS Mechanisms for Passing Parameters”. Also, see “Worked Example: Storing JSON in Your Relational Schema” for tips on using the :body bind variable to pass JSON, images, and other mime types in the body of REST calls.
Absolutely. RESTful Service modules can execute any valid SQL or PL/SQL block. If you wish to simply create an endpoint for executing a stored procedure, the APIs must be created manually. See blog post “ORDS Mechanisms for Passing Parameters”. Also, see blog post “ORDS and PL/SQL” which discusses the use of HTTP headers and other useful topics.
An upcoming version of ORDS will support automatic endpoints for remote execution of stored procedures via POST.
A very good way to publish RESTful APIs is to use the OpenAPI Initiative standard (formerly known as Swagger). This is a very popular API frameworks and used by the Oracle Cloud PaaS API Catalog Cloud Service. APIs can be published for both easy human and computer consumption. A nice feature is that code stubs can be generated in nearly any programming language for making the API calls.
An upcoming version of ORDS will include automatic Swagger integration.
This is expected behavior and not an error. You need to specify a resource you have defined in ORDS in the URI as well, e.g., https://<hostname><:port>/ords/<module>/<template> and created a handler in ORDS for processing the request you are making, e.g., GET, on that resource. This may be changed in future releases or ORDS.
ORDS is supported on three application servers: Oracle Weblogic Server (WLS), Tomcat, and GlassFish (deprecated). Glassfish support will be de-supported in a future release.
Note that ORDS does NOT run on IBM Websphere Application Server or Microsoft Application Servers.
ORDS can also be ran as a standalone Java process. In this configuration, it employs an embedded Jetty webserver which is purpose-built to serve ORDS REST and Application Express (APEX) applications.
Standalone is supported for production use, but it lacks ‘out-of-the-box’ features such as URI rewrites and NCSA access logs that may be considered production-critical.
For some tips on running ORDS in standalone mode see this blog post, "How to add an NCSA style Access Log to ORDS Standalone."
For on premise deployments use java -jar ords.war version
You can also query the ORDS metadata catalog in a database serviced by that particular instance of ORDS, e.g.
select 'ORDS is ' || version "Version of ORDS" from ords_metadata.ords_schema_version;
Yes. ORDS runs with the UCP (Universal Connection Pool) in all the Application Server environments it supports, e.g., Oracle Weblogic Server, Tomcat, Glassfish, and Jetty (standalone), and UCP supports Fast Connection Failover. See blog post “Does ORDS Support RAC Fast Connection Failover” for more information.
A common problem users may experience when they start running ORDS in production environments or under load in test mode is to have poor performance because there are too few database connections. ORDS uses the Oracle Universal Connection Pool (UCP). The ORDS defaults for initial number of connections created and the maximum number of connections allowed is 3 and 10 respectively, which may be low for active environments. Increase these by setting the jdbc.InitialLimit and jdbc.MaxLimit configuration file parameters to higher values. See section A.5 Understanding Configurable Parameters of the Oracle REST Data Services Installation, Configuration, and Development Guide.
There are four excellent worked examples available:
1) The documentation chapter “Getting Started with RESTful Services” which walks you through the basics of setting up RESTful APIs using ORDS culminating with setting up OAuth2 security to protect the APIs.
2) The documentation chapter “Configuring Secure Access to RESTful Services” which provides more in-depth examples of configuring the different security options that ORDS supports including First Party Cookie-Based Authentication and both two-legged and three-legged OAuth2 security.
3) The documentation chapter “Development Tutorial: Creating an Image Gallery” which provides an example of setting up more complex application for storing and retrieving picture images that includes setting up OAuth2 security.
4) The ORDS Hands-On Lab which now includes a lab that walks you through setting up OAuth2 security.
Where can I find examples of creating RESTful APIs using the ORDS PL/SQL API.
In addition to the documentation chapter ORDS PL/SQL Package Reference which provides some examples there is this blog post “Create an ORDS RESTful Service Using PL/SQL” which walks you through the steps needed to create RESTful services using the ORDS PL/SQL API from beginning to end. Also, look at the blog posts “Inserting Nested JSON Objects Into Relational Tables Using ORDS” and “Generating Nested JSON From Relational Data with ORDS” which provides worked examples of setting up RESTful services for these use cases using the ORDS PL/SQL API.
ORDS uses the ISO 8601, or more precisely the RFC3339, standard for encoding dates in strings. Generally, the format is dd-mmm-yyyy, e.g., 15-Jan-2017. ORDS automatically converts JSON strings in this format to Oracle Date data types when inserting or updating values in the Oracle Database. ORDS automatically converts Oracle Date data types to this string format when converting to JSON strings. For more information, including details on how time and time zones are handled, see the blog post "Working with Dates Using ORDS"
A very good way to do this is to use the SQL/JSON functions like json_table that are available in Oracle Database 12c. For details on how to do this see blog post “Inserting Nested JSON Objects Into Relational Tables with ORDS
A very good way to do this is to use the SQL/JSON functions, like json_object and json_arrayagg, that are available in Oracle Database 12c Release 2. For details on how to do this see blog post “Generating Nested JSON From Relational Data With ORDS
Set pagination to zero in either the module or individual template definitions.
To store the JSON you need to update the column using JSON contained in the body of a PUT or POST call. To retrieve or GET the JSON back you use the resource media type. See blog posts “ Worked Example: Storing JSON in Your Relational Schema” and “Working with RESTful Services, BLOBs, and Media Types” for examples.
A very good way is to go to Forum for ORDS, SODA, and JSON in the Database found