Very quickly, we’ll go from a uploading a Salary.csv dataset to rendering a chart of Job Titles for the highest education level and permitting RESTful queries on data…
Salary Data
Let’s start with a CSV dataset. For this exercise we’ll use a public domain Salary by Job Title and Country from kaggle.com. The download is a ZIP archive with two files. In this case it is just the Salary.csv which will be used.
I’m not going into all the details of downloading the archive file, unzipping it, creating a bucket, and uploading the Salary.csv but needless to say, those are important steps to get you this stage…
Note that the file has a URL for accessing and downloading it but that URL is not publicly accessible.
External Table
In the database, the DBMS_CLOUD package can create a table which has it’s data loaded from the CSV file. To get access to object storage from the database we require a credential object through DBMS_CLOUD.CREATE_CREDENTIAL. Details on achieving that are already covered in Ulrike’s Oracle Object Storage Access for all Oracle Databases with DBMS_CLOUD article and my previous article Data move mojo with ORDS 21.3.0. In fact, I’m going to reuse that DATAPUMP_CRED_NAME credential. That’s just out of convenience for me. You be you and do your own thing.
To create the SALARY table we must specify a column list with data types and sizes. That requires figuring out, based on the data and also the description on kaggle.com, what data types makes sense. Some columns are obviously numeric, but it is not always clear how big or small those columns should be.
begin
DBMS_CLOUD.CREATE_EXTERNAL_TABLE(table_name=>'SALARY',
credential_name => 'DATAPUMP_CRED_NAME',
file_uri_list => 'https://frckdrxj9oco.objectstorage.eu-frankfurt-1.oci.customer-oci.com/n/frckdrxj9oco/b/example-bucket/o/Salary.csv',
format => json_object('type' VALUE 'CSV', 'skipheaders' VALUE '1'),
column_list =>'Age NUMBER(4,1),
Gender varchar2(6),
Education_Level NUMBER(1),
Job_Title varchar2(100),
Experience NUMBER(4,1),
Salary NUMBER(9,2),
Country varchar2(200),
Race varchar2(100),
Senior NUMBER(1)'
);
end;
/
Note that the file_uri_list parameter came directly from the object details panel in Object Storage.
Unless there’s a syntax error, creating the table will always work. You’ll only find out about problems when you run a select query. That’s when the DB attempts to read the file.
Chart
ORDS Database Actions has a charts and dashboard feature that we’re going to use now. Charts enable you to create charts from the database. The chart is constructed using the input SQL command. Dashboards enable you to group charts together to create reports. Let’s define a bar chart showing the number of job titles for the highest education level. So that the chart is not too busy I’m limiting the chart to only show job titles that have more than 10 occurrences.
For simplicity my chart definition says that it is unprotected, so it is publicly available and anyone can see it with the URL. That’s ok with this scenario because the data is in the public domain anyway but please consider the protection on your charts and dashboards.
That URL can be shared and it will render in the browser like this…
Although the chart and its data is available to anyone with the URL, the data is just the data for the chart.
For many folks the only way they can create a chart based on a CSV file and share it is through using a spreadsheet and sharing that, which means sharing the underlying data. In this case the data is not shared, just the chart. So far so cool. Now let’s look at sharing that data and allowing clients to query it.
AutoREST
This will be a short section. It is as simple as:
EXEC ORDS.ENABLE_OBJECT(p_object => 'SALARY');
That’s it. The SALARY table is now accessible through a REST service at /ords/<my schema>/salary/. Moreover, the Filter Object query parameter provides a powerful search mechanism through the Salary.csv file. For example, let’s find people in the data set with more than 30 years experience: q={"experience":{"$gt":30}}
You can’t do that so easily in a text editor 😁
See Filter Objects in the ORDS Developer Guide for more information. Of course this RESTful URL can be protected to require a authorisation for accessing it too while the chart is left unprotected.
Note that with standard REST Enabled tables you can perform DELETE, GET, POST and PUT methods but with external tables, the only functional method is GET. All others will get a HTTP 555 response with this message: ORA-30657: operation not supported on external organized table.
Conclusion
If you have followed the above steps you have opened up new ways of accessing, sharing and interrogating data in a CSV file.
To reiterate, when using an external table the data comes from files in the Cloud or from files in a directory. The data does not get loaded or copied into a table in the database. Drop in a new Salary.csv file with different data and it is immediately picked up on the next query.
The combination of Object Storage, Autonomous Database and Oracle REST Data Services provide some interesting options for doing more with CSV files. It has its limits though. To be realistic, large volumes of data and complex queries would require actually loading copies of the data into the database. For that one should consider Autonomous Database Data Studio to load the data. You can still use ORDS as above to share the data as charts or expose a RESTful interface to the data.
ORDS 24.1.0 was released today and is available for download at https://oracle.com/rest. The release notes outline the enhancements and bug fixes. Many are notable. That includes performance improvements for JSON-Relational Duality Views and AutoREST in general.
Here’s an outline of test results using ORDS 24.1.0 running in my Developer DB VM on my laptop. It’s not a blindingly fast Oracle Exadata database setup so the absolute response times are nothing to write home about but the comparison of ORDS 24.1.0 over 2 previous ORDS releases is the point of the exercise here.
Data Shape
The data shape is the RESTful Car-Racing Example. In particular the GET of TEAM and TEAM_DV data through ORDS. Using k6 scripts for generating requests, there are 10 concurrent clients that are sending GET resource item requests 1,000 times each. The resource item identifier, team_id, is random so not the same row is returned ever time. The data set is not huge by any stretch of the imagination and does not reflect a likely production data shape. However, the purpose here is to compare changes in response times and throughput.
Authentication
Those AutoREST services provided by ORDS for the tables and duality views are protected because that’s most likely to be the situation in production. The k6 script is hard coded with client id and client secret for authentication. When the k6 test script starts the first thing performed is to get an access / bearer token which is then subsequently used for every request generated by the script. You will notice in every script run this output indicating the token that will be sent for every request.
There is also the output for checks performed on every response to ensure the expected data is returned. For example, if the authorisation token was invalid, the response status code would not be 200.
✓ is status 200
✓ is application/json
✓ is requested team resource
More on the k6 scripts later. Now let’s look at the database and ORDS configuration.
Configuration
Here’s a breakdown of the Developer DB VM configuration where the database and various versions of ORDS were run for the tests. The version of both ORDS and the Java Runtime Environment have been modified so if you are not familiar with that process see Get the latest – Enhance your DB Developer VM
Database
The same Oracle Database 23 Free edition that came with the Developer DB Virtual Machine is used. The HR schema is REST Enabled and contains the Car-Racing Duality View database objects.
ORDS Settings
The default configuration settings are used. These are the same configuration settings for all versions included in the test.
Java Runtime Environment
The virtual machine was originally packaged with Oracle Java 11.0.19 but it is easy to change that. In this virtual machine Oracle GraalVM for Java 17 is used.
It is straight forward to download another JRE distribution and create a symbolic link to it from your $ORDS_HOME/jre directory. The ORDS shell script will use that Java Runtime Environment irrespective of the $PATH or $JAVA_HOME environment variable.
[oracle@localhost ~]$ /home/oracle/ords/jre/bin/java --version
java 17.0.9 2023-10-17 LTS
Java(TM) SE Runtime Environment Oracle GraalVM 17.0.9+11.1 (build 17.0.9+11-LTS-jvmci-23.0-b21)
Java HotSpot(TM) 64-Bit Server VM Oracle GraalVM 17.0.9+11.1 (build 17.0.9+11-LTS-jvmci-23.0-b21, mixed mode, sharing)
That is the setup for every test run. Startup a specific version of ORDS in standalone mode using the same configuration folder, and JRE, for each ORDS version.
Start ORDS in standalone mode: /home/oracle/ords-23.3.0/bin/ords serve
Start the test run: k6 run team_dv.js
Gather the results
Start the test run: k6 run team.js
Gather the results
Shutdown ORDS
Repeat for /home/oracle/ords-23.4.0/ and /home/oracle/ords-24.1.0/ to cover those releases too. Not sure how to get your Developer DB VM to have multiple versions of ORDS? Take a look at Get the latest – Enhance your DB Developer VM.
Results
The test runs involve sending requests to GET a random team record, either as a Team Duality View query or query directly on the Team Table that the duality view uses.
Now for the test run with the latest version of ORDS.
Oracle REST Data Services version : 24.1.0.r0960841
Oracle REST Data Services server info: jetty/10.0.20
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 17.0.9+11-LTS-jvmci-23.0-b21
ords_auth.js provides a function for getting a bearer token
team.js tests the AutoREST endpoint for TEAM table and uses a function imported from ords_auth.js to get an access token. It is hardcoded with a client_id and client_secret and randomly gets team data based on their team_id.
team_dv.js tests the AutoREST endpoint for TEAM_DV duality view and uses a function imported from ords_auth.js to get an access token. It is hardcoded with a client_id and client_secret and randomly gets team data based on their team_id.
Do it yourself
With the RESTful Car-Racing Example data you should be able to run the same k6 scripts for testing your environment. If you have not done so already install k6 and download the team.js and team_dv.js script files. Just note that the CLIENT_ID and CLIENT_SECRET values will have to change for you database.
It is explained in the RESTful Car-Racing Example article how to get this information from the database. If your schema or port number is different, you’ll have to modify those too.
The upshot from my test runs is approximately 3 times improvement on throughput per second for JSON-Relational Duality Views and 2 times improvement for the throughput per second for a table. Your results may show different absolute response times but I expect you will see a similar improvement ratio when comparing with previous versions of ORDS.
Reply in the comments with your own findings. I look forward to hearing from you.
Oracle Database 23c Free introduced the concept of JSON-Relational Duality Views which allows you to overlay a JSON document representation over a relational data model. In this article we’ll take Car-Racing Duality View Tutorial setup script and build on that to explore Oracle’s RESTful interface to Duality Views – Oracle REST Data Services (ORDS)
What Are JSON-Relational Duality Views?
JSON-Relational Duality Views provide a seamless way to interact with both JSON documents and relational data within Oracle Database. These views expose relational data as JSON documents, allowing developers to perform query and DML (Data Manipulation Language) operations using conventional SQL or directly with JSON.
Key Features and Benefits:
Unified Access:
Developers can choose their preferred approach: SQL for relational data or JSON for document-centric applications.
Changes made via one method automatically reflect in the other, maintaining consistency.
Updatable Views:
Duality views allow insertion, update, deletion, and querying of documents.
Inserting a row into the root table creates a new document, and changes propagate automatically.
Document-Identifier Field:
Each document has an identifier (e.g., _id) that corresponds to the primary-key columns of the underlying tables.
Ensures efficient updates and consistency.
Integration with APIs:
Use SQL/JSON functions, Oracle Database API for MongoDB, Simple Oracle Document Access (SODA), or Oracle REST Data Services (ORDS) to manipulate data.
Analytics, reporting, and machine learning can operate on the same data using SQL, PL/SQL, JavaScript, or C.
Car-Racing Example: The Track Ahead
Data related to Formula 1 car races are used in Oracle documentation to present the feature. In our car-racing example, we’ll explore three kinds of documents:
1. Team Document
Contains information about racing teams, including team names and the drivers associated with each team. The underlying tables are TEAM and DRIVER. Example team duality view structure:
Represents individual drivers, including their names, and team affiliations. The underlying tables are DRIVER, TEAM and DRIVER_RACE_MAP. Example driver duality view structure:
{
"driverId":115,
"name":"Alexander Albon",
"points":15,
"teamId":307,
"team":"Williams",
"race":[
{
"driverRaceMapId":22,
"raceId":202,
"name":"Saudi Arabian Grand Prix",
"finalPosition":3
}
]
}
3. Race Document
Captures race details, podium standings, and results for each driver. The underlying tables are RACE, DRIVER, and DRIVER_RACE_MAP. Example driver duality view structure:
{
"raceId":202,
"name":"Saudi Arabian Grand Prix",
"laps":50,
"date":"2022-03-27T00:00:00",
"podium":{},
"result":[
{
"driverRaceMapId":24,
"position":1,
"driverId":101,"
name":"Max Verstappen"
}
]
}
Here’s an additional script for REST Enabling the tables and views so that an access token is required to access them. The following has been tested using the Oracle Developer DB Virtual Machine with Oracle 23free Database. See previous articles on the topic: Oracle Developer DB Virtual Machine – a closer look and Get the latest – Enhance your DB Developer VM. The database account used is HR which has already been REST Enabled. From here on, every URL will start with http://localhost:8080/ords/hr/ because that is the easiest to reference with the Developer DB Virtual Machine.
That pl/sql block makes the tables and views available through ORDS AutoREST but to access them requires authorisation and Car Racing Client OAuth client is created with the necessary roles to do that. It is a client credentials OAuth client so to see the username and password generated for it just run this query:
SELECT client_id, client_secret
FROM user_ords_clients
WHERE name = 'Car Racing Client';
That CLIENT_ID and CLIENT_SECRET are the username and password to use to get an access token so that the tables and views can be accessed through ORDS. More on getting that access token later. For now, let’s look at the tables and views. Send a request to http://localhost:8080/ords/hr/open-api-catalog/ and list of services available will include:
Follow those canonical links for an OpenAPI V3 description of the services available. For example, http://localhost:8080/ords/hr/open-api-catalog/driver_dv/ will show something like this…
That OpenAPI V3 document can be imported into your development or integration tools so you can get started with building clients to use the services straight away. As a convenience, ORDS Database Actions provides a graphical interface for the OpenAPI document.
Try It Out
In that OpenAPI View there’s even the option to invoke that service directly from your browser.
Click on “Try it out” and an Execute button appears at the bottom of the Parameters section.
The parameters are optional so just press that Execute button to “Retrieve records from DRIVER_DV”. Note that this is the same as running the following from the command line:
The DRIVER_DV, in fact none of the Car-Racing example tables and duality views, can be accessed through ORDS without authorisation.
Client Access Token
Let’s get that access token. The PL/SQL block for REST Enabling the tables and views also created a client with the necessary roles for access them: Car Racing Client. We retrieved the Client ID and Client Secret ( aka username and password ) earlier. Those credentials are used to obtain an access token and that access token is sent with every request to ORDS. The database user can revoke that access token and even remove the OAuth client so it is more secure than always relying on usernames & passwords. ORDS supports a variety of OAuth types as well as JWT Profiles for OpenID Connect. Now back to getting that access token…
Command Line
Using the Client ID and Client Secret as username and password the access token can be retrieved using cURL:
Note that every time a new OAuth access token ( aka Bearer token ) is issued the previous one issued for that OAuth client is invalidated. You only need to request a new one when the current one is about, or has already, expired.
Using Database Actions
While in ORDS Database Actions REST Workshop and logged in as the HR user you can retrieve that information too by selecting Security -> OAuth Clients.
Try It Out Again!
Now that we have a valid bearer token Vu1AJ02QdF_13_kjjsSXRw let’s use that in the OpenAPI View to try executing requests again. Navigate back to the OpenAPI View for DRIVER_DV.
The Authorize button will launch a dialog box to set the bearer / access token to be used when making requests. Other authorisation flows are supported but since we have a bearer token already we’ll just set that…
Enter the BearerAuth value and press Authorize to have it saved in the browser session. Now all HTTP requests executed in the OpenAPI View will have this token until you go back this “Available authorizations” dialog and press Logout. Let’s now use that token. Press the “Close” button and now you’ll see that the padlocks now show as locked. Repeating the steps for executing a GET request to Retrieve records from DRIVER_DV we now see data returned…
Note that the corresponding cURL command is also displayed so this could also has been executed from the command line as:
There are a lot more operations listed so experiment on what you can do through the OpenAPI View or from cURL. Note that Duality View JSON QBE parameter for querying uses the same syntax as SODA so it is not exactly the same as the Filter Object syntax used for AutoREST of relational tables and views.
How about trying to create a new driver record?
Build out the team
Using those Duality View services now let’s start creating new teams and drivers using batchload which is covered in the ORDS documentation. This allows you to perform multiple inserts of data that spans multiple tables. In this case the TEAM and DRIVER tables behind the TEAM_DV Duality View. The add-team.json file contains data for 7 more teams with 2 drivers each. Download it and use the file to populate those tables in one single request…
HTTP/1.1 200 OK Content-Type: text/plain Transfer-Encoding: chunked
#INFO Number of rows processed: 7 #INFO Number of rows in error: 0 #INFO Last row processed in final committed batch: 7 SUCCESS: Processed without errors
Relational Table
So far the focus has been on Duality Views but as one can see from the above image, the underlying table(s) for a Duality View get updated. ORDS can also provide access to these relational tables and the above PL/SQL block provided we have already configured that.
Thanks to the driver_race_map_trigger created when we ran the DualityViewTutorial.sql script we can now see the impact on the DRIVER table with Max Verstappen going from zero points to 25.
GraphQL
In this Car-Racing example the underlying tables for the Duality Views have been REST Enabled which also means they can be queried using GraphQL if ORDS is running on Oracle GraalVM Enterprise Edition with JS component installed. The advantage of GraphQL is that it allows you to define a query across the relational tables without having to define a Duality View but it would be a read only query. You do not get the create, update and delete capabilities of JSON-Relational Duality Views.
In this section we will take a look at a GraphQL query which can return the race date which is not currently included in the Driver Duality View. As a reminder, the Driver DV row looks like this:
{ "driverId":101, "name":"Max Verstappen", "points":25, "teamId":301, "team":"Red Bull", "race":[ { "driverRaceMapId":8, "raceId":202, "name":"Saudi Arabian Grand Prix", "finalPosition":1 } ] }
It shows the data from DRIVER table, the team name from TEAM table, the RACE name and DRIVER_RACE_MAP finalPosition. However, it does not show the date of the race. Albeit in a slightly different structure, this GraphQL query will return the same data, plus the date of the race:
query Drivers { driver { driver_id name points team_id team_team_id { name } driver_race_map_driver_id { driver_race_map_id race_id race_race_id { name race_date } position } } }
It is slightly more nested and uses names reflecting the foreign key constraints between the tables but it does include race_date. Running the query through cURL is simply a POST to the GraphQL endpoint.
Note that running the query through postman still requires setting the access bearer token. The added advantage of postman’s support for GraphQL is that it will use the same endpoint ( /ords/hr/_/graphql ) to automatically fetch the GraphQL schema so developing the query is guided. Here is the GraphQL query response for that query. It is quite long but it is informative to compare it with the corresponding DRIVER_DV Duality View structure.
GraphQL requires ORDS to be running on GraalVM with the JS component installed but unlike Duality Views, you don’t require Oracle RDBMS 23 to use it. The Car-Racing relational tables could still be used in a 19c database for example. However, JSON-Relational Duality Views in Oracle RDBMS release 23 can do so much more.
Crossing the Finish Line
In this blog post we have taken the Car Racing example Duality Views and their corresponding tables, created using DualityViewTutorial.sql, and REST Enabled them for secure RESTful access through ORDS. Using batchload we loaded multiple team and driver records in one single request. We have interacted with the underlying tables and even performed dynamic cross table join queries using GraphQL. By introducing GraphQL into the conversation it makes the Duality View advantages much clearer.
JSON-Relational Duality Views provide a powerful bridge between the structured world of relational databases and the flexible world of JSON documents. Whether you’re building APIs, applications, or analytical tools, these views offer a unified approach to data manipulation on the fast track. 🏁🚀
About a year ago Oracle 23c Database Free became available as a preview of the new “converged database” which introduces a huge range of application developer concepts right in the database. Since then I’ve written a couple of articles about it in the context of the Oracle Developer DB Virtual Machine…
The latter covers upgrading your version of ORDS which is important for this article which will explore some Oracle Multilingual Engine ( MLE ) concepts which will require ORDS 23.4.0 ( or later ) just for the simplicity of the MLE editing support in Database Actions.
The goal here is to take Javascript based function which generates sample data and make that available as a REST service which will return data in a format which can be POSTed to the REST Enabled HR Employees table. Strictly speaking there’s no great advantage there because you could do that all in the DB anyway, but what gets covered here is the approach to take JSON data from Javascript and return it in the snake case format which can be used in Oracle REST Data Services.
Today we’re building on previous articles. In fact, the starting point is Jeff Smith’s Using JavaScript to create test data in Oracle Database 23c. Jeff’s blog post highlights all that Martin Bach’s article Using faker-js/Faker to generate test data respecting referential integrity in Oracle Database 23c delivers, showing how to follow Martin’s steps in Database Actions MLE JS editor. That can be run in your Oracle Developer DB Virtual Machine with ORDS 23.4.0. Of course you could just use the database in the VM and have ORDS 23.4.0 running outside it too. Either way, once you’ve gone through Jeff’s blog post you will have an MLE JavaScript module in place called MLE_FAKER and an MLE Call Specification called MLE_FAKER_API.
Therefore you can run this select statement to get a JSON object with random data for the HR Employee record:
You would think that you could use the generated JSON object as the payload body for a POST to the REST Enabled HR Employees table. The difference in naming convention for field names prevents that from happening. Send the mle_faker_api.random_employee JSON object to ORDS and you will get a HTTP 400 Bad Request response. Of course we could change the MLE_MAKER function implementation to use snake case. That would deviate from what Martin has implemented and he may have subsequent articles building on that implementation so it’s best to avoid interfering with that.
Camel to Snake – we’ll handle it
It is quite simple really, we write a PL/SQL block handler which will take the fields from the JSON object and define the OUT parameters using the field naming convention we want. The handler takes a gender parameter to pass to the call specification and defines the data types for the fields. The database JSON_VALUE function is key here…
DECLARE random_employee JSON; BEGIN select mle_faker_api.random_employee(:gender) into random_employee; :first_name := JSON_VALUE(random_employee, '$.firstName');
For your convenience here is a script to define the module/template/handler…
Which in turn can be used in a POST request to insert the record for real and generate an employee id.
Since one of the steps covered in Martin’s blog posts dealt with importing existing Javascript libraries ( Faker ) into the database, the transformation of the generated Employee JSON structure could possibly be achieved through Javascript libraries such as change-case. That’s a homework exercise for extra merit.
The real foundational work for this simple example of generating HR Employee sample data has been laid by Martin Bach. It was through building on that with a visual representation of the steps through ORDS’s Database Action UI that Jeff Smith brought it to my attention. I am grateful to both for the starting point they provided.
Everyone was happy. The web application uptime and response rates have been well within the Service Level Agreements for months. Uptake of the published REST Services was on the rise. Both internally and externally, more and more systems were consuming the services and the three ORDS instances behind an NGINX load balancer took it all in their stride. No long running queries or inefficient PL/SQL code locking DB resources. Everything humming along like a finely tuned engine. Then it came time to upgrade…
The ORDS product team have been operating to the usual cadence of one major release every 3 months. Although not ancient, our system is a few releases behind and not only are there interesting new features that are worth exploring but there’s always the security fixes to recent critical 3rd party Common Vulnerabilities and Exposures ( CVE ) that justifies being at the most recent stable release.
Upgrade time comes around so the test environment is setup to run through the ORDS upgrade process before applying the same to production. Then this happens for all the requests: HTTP ERROR 400 Invalid SNI
What is my SNI and how did it get invalid?
The same web server could be hosting multiple sites. In the case of ORDS, the same ORDS instance could have multiple domain name entries that route to it. Server Name Indication ( SNI ) is an extension to the Transport Layer Security (TLS) networking protocol for a client ( such as your browser ) to indicate to the server ( ORDS running standalone mode for example ) which site it wishes to talk to. To put it simply, SNI is used at the start of the secure connection handshake between client and server. The client sends a Host header in the request to indicate what site it is requesting and the server goes looking for its certificate for that server name. See https://datatracker.ietf.org/doc/html/rfc4366#section-3.1 for more details on the role of Server Name Indication in keeping communication secure.
You’ll notice from the stack trace and the error page that the Invalid SNI response does not look like a standard ORDS error page. It is in fact generated by the Eclipse Jetty embedded in ORDS when running in standalone mode.
When running ORDS in standalone mode an embedded Eclipse Jetty server is used. Before ORDS 22.3.0 that was Jetty 9 but since that release ORDS has been using Jetty 10 and for very good reason: more secure. As mentioned in the second paragraph at the start of this article there a fixes and optimisation that are worth making the upgrade for. Jetty 10 addressed some issues in the TLS handshake. Not least Better handling for wrong SNI #5379. Beforehand it didn’t really matter what the client sent in the Host header, Jetty would return any certificate it had.
What is in that certificate anyway?
The certificate, self-signed or otherwise, holds some important information about the server and is essential in establishing trust. To see the contents of the certificate, use the keytool utility in your Java distribution.
The above output shows that the Owner of this certificate has a Common Name ( CN ) value localhost which is the default standalone.https.host configuration setting value.
What to do about it
Now that you know the root cause how do you go about resolving it? It is as simple as ensuring the Common Name ( CN ) or Subject Alternative Name ( SAN ) field in the certificate matches what the client is sending the in Host header of the request. For more information on having more than one hostname in a single certificate see a previous article: Beyond the Common Name: Secure multiple domains with a single self-signed certificate
ORDS will generate a self-signed certificate if necessary when it is started in standalone mode and configured for HTTPS. In other words, the standalone.https.port is specified in the global configuration settings or --secure option is used in the ords serve command. If no certificate exists at the configured standalone.https.cert location, ORDS will generate the self-signed certificate and key file.
The Common Name used for the self-signed certificate is taken from the standalone.https.host configuration setting. If not set, the value is localhost. Traditional certificate practices often rely solely on the Common Name (CN) field. That’s the approach taken by ORDS by default when generating the self-signed certificate.
This can sometimes catch people out when they start up ORDS in secure standalone mode the first time. The self-signed certificate is generated but they may not have specified the standalone.https.host configuration setting and when they do later, they still get HTTP 400 Invalid SNI responses. That’s because the self-signed certificate is already generated so no matter what the standalone.https.host configuration setting says, the certificate will not be generated again.
Simple steps
The simplest course of action is to rename the self-signed certificate and key files and restart ORDS. It will not find the files and therefore will attempt to generate new self-signed certificates.
In summary, make sure the standalone.https.host configuration setting matches the Host header value that clients will send for requests being routed to your ORDS standalone server.