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 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!
Once you’ve installed ORDS, you need to REST-enable your schema before taking advantage of ORDS (I used to forget this step, but now it’s like second nature).
RESOURCES: I've discussed ORDS installation here and here. I'd check both pages if you're unfamiliar with it or want a refresher.
ORDS.ENABLE_SCHEMA / ADMIN_ORDS.ENABLE_SCHEMA
While logged into your database with SQLcl, you can issue the following commands to not only create a new user but grant them the required Roles (and the underlying Privileges) and REST-enable their schema (aka “ORDS-ifying” a schema):
/* Remember to remove the brackets when you run this code */
Create User [username] Identified By [password];
Grant Connect to [username];
Grant Resource to [username];
Grant Unlimited Tablespace to [newuser];
/* This PL/SQL procedure assumes you are logged in as the SYS.
If you are logged in as that new user, AND HAVE BEEN GRANTED
THE DBA ROLE, then you can execute the ORDS.ENABLE_SCHEMA
procedure */
Begin
ORDS_ADMIN.ENABLE_SCHEMA(p_schema = > '[username]');
End;
/
Create User [username] Identified By [password];
Grant Connect to [username];
Grant Resource to [username];
Grant Unlimited Tablespace to [newuser];
Begin
ORDS_ADMIN.ENABLE_SCHEMA(p_schema = > '[username]');
Commit;
End;
/
Automate because lazy
But even that is too much work for me, so I took a stab at automating this via an SQL script. As you can see, the above commands are simple and repeatable (a perfect candidate for automation). And since I’m constantly adding and dropping users for various reasons, copying and pasting code from an old blog or writing everything by hand gets annoying. Additional reasons for automating:
laziness
a desire to improve SQL and PL/SQL skills
an interest in scripting
I get easily distracted
The script
After about a day and a half, I have a working prototype script to call upon when I’m on the SQLcl command line. Here is what I came up with:
NOTE: If you just came here to remix the code, I have this in the scripts folder in my GitHub blog repo as well. Please feel free to sample it and/or roast it/me ๐ฅ. But if you keep scrolling, I'll go into more detail section-by-section.
18-OCT-2023 UPDATE: I've slightly changed this code to include the Commit; command in the PL/SQL portion of the script. You'll see that reflected in line 44. Thanks Renรฉ ๐๐ป!
INPUT
PROMPT Choose a new database username:
ACCEPT NEWUSER CHAR PROMPT 'Enter new user name hurrr:'
PROMPT Choose a temporary password for &&NEWUSER:
ACCEPT NEWPASS CHAR PROMPT 'Make it super secret:'
/*
I wish I could figure out a way to ONLY ask for username > check
that against existing database users > AND THEN allow a user to
continue with the 'choose password' step. I was only able to figure
out how to ask for the username and password AND THEN checks
against the database. I stole the code from this thread:
https://stackoverflow.com/questions/30710990/creating-an-oracle-user-if-it-doesnt-already-exist
Currently, its just extra steps for the user; kind of annoying. If you're
reading this and can figure out a way to get this working, let me know!
I'll make the change and attribute you in the comments :)
*/
Set Verify On
/*
You can refer to section 6.3.10.11 for more details on this
SET VERIFY OFF command
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/using-substitution-variables-sqlplus.html#GUID-3ACD41F3-A5A2-48D5-8E81-C29F9C14C865
*/
/*
The difference between using single and double ampersands:
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/using-substitution-variables-sqlplus.html#GUID-C6BE6E41-821F-413E-B4B1-56AAE4A46298
*/
Declare
check_if_user_exists Integer;
plsql_block VARCHAR2(500);
NEWUSER VARCHAR2(20) := '&&NEWUSER';
Begin
Select count(*) Into check_if_user_exists From dba_users Where username=NEWUSER;
If (check_if_user_exists = 0) Then
Execute Immediate 'Create User &&NEWUSER Identified By &&NEWPASS';
Execute Immediate 'Grant Connect To &&NEWUSER';
Execute Immediate 'Grant Resource To &&NEWUSER';
Execute Immediate 'Grant Unlimited Tablespace To &&NEWUSER';
plsql_block := 'Begin ORDS_ADMIN.ENABLE_SCHEMA(p_schema => :1); Commit; End;';
Execute Immediate plsql_block using NEWUSER;
End If;
End;
/
/*
The p_schema parameter is mandatory, that's why I'm including it.
If you omit the other parameters, the procedure will use the default
parameter values.
Learn more about this procedure here:
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.2/orddg/oracle-rest-data-services-administration-pl-sql-package-reference.html#GUID-459B8B6F-16EC-4FEC-9969-E8231668AD85
I was able to get this entire thing to work through trial-and-error,
while also using this for reference:
https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems017.htm
*/
PROMPT
PROMPT
PROMPT Congrats ๐ the user: &&NEWUSER, with the password: &&NEWPASS is now a bona fide database user ๐๐ป!
PROMPT Not only that, &&NEWUSER can log into Database Actions and REST-Enable their database objects too ๐!
PROMPT
PROMPT
PROMPT Click RETURN to return to the SQLcl prompt. And NEVER forget:
PAUSE "You're good enough, you're smart enough, and doggone it, people like you!"
PROPS: I owe much credit to this StackOverflow post and Jon Heller's and Mark Bobak's comments.
Breaking it down
Starting up podman
I’ll first start up podman and cd in the correct directory. “Correct” insomuch that this is where my ordsuserl.sql script lives.
ordsuser.sql, the script I’ll be working with.
Once my container status displays healthy, I’ll execute the ords serve command. This will launch ORDS in standalone mode (using the embedded Jetty server). I’ll use ORDS in a few minutes, so I should prepare it now.
Checking the container health.Issuing the ords serve command.ORDS is ready.
Prompting the user
I’ll then log into my podman container using the following string:
sql sys/password1234@//localhost:41465/ORCLPDB1 as sysdba
But why not the podman exec command?
NOTE: I'm logging in as SYS (I've since been informed you shouldn't really be doing this as SYS; in fact, it looks like you shouldn't be doing this as SYSTEM. Tom has some good comments in this post here.
Why am I not using the podman exec command here?
If you’ve seen my latest post on podman ports and networking, this command contradicts that entire article. There are actually two ways (maybe more, if I’m unaware) you can connect to your Oracle database in a podman container. The first way is to simultaneously hop on over to the container and sign in from inside that container. Your connection string would look like this:
podman exec -it 21entdb sql sys/password1234@//localhost:1521/ORCLPDB1 as sysdba
The second option is to sign in remotely like I’m doing in this current example:
This is analogous to when you SSH into a machine remotely. I should have mentioned it in this recent YUM/Oracle Linux post. Still, when you create a Compute Instance, you can later SSH into that Instance and perform actions like you usually would in the Terminal or the Command Prompt. But instead of being on your own machine, you’re on a remote machine. Ports are very confusing (for me, at least), so please read that podman ports post.
When you use the exec command, consider yourself on another computer on the Linux operating system. Once there, you must log in using the 1521 port because that is where the database’s TNS Listener (deep dive on Oracle database connection) is. However, when you are outside that container (i.e., that machine’s Linux OS), you need to use your local port (in this case, 41465) because it essentially acts as a proxy or a pass-through to the container’s 1521 port. Savvy ๐ดโโ ๏ธ?
DISCLAIMER: This my best-effort attempt at explaining this confusing concept. It is subject to change. But I really want people to take advantage of our tools in the Oracle Container Registry, so I hope this helps!
Hath connected to the database
Once I’m in, I can call upon my script to quickly create a new user and REST-enable their schema (recall, I “cd” into the correct directory in an earlier step). The syntax:
@ordsuser.sql
MORE SQLcl: You can read the different SQL, SQLcl, and PL/SQL commands here.
After pressing Return/Enter, a prompt will appear; this is what it looks like on the “front end”:
Notice the prompts in those first four lines.
HINT: I would also spend some time here, learning about the PROMPT, ACCEPT, and INPUT commands. Yes, this comes from the SQL*Plus documentation, but this works as you'd expect in SQLcl.
Meanwhile, here is the corresponding section in the script:
The first section of the SQL script.
Once I enter the password and press Enter/Return on my keyboard, the rest of the script is automatically executed using the provided username and passwordas substitution variables for the rest of the script! There is an in-depth explanation in the docs here, but you should grab the code and tinker with it to see how everything interacts and works together.
NOTE: The Set Verify On command displays the changes made from the original PL/SQL procedure and the updated PL/SQL procedure (with the updated username and password). It isn't necessary, but I wanted to provide some feedback to a user.
PL/SQL procedure
Assuming the user (i.e., the one you selected) doesn’t exist, the PL/SQL procedure should execute without issues. In the following image, you can see what is happening in real time:
A new user is created with the assigned username and password
That user is granted the Connect and Resource roles
The schema is then REST-enabled using the ORDS_ADMIN.ENABLE_SCHEMA PL/SQL procedure
Why NEWUSER in the Declare block?
Yeah, good question. At a minimum, you need to include the p_schema in the ORDS_ADMIN.ENABLE_SCHEMA procedure. Read up on that here. But, I wanted the username to remain in lowercase since that will later be used for my schema’s URIs (e.g., http://localhost:8080/ords/ordstest/emp/).
So I quickly taught myself/attempted a crash course on define variables and bind arguments; most of what I learned came from this EXECUTE IMMEDIATE documentation. And that’s why you see this in lines 25 and 34 of the code:
NEWUSER VARCHAR2(20) := '&&NEWUSER';
/* as well as */
ORDS_ADMIN.ENABLE_SCHEMA(p.schema => :1);
You can see the interaction between lines 25 and 34.
And on the front end, courtesy of the Set Verify On command, you’ll see that updated block of code:
The username and password fields are updated.
Feedback
I’ll then provide myself (or the user) with helpful feedback (along with words of encouragement). Once satisfied, I can click the Return/Enter key to exit the script and sign in to Database Actions as that new user.
The end of the script, after pressing Return/Enter.Exiting from SQLcl.
The corresponding section in the script looks like this:
I’m cheating by adding PROMPT to give line breaks.
Sign-in to Database Actions
Now, I can navigate to the Database Actions sign-in page at localhost:8080/ords/sql-developer. If I wanted to, I could also navigate to the newly introduced landing page at http://localhost:8080/ords/_/landing (obviously, depending on your deployment, this address will differ).
The landing page you’ve probably seen before.Signing in with the ordtest user I created with the script.This is our recently introduced landing page with more options.
SQL Worksheet then the proof
I’ll head to a SQL Worksheet, select All Objects in the Navigator tab, and a clean schema ready to take on the world!
Navigating to a SQL Worksheet.Selecting All Objects in the Navigator Tab.
Summary
And this marks the end of today’s lesson. So what did we learn?
You can execute SQL scripts directly from the SQLcl command line.
My script is cool, but I wish I could verify if a user exists sooner (I end up forcing the user to go through that password step).
The script has no exception handling (I’m giving myself a pass on this since I’m just now getting into PL/SQL).
Spend some time with the documentation on one screen and your script on another. After some time, you can actually understand how everything interacts.
One final thought. My process required a lot of trial and error, but seeing how everything flows and works is entertaining.
Please sample/remix my code; it’s located in my GitHub blog repo. Make it better, and let me know what you come up with! And be sure to check out ORDS and SQLcl ๐!
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
The other day, I wrote about how I had to start from scratch on my podman containers ๐ข. I’m now at the step where I need to reinstall ORDS in these two new database containers (21c and 23c). And since I’m doing this install yet again, I figured I would point out some things I’ve learned while doing this with podman containers. This post isn’t meant to be all-inclusive; I’m simply highlighting the areas that gave me the most trouble.
Lettuce begin
My assumptions are that you’ve downloaded the ORDS zip file or from a Yum repository (how-to article here). You’ve also set the ORDS configuration folder path and the ORDS product folder path (both are necessary steps for ORDS pre-installation). You can read up on that step here.
The ORDS Interactive Installer
Here, I’m installing ORDS with the Interactive Installer.
Do this with the ords install command
The fine print
NOTE: For a vanilla installation, most of the default prompts are correct. But for working with a podman container, I do not believe all the default settings will work (at least, this has been my experience).
The ORDS Interactive Installer will prompt you with the default settings, where appropriate. You’ll notice the Choose [value]: convention. These settings are okay to use in many steps, but if you mindlessly follow them in specific steps, you might end up with the incorrect ORDS installation for your particular use case.
Select the type of installation
For instance, in the “Enter a number to select the type of installation” step, I’m prompted with the [1] option. For me, that is incorrect; I need to choose [2].
Option [2] I choose you!
Database pool to update or create
Things can get tricky here, too. In this step, I WILL choose option [1], but in the next step, I WILL NOT selectthe default settings (read on about host names, ports, and service names).
This default string is simply an example; with podman you may not be using 1521 as the port.
Selecting the database connection type
And here’s why I won’t use the default settings. It’s because I have mapped the ports to/from my podman containers like this:
My 21entdb container is set up such that my computer sends and receives podman container traffic on port 41465. Meanwhile, my podman container is set up so that it will send and receive data on port 1521 (which is the default port for Oracle’s TNS Listener).
Another way of looking at this is to imagine port 41465 is sort of spoofing port 1521. Ehh..maybe it’s better to think of it like a pass-through, a proxy, a go-between if you will…but more on this in a second.
Demystifying the connection string
Here, I’ll test both ports, the Container (ORCLCDB) and Pluggable (ORCLPDB1) databases, with various connection strings.
QUESTION: How do I even know my options are ORCLCDB or ORCLPDB1? Well, I learned about them in the container registry documentation.
Using port 1521
First, let’s see what happens when I try to log into my database with SQLcl, using 1521 as the port:
Attempting in the Container databaseAttempting in the Pluggable database
Nothing! Initially, for me, this made no sense! And that’s because, in my mind 1521is the port that you would expect to connect with! This whole network business was confounding! That was until I realized that you have to use your computer’s port to connect to the podman container (which is listening on port 1521).
Using the port podman assigned to you
Ah-ha! Now, if you make that slight change to the ports, you can connect to your Container (ORCLCDB) and Pluggable (ORCLPDB1) databases.
We’re sys-dba’ing now, babyyyy ๐พ!
Does this help? Do you have a better understanding of why your port might not be 1521?
Host, port, and service names
You can probably keep the localhost default selection. When it comes to the listen port selection, I must choose 41465 and not1521. And for the database service name, you could choose ORCLCDB (i.e. Oracle Container Database), but we recommend installing ORDS into a Pluggable Database (read up on this in our ORDS Best Practices). Here you see me do just that; ORCLPDB1 is the ORDS default, but I wanted to highlight why this is the default.
Provide database user
In this step, I’m supplying the username and password of a user with the necessary privileges to log into the database to complete the ORDS installation. In this case, unsurprisingly, it is the SYS user (the default). We also have an ORDS Installer Privileges script you can execute if you’d rather grant another user privileges to install, upgrade, repair, and uninstall ORDS. You can find more details on that script here.
A great example showing how the JDBC driver is using the host, port, and service name values you provided.
Enabling features
This isn’t the final ORDS install step; this is just the last step I wanted to point out. The default here is also [1]. And I think you should keep it like that, here’s why. When you select [1], you’ll also give users access to Database Actions – the Graphical User Interface (which shares much in common with the SQL Developer desktop client). Once you start using Database Actions, it’s hard to stop.
Don’t ask any questions; select option [1].
NOTE: You'll also be enabling the REST-enabled SQL service (as well as the Database API). I recently wrote about the ORDS REST-Enabled SQL Service; it's very cool, and you should check out that article here.
The end
Aaanndd, that’s my time. They’re flashing the lights, so I have to get off the stage ๐. Hopefully, this note will be helpful when you create some containers from images on the Oracle Container Registry. And if you think you might like to tinker with ORDS, bookmark this post so you can refer to it later!
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
You’re working with podman containers (maybe like me – the ones from the Oracle Container Registry), and when you execute the podman ps command, you see something like this in the standard output:
A container status of unhealthy
In this case, I already had another container with an Oracle 21c database; that one washealthy. I previously wrote up a tutorial on how I did that here. But when I attempted to create another container with the Oracle 23c Free database, this is where things went sideways. This new container would constantly display an unhealthy status ๐ญ (not working).
Why am I even doing this? Well, we have a bunch of cool new ORDS features that take advantage of the 23c database, and we want to start showcasing that more. It would be nice to be able to show this in containers.
Digging deeper
Issue the podman logs (not log, duh) command for this particular container. Very few details are revealed (technically, it does reveal relevant information, I just need to figure out what I’m looking at here).
That ORA-12752 error message is…an error message.
You can clearly see that the database starts to provision, and then it just craps out1. I’ll spare you most of how we (I write “we” because this stuff is never really resolved by just one person) fixed this issue. But we finally figured it out; I’ll include some brief background. If you don’t care and want to see the resolution, then ๐๐ผ
1craps out is a technical term used by only the upper-most echelon of technical masters and internet luminaries.
Looking back, that Using default pga_aggregate_limit of 2048 MB line makes A LOT more sense now.
About podman machines
When you initiate a Podman machine, the default CPU and memory settings are 1 and 2048 MB (or 2,147,483,648 Bytes), respectively. I don’t see this in the documentation anywhere, but it is consistent with my assumptions when I created a second test podman machine with the default settings.
The test machine with default settings
After a ton of reading online, tinkering with podman volumes, pouring through the open issues in the podman and Oracle container GitHub repositories, and bugging the hell out of Gerald, we finally figured out the problem. Gerald, quite astutely, asked to see my output from the podman info command.
REMEMBER...this is the output from the original default configuration of my podman machine. The one where I already had a 21c database container. So, briefly ignore that test podman machine.
I included line numbers so you could more easily scan. Again, this output is from when I had a default podman machine.ย With this machine, I also had a 21c database container with a volume attached to it. I HAVE NO IDEA what the implications are of attaching volumes to containersย (as far as memory is concerned)! I also don’t know what it does to the memory of the Linux virtual machine (what your podman machine actually is) ๐ฌ.ย
A closer look at the machine configuration
Take a look at lines 39 and 40; you’ll see
memFree: 1351737344
memTotal: 2048716800
1351737344 Bytes equals 1.35 GB, while 2048716800 is equivalent to 2 GB. That is consistent with what you see in the podman machine’s default settings. And given that I have a 21c database container with a volume attached, that used memory (696979456 or 0.7 GB) could, at least partly, be attributed to the existing container.
Aaaand…that earlier default pga_aggregate_limit of 2048 MB (read more here) line further supports the assumption that insufficient memory (in the podman machine) is the culprit. The way I read it, that database could consume as much as 2 GB of memory.
So, how could I expect to create another container of equal size in a machine that is only large enough to support one container?!ย
Myself
Resolving the unhealthy issue
Well, after completely removing my podman machine, I re-initiated a new one with the following parameters (docs here):ย
podman machine init --cpus=2 --memory=4096
NOTE: podman memory allocation is done in Megabytes. So 4096 Megabytes is equal to 4 Gigabytes.
I then recreated two separate volumes, 21cvol, and 23freevol. From there, I created a container for the 21c database using the following command (sorry, I didn’t get a screenshot of this one):
podman run -d --name 21entbd -p :1521 -v 21cvol:/opt/oracle/oradata container-registry.oracle.com/database/enterprise:latest
And then another for the 23c database:
podman run -d --name 23freedb -p :1521 -v 23freevol:/opt/oracle/oradata container-registry.oracle.com/database/free:latest
And FINALLY, as you can see in the following image, both containers show a healthy status!
NOTE: I've yet to sign into either of these databases, and I still have to reinstall ORDS in each. So if you are following along, this is where I leave you.
Inspecting the new machine
And just for fun, I inspected the sole podman machine (after removing that temporary test machine) to review its configuration.
In conclusion
After about a week, I’m NOW ready to start working in the 23c container. We have some great new functionality and other ORDS stuff planned, too. I plan to start sharing that here!
One more thing
I’m bringing these findings up with the Oracle Container Registry team. I’ll probably share with the podman squad too. I feel I’m not alone in having trouble figuring out what the hell is going on under the covers.
If you found this useful, please share the post! This one is more of a Public Service Announcement than anything else. I can’t tell you how frustrating it is when you can’t figure out why something isn’t working.
Hey, I hope this expedites your misery, though ๐!
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
I explore ETags and how they can be used in cURL commands when interacting with Oracle REST APIs. I also discuss some of the performance benefits of using ETags. This is not exhaustive, but I hope it introduces you to ETags or reminds you of their existence! But first…
LATE-BREAKING NEWS!!
A related video
FYI: I reference a CSV_DATA table throughout this post. We use it pretty extensively in this LiveLab. And we just recently presented a webinar based on that same LiveLab. You can check that out below!
Don’t know what ETags are? No worries, here is a definition:
The ETag (or entity tag) HTTP response header is an identifier for a specific version of a resource. It lets caches be more efficient and save bandwidth, as a web server does not need to resend a full response if the content was not changed. Additionally, etags help to prevent simultaneous updates of a resource from overwriting each other (“mid-air collisions”).
If the resource at a given URL changes, a new Etag value must be generated. A comparison of them can determine whether two representations of a resource are the same.
ETags can help to guarantee the provenance of your resources (like the auto-REST enabled table you’ll see shortly) but they can also ensure your applications consume fewer server/database resources, and load comparatively faster.
To illustrate how ETags work, I did some tinkering with cURL commands, ORDS, and a Podman container. Read on if ye dare…to see what I discovered!
Oracle REST APIs and ETags
A couple of weeks ago, I noticed in the cURL documentation there was support for ETags. And the cURL docs have options for both --etag-save and --etag-compare (practical examples to follow). When you use these options in your cURL commands, you’ll either:
save an eTag to a separate text file (locally, like on your desktop in my example below), or
compare the ETag (in that existing file) to an ETag that belongs to your REST-enabled resource (the CSV_DATA table, which you’ll see in a second)
Oh, that’s a lot of words! So read it again and then continue with my walkthrough. Meanwhile, I’ll spin up this Podman container.
We are back in Podman.
INFO: Want to learn more about using Podman and Oracle database tools? Check out my other two Podman-related posts here and here!
ORDS in Standalone mode
I need ORDS up and running for this demonstration, so I issued the ords serve command in my Terminal. This will launch ORDS in standalone mode (using a Jetty server, as seen in the image). Once it’s initialized, I can log into SQL Developer Web to interact with my database (remember, in this example, it lives in a Podman container).
Here, I’ve logged into SQL Developer Web as a non-ADMIN user (ORDSTEST in this case).
From the Database Actions Launchpad, I navigated to the SQL Worksheet.
And to keep this quick, I reused a table I created for that webinar we just did. I also auto-REST enabled it (so I could play with the cURL commands). Below, you’ll see it’s just a quick right-click with the mouse.
FYI: As a reminder, if you want to learn more about cURL commands, check out the LiveLabs workshop that this is based on. You can find that here.
Getting the cURL Command
Once I auto-REST enabled the CSV_DATA table, I selected the GET ALL REST cURL command.
This is the cURL command I’ll use for this experiment.
At this point, I still wasn’t sure that an ETag was sent from the server for those auto-REST-enabled resources (in this case, the CSV_DATA table). I know they are present when you build your own REST Modules with ORDS; (at the time) I was just less confident about the auto-REST resources.
SPOILER ALERT: ETags are present for auto-REST-enabled resources too (I'm dumb, and this is pretty widely known)!
–etag cURL options
Once I knew ETags were accessible for auto-REST-enabled resources, I experimented with cURL‘s --etag options (you’ll see how I implemented these in the upcoming cURL command).
The --etag-save [filename] and --etag-compare [filename] options work such that when you issue the --etag-save in addition to that initialcURL command, a single-line file will be saved to the directory you are currently in (you’ll see that file shortly).
This differs from how an application might work, but the concept is the same. You’re storing the ETag’s value somewhere accessible to the application. For my purposes, I need to keep this ETag somewhere the cURL command line utility can find it.
The initial cURL command
I hopped over to my Terminal and used that [slightly modified] cURL command (the one I previously retrieved from the SQL Worksheet). You’ll see that I included additional options/arguments:
--verbose
--etag-save
| json_pp
This is the first cURL command I issued.
FYI: Apparently, the json_pp command utility is a part of Perl. I think this ships with the macOS, but I'm not 100% sure. Do you know? It worked for me and pretty printed out my JSON response (notice how I used the pipe "|" in addition to the actual command).
When you use that --etag-save option, a file with the value of the ETag will be saved locally. You can see me retrieving that file and reviewing the ETag file (note in the above cURL command, I named the file “myobjectetag.txt“).
Listing the files in the current directory.Locating the myobjectetag.txt file.Opening the file and inspecting the ETag value.
I can now use this ETag in subsequent GET requests to determine if the resource (the CSV_DATA table) I’m requesting has changed since I last interacted with it. What would constitute a change? Maybe rows have been updated or removed; perhaps an additional column was added. Or maybe the table was restructured somehow; it could be any change.
But, let me pause briefly and explain the --verbose option.
About the verbose option
The printout from the --verbose option.The remaining JSON object is nicely printed out.
I used the --verbose option to inspect the information available when interacting with this Oracle REST endpoint. I don’t need to include it now since I know the ETag is coming through, but I left it in this cURL command example so that you could have a look yourself. You’ll see loads of information, including (but not limited to):
Connection information
The cURL version used
The Status Code returned (200 or OK in this case)
ETag info
In this example, all I care about is the presence of an ETag. I can now use that ETag in a subsequent GET request to determine if the resource on the server side has changed. Here is what the cURL command looks like with the --etag-compare option:
That cURL command looks very similar, except for that --etag-compare option. In this situation, cURL first checks to see if your ETag and the resource’s (the API endpoint on your server) ETag match. If they do, the request stops. And if you use the --verbose option, you can see what comes back from the server:
A whole bunch of nothing. Not really, though. That “If-None-Match” Header is the secret sauce, though. That is a conditional Header that is passed over to the server. Essentially it says, “If this Header value doesn’t match yours, then send over the requested data; otherwise, end the request here because we already have the information we need/. It’s stored/saved (presumably) locally.“
INFO:Read up on caches, because that's essentially what your application is going to use instead of having to go through the entire GET request/response cycle.
The request is terminated, but what does this mean from a performance perspective? Well, say you have a webpage that loads and later reloads in response to a user’s interaction (I simulated this with the two cURL commands). That page will probably need some information from the server to populate that page. In a situation like this, you could first ask your application to share your copy of the ETag with the server in a subsequent GET request header (“If-None-Match“). And if nothing has changed, you could speed up page load times by just refreshing with what you have stored in a cache while freeing up resources on your server for other processes. But this is just one example.
Possibilities with ETag
I’ve given this some thought, and I bet there are quite a few use cases where referring to an ETag before executing an HTTP method (like a GET or GET ALL) might be helpful.
You may want to periodically check to see if a resource has changed since you last interacted with it. Could you incorporate ETags into your build processes or longer-running jobs (maybe something around data analysis)?
Actually, ETags play a massive role in JSON-Relational Duality Views. We have an entire section in the ORDS docs on how to use them! And suppose you want to download a containerized version of the Oracle database 23C (the one that supports JSON-Relational Duality views). You can do that via this link (I think I should do this too and highlight some of the cool ORDS + JSON Duality View features)!
Well, this brings me to the end of this post. I’m hoping you learned something and came away with some good resources. And if you found this post helpful, please pass it along! And don’t be afraid to comment too! I’d love to hear your thoughts. Maybe I’ll even include your idea in a follow-up post ๐คฉ!
The title says it all. I’ve run through this about ten times now. But I’ll show you how to start a Podman container (with a volume attached) and install ORDS on your local machine. And then, once installed, we’ll create and REST-enable a user so that the user can take full advantage of Oracle REST APIs. (aka ORDS). I’ll finally show you how to log into a SQL Worksheet as that new user. Ready? Let’s go!
Oracle Container Registry
First, visit the registry. Navigate to the Database product category, then select Enterprise. Even better, just navigate to the 21cor23ai images directly (thanks Killian ๐! Ensure you’ve also signed into the site (otherwise, you won’t be able to pull this image).
For Podman, I’ll review the preferred way to start this container (with a volume; for persisting your data across sessions).
Volumes
Start your Podman machine with the podman machine startcommand. Then create a volume with the podman volume create command (that way, you can save data locally and use that volume each time you start your container). Now that we have the volume, we can create a new container and attach that volume simultaneously (more Podman volume info here).
There are a few ways you can attach volumes when starting a container, but I like the one I found in this video:
Ironically, this is an Oracle video. But it is one of the most straightforward ones I found. To start the container, here is the command I used:
podman run -d --name entdb213 -p 1521:1521 --mount=type=volume,source=entdb213vol,destination=/opt/oracle/oradata container-registry.oracle.com/database/enterprise:21.3.0.0
About port mapping
You’ll notice that I used the following port mapping -p 1521:1521. You can remove that leading 1521. If you do, Podman will bind any exposed port to a random port on your host (a MacBook, in my case) within an ephemeral port range. Ephemeral?? (I guess that means all/any available ports, assuming none of these).
At this point, I have created a volume and started a container (with an Oracle Enterprise database inside).
PRO TIP: If this is your first time starting a container with a database of this size, it will take several minutes. So go do some chores while you're waiting ๐คช
Altering the db password
I’ll change the database Administrator password to something I can easily remember (“oracle“) using this command:
# Original command
# docker exec <oracle-db> ./setPassword.sh <your_password>
# My modified command
podman exec entdb213 ./setPassword.sh oracle
Changing the password to something I can easily remember.
Note: There are several shell scripts included in this container; one of which is the change password script. There are more details on the Oracle Container Registry > Oracle Database Enterprise Edition page (redirects prevent me from linking directly to that page).
Downloading ORDS
Next, I’ll head to the Oracle REST Data Services download page. And download the latest ORDS build (I’ll be setting this up shortly, I’m just gathering and configuring everything now).
Once that ZIP file is in my downloads folder, I’ll unzip it. At this point, this folder will still be named ords latest. You can certainly keep it like that, but I’ve renamed it to ords_product_folder. This is similar to how we refer to it in our installation/configuration documentation (changing it might make it easier to follow along).
ORDS Configuration
There are two configuration steps I need to perform before I can begin the ORDS installation. You’ll need to set an Environment Variable to the binaries (these are in the bin folder, you should see that in the above image) found in the ords_product_folder. Secondly, you’ll need to create an ORDS Configuration folder.
WAIT: If you're still reading this, how is my approach? After some research, placing these two folders in the "Library" seemed to make the most sense. I'm not sure what the analog on a Windows machine would be though. Drop a comment below if you know!
At this point, I’m nearly finished with this initial configuration. I next opened my .zprofile file (this is the file where I’m keeping most of my environment variables) and added the following paths:
Pourquoi? I can't seem to find a definitive answer as to where these paths should be saved, but this thread on Stack Exchange does a great job explaining all these files (and when/where they are used).
ORDS Installation
You’ll want to exit out of all your Terminal sessions so that the next session can pick up those changes to the .zprofile file. Podman will still keep doing its thing in the background, and hopefully, by this time, the database container will display as “healthy.”
๐ STOP: This may be obvious to you, but it wasn't to me, the database needs to be ready (healthy), online, and active (whatever you want to call it) for ORDS to install. You can always issue the podman ps command to check the status of the container.
Remember this; you’ll need it shortly.
ORDS install, the first attempt
In a new Terminal, I’ll issue the ords installcommand. If you’ve set up your bin and config environmental variables like me, then you shouldn’t have any issues. Moving through the first few steps is easy.
The ORDS interactive installer will default to recommended settings. Most of the time, these will be correct. Since this is my first time installing ORDS, I’ll choose Option 2 in that first step. I can use “localhost” as the database hostname and 1521 as the port.
When you get to the database service name, that’s where you might get hung up. The ORDS installer assumes default configuration settings. But here, if you select “orcl” as the database service name, it will install ORDS in the entire database. This is not technically incorrect, but our ORDS Best Practices recommends you install ORDS in a Pluggable Database (PDB). So I’ll issue the podman logscommand (in my case: podman logs entdb213) to find the name of the PDB; ORCLPDB1 (that’s the default for this container, it’s well-documented in the container registry docs, I’m just an idiot).
This is a neat trick, but it’s also documented in the Container Registry docs.
The ORDS interactive installer is very forgiving. I’ve noticed I can exit out of the installation process pretty much anywhere. Given that fact, I’ll restart my Terminal and start the ORDS install over (now that I have the correct database service name).
ORDS install, for real this time
In these images, you can better see the ords installcommand (it was slightly grayed out in the previous image). And you’ll also see all the steps completed. You can probably keep everything default as I did. You might want to since our documentation refers to that 8080 port (as seen in the images) in most of our tutorials and examples. I find it easier to follow along with the docs when everything matches.
๐๏ธ NOTE: Make sure you choose to run ORDS in standalone mode. That way you can continue to follow along in later steps.
Here you can see the paths to the bin and config folders.Conditions are perfect.
The rest of the installation is largely unremarkable. You will notice a few things, though:
The paths we’ve pointed to for the config and bin folders
The “settings” names(no action required by you, it’s just cool to see)
once the installation finishes, the text “Oracle REST Data Services initialized” will appear
That final image confirms that ORDS is now running in standalone mode. You can visit the following:
localhost:8080/ords/sql-developer
Logging into the SQL Worksheet, the first attempt
And try to log in with the SYS credentials.
One does not simply log into Database Actions…you must REST-enable a user first.
SPOILER ALERT: You can't ๐! Muahahahahaha!
That was a dirty trick. While ORDS is installed in ORCLPDB1, we must first create and REST-enable a user. I will make up for this dirty trick by sharing one of my favorite cheat codes for learning SQL and PL/SQL.
๐๏ธ NOTE: You cannot REST-enable the SYSTEM or SYS user.
If lucky enough, you have an Oracle Cloud Free Tier account with at least one Autonomous database provisioned.
CORPORATE SHILL ALERT: You can sign up for one here ๐.
Code cheating with the Autonomous database
Login to Database Actions as the Administrator. Navigate to User Management. In that dashboard, select the + Create User button.
In the Administration section.Click the Create User button.
When the slider appears, enter the information for the ORDSTEST user (like you see in the image here).
Once you’ve entered everything in, hit that “Show code” switch.
You’ll need to enable “Web Access” for this user. When you do this (click the switch), two things will happen:
The CONNECT and RESOURCE roles will be automatically selected for you
The Authorization required toggle will be enabled – shut this off for now
Once you’ve done that, you can select the “Show code” switch at the bottom of the slider. This will reveal the actual code that is being executed should you click the “Create User” button (which you will most certainly NOT!).
Copy and paste this into a text editor.
I copied this code and placed it into a text editor. I made one small change to the QUOTA line (at the bottom of the script).
Stole this from the ORDS Quick Start Guide.I am making a slight change to that last line.
Then I headed back to my Terminal and opened a new tab. I’ll execute this code in the database (remember, it’s in that Podman container running idle in the background this entire time) using SQLcl.
Forgot your connection string?
IF YOU FORGET the connection string format for logging in, have no fear! That Jeff Smith recently showed me the history command. I also have a couple of shorts on how I used the command:
Using the history + the number of your choice.Here is a connection string that is close enough!Entering the slightly modified string into SQLcl.
Using SQLcl to REST-enable a user
Now that I have the proper format for the connection string, I’ll adjust it so the password is correct. Then I’ll execute the code in SQLcl to create a new database user and REST-enable that user’s schema.
I changed that final line; this is what it looks like in SQLcl.
Cheat code with PL/SQL in SQLcl
I’ve just learned you can REST-enable yourself by logging into SQLcl (i.e., connecting to the database in the Podman container) and issuing the following command:
EXECUTE ORDS.ENABLE_SCHEMA;
This command assumes that you have already been granted the CONNECT and RESOURCE roles but have yet to REST-enable your schema (what allows ORDS to act as the intermediary between the database and the rest of the web).
The command will employ the procedure’s default parameters, which are:
ORDS.ENABLE_SCHEMA( p_enabled IN boolean DEFAULT TRUE, p_schema IN ords_schemas.parsing_schema%type DEFAULT NULL, p_url_mapping_type IN ords_url_mappings.type%type DEFAULT 'BASE_PATH', p_url_mapping_pattern IN ords_url_mappings.pattern%type DEFAULT NULL, p_auto_rest_auth IN boolean DEFAULT NULL);
Here is what a sample output would look like, if I were signed in as the HR user:
An example output is if I were signed in as the HR user.
๐ FYI: This above image is just a sample, and not related to the rest of the images in this article. Be sure to pay attention to the connection string (sql hr/oracle@localhost:1521/freepdb1). This is if the HR user is logging into SQLcl and REST-enabling their own schema. That's why you see references to HR throughout. I don't want anybody to get confused!
Logging into the SQL Worksheet, for real this time
With all this code executed, I can NOW go back to the SQL Worksheet (remember, we’re on localhost:8080/ords/sql-developer) and log in as the newly created ORDSTEST user.
I am logging in as the ORDSTEST user.A true “will-they, won’t they” moment.Congrats, you have arrived!
And once you’re in, you’ll notice the SQL Worksheet is no different than what you might expect in the Oracle Autonomous database. So if you made it this far, go forth and CREATE, DELETE, INSERT, SELECT, and Query away, friends!
Shutting it all down
Once you are done playing and tinkering, you can log out of the SQL Worksheet, stop the ORDS process with CTL + C (on Mac, at least), stop the Podman container, and shut down the Podman virtual machine.
Use Control + C to stop the ORDS process.I am stopping the entdb213 container.Exiting from the Podman virtual machine.
And since we set this all up with a volume (so….so long ago, I know; we called it entdb213vol), you can start the container later on, and all your work will still be there (i.e., It shall persist!).
The end
Congrats, we made it! What do you think? Did I miss anything? If so, comment, and I’ll respond and update this post as needed. And if you think this could be useful for others, do share!
Podman is a daemonless container engine for developing, managing, and running OCI Containers on your Linux System. Containers can either be run as root or in rootless mode. Simply put: alias docker=podman.
I’ve spent the past couple of weeks setting up Podman to work on my MacBook.
My current setup
I really wanted to take advantage of our Oracle Container Registry. While, we have several containers, the one I’m most interested in is the [Ace] freely available Oracle Enterprise Database version 21.3.0.0.
FYI: It can be found in the Database containers category.
I wanted to learn more about containers while also connecting locally (i.e., from my MacBook) via SQLcl to said container. In that scenario, as far as my computer thinks, the container is a production database running elsewhere in the world. Oh, and I’m using Podman instead of Docker to do all this.
be able to connect to it with various Oracle database tools
I began this exercise with SQLcl since it was used in one of my recent posts. But as a follow-on to this article, I’d like to install ORDS on my local computer and then connect again but with ORDS joining the party. But that’s for another time.
But before connecting to this container, you’ll need a lot of prerequisites. As far as “ingredients” go, you’ll need the following:
Homebrew installed and updated. (If you need to do this, review my recent article for instructions.)
Podman installed and updated
Apple X-Code Command Line Tools updated (this is tricky, so check my notes below)
SQLcl (you can review the installation steps here)
A sample CSV file (The subject doesn’t matter; I grabbed one from Kaggle – “IMDb’s Top 100 Movies“)
The setup before the setup
Since this was such a huge PITA, I’m going to walk through all the steps I took to make Podman work on my MacBook. I’ve done this about ten times so far to make sure I’m clearly explaining every step I took.
I first opened up a new Terminal session using Spotlight (Left Command + Spacebar). Once in Spotlight, I searched for “terminal” and then hit enter. A new Terminal window will appear.
Opening SpotlightSearching for the Terminal applicationA new Terminal window
From there, I reviewed Homebrew using the brew list command. If you’re following along, you’ll see a list similar to mine, depending on what you have installed.
The next part is easy. I installed Podman with the following command: brew install podman. Homebrew will run through an auto-update, and eventually, Podman will begin installing.
Upon the first installation, and depending on the macOS you are on, you may see a couple of errors appear. I can tell you they will cause issues within Podman later on down the line. So (and without having to take you back down the rabbit hole with me), you’ll need to uninstall Podman with the brew uninstall podman command.
The errors can be seen in this image:
Errors with Podman installing
There are a few ways one can remedy this. First, you should uninstall Podman, close your Terminal window, and open up a new Terminal window. I found (via this GitHub issue) that this is a known bug. Some have suggested running the brew doctor command to review a list of possible problems (this will reveal any potential problems Homebrew has discovered). This seems like a good practice, regardless, and I wasn’t aware of this feature until now!
And while writing this article, I did just that and found two errors I’ll need to fix. I’m still trying to figure out what either means, but the one about the executable is troubling.
But back to the Podman issue. To resolve the xcrun errors, I stumbled upon a solution buried deep in the recesses of the internet. Long story short, I needed to manually install Apple’s X-Code Command Line tools. But if you try and the installation fails, you have to take an extra step.
BTW, it did NOT take 78 hours to download and install
The x-tra step
If the xcode-select --install command fails, you have to remove the Command Tools from your machine altogether. I did this with the following command:
sudo rm -rf /Library/Developer/CommandTools
If you want to bore yourself with this issue, here are some resources I found:
I’ve seen sudo in the past; I wonder if I ever bothered to look up its meaning. Taken directly from the docs:
Sudo (su โdoโ) allows a system administrator to give certain users (or groups of users) the ability to run some (or all) commands as the superuser or another user, while logging all commands and arguments. Sudo operates on a per-command basis, it is not a replacement for the shell.
Back to our regularly scheduled program…you’ll probably need to enter your system’s password (the thing you use to log on to your computer when it first starts up and/or wakes). And after that, restart your Terminal (I don’t believe I did this, but it’s probably a good idea to restart the Terminal).
Once that new Terminal window fired up, I used the following command to install the latest X-Code Command Line tools:
sudo xcode-select --install
Reminder, it will not take 78 hours to install this. I just followed the prompts (license terms, the usual stuff, etc.).
NOTE: I suspect we have to do this because for some reason, X-Code Command Line tools are not updated upon every macOS version update. So, who knows when the last time these tools have been updated. This is just a hunch, but in reality, I've no idea what I'm talking about.
Are we ready yet? Well, almost. Again, if you’re following along, navigate to our Oracle Container Registry site to retrieve the database container for this “recipe.” The path I took was Landing page > Database > Enterprise.
The main landing pageYou want “enterprise”Pay attention to the sign-in
YOU NEED TO SIGN IN for this to work!!! Oh, suuuuure… it’ll seem like it’s working when you’re in Podman, and you’ve tried ten times…but then it just keeps failing, and failing, and failing! So be sure to sign in (or create an account if you haven’t already).
Once signed in and chosen your preferred language, you’ll see this:
I’m ready to head back to the Terminal
PAUSE: reviewing the limitations of this exercise
Alright, so there are a few limitations I should address, and in no particular order:
Checkpointing containers in Podman
Volumes in Podman
Creating the database versus signing on to the database
Checkpointing currently doesn’t work in Podman (at least for Macs on macOS Ventura). This is documented as well. Here’s a GitHub issue I found. I don’t seem to be the only one with the issue. I spent about a day on this trying to get it to work. I couldn’t figure it out; maybe if you’re reading this, you know the secret. Please share if you know!
Secondly, I couldn’t figure out how to mount a volume to a container. I know this is fundamental to containers, but I encountered error after error for days. And for the purposes of this exercise, it isn’t a big deal. Now, if I were on an actual development team, that would be a different story. But I’m too dumb for development, that is why I’m a product manager ๐คฃ!
Finally, working with containers requires a paradigm shift. Shortly you’ll see that I’m setting up a container and “starting” the database therein. Later, I’ll separately log on to that database, using SQLcl,after the database is up and running. They are two different steps.
Looking at this screen you would think, “I’m just going to jump right in and execute the first command I see on this page.” Wrong!
Initial docker run command
Actually, you do NOT want to do that. You must scroll down to the “Connecting from outside of the container” section. Because I’m going to be connecting to this container from the outside.
Referring to the Custom Configurations section
I know this documentation mentions SQL*Plus, but this all applies to SQLcl also. And if you refer to my previous SQLcl post, you can review the logon syntax for logging on. The critical point is that I need to start the container with the -p (or Port) option included. Are you still with me? Let’s take a trip to the “Custom Configurations” section.
docker run -d --name <container_name> \
-p <host_port>:1521 -p <host_port>:5500 \
-e ORACLE_SID=<your_SID> \
-e ORACLE_PDB=<your_PDBname> \
-e ORACLE_PWD=<your_database_password> \
-e INIT_SGA_SIZE=<your_database_SGA_memory_MB> \
-e INIT_PGA_SIZE=<your_database_PGA_memory_MB> \
-e ORACLE_EDITION=<your_database_edition> \
-e ORACLE_CHARACTERSET=<your_character_set> \
-e ENABLE_ARCHIVELOG=true \
-v [<host_mount_point>:]/opt/oracle/oradata \
container-registry.oracle.com/database/enterprise:21.3.0.0
Parameters:
--name
The name of the container (default: auto generated
-p
The port mapping of the host port to the container port.
Two ports are exposed: 1521 (Oracle Listener), 5500 (OEM Express)
-e ORACLE_SID
The Oracle Database SID that should be used (default:ORCLCDB)
-e ORACLE_PDB
The Oracle Database PDB name that should be used (default: ORCLPDB1)
-e ORACLE_PWD
The Oracle Database SYS, SYSTEM and
PDBADMIN password (default: auto generated)
-e INIT_SGA_SIZE
The total memory in MB that should be used for all
SGA components (optional)
-e INIT_PGA_SIZE
The target aggregate PGA memory in MB that should be used
for all server processes attached to the instance (optional)
-e ORACLE_EDITION
The Oracle Database Edition (enterprise/standard, default: enterprise)
-e ORACLE_CHARACTERSET
The character set to use when creating the database (default: AL32UTF8)
-e ENABLE_ARCHIVELOG
To enable archive log mode when creating the database (default: false).
Supported 19.3 onwards.
-v /opt/oracle/oradata
The data volume to use for the database. Has to be writable by the
Unix "oracle" (uid: 54321) user inside the container If omitted the
database will not be persisted over container recreation.
-v /opt/oracle/scripts/startup | /docker-entrypoint-initdb.d/startup
Optional: A volume with custom scripts to be run after database startup.
For further details see the "Running scripts after setup and on
startup" section below.
-v /opt/oracle/scripts/setup | /docker-entrypoint-initdb.d/setup
Optional: A volume with custom scripts to be run after database setup.
For further details see the "Running scripts after setup and on startup"
section below
I believe the colons you see throughout the original code block (with certain exceptions) are there for the definitions (you wouldn’t actually include these in your commands). If you are coming from database development, I suspect some may think, “ahh, bind parameter.” I do not think that is the case here.
You might be asking, in this code block, what the hell am I supposed to be looking at? Well, the container has a “listener”, listening on port 1521. So if I want to connect to the container, I’ll need to “map” to it. I’m not sure if that is what it is called exactly (not a networking guy, don’t claim to be). But the next question is, what is my <host port> (How it is referred to in the code block above)?
Everything matters, and nothing matters
Executing a ping command in my Terminal, to see what my computer’s address is great, but it tells me nothing about the port.
Use ping localhost to see your IP address
So I took to the internet to try to figure out the appropriate port…Honestly, I’ve tried searching but I can’t find anything definitive. In the Podman documentation, I see a lot of reference to port 8080; as in localhost:8080.
Care to review it? Here are some search results using “8080” as the search parameter.
Buried in the docs, there is a brief mention of the port and it can be found in the –publish or -p parameter. The way I understand ports on your local machine is that if you omit the local host information, you shouldn’t have any problems. It will just default to…something. So..it doesn’t matter, nothing matters. It’s all an illusion.
I also reviewed the cURL documentation. I found something in the --connect-to option:
Can you leave it empty?
Aaaand, more port nonsense (if you are having trouble sleeping at night, try reading this):
Okay, with all this out of the way, I can finally start to make some progress (almost there, promise).
Remember, you have to start the Podman Linux virtual machine before you do anything (this is in the instruction steps, so review that first (steps for macOS). This is where the container “lives.” Once the virtual machine is up and running.
Podman virtual machine is ready
I then grabbed the Oracle container. But, since I’m using Podman I needed to modify the run command, like this:
podman run -d -p :1521 --name myoracledb container-registry.oracle.com/database/enterprise:21.3.0.0
REMINDER: Make sure you are logged into the Oracle Container Registry site before you attempt all this!
Assuming you’re still following along, you’ll see something like this in your Terminal:
New container ID
I used the podman ps command to check the status of the container. You should see something like this:
“Starting” and “Healthy” statuses
For several minutes, you’ll continue to see the container status as “starting”. You can do like me and just periodically enter the podman ps command, or you can go do something meaningful with your time. Check back in 10 mins or so. The choice is yours. Either way, you’ll eventually see the status change from “starting” to “healthy”.
The container is healthy and ready
“Healthy” means I now have an Oracle Enterprise database (version 21.3.0.0) running in my Linux virtual machine as a container. I still need to log in with SQLcl, though.
Hold up, I can’t just log into SQLcl. I still have some more setup to do. I need to reset the randomly generated password to one of my choosing. Our instructions tell you to first issue the docker logs + [your database name] command to view your database logs. And from there you should be able to locate it. I couldn’t maybe you can. Let me know if you were able to.
Using the logs command
Since I’m doing this in Podman, that command is slightly modified:
podman logs myoracledb
The printout of that command will appear like this (yours will be very similar). Although I wasn’t able to locate the password, there are still some important pieces of information that you’ll want to review and note.
podman logs [your database name] printout
In this print out you’ll see things like the local host and port information, and the “Global Database Name” and “System Identifier (SID)” can be seen as well. You’ll see where the log files are located (your temporary password can be retrieved from here) and the database version you are running. Finally, you’ll see the message “DATABASE IS READY TO USE!”
Use the included shell script for changing your password
We are this close to logging onto the database. Even though I couldn’t find the temporary password, it doesn’t matter. You have to change your password anyways. If you refer back to the instructions on the Oracle Container Registry page, there is a section entitled “Changing the Default Password for SYS User” and it reads as such (emphasis added):
On the first startup of the container, a random password will be generated for the database if not provided. The user [must] change the password after the database is created and the corresponding container is healthy.
Using the docker exec command, change the password for those accounts by invoking the setPassword.sh script that is found in the container. Note that the container must be running. For example:
Easy enough, and since my container is “healthy” at this point, I can execute this script. But since I’m using Podman, the command will look like this:
And the output of that command will look like this:
Automated password change with the provided Shell script
I guess it worked. As you can see, my new password is password1234 (pleeeeease, do NOT share that with anybody). And at this point, I’m ridiculously close to logging onto this completely containerized Oracle enterprise database. All I need to do now is log on using the same steps as before (in my previous post).
Referring back to the Oracle Container Registry docs, I see the following:
The different login options
NOTE: Remember I'm logging into this container from the outside.
The connect options are cut-off in that image, so let me copy/paste them here. Also, assume where it states “sqlplus” I’ll be connecting with SQLcl. The options are as follows:
Turns out you can just use the port command to discover the container’s port (I’m guessing this is the route the container uses to communicate with my MacBook – it’s all quite muddled at this point).
Here is the command I executed:
podman port myoracledb
And here is what was returned:
Exposing the ports for this network
If you are starting your journey from the MacBook, its address would be 0.0.0.0 with a port of 43073. Data/info flows in and out of that port. And 1521 is a reference to the [bleep blurp ๐ค] TCP port at which the Transparent Network Substrate (TNS) Listener is located.
Actually, if you look at the previous output (from the podman logs myoracledb command) you’ll see how all the addresses and ports connect (including the TNS Listener).
TNS Listener information
It's in the logs, how could you not know this!?
Honestly, this is all ludicrous. You shouldn’t know all this, nobody should! It’s too much to retain, but that’s okay, I’m glad you’re still here…toughing it out with me. Once you get past this first big hurdle, I imagine working with containers is very fun.
Here is where I actually logged on (or is it logged into?) to this database with SQLcl. Here’s the command I used:
sql sys/password1234@//localhost:43073/ORCLCDB as sysdba
Which, if you recall is modeled on the original command found in the Oracle Container Registry docs; it looks like this (it’s also a few paragraphs back):
$ sqlplus sys/<your_password>@//localhost:<exposed_port>/<your_SID> as sysdba
NOTE: Exposed port is that where the TNS Listener is located, and the SID is the “System Identifier” – I showed that in the database logs earlier.
And again, I don’t think it matters if you include the localhost port. Here is what the output looked like in my Terminal:
Alright, so finally, I’m in! Next, I tested a SQLcl function to see if it worked as expected. I chose the LOAD function. And just as a refresher on the LOAD function I referred to the SQLcl help for in-context assistance. That help looks like this:
SQLcl Help and LOAD information
Specifically, I am going to test out the “CREATE TABLE” and “LOAD TABLE” function. So I scrolled down to the examples for reference.
Example showing how to create and load a table at the same time
At this point, the commands are pretty straightforward. I decided to use the LOAD NEW command, as seen above.
The beginning of the LOAD...NEW commandAlmost forgot the “NEW“Ready to execute
PRO TIP: You can simply drag the file and drop it into Terminal to get the complete file path.
DON’T forget to include the “NEW” at the end of the command. I forgot it the first time and my load failed. If doesn’t break anything, just a silly mistake.
I hit enter, and if you look at that image with the “Ready to execute” caption, everything worked as expected, here it is a zoomed-in (please excuse the gray shading):
Alright, so I have a brand new table. And if you recall, this was a data set that included the IMDb top 100 highest-rated movies of all time.
IMDb dataset by way of Kaggle
Well in the next few images, I wanted to test some SQL on the new table. I first searched by genre, with the following SQL:
SELECT DISTINCT genre FROM t100movies;
Selecting by the movie genre
Which returns all the distinct matches. Easy enough right? Then (because I like Adventure and Fantasy) I selected only those films that match those criteria, with this SQL statement:
SELECT * FROM t100movies WHERE genre = 'Adventure, Fantasy';
Single quotes and don’t forget your semi-colon ๐ซก
And once I correctly entered the SQL query, I found a single movie in the top 100 that meets those criteria. I’m actually surprised this one made the top 100 list.
Okay, but there’s just one more thing. The data persists inside the container even after I’ve stopped it. This isn’t necessarily the focus of this article, but I just wanted to demonstrate that even after shutting everything down, the table still exists.
This is true even after completely stopping my Podman container and shutting down the Podman Linux virtual machine.
The process was as follows:
Exited out of the SQLcl application
Stopped the myoracledb container process
Checked to make sure the process was actually stopped
Stopped and then restarted the Podman Linux virtual machine
Restarted the myoracledb container
Executed the same SQL query as before
Exited from the SQLcl application a final time
And if you take a look at all these images (they are numbered in order) you can see all the steps I took to during this little test. Pretty cool, eh!?
1. Exiting out of SQLcl2. Stopping the Podman container3. Stopping the Podman virtual machine4. Restarting the Podman virtual machine5. Restarting the myoracledb container6. Using the same SQL from before7. A final exit from SQLcl
A couple of notes here:
When I restarted the container, it only took about a minute for it to move from a “starting” to a “healthy” status. So I think the first time you start this container it takes a while. Subsequent start-ups, a minute or less.
When you start back up the container, you don’t have to map anything, I believe all those settings are still intact. Either that or I just spent a whole bunch of time networking when I didn’t need to.
And that does bring us to a close. If you’ve made it this far, you understand how you can at least get this container started and log on using SQLcl. Remember you can use Brew to install SQLcl and Podman. And of course, you’ll need to get the container I used (from our Container Registry; you can find it here in the Database category).
Remember, I didn’t do anything with checkpoints (or checkpointing containers) or with volumes. At the time of this article, I wasn’t 100% confident in my approach, so I wanted to exclude it. However, as I understand it, volumes (and their use) are the preferred approach to persisting data for later use (in containers). Just keep that in mind.
Finally, I’m not going to sugarcoat it. This was a grind – it was very tedious and frustrating, so hopefully, you can learn from my mistakes. I’m not claiming to be a developer or an expert in CI/CD. But I can totally see the appeal of having portable containers like this. The barrier to understanding stuff like this is incredibly high, so good luck to you. But hey, if you screw up, don’t worry about it. You can always uninstall and reinstall and try again.
Be sure to leave a comment if you see something amiss or if you have a better approach to something I’ve shown here. And as alwaysโฆ