EXTRA EXTRA! ORDS now supports JSON Web Tokens (JWTs). You can find most of the details in the OAuth PL/SQL Package Reference chapter of the ORDS Developer's Guide.
Why is this in the OAuth chapter?
Apparently, JWTs fall under the purview of the OAuth Working Group, a section of the Internet Engineering Task Force (IETF). Here is a draft of the JWT specification I found. This makes sense; I’ve since relearned that OAuth = Open Authorization 🤦🏻.
ORDS JWT OAUTH parameters
You’ll notice two new procedures in that package: OAUTH.CREATE_JWT_PROFILE and OAUTH.DELETE_JWT_PROFILE. After getting acquainted with them, I wanted to highlight three parameters of the OAUTH.CREATE_JWT_PROFILE procedure:
p_issuer
p_audience
p_jwk_url
Your JWT issuer (e.g., Microsoft Entra or Oracle Identity Cloud Service) will provide you with these three values required for the OAUTH.CREATE_JWT_PROFILE procedure.
However, they might be referred to by slightly different names. I first noticed this as I set up Microsoft Entra to work with ORDS (below are images taken from a slide deck I’m working on).
In fact, I could use some help finding [what we refer to as] the p_jwk_url in the Microsoft Entra dashboard. I'm sure it's there, but I couldn't find it. In case you need it, this is the link that I used.
So, the names are all slightly different. But if I can figure it out, you definitely can.
Decoding JWTs
Once you acquire your JWT, you’ll need a way to decode it so you can use it for testing or development, and you’ll need some of the information for the ORDS profile procedure! Several resources exist, but here is a [non-exhaustive] list I put together:
To illustrate how this function works, I took some “boilerplate” HTML from the Bootstrap docs page and spun up a LiveServer in VS Code. I’m also “inspecting” the results from the console.log();. I’m not really sure how far I’ll take this, especially now that I’ve learned about all the existing libraries. But feel free to remix this code!
Thank you for your time 🙇🏻♂️
And that’s all I have to share for today!
If you still need to download and upgrade to the latest ORDS, you can find the.zip file here. Be sure to explore GraalVM, too; you can “unlock” some newer ORDS features by using GraalVM as your primary Java Developer Kit (JDK)!
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
If you are coming from the previous related post, then you’ll recall I used the following SQL query:
Remember, this SQL is querying the Movie View.
My next step is to take this SQL and bring it to the REST Workshop, where I’ll turn it into an API.
REST Workshop
There are several ways you can navigate to the REST Workshop. Typically, I return to the Database Actions LaunchPad. From there, I select REST.
The Handler code
I've already created my Resource Module, Template, and Handler. I kept everything default, with no authentication enabled.
The only thing I changed was the SQL query. I removed the final line, fetching the first 10 only. I want to be able to control the pagination of the API. If I were to keep that last line, this eventual endpoint would always only return the first 10 rows. And what if I want the next ten rows thereafter? Well, if I hard-code this, then I can’t really make that work. So, I chose to leave it open-ended.
Technically, it is NOT open-ended because I retained the default pagination of 25. But, by removing that fetch first 10 rows condition, I can now fetch ALL rows that fit those parameters (in increments of 25).
If I visit this new endpoint, it will appear like this:
And if I collapse the items, you’ll see something that is EXTREMELY confusing. If I removed that fetch first 10 rows condition in the original SQL query, then why do we see a limit and offset of 10?
The answer is because I actually set the Items Per Page equal to 10 (in the Resource Handler). This is the REST equivalent of a dirty joke. Consider yourself roasted…
JavaScript
With that endpoint live, I can take the API and drop it into some sample JavaScript and HTML code.
JavaScript and HTML
I learned a great deal about this JavaScript by reviewing this YouTube video. That is where I learned how to map through the items of my ORDS payload. And there was a refresher on JavaScript string interpolation (with template literals) too!
PAUSE: Don't be too intimidated by string interpolation and template literals! Read the link I included, and take your time. If you are coming from Python, its similar to Jinja (when using Flask) and f-string literals 🙃.
You can see that I’m using the map() constructor to iterate through all the data in my JSON payload. Remember, this was the payload in the items portion of my endpoint!
I believe the item in list.map((item) is a reference to an individual item inline 4’s data.items. The reason why I think this is because if I change the items in lines 7-10 in my JavaScript to something random, like the name bobby, things start to break:
However, if I change everything back to item, and start the live server in VS Code, I’ll be met with the following rendering:
That’s it, though. Combining the ORDS API, the Fetch API, simple JavaScript, and HTML will allow you to create this straightforward web page.
Reviewing Inspector, Console, Network
I also have a few more screenshots, one each for the HTML Inspector, Console Log, and Client/Server Network. All of these show what is happening under the covers but in different contexts.
Inspector
In the Inspector, you can see how the JavaScript map() constructor plus the document.querySelector() in line 18 of the JavaScript code work in tandem with line 12 of the HTML script to display contents on the page:
Console
Here, you can see the items in the Console. This is because we added console.log(item)in line 19 of the JavaScript code.
Network
Finally, you can see the 200 GET request from our ORDS API. Then, on the far right of the screen, you can see the JSON payload coming from that same ORDS endpoint.
Admittedly, the way the “Cast” is displayed is not correct. That is yet another array of cast members. And I’ve yet to learn how to structure that correctly. So, if you are reading this, and you know, let me know!
I found JavaScript and HTML code here and here and “remixed” it to work with one of my sample ORDS APIs. Here is the result:
ORDS + JavaScript + Fetch API + HTML
Impressive, no? Care to try it out? Read on friend!
References
I’ll front load with all the necessary stuff. That way, you can bounce if you don’t feel like reading. You’ll get the gist if you follow along with what I’ve provided.
Much of what I learned came from the MDN Web Docs site. I would get acquainted with the following pieces of code (or at least have them handy) since they heavily influenced me (a.k.a. plagiarized).
MDN Web Docs
I either used or referenced these files in my version of the code. They are all available in the two links I mentioned above, but I’m adding them here for convenience (in case you need to leave or want to review while on this page).
👈🏼 click these to reveal the contents
No! Not this one, dummy. This one is just an example…duh 🤕!
response: json() method
const myList = document.querySelector("ul");
const myRequest = new Request("products.json");
fetch(myRequest)
.then((response) => response.json())
.then((data) => {
for (const product of data.products) {
const listItem = document.createElement("li");
listItem.appendChild(document.createElement("strong")).textContent =
product.Name;
listItem.append(` can be found in ${product.Location}. Cost: `);
listItem.appendChild(document.createElement("strong")).textContent =
`£${product.Price}`;
myList.appendChild(listItem);
}
})
.catch(console.error);
Check out my remixed JavaScript code, DDL and ORDS module definitions (for this example) on my GitHub blog repo. I'll also include select items below.
Here are a few things to point out:
In line 16 of myindex.html code, I referenced the JavaScript code (script.js) separately. This approach achieves the same effect as embedding the JavaScript directly into the HTML file (as seen in the MDN’s version of the index.html file).
The script.js contains the Fetch API and the JavaScript concept of “promises.” The following were super helpful for me. Maybe the will be for you too:
The JSON file contains an example of what an ORDS GET request response looks like (if viewing in the browser). The structure is nearly identical if you compare it to the MDN JSON file.
This means you can take their HTML and JavaScript code and populate it with an ORDS endpoint and [subsequent] response data (i.e., the stuff you see in this localhost.json file).
const ordsApi = "http://localhost:8080/ords/ordstest/api/example/api/";
// This next one is just an example using query parameters. I just chose a random employee number:
// const filteredOrdsApi = 'http://localhost:8080/ords/ordstest/api/example/api/?q={"empno":"7876"}';
const myList = document.querySelector("ul");
fetch(ordsApi).then((response) => {
if (!response.ok) {
throw new Error(`HTTP error, status = ${response.status}`);
}
return response.json();
}).then((data) => {
for (const item of data.items) {``
const listItem = document.createElement("p");
const empnoElement = document.createElement("strong");
empnoElement.textContent = item.empno;
const enameElement = document.createElement("strong");
enameElement.textContent = `${item.ename}`;
const dnameElement = document.createElement("strong");
dnameElement.textContent = `${item.dname}`;
const jobElement = document.createElement("strong");
jobElement.textContent = `${item.job}`;
listItem.append(`Employee number `, empnoElement, ` was hired on ${item.hiredate}.`, ` Their last name is `, enameElement, ` and they work as a `, jobElement, ` in the `, dnameElement, ` department.`
);
myList.appendChild(listItem);
}
})
.catch((error) => {
const p = document.createElement("p");
p.appendChild(document.createTextNode(`Error: ${error.message}`));
document.body.insertBefore(p, myList);
});
I’m also using the Live Server extension for VS Code. If you don’t have it, you’ll need it to run the code I’ve provided. You can download it from the VS Code Marketplace here.
You’ll want Live Server for this one!
How I met your Mothra 👾
Where to start? From the beginning, right? What you see below are two JSON files. On the left, from ORDS. On the right, from the MDN Web Docs sample code (direct link to that file).
Comparing JSÒN
ORDS on the left, MDN on the right.
They are nearly identical. They are both a JSON object {} comprised of key: value pairs, where the first key’s value is an array []. In both files, this array has moreobjects {}. And each of those objects has its ownkey: value pairs…marone 🤌🏼!
I mention all this because this makes the existing code easy to work with. Which you’ll see shortly.
Comparing JavaScript
Next is the JavaScript code; I’ll compare both my version and the MDN Web Docs version.
ORDS on the left; can you spot the differences?
You’ll notice that a lot of the code is quite similar. I kept it this way, so I wouldn’t unintentionally break anything. The main differences in my code are the:
const ordsAPI on Line 1 (as opposed to referencing a JSON file).
Naming conventions in lines 14-27.
listItem.append(); on line 29 is heavily remixed (I did this so I could create individual lines for each entry).
Templating in my code (i.e., wherever you see the little ``` marks; they allow you to embed text directly into the HTML) I use A LOT more of it!
About the ORDS JSON Object
If you were to navigate to your ORDS endpoint, it would look like the images below. I’m including them for a couple of reasons:
You can see those key: value pairs in a different presentation.
These images help connect what is coming through in that GET request and what you see in the JavaScript code.
The items key with its value (an array).Remember the other key: value pairs, too!
Reviewing the HTML
Assuming you’ve started up Live Server (along with setting up your environment to mimic my own), you’ll immediately see this beauty of a web page. This image alone doesn’t tell a complete story, though.
Review line 29 in the JavaScript code; it’ll help to “connect the dots.”
However, when you open up the developer tools in your browser, you’ll see what is happening under the covers.
Live Server starts up, sees the index.html file, and “serves” it up.
In that HTML file is a reference to script.js; the JavaScript is run.
The JavaScript composes a list and then appends all the data you see here (on screen):
With developer tools open, you can see the HTML. This HTML should look similar to lines 12-27 of the JavaScript code.
Summary
After writing this up, I’m realizing this clearly needs to be a video. But if you get it, great! Otherwise, stay tuned!
There isn’t anything ground-breaking here. I’m highlighting an example of manipulating existing ORDS JSON objects (with the Fetch API) because I hadn’t seen anything quite like what I am presenting here.
Also, the web page that I’m showing is very, very basic. I’m neither a UX nor UI designer, so this is what you get, folks!
The main point is that the ORDS APIs are effortless to work with if you have a fundamental understanding of manipulating JSON objects using JavaScript. They are no different than what you see out in the wild.
Some follow-up
I want to take this and add some React to it. And I’d also like to add authentication (Basic, OAuth 2.0, and Java Web Tokens). But baby steps.
Okay, that’s all for now, folks, Sayonara!
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
