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.
Can ORDS create RESTful Services that execute PL/SQL stored procedures?
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.
How can I document and publish my RESTful APIs I create with ORDS?
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.
Why am I getting a 404 Not Found error message when I first do a GET on ORDS using the URI https://<hostname><:port>/ords ?
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.
What application server should I use with 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.
What is Standalone mode and when should I use it?
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."
How can I tell what version of ORDS I am running?
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;
Does ORDS Support RAC Fast Connection Failover?
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.
How do you do performance tuning if ORDS appears to be running slow?
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.
Where can I find actual worked examples of implementing OAuth2 security with ORDS?
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.
How does ORDS handle Date data types? The Oracle Database supports date data types but JSON does not.
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".
How can JSON objects containing nested arrays be inserted into multiple, hierarchical tables in the Oracle Database?
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”.
How can I generate nested JSON objects from multiple, hierarchical tables in the Oracle Database, e.g., a JSON purchase order containing a nested array of purchase order items from purchase order and purchase order items tables?
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”.
How can I turn off pagination so that GETs that return arrays of JSON Objects are processed correctly?
Set pagination to zero in either the module or individual template definitions.
How can I store and retrieve JSON in a column of a relational table using REST and ORDS?
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.
How can I get answers to other technical questions I may have about ORDS?
A very good way is to go to Forum for ORDS, SODA, and JSON in the Database found here.