As always, the complete, official list of enhancements and fixes can be found here. Contained in this brief post are some of the highlights from this latest release.
My top enhancements
Of the new features the team has published, I’d say these next two are probably going to be the “most sought after” for folks.
Managed service connections
In short, you can now use either Instance Principal and OCI Profile credentials for the db.connectionType in your ORDS configuration settings. Details can be found here.
My takeaways from this:
It is going to make credential storage a LOT easier
Use Instance Principal for when you are using a VM to host ORDS in OCI, and OCI Profile for all else (easy way to look at this without getting too complicated)
I am working on a tutorial for this, using the OCI Profile method. Once I’m back from traveling, I’ll publish on the official Oracle blog site.
JWT Profile at the pool level
The elevator pitch for this: “You know how you could only set a JWT profile for a schema? Well this makes it possible to set this profile at the pool level, for all consumers (schemas) of that pool.” Meaning, this makes it optional, should you want to “share” a JWT Profile across schemas. Details can be found here. Expect an official tutorial soon.
Pre-Authenticated Requests (PARs)
ORDS recently introduced support for Pre-Authenticated Requests (PARs). Well, we now have updates in the SQL Developer Web UI. You can create PARs from within the Resource Handler Dashboard. You can revoke PARs too.
SQL Developer Web UI
Creating a new PAR
Creating a new PAR from an existing Resource Handler is fairly effortless now. After creating the PAR, you’ll be provided a Token, Alias, and complete PAR URI. You’ll see how the URI is a concatenation of the existing URI and the PAR token. But what if you want to revoke access?
Revoking a PAR
PARs are automatically revoked when they expire. You can alternatively revoke the PAR:
In the PAR dashboard, or
From within the same Resource Handler dashboard
PAR Expiration by Alias function
We have introduced a new function that will ingest the PAR alias, and return the time left till expiration. You can find this new function under the ORDS_PAR package of the ORDS_METADATA schema.
I’d like to know, how would you use this though? Would you use this in some sort of automation, or would you somehow display the remaining time on screen (if we are talking about a client application)?
MLE/JS
Now, when a Resource Handler has anything to do with MLE and you encounter an MLE error, you’ll receive a message with a callout to MLE.
Exception handling
In the example below, I’ve created a simple MLE/JS Resource Handler that should display some details about a specified employee (identified by their employee number). However, since this test schema has zero objects in it, I’d expect to receive an error.
In the example below, I use a random employee number, for a non-existent “employees” table. And to truly see the benefits, you’ll want to have the ORDS printDebugToScreen configuration setting set to TRUE.
ORDS CLI
Verify command
I cannot tell you how many times people ask questions that can be answered with this one command.
On its face, it seems trivial, but this is such a nice shortcut to determining if an ORDS installation is valid. I can see this being used in your shell scripts too, for validation. Something like:
sed'ords config --db-pool default verify'
Or perhaps, add it to the compose.yaml file that we include in our ORDS container?
Other/Misc
Want to share your story?
If you’ve stopped by the ORDS product page recently, then you may have seen our latest customer testimonial. Sphere is a heavy consumer of ORDS, and they love the product. If you have an ORDS story to share email me. Let’s collab on a similar write-up.
GitHub
Apparently the oracle/docker-images/ords repo has been “dormant” for quite sometime. Well no more. We (Adrian and I) are now in full control of the ORDS content. So we are updating the Dockerfiles and README. They should be ready in a week or so (end of July 2025), we are finalizing the drafts now. And if you have an outstanding/pending issue you can expect to see some movement on your ticket.
Oracle Container Registry
We have taken your feedback and updated the README for the ORDS official container. You can see the latest here. However, we have another update planned, so if you don’t see your suggestions yet, give us another week! We are updating the README to make it more approachable for a container newbie, and we are adding in some additional, helpful comments in the compose.yaml file too!
Oracle Cloud World 2025
We are in full Cloud World mode now, till the end October. View the details and register here. Kris, Jeff, and I will be there. We’ll be presenting on several topics. But if travel isn’t in your future, never fear, we’ll have recordings of the presentations once Cloud World has wrapped up.
Official Release Notes
And finally, the official release notes can be found here.
This actually came about from a Support Request. We were troubleshooting a JWT issue, that had ORDS in the mix. Ultimately, this is looking more and more like a networking issue (maybe something to do with routing rules between the load balancer and the backend server).
But in the course of our troubleshooting, we wanted to rule out some variables. One of those variables was the JWK URL we were using for the ORDS_SECURITY.CREATE_JWT_PROFILE procedure. So we had this idea to kind of fool ORDS into thinking it was visiting the IDCS (the predecessor to Oracle IAM) JWK URL when, in fact, it was just visiting another ORDS endpoint.
Obvious disclaimer: NOT FOR PRODUCTION USE. For troubleshooting purposes only. This article just serves as an example of how you can build TONs of ORDS APIs without always having to create a table, JSON collection, function, procedure, view, etc…
If you’ve seen my JWT + ORDS tutorials (one for OCI IAM, another for Microsoft Azure), then you know when you create an ORDS JWT Profile, it will look something like this:
In the above code block, just know that p_issuer is your Identity Provider (e.g., IAM, IDCS, Microsoft Entra, etc.), p_audience is the Primary Audience (which you created when you registered your client application with your Identity Provider), and the p_jwk_url is where ORDS goes to look up the keys for decoding a JWT.
The eventual JWT that ORDS receives would include the Issuer and Audience, too. But it would also include other values such as the Scope. That Scope name would be associated with the ORDS Privilege–the privilege you created specifically to use for protecting/associating with your ORDS Resource Module. And ORDS would use all these values (and the JWK “keys” found at that JWK URL to decode the JWT) together to:
Verify you are who you say you are, and
You have the proper Scope (or as ORDS interprets it…the proper privilege) to access this protected resource
TL;DR This recap wasn't meant to explain in detail how JWTs work, but rather meant to demonstrate how everything is interconnected. ORDS can't do anything with a JWT unless an ORDS JWT Profile has been created beforehand. That JWT Profile itself works in tandem with the ORDS Privilege. And that Privilege is the same name as your Scope. So if the Privilege doesn't exist, the Profile is worthless. And if the Scope doesn't exist, well then certainly nothing is going to work! And until all that "stuff" is connected, then that protected resource cannot be reached.
Back to the use case. So, for troubleshooting reasons, we took that JSON object located at that JWK URL and recreated it as an ORDS endpoint. Let me point out that this is cool, but what you should take away from this is that there are tons of things you can do with ORDS, and you have a lot of control over how ORDS will send back information to you, your application, or your web client.
Here is how we structured the Handler code:
BEGINHTP.P('{" keys": [ { "kty": "RSA", "x5t#S256": "Lorem ipsum dolor sit amet__pellentesque elementum", "e": "AQAB", "x5t": "xwSmccaQZDvAZPOpZPHOiQDlLgo", "kid": "SIGNING_KEY", "x5c": [ "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla ex sed felis. Maecenas auctor risus ac nisl efficitur, sit amet euismod odio finibus. Praesent lacinia nunc id ex tempor, sed tempor nisl porta. Integer semper efficitur arcu, a dictum quam vulputate tempus. Nulla congue dapibus tortor vel volutpat. Curabitur et sollicitudin purus. Mauris quis aliquam augue. Nulla ac leo tristique, ultricies mauris sit amet, ultrices quam. Praesent placerat a lectus sit amet dapibus. Donec rhoncus felis velit, sed placerat nunc pellentesque elementum==","Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla ex sed felis. Maecenas auctor risus ac nisl efficitur, sit amet euismod odio finibus. Praesent lacinia nunc id ex tempor, sed tempor nisl porta. Integer semper efficitur arcu, a dictum quam vulputate tempus. Nulla congue dapibus tortor vel volutpat. Curabitur et sollicitudin purus. Mauris quis aliquam augue. Nulla ac leo tristique, ultricies mauris sit amet, ultrices quam. Praesent placerat a lectus sit amet dapibus. Donec rhoncus felis velit, sed placerat nunc pellentesque elementum" ], "key_ops": [ "verify", "wrapKey", "encrypt" ], "alg": "RS256", "n": "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla" } ]}');END;
The complete PL/SQL Module Export (if you want to take a closer look):
-- Generated by ORDS REST Data Services 24.4.1.r0381713-- Schema: ADMIN Date: Thu Apr 03 07:10:42 2025 --BEGIN ORDS.DEFINE_MODULE( p_module_name => 'jwk', p_base_path => '/jwk/', p_items_per_page => 25, p_status => 'PUBLISHED', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'jwk', p_pattern => 'jwk', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'jwk', p_pattern => 'jwk', p_method => 'GET', p_source_type => 'plsql/block', p_mimes_allowed => NULL, p_comments => NULL, p_source => 'BEGIN HTP.P(''{" keys": [ { "kty": "RSA", "x5t#S256": "Lorem ipsum dolor sit amet__pellentesque elementum", "e": "AQAB", "x5t": "xwSmccaQZDvAZPOpZPHOiQDlLgo", "kid": "SIGNING_KEY", "x5c": [ "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla ex sed felis. Maecenas auctor risus ac nisl efficitur, sit amet euismod odio finibus. Praesent lacinia nunc id ex tempor, sed tempor nisl porta. Integer semper efficitur arcu, a dictum quam vulputate tempus. Nulla congue dapibus tortor vel volutpat. Curabitur et sollicitudin purus. Mauris quis aliquam augue. Nulla ac leo tristique, ultricies mauris sit amet, ultrices quam. Praesent ' || 'placerat a lectus sit amet dapibus. Donec rhoncus felis velit, sed placerat nunc pellentesque elementum==","Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla ex sed felis. Maecenas auctor risus ac nisl efficitur, sit amet euismod odio finibus. Praesent lacinia nunc id ex tempor, sed tempor nisl porta. Integer semper efficitur arcu, a dictum quam vulputate tempus. Nulla congue dapibus tortor vel volutpat. Curabitur et sollicitudin purus. Mauris quis aliquam augue. Nulla ac leo tristique, ultricies mauris sit amet, ultrices quam. Praesent placerat a lectus sit amet dapibus. Donec rhoncus felis velit, sed placerat nunc pellentesque elementum" ], "key_ops": [ "verify", ' || ' "wrapKey", "encrypt" ], "alg": "RS256", "n": "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla" } ]}'');END;');COMMIT;END;
With that new ORDS endpoint, we (temporarily) recreated the ORDS JWT Profile with the new ORDS API as a proxy for our true JWK URL. Like this (this JWK URL is entirely fake, BTW):
Hypothetically if you were to navigate to that ORDS URI, you’d see a facsimile of the actual JWK information:
So now, we’ve temporarily removed the JWK URL from the troubleshooting equation. However, we can still access that protected endpoint since we have a valid token and keys to decode it (courtesy of the ORDS endpoint we created)!
And this my friends, is the type of mad scientist stuff that I live for. I had to share our experience to underscore two main points:
You have a lot of flexibility with these ORDS APIs, and
When it comes to troubleshooting/root cause analysis, you must break things down into individual parts!
You have an Oracle Cloud Infrastructure Domain (OCI), or intend to set up a Domain in OCI, so that you may register an "Integrated Application" therein, and use the JSON Web Tokens (JWTs) provided by OCI Identity and Access Management (IAM) to request access to protected ORDS Resources (aka ORDS APIs).
If this describes you, then keep reading!
JWTs with Entra? If you are looking for how to configure ORDS to be used with Microsoft Entra JWTs, then you'll want this tutorial.
Creating a Domain
NOTE: Skip this section if you already have a Domain or know how to set one up!
Assuming you are at the central OCI Console, click the navigation icon at the top of your screen. Then, navigate to Identity & Security and click Create domain.
Make sure you are in the correct Compartment!
NOTE: Make sure you are in your correct Compartment (this will be up to you to decide)! Everything I show here is done within the same /ords Compartment.
In this example, I’ve chosen to create an “External User” Domain type. I haven’t tested with any other Domain types, but I assume they all work similarly.
Double check. Are you in the correct Compartment?
Once you’ve entered all the information (e.g., Domain administrator values and Compartment), click Create Domain. Then, click on the Domain you just created. Next, you’ll need to create and add an “Integrated application.”
Mini-tutorial: Adding a user to your Domain
In this mini-tutorial, I demonstrate how to create a new user. Notice how I am using a personal email account, you can totally do this!
Once you’ve added this user, they recieve a password reset email for this Domain. They must create a new password before logging in.
NOTE: I'll be logging in as this user for the JWT demo.
1. Navigate to your Domain.2. Click Create user.3. Enter in your details, and click Create.
Configure client access
NOTE: This box must be checked so your application can automatically access a JWK URL (to be used for decoding a JWT) without having to sign in to the OCI tenancy.
First, navigate to your domain, then select Settings.
If this Configure client access box is unchecked, check it, then select Save Changes (the button at the bottom of the screen).
That’s it. You’re done!
Adding an Integrated Application to the Domain
Adding the application
From within your Domain dashboard, click the Integrated Applications menu item. The “Add application” modal will appear. Read through the application types and choose the most appropriate one. For this demo, I selected “Confidential Application.”
Continue filling in the modal fields. Make sure you choose a unique name!
I’ve left the URLs section empty for this demo and kept the Display settings checked. The Display settings are options for users when logging into their Domain (i.e., they don’t impact this workflow, but they are nice to have set for later).
PRO-TIP: Even though the Description is optional, I recommend including at least a brief sentence. Trust me, your future self will thank you later! Oh, and this same advice applies to your ORDS Resource Modules too!
Click Next to continue to the Resource server configuration section.
Configuring OAuth2.0
In this section, you’ll configure your Resouce server and Client. First, we’ll create a Scope1.
Resource server configuration
When you configure your Scope, you must include a Primary audience field. Here, I’m just choosing the Domain’s name. The Primary audience needs to be structured exactly like this (with the trailing backslash)! Next, create a Scope (I’m using dot notation, since this will mirror the privilege I’ll create in ORDS, later).
Your fields should look like the above.
When finished, click Add.
Client Configuration
Next, you’ll include details about your client. In this demo, I use Postman as a client application stand-in. For this example I’m using the Authorization code grant type2, along with a Redirect URL.
FYI: Postman has a /callback URL that you can use as a Redirect URL.
Mini-tutorial: How do I find Postman’s Callback URL?
From the Authorization tab, select OAuth 2.0. Then scroll down to the Configure New Token section. There you will find the Callback URL. Copy it. You’ll need it!
INFO: The "Redirect URL" is simply the location that OCI IAM should send you back to, once you have authenticated/retrieved a JWT. If this doesn't make sense, don't worry, you'll see that in practice in a later section.
I’ve chosen “All” for the Token issuance policy. Next, click Finish.
TIP: Click the (?) and you'll see a pop-up with information that can help you decide which option is best for you. For demonstration purposes, "All" is the most generous option.
Issuing a POST request to obtain a JWT
You can send off that initial POST request to obtain the JWT from IAM with your Domain set. You’ll then use the details contained in that JWT to configure ORDS.
Gathering the required values for the request
If your application hasn’t already been activated, do so now.
Next, click the Edit OAuth configuration button and retrieve your Client ID and Client secret. Save it to a clipboard or your environment files (if using this for an application).
You will also need the Domain URL. Navigate to your Domain’s main dashboard (❗️make sure you are still in the correct Compartment). Then copy the Domain URL to your clipboard, or environment file (aka.env file).
Setting up Postman
In Postman, make sure you have selected “Request Headers” in the Add authorization data to field. Also, add the word “Bearer” to the Header Prefix field.
You’ll want to select Authorization Code as the Grant Type. For the Authorization URL and Access Token URL, you will use your Domain URL followed by:
Next, add your Client ID, Client Secret, and Scope. Notice how the Scope in Postman uses the Primary audience and the Scope (if you don’t remember where these came from, review the Resource server configuration section). Select “Send as Basic Auth header” for the Client Authentication field.
Requesting the JWT
This next part is easy. From Postman, scroll down to the bottom of the Authorization window until you see the Get New Access Token button. Click it; a new browser window will appear. Enter the user credentials (I’m using the one I created specifically for this demo), and if any pop-up blocker notifications appear, make sure you “allow pop-ups.”
Once authenticated with IAM, you’ll be redirected back to Postman (remember, this is the Redirect URL you added in the Client Configuration section).
Copy the entire JWT, and then click Use Token. You’ll now see the token in the Current Token field in Postman.
HELP ME! Okay, if the Sign-in and/or Redirect didn't work for you, it might be that you are still signed into another OCI session, with a different user (a user that doesn't belong to this Domain). So, what I've had to do in the past is make sure I'm logged out of all OCI sessions, clear my cookies, close out my browser, and then restart with a new one. This has nothing to do with ORDS, but its what worked for me.
ORDS configuration
In these following sections, you will configure your ORDS JWT Profile and ORDS Privilege (associated with the Scope you’ve created). But first, you’ll need to decode your JWT.
Decoding the JWT
In the past, I’ve used two different tools for decoding these JWTs: JWT.io and JWT.ms. In this demonstration I’m using JWT.io.
Paste your JWT into one of the decoders and copy the following values to your clipboard:
iss
scope
aud
The values you’ll need:
IssuerScopeAudienceMy ADB setup
So as not to make too many assumptions, let me show you my ADB setup. I’ve created a 23ai ADB, in the same Compartment as my Domain. And I’ve created an “ORDSDEMO” user. So, everything I’m doing, in ORDS, from here on out is using that user.
You don’t need any other users up until this point. In fact, you could just use the ADMIN account to do all of this. But I’m choosing to use the ORDSDEMO user to demonstrate how to do this with a non-ADMIN, or non-DBA user. So, when we get to the ORDS PL/SQL procedures (found in the ORDS_METADATA schema), you’ll notice they are the non-ADMIN versions of the packages.
Creating the ORDS JWT profile
Step one: Create your JWT Profile. This process “registers” a JWT profile with ORDS, so when an incoming request comes in, ORDS can “cross-reference” information related to Issuers, Audiences, and Scopes.
The easiest way to do all this is to first navigate to an SQL Worksheet and then select the schema from the Navigator tab. Then, under the database objects drop-down menu, choose Packages.
Click the OAUTH package to expand it, scroll to the bottom of that list, right click the CREATE_JWT_PROFILE PL/SQL procedure, and click Run. A slider will appear.
⚠️ REMINDER: The old OAUTH PL/SQL packages are deprecated and will be officially de-supported October 2025. We still have the old PL/SQL Security packages in our docs, but the new ones are included too. Link.
Enter your details exactly like you see below:
P_ISSUER is https://identity.oraclecloud.com
P_AUDIENCE should be whatever your Primary audience is, with the trailing slash included!
P_JWK_URL
FYI: The P_JWK_URL is a combination of [Your Domain URL] + /admin/v1/SigningCert/jwk (this is the endpoint identified in the OCI IAM Domains API doc). And since this is a /v1 endpoint, I assume there may be more. So, double-check the docs in case of a different/updated version.
❗️STOP You need to either comment out, or completely remove the P_ALLOWED_SKEW and P_ALLOWED_AGE values (in both blocks), along with the trailing comma of the L_P_DESCRIPTION value. For demonstration purposes those to values aren't required. And leaving them as "0" will make it impossible to obtain a JWT. They can be "NULL", but not "0," those are two totally different values!
Then, execute the PL/SQL procedure.
Next, you’ll create an ORDS Privilege (to associate with our protected ORDS Resources).
Creating an ORDS privilege
Aka, the “thing” we are protecting and making accessible with a valid JWT. Navigate to your REST Workshop. From the Security tab, select Privileges.
Create a new Privilege and name it exactly the same as the Scope you created in IAM. DO NOT INCLUDE the Primary audience in this scope name (In fact, you can’t; we won’t let you anyway 🤣)! In the Protected Modules tab, select the Module you want to protect. Either drag it from the Available Modules tab to the Selected Modules tab or use the arrows to move it over. Then click Create.
A peek at the ORDS API I’m using:
This just prints out the Current User, using the ORDS :current_userImplicit parameter.
Accessing your protected Resource (aka ORDS API) with the JWT
Now, you may access this protected resource. Unless you’ve taken an hour to set ORDS up, your token will still be valid (it’s valid for an hour). Otherwise, you’ll need to obtain a new, fresh JWT. Using the target ORDS API (mine is the “Hello :current_user” endpoint) in your Postman request field, click Send. You’ll see two things happen (if you are viewing your Postman Console):
An initial POST request to the Token server, and
A subsequent GET request to your target ORDS API
I should clarify…you might not see that initial POST request because you would have accomplished this before you opened the Postman Console. So, if you’d like to see these requests in real time, do this:
Clear out your cookies
Delete your existing token
Clear out the Console
Open the Console back up
Request a new JWT
Request your ORDS API (your protected Resource)
The End
And that’s it, finished! If you’ve made it this far, congratulations. That was a lot of work. Time for a coffee break!
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
There are four different Grant Types in the OAuth 2.0 framework: Authorization Code, Implicit, Resource Owner Password Credentials, and Client Credentials. You can read more about them here. ↩︎
There’s plenty to talk about in this release. However, I’m most excited about the performance improvements, ORDS sample applications, and documentation changes. Read on to get the whole story.
Enhancements
API Performance
REST API responses from either AutoREST or customer based modules could see as much as a 30% improvement in response times.
About a year ago, we introduced (we owe all our progress to Orla though) an internal program to track performance changes/improvements across ORDS APIs quantitatively. I can’t go into too much detail, but here is what I can divulge:
Although we use K6 for our performance testing, we are not promoting its use over any other available performance testing solution. There are other great tools available (e.g., Artillery, JMeter, and, of course, k6).
Testing is performed nightly against a 23ai database (installed in a PDB); we also include APEX in these tests.
For the tests, 250 schemas are created and then populated with various database objects (e.g., Functions, Materialized Views, PLSQL Packages, Sequences, Tables, Triggers, JSON Relational Duality Views, etc.)
These schemas are then absolutely hammered with Virtual Users. Users perform actions such as auto-REST enabling objects, creating custom Resource Modules, creating JSON Relational Duality Views, interrogating ORDS Metadata, and performing bulk inserts (BATCHLOAD) GETs, POSTs, etc.
These metrics are what we use to track the ORDS quantitative metrics longitudinally.
So, that’s what we mean by “performance improvements.” Pretty cool, eh?
NOTE: I don't know if that 30% average is mean or median. So, for all you stat nerds, don't ask 🤣!
ORDS Sample applications
We have not one but TWO sample ORDS applications for you 😍!
Flask/Python
The first is a fully contained LiveLabs sandbox workshop, which can be found here. But if you want to remix the code, check out my repo here (everything is heavily commented; hopefully, this will ease your pain).
Node.js/React
Secondly, our development team has created a brand new advanced application. Details are here.
NOTE: We'll continue to iterate and improve on both, so please share with us your feedback!
OAuth2.0 changes
A consolidation and streamlining of the OAUTH and OAUTH_ADMIN PLSQL packages. The details:
We’ve consolidated those mentioned above into these two new packages:
ORDS_SECURITY
ORDS_SECURITY_ADMIN
The OAUTH and OAUTH_ADMIN PL/SQL Packages have been deprecated by royal decree. However, they’ll still be included until ORDS version 25.3 (this time next year).
Creating a client and receiving your Client ID and Client Secret is now streamlined, and Client Secrets can now be rotated (by supporting two active Client Secrets while in rotation).
Locating the new PL/SQL Packages:
Finding PL/SQL Packages in Database Actions.
23ai Boolean
ORDS now returns BOOLEAN types as JSON TRUE|FALSE properties instead of 0|1.
What this looks like in practice with various configurations1:
Oracle Database 23ai + ORDS 24.3
Oracle Database 23ai + ORDS 24.2
Oracle DB 21c Enterprise Edition + ORDS 24.3
Not possible. #RedHerring
1Thank you internet stranger for providing us with this juicy bit of code.
Mong[ooohhh, no, you didn’t?!] DB API
Support for even more Database Administration commands:
listIndexes
dropIndexes, and
optional parameter expireAfterSeconds (which applies to the createIndexes command)
The following MongoDB Aggregation Stages are now supported:
UPDATE: MongoDB API update article (October 10, 2024)
A brand new article about the latest MongoDB API updates just dropped! Thanks to Hermann for publishing and sharing the latest. Details are here.
Documentation
Introduced the following new sections:
6.2.4 Using OCI Monitoring Service with Oracle REST Data Services
This new section details the configuration of the recently added ords-metrics utility. You can find details on how to set up this monitoring service (to communicate with OCI) here.
3.2 Deploying ORDS with Central Configuration Server
Along with the updated docs, we’ve included the OpenAPI spec for creating the endpoints required for a central configuration server (and a special video clip of me retrieving the PL/SQL definitions and the OpenAPI spec in Database Actions).
In our Release Notes, we claim support for the following JDKs:
Oracle Java 11, 17, or 21
Oracle GraalVM Enterprise Edition for Java 11
Oracle GraalVM Enterprise Edition for Java 17
Oracle GraalVM Enterprise Edition for Java 21
However, this may be confusing regarding Oracle GraalVM Enterprise Editions. You should know that there are currently TWOOracle GraalVM Enterprise Edition JDKs:
Oracle GraalVM Enterprise Edition 20
Oracle GraalVM Enterprise Edition 21
Instead of how we’ve presented, here is another, cleaner presentation of these JDKs:
Oracle GraalVM Enterprise Edition 20
Oracle GraalVM Enterprise Edition 21
Linux (x86-64): Java 8, 11
Linux (x86-64 and aarch64): Java 8, 11, 17
macOS (x86-64): Java 8, 11
macOS (x86-64 only): 8, 11, 17
Windows (x86-64): Java 8, 11
Windows (x86-64 only): 8, 11, 17
Oracle GraalVM Enterprise Edition details
So when you are choosing your JDK (to use with ORDS), make sure you consider your platform and use cases. Details on using GraalVM with ORDS here.
fin
This concludes the release notes supplement.
This space ⬇️ left intentionally blank.
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
This article is a walkthrough and overview of setting up the example Java plugin that ships with ORDS. These plugins allow you to add functionalities and capabilities above what is possible with a standard ORDS configuration.
In this example, the plugin allows you to use a name as a query parameter in an HTTP request. What you receive in response is a greeting from the schema plus the name you used in that HTTP request. Like this:
I know what you are thinking…so what? And for about a day and a half, I felt the same. Eventually, it dawned on me that since this plugin relies on APIs from the Oracle REST Data Services Java API Reference, you can create a plugin for anything in that book. I’ll have to spend time in another article, stepping through the Java code. I’m realizing that you don’t need to know Java fluently to understand what is happening with this application. So, if you are a Java newbie (like me), this is a great way to get acquainted with the language.
Chapter 11 Extending ORDS Functionality with Plugins
Below, you’ll see the exact text from Chapter 11 Extending ORDS Functionality with Plugins of the ORDS Developer’s Guide. If you want to follow along, you won’t have to switch between the official docs and this blog post.
Further still, I’ve broken up the steps in Section 11.1 Plugin Demonstration Example into individual sections. I’ve added some commentary and images throughout. Some directions were challenging for me, and pictures are always helpful.
Oh! And there’s a special “How the hell do I set up Ant?!” section, too. Since it took me about 30 mins to figure out how to download, install, and configure the damn thing. It would be best if you didn’t have to suffer like I did 😘.
And yes...as soon as I hit publish on this post, I'm logging a ticket to enhance this section of the ORDS Developer's Guide 😊.
The unadulterated ORDS docs
HINT: Click the expand/collapse arrow to view the full text.
11 Extending ORDS Functionality with Plugins
This chapter explains and provides examples on using ORDS plugin framework.
ORDS has a plugin framework that allows you to add your own custom functionality into the ORDS web application. Plugins can be added to the ORDS runtime by placing the jar files in the lib/ext directory. The ORDS distribution contains the source for example plugins. The plugin examples can be built using Apache ant, a software tool used for automating the build processes.
11.1 Plugin Demonstration Example
This section shows how you can locate and build a plugin demonstration example.
The plugin-demonstraion example is at examples/plugins/plugin-demo location and contains the source for a HttpServlet that gets a database connection injected at runtime. The servlet uses that JDBC database connection to run a query in the database and return a response at runtime.
Perform the following steps to build and use the demonstration example:
Change the directory to examples/plugins/plugin-demo
Run ant to build the examples/plugins/plugin-demo/built/plugin-demo.jar file
Copy the plugin-demo.jar to the ORDS distribution lib/ext directory and start an ORDS instance.
Invoke the servlet using the following URL pattern:http://server/ords/schema/demos/plugin?who=somebody
For example: http://localhost:8080/ords/hr/demos/plugin?who=scott where ORDS is configured with a default pool and HR is an alias for a REST Enabled Schema in that database.
The details of developing and deploying Java based plugins is available in the Oracle REST Data Services Java API Reference book.
Change the directory to examples/plugins/plugin-demo.
This step assumes you have Ant installed and are ready to build the application. In other words:
You’re in your Terminal, Shell, or Command Prompt.
You have issued the cd command, so you are in the /plugin-demo directory, and
You’re about to run the ant command on the command line.
A look at the /plugin-demo directory. We’ll come back to this in a second.
HOWEVER…If you don’t have Ant installed, this exercise won’t work. Let’s get Ant.
Get Ant
Introducing a special section dedicated to making Ant acquisition much easier!
You’ll need to visit the Apache Ant main page. But don’t waste your time navigating to the Downloads page. It makes no sense.
Just click Manual (located under the Documentation section). A new page will appear, and you’ll see a Table of Contents. Click “Installing Apache Ant.” Then, click “Getting Ant.”
Once you are at the “Getting Apache Ant” page, you’ll see five steps. For Step 1, I’ll assume you have Java installed.
Also, if own a computer, one that you bought and didn't build, there's like a 99% chance you have Java installed. It just might not be the latest version.
In Step 2, we need the Binary Distribution. Of the four links there, I chose the .zip file. It seemed the most normal (I haven’t seen .tar files since working on IBM Z 😵💫).
Step 3 states to unzip and then place the directory somewhere. I’ll show you what I did in a second.
Step 4 instructs you to do some things and references the Setup page for details.
If you don’t understand any of the instructions in Step 4, please feel free to click the Setup link. You’ll be presented with even more steps that don’t make sense.
Let me show you how I have this all set up. If you are on a Mac, you can see how I have this bin directory and ANT_HOMEvariable set up. I open a new Terminal and issue the following command: open .zprofile
At the bottom of the file, you’ll see how I have Apache Ant set up for both bin and ANT_HOME. Set yours up like this: save the file, then restart your Terminal.
Wut did I just do? My idiot's take...After doing this, you can now execute the ant command in any terminal. And you've also "pointed" to all the required dependencies (i.e., ANT_HOME) for Ant to make the magic happen.
After restarting, you can issue the ENV or env command to review your environment settings. Here, you’ll see the Ant bin is in my PATH. You can now build with Ant 👍🏻.
And to answer the question, “Where do I put the Ant files?” I placed them in my Library on my Mac.
The Apache Ant docs mention two commands for testing to see if Ant is working as expected. The first command ant can be issued anywhere.
However, I assume if you issue this command in a directory where source files are waiting to be built, then you'll actually end up building a new Java app. I believe this is why they recommend issuing this command in a new shell. It assumes you don't have files there, waiting to be built.
Secondly, you can issue the ant -version command. If you have Ant installed, you’ll see the version information.
Okay! You now have Ant; let’s get back to the colony 🐜🐜🐜.
Build with Ant
Run ant to build the examples/plugins/plugin-demo/built/plugin-demo.jar file
README: The plugin-demo directory is located in your ORDS product folder. This step assumes you have ORDS installed and configured for Standalone, Weblogic, or an Apache Tomcat server.
Next, “cd” to the plugin-demo directory. I am lazy, so I right-click on the folder and choose to open a “New Terminal at Folder.”
The official instructions state that the source files for this Java plugin are contained in this folder. You do not need to modify these files for this to work. They are shipped with every ORDS version, so they will always be there, even when you upgrade (have you upgraded to the latest ORDS?).
You are not losing your mind. The build literally takes about 5 seconds. You’ll see a printout like the image below. You only need to be concerned with the dist: row at this stage. That is your plugin, and it is ready to be deployed.
Jar configuration
Copy the plugin-demo.jar to the ORDS distribution lib/ext directory
Navigate back to the plugin-demo directory. While you’re there, you can expand the sub-directories. You will see the original src directory and the new built directory, with its sub-directories and files. Copy the plugin-demo.jarfile.
Return to your ORDS product folder and place the plugin-demo.jar file in the lib/ext directory. Just so we are clear, there is anotherlib directory nested in examples/plugins/lib. You DO NOT want to move the jar to that folder; you must go to the ords product folder/lib/ext directory.
Your lib/ext the directory should now contain the plugin-demo.jar file. Double check!
Starting ORDS
Start an ORDS instance with the ords serve command.
Okay, we are almost there. I have a couple of ORDS configurations here. One is for working in ORDS Standalone mode with a 23ai database via a Podman container (the Podman image can be found here), and another is a Customer-managed ORDS installation (identified by the moviestream23ai_medium database pool name) for working with my Autonomous database (in OCI).
ORDS customer-managed installation instructions can be found here.
Test the plugin
Invoke the servlet using the following URL pattern: http://server/ords/schema/demos/plugin?who=somebody.
For example: http://localhost:8080/ords/hr/demos/plugin?who=scott where ORDS is configured with a default pool and HR is an alias for a REST Enabled Schema in that database.
Now that ORDS is up and running, we can do some testing. In this first example, I challenged my creativity by using the name “Chris” for the test. Here, I’m issuing a request to the plugin. Of which it successfully returns the following message:
[ORDSREST-enabled schema] says hello to: [the name passed asaqueryparameterintheoriginalrequest].ORORDSTEST says hello to: Chris
Testing with the Podman container:
And with the Customer-managed ORDS with Autonomous database installation:
Success! I am me!
Next steps
You’re probably asking yourself, “Now what?” Well, here is what I would do:
Review the above steps again and on the docs for building this Java plugin.
Look at our support for JavaScript plugins and the extra configuration step if you use GraalVM for your JVM.
Start learning Java. The two resources I’ve been reading in my spare time:
Make your own plugin and share it with me so I can try it out 😬
Oracle CloudWorld 2024 updates
And finally, yes, we are fast approaching this year’s CloudWorld conference. If you didn’t see my last post on our teams’ activities, review it (and hopefully register for at least some of our sessions). Details are here. And that’s all for now!
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
In response to various support requests and internal feedback, we've expanded on the existing ORDS Status Codes (the current list can be found here)!
Why? The TL;DR is that in many cases, the error or exception caught was being "bucketed" into a category of codes, like a 400 Bad Request or a 500 Internal Service Error. While true, we can be more explicit with some of these error codes. Now, we just have to ask, at what level do we stop?!
Ahem, this sounds like our entire dev team is lazy; it’s quite the opposite. Let me illustrate what’s actually going on with an example from one of our related tickets.
Say a user receives a 503 Service Unavailable server error response. In their Java logs (trace files, access logs, etc.), they also observe an oracle.net.ns.NetException error. While classifying this as a 503 is accurate, we can do better. For instance, you may encounter an oracle.net.ns.NetException with any of the following ORA codes:
In this example, moving forward, instead of receiving a 503, you’ll now receive a 571 server error response.
We’ve done this for several other scenarios, too! Why keep everything generic, as a 503, when we can be more discrete and specific with the information provided? It makes no sense. So, by shifting some of these exceptions to unique response codes, we make it easier for you to identify what is happening.
What if everything was either 200, 400, or 500? Could you imagine?! It would be nearly impossible to quickly identify what is happening in your stack.
ORDS Central Configuration
ORDS now supports deployment in a Central Configuration type deployment.
This one is big. The short version is that we’ve made it so you can dynamically start up and shut down individual ORDS “nodes.” This requires three main pieces, but I’ll try to be brief.
This all assumes you’ve already configured ORDS globally (i.e., you have a global.xml file) as well as your database pools (i.e., the default.xml, database_pool_one.xml, database_pool_two.xml, etc. files). But the idea is such that you’ll have stored in a central Vault/Secrets storage/Key Store your:
ORDS global configuration – which will be in a JSON format (basically a JSON version of the global.xml file)
ORDS database pool configuration/s – the individual configuration files for your ORDS “nodes” (again, just JSON versions of your database-pool.xml files)
A mechanism for the ORDS_PUBLIC_USER to authenticate with the Vault or Keystore (this could also be something as simple as an ORDS webhook that has been protected with an OAuth2.0 client)
*And a conditional fourth item, the makestore or orapki utilities (for creating SSO Wallets).
One way to “do” this (we’ve tested this internally; we just can’t, and won’t endorse, a “one-size fits all” method) is to store your “secrets” in a vault-type service and retrieve them securely and dynamically.
NOTE: The vault contains "secrets," including your global configuration and database pool files. Each of those secrets might be behind an OAuth 2.0-protected endpoint/s.
Separately, and before starting up ORDS, you’d create an SSO Wallet with the credentials for the ORDS_PUBLIC_USER, its password, and additional hostname information (all can be found in our documentation). You’d also choose where to store that Wallet.
Then, before launching ORDS, you’d include two Java options:
Your wallet location (so when ORDS starts up, it is aware of the location of the Wallet and the credentials) and
The REST endpoint (the URI) for your global configuration
Authentication can take several forms here. You can use Basic Authentication (but…don’t, even though we support it, perhaps for testing, but please don’t use it for production), JWTs, or OAuth 2.0. Once the ORDS_PUBLIC_USER has authenticated with its credentials/token, ORDS can acquire its global configuration.
When that HTTP request comes across ORDS, the database pool name can be passed as a header value (we can also read the database pool name if it is appended to the beginning of the URL) and used as a “search” value to retrieve the relevant database_pool-config.json file.
That is ORDS Central Configuration in a nutshell. And since I lost you, I’ll have to write a follow-up blog on this. I don’t blame you; conceptually, it’s tough to envision, but it’s pretty simple in practice. The basic components are laid out in our documents.
OCI Monitoring of ORDS
ORDS now provides a create_alarms.sh script to create ORDS alarms using OCI Monitoring Services, which can be found in the [ords product folder]/examples/ords-metrics directory.
How shall I explain this without getting in trouble with our legal department…? This is being rolled out globally right now for the Oracle Autonomous database. Before this ORDS release, errors or exceptions would be caught and streamed to an OCI metrics dashboard. And you might have seen an ORDS-related exception with a 404 or a 503. Unfortunately, the root-cause analysis would have already been off to a bad start. Because most of these exceptions aren’t just 404s or 503s. There had yet to be a mechanism to make some of these exceptions more discrete. And that is where this create_alarms.sh script enters the chat.
If you review the now-included create_alarms.sh file, you can get an idea of what will now be streamed to your OCI Metrics/Monitoring Explorer. We’ve made it so the ORDS access logs and more appropriate response codes can be streamed to OCI. This makes root-cause analysis and troubleshooting far more straightforward. So if you elect to configure a customer-managed ORDS node, then be sure to take advantage of this new capability.
A new standalone configuration option
New standalone.access.log.retainDays configuration option for ORDS standalone.
Users can now customize the number of days before access log files are overwritten. The default is 90 days, but you can now select the exact number. This, along with numerous other standalone settings can be found in the Understanding Configurable Settings appendix of our ORDS Installation and Configuration Guide.
Java options in the ORDS CLI
Users may now include Java Options parameters when executing ORDS CLI commands.
For instance, a user may execute the following command:
In the above example, you’ve told ORDS to start up, and then, first thing, go to the target URL to retrieve ORDS’ global configuration settings.
You may wonder about JAVA_OPTIONS and JDK_JAVA_OPTIONS; how are those settings impacted? Well, here are some essential details:
ords --java-options only apply to the current ORDS execution.
In order of precedence, options will be picked up like this:
JAVA_OPTIONS are of the highest precedence >>> then >>> ords --java-options >>> then >>> JDK_JAVA_OPTIONS
INFO: Where conflicts arise, ORDS will pick up and use the left-most option.
Jetty 10.0.21
ORDS standalone updates the embedded Jetty Web Sever version 10.0.21.
There’s not much to say here, but if you’d like to learn more about Jetty, I’m including the Operations and Programming guides. When using ORDS in standalone mode, the idea is that Jetty is just there—and it just works.
We’ve architected Standalone mode so that any ORDS configuration can be achieved via the ORDS CLI or by directly manipulating the XML configuration files (while you can do this, you shouldn’t; you might mess up one of the properties in the files). Designing ORDS Standalone mode this way makes it so you don’t really need to do anything Jetty-related. I’m just including the docs if you want something to read on your lunch break 🤪.
Database Actions’ Data Pump
Data Pump allows you to delete jobs and their files from within the Database Actions Data Pump UI. And the Data Pump Import Wizard features some major upgrades and visual enhancements.
Users who have DBMS credentials to access Oracle Cloud Services (via the DBMS_CLOUD.CREATE_CREDENTIAL PL/SQL procedure) can now perform Data Pump Imports from OCI Buckets (previously, it was Resource Principals only). And we’ve added the following abilities/changes:
Buckets from any Compartment level are now accessible
Auto-generated Import Patterns for DMP Files are now included
Automatic mapping for Schemas and Tablespaces is now present
A new “Append Timestamp to Log and Job Names” toggle option has been added
NDJSON files
Users can now import Newline Delimited JSON (NDJSON) or .ndjson files into the SQL Worksheet.
This actually arose from a bug we encountered. After some investigation, the user attempted to import a “Newline Delimited” JSON document. I’m not familiar with this document type, but cursory research reveals the following:
There is currently no standard for transporting instances of JSON text within a stream protocol apart from [Websockets], which is unnecessarily complex for non-browser applications.
A common use case for NDJSON is delivering multiple instances of JSON text through streaming protocols like TCP or UNIX Pipes. It can also store semi-structured data.
If you’d like to learn more about this specification, I recommend visiting this site. It doesn’t seem affiliated with the project, but it is very informative compared to the official NDJSON GitHub page.
Are you using NDJSON now? Or did you just learn of its existence? Do you think you’d start using it, or do you have any use for it? Let me know. I’m curious about the potential applications.
New Walkthroughs (Tours) for Charts and Data Modeler
Updates to the Database Actions Launchpad.
We continue to refine the Launchpad, and these are but two more examples. The Data Modeler and Charts pages have Walkthrough Tours (and documentation too):
What are your thoughts on the new Launchpad? Do you use the “pin” feature? Is it intuitive? What else could we include or improve? Or is it perfect (it’s perfect, isn’t it? …I knew it!)
Java
ORDS requires Oracle Java or Oracle GraalVM Enterprise Edition 11, 17, or 21 to run ORDS 24.2.
If you intend to do anything GraphQL-related, use GraalVM 17. Instructions for downloading can be found here. Just make sure you set your JAVA_HOME to GraalVM 17 so that when ORDS starts up, it does so with GraalVM 17! At this time, ORDS GraphQL support only works with GraalVM 17.
I’m trying to be as succinct as possible. I just accomplished this but couldn’t find anything that explicitly walked me through how to do it. Here are some assumptions:
You have followed these instructions for Installing Flask on an Ubuntu Virtual Machine (VM) in Oracle Cloud Infrastructure1
You at least have a Free Tier account in OCI
You have an existing GitHub account
You’re already or presently signed into (via SSH presumably) your Linux Virtual Machine (like I am in the images you’ll soon see)
I suppose you don’t need anything related to Flask or Python; you need to follow the steps to the point where you need to git clone something in your Virtual Machine (aka your Compute Instance). ↩︎
REMINDER: You are signed in to a Linux machine! The commands you will follow (in GitHub or here) WILL NEED TO BE FOR LINUX!
What happened?
I am trying to clone an already existing repository in my Virtual Machine.
I received the following fatal: Authentication failed for... error when attempting to authenticate with GitHub from within my Virtual Machine with my GitHub username and password:
Epic failure when trying to sign in using your username and password.
Authenticating with SSH
Since the HTTPS method of authenticating doesn’t work, the next available option is to clone via SSH.
Even if you don’t know what this means, as long as you follow the Linux instructions in the GitHub documentation, you’ll be fine. First, you’ll want to check to see if you have existing SSH keys in your Linux VM (spoiler: you won’t, at least not for GitHub).
Then, add your PRIVATE key (NOT your public key) to your SSH agent (don’t even ask me what the hell an SSH agent is, isn’t, or does). I presume the SSH agent assists in authentication.
Finally, add your Public key to GitHub. They’ve provided instructions here, but I have included some screenshots of what this looks like when done for your OCI VM.
Navigate to settingsNext, click SSH and GPG keysAdd your public SSH key with your email, too!
Once you’ve saved it, you can use it git pull from within your OCI VM. As can be seen here:
And that’s all for now. I mostly wrote this blog out of sheer frustration. Please share it with your friends and bookmark it for future use!
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
You can visit it; we use it for one of our LiveLabs (this one), so I’m sure it will stay live for a while 😘. Once there, you’ll see all the available files in that bucket:
A mix of CSV and JSON files.
FYI: A bucket is one of the three core components of Oracle Cloud's Object Storage. The other two are Objects and Namespaces (more details at Object Storage FAQs).
In this case, there were more than just .CSV files; the script I created will also download those. But, of course, your specific situation may vary. Onto the Python script!
The code
Allow me to first address a few considerations and limitations:
There is probably a way to create a function that accepts a parameter to make this even more elegant and compact. I’m not “there” yet. So, if you have a better solution, please comment, and I’ll amend my code!
I still haven’t learned error and exception handling in Python. So anytime you see me “code in” print(...) that is basically me attempting to check to see if what I think will happen actually happens.
I’m not sure if my range() and len() practices are appropriate, but it works, so I’m going with it!
Don’t ask me how I even found out about the webbrowser library. I must have found it on a forum, or StackOverflow (hate that I can’t attribute credit to my savior).
The code for real
import requestsimport jsonimport webbrowserurl ='https://objectstorage.us-phoenix-1.oraclecloud.com/n/dwcsprod/b/moviestream_data_load_workshop_20210709/o/'r = requests.get(url)data = r.json()# print(data)newlist = []for name in data['objects']: newlist.append((name['name']))# print(newlist)length =len(newlist)newurl = []for i inrange(length): newurl = url + newlist[i] webbrowser.open(newurl, new=0, autoraise=True)
As you can see, with spacing, it’s less than 25 lines of code. I’m using a total of three libraries: requests, json, and webbrowser. The first two libraries you are probably familiar with, the third maybe not so much. Webbrowser() is great, when used correctly, the code will automatically open a new browser tab and execute whatever it is told to do (in this case, go to a URL and issue a GET request). Make sense?
Not to worry, I’ll break this down into smaller blocks, to better understand what is happening.
I’ve imported the three relevant libraries (remember if you don’t have these libraries, you can use pip and perform a pip install to grab them). And I assign URL equal to the target bucket. And from here I perform my firstGET request. I do this for two main reasons:
to remind me of the structure of the JSON, and
because I am about to loop through all the available files in this bucket (so I need to capture them somehow)
Since I’ve essentially assigned the value of r.json() equal to data, I can now print(data) to visually inspect the….um….data. After executing lines 1-10, I can see the corresponding output in my terminal:
Now that I know this is working (because of my manual check), I can proceed to the next step.
Lines 12-16
Executing this code…
import requestsimport jsonimport webbrowserurl ='https://objectstorage.us-phoenix-1.oraclecloud.com/n/dwcsprod/b/moviestream_data_load_workshop_20210709/o/'r = requests.get(url)data = r.json()print(data)newlist = []for name in data['objects']: newlist.append((name['name']))print(newlist)
Will yield a new list:
But how you ask? Good question, lots of trial and error. In short, I created an empty list, named newList. And later populated it with “stuff”. In normal people speak, lines 13-14 say the following,
“There is an item, actually many items in the JSON payload we received from our GET request. For simplicity, lets call each of those items name, since ultimately what we want are file names. And we’ll use bracket notation to ‘drill’ down into more specific sections of the original JSON payload. And since we want just the file name (values), we want all the stuff that is in objects (if that doesn’t make sense review the lines 1-10 section again). We are then going to add to newListonly the names (aka values) of the files. And these lines of code are what help us to populate the newList list.
Maybe by this stage, you’ve caught on to what I’m trying to do. If not, I won’t spoil it for you 🙃.
Lines 18-19
And if you don’t quite “get it” yet, not to worry, it’ll click here in a second. Next, I need to see how many items are in this newlist I just created. Here is the code that is responsible for making that happen:
import requestsimport jsonimport webbrowserurl ='https://objectstorage.us-phoenix-1.oraclecloud.com/n/dwcsprod/b/moviestream_data_load_workshop_20210709/o/'r = requests.get(url)data = r.json()# print(data)newlist = []for name in data['objects']: newlist.append((name['name']))# print(newlist)length =len(newlist)print(length)
Here is the result in my terminal:
It looks like I have 85 elements, things, stuffs in my list. I’ll use that knowledge for the final steps in my script.
But why does that show as 84, and not as 85? Because Python uses zero-based numbering. So number 1 is actually number 0, and so forth and so on, etc...
Line 21, you’ve seen this before (like 2 mins ago).
However, lines 23-25 are new. If you recall my “considerations and limitations” section. I bet there is a better way to do this (or all of this actually), but this worked for me, so I’ll keep it. In short, we know we have 85 “iterables” (that’s the for i seen in line 23) and the range(length) portion simply says, “Begin at 0 and then keep going till you reach the entire length of…length()…which in this case is 85).”
Next, I take the original URL (from line 5 in the code) and add each element of the newList to the end, making a newurl. From there, we open a web browser (new tabs actually) and visit that new amended URL aka newurl (I think more appropriately this is a URI, no?).
And for the visual, a video of the entire process (no audio):
And finally, for my own gratification, I’ll print out the newurl list just so I can marvel at my work. The new URLs:
Now that I have these individual files, I can simply drag and drop them into my tables (in Database Actions obviously).
As always, this and other code can be found in my blog repo. I hope this comes in handy someday! That’s all for now 😀!
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
In this example, we call this collection Movie_Collection.
-- create and load movie json collection from a public bucket on object storage
begin dbms_cloud.copy_collection ( collection_name => 'MOVIE_COLLECTION',file_uri_list => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/moviestream_gold/o/movie/movies.json', format => '{ignoreblanklines:true}'); end; /
👆🏻 This is the code I used to create, copy, and ingest this collection into my Autonomous Database.
README: As far as I know, the link above (the one in the code example) will remain stable for the foreseeable future. I'm pretty sure we use it in one of many of our LiveLabs. A lot of what I'm covering here is actually in Task 7 of this LiveLab.
Notice the “PL/SQL procedure successfully completed.” message.
Create a view
With that JSON collection in place, I can then create (aka I’ll continue stealing this code from the same LiveLab) a View of it using the following SQL code:
/* Create a view over the collection to make queries easy */
create or replace view movie as select json_value(json_document, '$.movie_id' returning number) as movie_id, json_value(json_document, '$.title') as title, json_value(json_document, '$.budget' returning number) as budget, json_value(json_document, '$.list_price' returning number) as list_price, json_value(json_document, '$.gross' returning number) as gross, json_query(json_document, '$.genre' returning varchar2(400)) as genre, json_value(json_document, '$.sku' returning varchar2(30)) as sku, json_value(json_document, '$.year' returning number) as year, json_value(json_document, '$.opening_date' returning date) as opening_date, json_value(json_document, '$.views' returning number) as views, json_query(json_document, '$.cast' returning varchar2(4000)) as cast, json_query(json_document, '$.crew' returning varchar2(4000)) as crew, json_query(json_document, '$.studio' returning varchar2(4000)) as studio, json_value(json_document, '$.main_subject' returning varchar2(400)) as main_subject, json_query(json_document, '$.awards' returning varchar2(4000)) as awards, json_query(json_document, '$.nominations' returning varchar2(4000)) as nominations, json_value(json_document, '$.runtime' returning number) as runtime, json_value(json_document, '$.summary' returning varchar2(10000)) as summary from movie_collection ;
Here is what the code looks like in the SQL Worksheet (a part of Database Actions).
Accomplished in the SQL Worksheet.
With that View created, you could go one step further and query with even more specific SQL. In this case, I’ll query the View but exclude any entries where a movie cast does not exist:
Select title, year, gross, cast from movie Where cast is not null Order By 3 DESC nulls last Fetch first 10 rows only;
Here is the SQL, with the Script Output below:
Notice the output of the executed SQL.
ORDSify it®
With ORDS, we can REST-enable pretty much any database object we want.
I have objects, Greg. Can you REST-enable me?
But after spending a few minutes with this collection, I found the MOVIE View to be the easiest, most sensible object to highlight. It’s a straightforward process, with primarily right-mouse clicks.
From the Navigator Panel, select Views from the list of available database objects.
From the Navigator Tab, select Views from the drop-down menu.
Then, right-click on the Movie View, and select REST, then Enable.
Right-click on the Movie View and select REST then Enable.
A slider will appear, for this example, I’ll keep everything default and click Enable (no authentication either, I’m being lazy).
This slider will appear; since you are auto-REST enabling, you can accept the default settings and click Enable.
A Confirmation notification will appear in the upper right-hand corner of the browser 👍🏻.
You’ll know it worked because you’ll see this confirmation message appear.
Navigate back to the Movie View, and select the cURL command option. Twasn’t there before, tis now!
Go back to the Movie view and select the [new] cURL command option.
Select the GET ALL endpoint, and copy the URI. JUST the URI portion!
From the list of available auto-magically created REST endpoints, copy the GET ALL endpoint.
Open a new browser tab, or window, and navigate to the URI. You’ll see everything in the Movie View now!
That endpoint (URI) in the browser looks like this 👆🏻
Why a view?
Yeah, good question. There is probably a performance improvement with using Views. Based on what I’m finding, I can’t definitively say, but they require zero storage, so that’s at least a savings on some resource somewhere. Additionally, I think the customization is pretty compelling, too. What do I mean by that? Well, allow me to elucidate:
Views can provide a different representation (such as subsets or supersets) of the data that resides within other tables and views. Views are very powerful because they allow you to tailor the presentation of data to different types of users.
In this case, the Movie View returns everything found in the collection. I can subset this even further though; by taking that SQL query and REST-enabling it, too. I will, but in a future post.
For now, I’ve left you with an easy way to REST-enable a View (In this case, based on a JSON Collection) that resides in your Autonomous Database.
If you want to try the LiveLab (which you should, as it’s easy and VERY informative), go here. You’ll need an Always Free OCI account, too (so you can provision an Autonomous Database). You can sign up here.
Oh, and we are entering into the season of Cloud World 2024, so mark your calendars 🤪!
That’s all for now 😘.
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
BREAKING NEWS!! APEX packages with ORDS and SQLcl now available in Oracle Linux YUM servers and through ULN
The deets
Here is what you need to know. We are releasing three new APEX (a.k.a. “The Artist formerly known as Oracle Application Express”) packages for the following repositories:
ol7_oci_included
ol8_oci_included
ol9_oci_included
NoARCH RPMs
One more update! These RPMs will be packaged as NoArch. This means you will be free to install these on any CPU architecture (Intel x86_64, AMD, and ARM). As a reminder, OCI Always Free tenancy Compute Instances are available with:
Micro instances (I’m using a VM.Standard.E2.1.Micro; an AMD processor)
TIP: These will all be available through both the publicly available Oracle YUM servers andthe Unbreakable Linux Network (ULN) channels.
Fortunately, I was provided advanced copies of the brand-new RPM files that will be included in their respective oci_included repositories. You’ll soon have access to no less than three flavors of APEX (and their dependencies). YUM!
You’ll soon see the following packages:
Oracle Application Express 23.1
NOTE: In this write-up, and the images below, you'll see me reference this as apex-core.
The Oracle Application Express package contains everything you need to install APEX in your database. We also package it with the SQLcl package to simplify installation.
Oracle Application Express Images 23.1
NOTE: In this write-up, and the images below, you'll see me reference this as apex-images.
This Oracle Application Express Images package includes your APEX images (i.e., you may be familiar with this as these are symlinked to your /i/ virtual directory for Oracle APEX images). This package also contains the ORDS package, making it easier to run the APEX image of your choice.
This package will also automatically add a symbolic link (aka symlink) from your APEX images files to your default ORDS doc_root path.
Oracle Application Express Patch Set Bundle 23.1.3
NOTE: In this write-up, and the images below, you'll see me reference this as apex-patch.
The Oracle Application Express Patch Set Bundle provides a means for you to take the latest APEX patch sets, compare them against your existing base APEX images, overwrite those base files with the latest changes, and then place them into their own /opt/oracle/apex/[latest patch]/images directory. Oh, and you’ll also retain the APEX base images in their own /opt/oracle/apex/[apex base]/images directory too!
And when you’re ready, you can update APEX in your database with the latest patches. The package even symlinks the newest patch set images to your APEX installation’s virtual image directory (i.e., the /i/ directory). And finally, we automatically symlink (as mentioned in the Oracle Application Express Images 23.1 section) this folder to your default ORDS doc_root path.
REMEMBER: If you create a Compute Instance in OCI, these repositories will automatically be accessible to you (if you aren't though, check this resource out to get set up)!
A week ago, I had limited (i.e., zero) experience with the Linux package managers Yellowdog Updater Modified (YUM) and Dandified YUM (DNF). But since then, I’ve tinkered around with Oracle Linux (OL) quite a bit. I even successfully installed Oracle Linux 8 (OL8) on an old Raspberry PI 3 Model B V1.2 (which, if you can believe it, was first released in 2016)!
PAUSE: If you want to see a tutorial of I how I did this, let me know in the comments, or @ me on Twitter/X or Threads.
Exploring OL8 and OCI Compute Instances
If you have an OCI Always Free account, you too can get acquainted with Oracle Linux 8 (OL8), as that is provided when creating a new Compute Instance in your OCI tenancy.
After creating that Instance, you can SSH into it and check out what Oracle Linux ships with. Once in, use one of these commands (depending on the version):
# Command for Oracle Linux 7:
yum list *release-el7
# Command for Oracle Linux 8:
dnf list *release-el8
# Command for Oracle Linux 9:
dnf list *release-el9
And here I am checking the packages and repositories (with the dnf list *release-ol8 command) in my Compute Instance:
Since I first created this Compute Instance, I’ve been tinkering with DNF and the repositories, so your output might look different. But at a minimum, you can expect to see the ol8_oci_included and ol8_baseos_latest repositories.
Oracle Database Tools via YUM or DNF
NOTE: This next section WILL NOT apply if you are working with the Oracle Linux that is provided in your OCI Compute Instance!!!
You won’t find a direct link to our Database Tools repository if you do this independently (i.e., not creating an OCI Compute Instance as I did). That is an easy fix; you can manually configure the repositories.
NOTE: If you are doing this through OCI (as in with a newly-created Compute Instance), you won't need to configure this, it will be included in the ol8_oci_included repository (this applies to OL7 and OL9 too)!
But for manual configuration, here are the direct links for the available Oracle Linux versions (you’ll want to use whichever one corresponds to your Linux version):
Oracle Linux 7 http://yum.oracle.com/repo/OracleLinux/OL7/oracle/software/x86_64
Oracle Linux 8 http://yum.oracle.com/repo/OracleLinux/OL8/oracle/software/x86_64
You can manually configure the repositories like this:
You can then inspect the packages in this repository with the following command:
dnf repository-packages [the repo id] info --all
I can now download our ORDS and SQLcl packages, our two must-haves:
In the above images, you can see these packages are being installed from the ol8_oci_included repository. This is because Compute Instances in OCI automatically have access to the Unbreakable Linux Network (ULN); you can read all about it in the OCI documentation found here.
NOTE: Once again, taking the extra step to configure this additional repository is unnecessary; I'm only demonstrating how to do it if you are working with something other than an OCI Compute Instance.
The big reveal/super secret surprise
If you’ve been working with Linux (and specifically Oracle Linux), then none of this will be much of a surprise to you. And you probably (definitely) know more about this than I do. But what youDON’T know is that we are releasing three new APEX packages for the ol7_oci_included, ol8_oci_included, and ol9_oci_included repositories.
Since I know a guy (thanks, Adrian 🤫), I was provided advanced copies of the brand-new RPM files that will be included in the respective oci_included repositories. You’ll soon have access to three flavors of APEX (and their dependencies):
Oracle Application Express 23.1
The Oracle Application Express package contains everything you need to install APEX in your database. We also package it with the SQLcl package to simplify installation.
Oracle Application Express Images 23.1
The Oracle Application Express Images package includes your APEX images (i.e., you may be familiar with this as these are symlinked to your /i/ virtual directory). This package also contains the ORDS package, making it easier to run the APEX image of your choice.
It will also automatically add a symbolic link (aka symlink) from your APEX image files to your default ORDS doc_root path.
Oracle Application Express Patch Set Bundle 23.1.3
The Oracle Application Express Patch Set Bundle provides a means for you to take the latest APEX patch sets, compare them against your existing base APEX images, overwrite those base files with the latest changes, and then place them into their own /opt/oracle/apex/[latest patch]/images directory. Oh, and you’ll also retain the APEX base images in their own /opt/oracle/apex/[apex base]/images directory too!
And when you’re ready, you can update APEX in your database with the latest patches. The package even symlinks the newest patch set images to your APEX installation’s virtual image directory (i.e., the /i/ directory). And finally, we automatically symlink (as mentioned in the Oracle Application Express Images 23.1 section) this folder to your default ORDS doc_root path.
REMEMBER: If you create a Compute Instance in OCI, these repositories will automatically be accessible to you (if you aren't though, check this resource out to get set up)!
That was a lot of words. But in practice, the following images illustrate what to expect when you issue the dnf install command (for the APEX-CORE or APEX-IMAGES packages).
APEX-CORE
REMINDER: The official package name will be Oracle Application Express 23.1.
APEX-CORE install via a local RPMCompleting the APEX-CORE package installation, revealing helpful installation information
APEX-IMAGES
REMINDER: The official package name will be Oracle Application Express Images 23.1.
Alternatively, you may also decide to install the APEX-IMAGES packages (if you intend on using many versions of APEX along with its ORDS companion):
APEX-IMAGES install via a local RPMCompleting the APEX-IMAGES package installation, revealing helpful installation information
Once these packages are available in the ol7_oci_included, ol8_oci_included, and ol9_oci_included repositories, install commands become much more straightforward. You won’t have to bother with the individual RPM files; you can issue either the yum install or dnf install commands.
NOTE: We switched to DNF for OL8 and later but the commands are symlink'd so I believe either will work. You can read about that here.
The only thing you have to do now is decide which packages you’ll want to install!
Check for updates
This last section is just a reminder. If you haven’t done so yet, check for updates to all your repositories (namely the ol7_oci_included, ol8_oci_included, and ol9_oci_included repositories).
TIP: You can issue the yum --help or dnf --help command to refresh your memory on which "update" command to use.
But once again, I suspect you already know how to do this if you have more than a week’s experience with Oracle Linux, YUM, or DNF. Be on the lookout for updates to the Oracle Linux release notes too!
What is new in Oracle REST Data Services 24.2?
