Author: Chris

ORDS 24.3 Release Highlights

Overview

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 configurations¹:

Oracle Database 23ai + ORDS 24.3

Oracle Database 23ai + ORDS 24.2

Oracle DB 21c Enterprise Edition + ORDS 24.3

¹Thank 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:
- $external
- $lookup
- $sample, and
- $lookup (see docs for details)
Users may now specify a JSON Schema validator when creating a collection
You may now create MongoDB API connections without TLS connections (Oracle Database 19c and later) can now be made.
Users can now set the createIndexes’ “online” option to TRUE to allow DML operations at index creation time.

To-Do: More details will come from the MongoDB API team as they publish new content.

In the meantime, here are some resources I found:

MongoDB API article from Julian
Medium arti c le from Hermann
Oracle blogs from Hermann:
- Article one
- Article two

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
- Officially introduced documentation for deploying ORDS using a central configuration server (introduced in the last release notes blog). Docs here.
Appendix D ORDS Central Configuration OpenAPI
- 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).
- OpenAPI spec doc here.

Java notes

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 TWO Oracle 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!

October 1, 2024

How to kill an ORDS process

How do I “kill” an ORDS process?

FYI: This was all done on a MacBook, so...this may differ on a PC or Linux machine. Apologies m'Lord...

UPDATE: Here are some helpful references for Windows/PC and macOS/Linux, too!

Since I’ve written this article, I’ve done some more digging. Some version of this will go into our documentation. I’m just not sure how much! But here are some of my findings:

“…searching for an ORDS and JVM Process ID.” Can be done with:

macOS/Linux	`ps`
Windows: Command Prompt	`tasklist`
Windows: PowerShell	`Get-Process`
macOS	Activity Monitor (application)
Windows	Task Manager (application)

“…to end processes.” Try these:

macOS/Linux	kill [ ORDS PID] [JVM PID]
Command Prompt	taskkill /PID PID /PID [ORDS PID] /PID [JVM PID] /F
Powershell	Stop-Process -ID [ORDS PID],[JVM PID] -Force
macOS Activity Monitor	End a process manually (with the mouse)
Windows Task Manager	End a process manually (with mouse)

References:

macOS/Linux:

PowerShell details:

Command prompt:

Here are the options I’ve found to “kill” an ORDS process:

Use the kill command followed by the ORDS PID (i.e., Process ID) and the related JVM (i.e., Java Virtual Machine) PID
Press the Control + C keys in the same terminal session that ORDS is running in (assuming it remains uninterrupted)

It looks like we’ve included in our docs how to start ORDS (with the ords serve command), but we leave it up to you to figure out how to close down/kill/terminate an ORDS process.

FYI: As I'm writing this PSA, I'm also filing a ticket to update our docs too 🤓!

Some observations

First approach

The Control + C key option is the simplest option. But this might be the most impractical. If you start ORDS in Standalone mode (using the embedded Jetty server), then you might have ORDS running in its own terminal tab, kind of like this:

Assuming this is the case, while ORDS is running in a separate tab, you can issue the ps command to review a list of the running processes:

Afterward, you can return to where ORDS is running and press and hold the Control key followed by the C key. You’ll see a new shell prompt appear:

There isn’t any real feedback (maybe we should change that). But if you issue the ps If you run the command in a new tab, you’ll see that both the ORDS and Java processes have been “killed.” This signifies that ORDS and the JVM are dead—RIP 🪦.

Second approach

If you exit out of a terminal session but ORDS is still running (which, in practice, is entirely possible), you’ll have to search for the appropriate PIDs. The easiest way I’ve found is to issue the ps command (like you saw before) and then issue the kill command plus the relevant PIDs.

I’m unsure if you need to “kill” the Java process and ORDS. I assume that you do. The only basis I have for this assumption is that when you use the first approach (see above), both the JVM and ORDS are killed. So, I’m attempting to mimic what automatically happens when using the Control + C option.

Issuing the kill command results in the following message (which you’ll see, assuming the original ORDS terminal window is still viewable):

And if you reissue the ps command, you’ll see both the ORDS and JVM PIDs have disappeared:

Summary

Since I’ve only tested this in ORDS Standalone mode, I can’t comment on Apache Tomcat or Weblogic. I’m assuming they both function very similarly. But if you are using ORDS as both an admin (to get it up and running) and a developer, then some of these commands (that we take for granted) may not be obvious to you. So, as rudimentary and fundamental as this article appears, I hope it helps at least one person early on in their database or ORDS journey.

Call to Action

And if you haven’t seen it by now (and why should you? I haven’t plugged it yet), we’ve made a YouTube playlist to accompany one of our more popular ORDS LiveLabs. You can find the LiveLab here.

And here’s the complete playlist:

Enjoy 🤗

That’s all for now, folks!

Follow

And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!

September 23, 2024

Extending ORDS with a Java plugin
About this article

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:
1. Change the directory to examples/plugins/plugin-demo
2. Run ant to build the examples/plugins/plugin-demo/built/plugin-demo.jar file
3. Copy the plugin-demo.jar to the ORDS distribution lib/ext directory and start an ORDS instance.
4. 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.

See Also:

Route Patterns Specification

Getting Started Guide

Developer Guide
Chapter 11.1 Steps, expanded

Locate the directory

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:
1. You’re in your Terminal, Shell, or Command Prompt.
2. You have issued the cd command, so you are in the /plugin-demo directory, and
3. 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 another lib 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:
```
[ORDS REST-enabled schema] says hello to: [the name passed as a query parameter in the original request].

OR

ORDSTEST 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:
1. Review the above steps again and on the docs for building this Java plugin.
2. Look at our support for JavaScript plugins and the extra configuration step if you use GraalVM for your JVM.
3. Start learning Java. The two resources I’ve been reading in my spare time:
  - 2 Day + Java Developer’s Guide
  - Introduction To Programming In Java — part of MIT’s OpenCourseWare
4. Check out Peter’s ORDS plugin example too
5. 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!
- YouTube
- LinkedIn
- Instagram
- TikTok
- GitHub
- Mail
- Facebook
- Medium
August 27, 2024
See you at Oracle Cloud World 2024
Are you going?

If so, great! I’ve taken the liberty of hand-picking and listing the sessions where our team will be:
- presenting
- co-presenting, or
- contributing
The sessions I’m most excited about are the two ORDS-based sample applications. We have one that our development team created (that’s the React one) and another Python one that was mostly (with a lot of help from too many people to name) created by yours truly!

We’ve also updated our ORDS primer or “Master class,” which is really geared toward beginners. So, if you’ve not signed up yet, I recommend that one for “getting your feet wet.”

Just click the caption of each image. The links will take you directly to the online schedule. There you can favorite, and “add to your schedule.”

ORDS and APEX for Microservices

Add to my schedule

ORDS APIs in the Oracle Autonomous Database

Add to my schedule

An ORDS, Python, Flask application – 2024 edition

Add to my schedule

Accelerate App Development

Add to my schedule

React Applications with Oracle Database 23ai

Add to my schedule

CI/CD for the Oracle Database Developer

Add to my schedule

Before I forget

Two things. First, the last few Pull Requests are being approved for our Live Labs. But once those are live, I’ll be sure to do a follow-up post as well. For now, take a look at our sessions, and we hope to see you in a few weeks.

Second, if you register for any of these sessions, be sure to mention code “ORDSigami”! The code itself is meaningless, but it’s just fun to say aloud.

Hope to see you there 😍

An actual photo of Las Vegas, NV from 2024.

Follow

And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
- YouTube
- LinkedIn
- Instagram
- TikTok
- GitHub
- Mail
- Facebook
- Medium
August 22, 2024
ORDS 24.2 Release Highlights
```
NOTE: This can be thought of as a "companion piece" to the official ORDS release notes 🤓.
```
What is new in Oracle REST Data Services 24.2?

New HTTPS Response Status Codes
```
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:
```
ORA-12514 TNS: The listener does not currently know of the service requested in the connect descriptor
ORA-12506 TNS: listener rejected connection based on service ACL filtering
ORA-12529 TNS: connect request rejected based on current filtering rules
ORA-12516, TNS: The listener could not find an available handler with a matching protocol stack
```
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:
1. ORDS global configuration – which will be in a JSON format (basically a JSON version of the global.xml file)
2. ORDS database pool configuration/s – the individual configuration files for your ORDS “nodes” (again, just JSON versions of your database-pool.xml files)
3. 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:
1. Your wallet location (so when ORDS starts up, it is aware of the location of the Wallet and the credentials) and
2. 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:
```
ords --java-options "-Djava.util.logging.config.file=logging.properties"
```
Or, maybe you are testing the new ORDS Central Configuration strategy. In that case, you might want to include something like this when starting ORDS:
```
ords --java-options "-Dconfig.url=//localhost.8080" serve
```
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:
1. ords --java-options only apply to the current ORDS execution.
2. 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:
1. Buckets from any Compartment level are now accessible
2. Auto-generated Import Patterns for DMP Files are now included
3. Automatic mapping for Schemas and Tablespaces is now present
4. 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.
NDJSON spec Github

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.

That’s it for this release. Download the latest and enjoy!

Oh, and if we missed your enhancement this time, let me know. We’ll add it to one of our sprints!

Follow

And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
- YouTube
- LinkedIn
- Instagram
- TikTok
- GitHub
- Mail
- Facebook
- Medium
June 27, 2024
Setting up SSH to GitHub git clone into your Oracle Cloud Linux Ubuntu VM with VNC
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 Infrastructure¹
- 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)
1. 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).

Next, follow the Linux instructions for generating new SSH keys.

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 settings

Next, click SSH and GPG keys

Add 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!
- YouTube
- LinkedIn
- Instagram
- TikTok
- GitHub
- Mail
- Facebook
- Medium
May 7, 2024
Random Access Memories: ORDS and JWTs
```
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).
Learn more about these parameters.
- About Registered Claim Names (this includes “iss” and “aud“)
- About JSON Web Keys (JWKs)
```
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:
- the JWT.io debugger
- Microsoft’s JWT debugger
- Comprehensive list of libraries for various languages and frameworks
If you choose to use a web-based decoder, it’ll look like this (paste your Token):

But you might prefer something other than putting your JWT into a browser for decoding.

Homegrown

So, if you’re like me (and didn’t do your research beforehand), you might try to come up with something independently. Something you can run locally.

I created a JavaScript function that expects a JWT and “splits” on the periods. From there, Base64 decodes the necessary stuff for you and returns it:
```
function decodeJwt(newjwt) {
    var headerJwt = newjwt.split(".")[0];
    var headerJwtdecoded = atob(headerJwt);
    
    var payloadJwt = newjwt.split(".")[1];
    var payloadJwtdecoded = atob(payloadJwt);

    var signatureJwt = newjwt.split(".")[2];
    // var signatureJwtdecoded = atob(signatureJwt);

    // var signatureJwtBase64 = signatureJwt.replace(/-/g, "+").replace(/_/g, "/");
    // var signatureJwtBase64decoded = atob(signatureJwtBase64);

    console.log(headerJwt, payloadJwt, signatureJwt);

    console.log(headerJwtdecoded, payloadJwtdecoded);

    return(headerJwt, payloadJwt);
  };


decodeJwt("Your JWT goes here.");
```
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!
- YouTube
- LinkedIn
- Instagram
- TikTok
- GitHub
- Mail
- Facebook
- Medium
May 2, 2024
Python script to retrieve objects from Oracle Cloud Bucket
For…reasons, I needed a way to retrieve all the .CSV files in a regional bucket in Oracle Cloud Object Storage, located at this address:

https://objectstorage.us-phoenix-1.oraclecloud.com/n/dwcsprod/b/moviestream_data_load_workshop_20210709/o

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:
1. 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!
2. 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.
3. I’m not sure if my range() and len() practices are appropriate, but it works, so I’m going with it!
4. 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 requests
import json
import webbrowser

url = '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 in range(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.

Lines 1-10
```
import requests
import json
import webbrowser

url = '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)
```
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 first GET request. I do this for two main reasons:
1. to remind me of the structure of the JSON, and
2. 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 requests
import json
import webbrowser

url = '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 newList only 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 requests
import json
import webbrowser

url = '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...
```
Lines 21-26
```
import requests
import json
import webbrowser

url = '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)

newurl = []

for i in range(length):
    newurl = url + newlist[i]
    webbrowser.open(newurl, new=0, autoraise=True)
    print(newurl)
```
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!
- YouTube
- LinkedIn
- Instagram
- TikTok
- GitHub
- Mail
- Facebook
- Medium
April 9, 2024
Build an ORDS API Resource Module, GET request with JavaScript fetch, display in HTML
```
Really trying to optimize SEO with that title 👆🏼!
```
Recap

💡 All the code you'll see in this post can be found in my moviestreamjs github repository.💡

This post is a continuation of a previous one, which can be found here. In this post, I’ll:
- Further query the View I created with SQL
- REST Enable that same SQL (in the REST Workshop)
- Use the Fetch API in JavaScript
- Display the JSON items (from that GET request) from my Autonomous Database using the Bootstrap HTML and CSS framework
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).
```
Refresh yourself on ORDS' Limit and Offset parameters.
```
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!

Finally, all the code you’ve seen in this post can be found in my moviestreamjs github repository.

That’s all for now!

Follow

And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
- YouTube
- LinkedIn
- Instagram
- TikTok
- GitHub
- Mail
- Facebook
- Medium
April 8, 2024
Create a view from a JSON Collection and REST-enable it with ORDS
The DBMS_CLOUD PL/SQL Package

You can use this PL/SQL procedure (in the DBMS_CLOUD package) along with the file_uri_list URL (seen in the code below) to create and then add JSON documents to a JSON Collection (good info on JSON Collections in the Oracle database).

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.
Oracle Database Administrator’s Guide Release 23

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!
- YouTube
- LinkedIn
- Instagram
- TikTok
- GitHub
- Mail
- Facebook
- Medium
April 4, 2024