Search for a command to run...
"Bumpy roads lead to beautiful places"
No comments yet. Be the first to comment.
This series will walk through the steps in order to install a local development environment that includes Oracle 23ai, ORDS & APEX.
Unless you’ve been living under a rock, you’ve likely heard about AI & LLMs more than once. LLM stands for Large Language Model. It's a type of artificial intelligence (AI) model that's designed to process and understand human language at a massive s...
I saw Dimitri's post on SQL Assertions today and it piqued my curiosity. Putting more business rules closer to data is almost always a great idea, as you can now call DML from any technology and rest
Almost 20 years ago to the date, I submitted my resignation to Oracle to go start my own company. It was a difficult decision to make, as APEX - or HTML DB as it was called then - was just starting to gain traction. However, I was presented with an o...
One of the more difficult parts of the software development lifecycle is deployment. While it seems simple on the surface - just write some scripts and run them - it gets pretty complicated pretty quickly as the level of sophistication of your system...
Another AI-centric tool that has raised a lot of interest is Claude Desktop. Like Cline, Claude Desktop uses the same reasoning engine at the core - Claude Code - to assist with development tasks. While they share the same engine, there are use cases...
When I first heard the term MCP used in the context of AI, I was immediately taken back to the 80s when the movie Tron was released. For those who have not seen it, Tron is a futuristic movie about a curious user who is literally sucked into a comput...
The ORDS container that we are using not only exposes APEX, but it also provides a robust set of database-centric tools known as Database Actions (formerly SQL Developer Web). You can read a lot more about the specifics of Database Actions here or here.
This post won’t dive into what’s possible with Database Actions, but rather walk through how to enable them on our local containerized instance of Oracle 23ai.
There’s really only one thing to do before we can use Database Actions. It involves REST-enabling any schema that we want to use to connect to it. This can be done via command line or via APEX.
Let’s walk through both scenarios.
It takes only a couple commands to REST-enable any schema in the database. But before we do that, let’s be sure we have a clean schema to walk through the steps with.
Let’s create a schema called HR.
Open a new terminal window.
Connect to the freepdb1 container as system with the following command:
sql system/oracle@localhost:1521/freepdb1
create user hr identified by oracle quota unlimited on users
/
grant connect, resource to hr
/
Now that we have a new schema, let’s try to connect to Database Actions.
Open a new browser and navigate to the following URL:
https://localhost/ords
Click the Go button under the card labeled SQL Developer Web.
Enter HR for the Username and oracle for the Password and click Sign In. You’ll see something like this:
This is due to the fact that the HR schema has not yet been REST-enabled, which is a requirement for using Database Actions. Thus, let’s get it enabled.
The first step is to allow SYSTEM to be able to call the ORDS schema objects. We need to issue a grant for that to happen.
grant inherit privileges on user SYSTEM to ORDS_METADATA
/
The next and final step is to actually issue the ords.enable_schema API call.
BEGIN
ORDS.ENABLE_SCHEMA
(
p_enabled => TRUE
,p_schema => 'HR'
,p_url_mapping_type => 'BASE_PATH'
,p_url_mapping_pattern => 'hr'
,p_auto_rest_auth => FALSE
);
commit;
END;
/
One thing to note: in a production environment, it’s better to obfuscate the schema name in the p_url_mappting_pattern so as not to give away the name of the schema to a would-be hacker.
All that’s left now is to try to login to Database Actions again.
Switch back to the browser that was just used to try to login to Database Actions.
Enter HR for the Username and oracle for the Password and click Sign In. Now, you’ll see something like this:
You can now login to Database Actions as the HR user, and perform any tasks that schema has access to do.
If you already have an APEX workspace created, you can easily REST-enable your schema from APEX in just seconds. Here’s how:
Open a new browser and login to your workspace.
Click the SQL Workshop icon.
Click the RESTful Services icon.
Click Register Schema with ORDS.
On the popup region, de-select Install Sample Service.
That’s it! To confirm it worked, simply login to Database Actions with the schema username and password that’s mapped to the workspace.
Same warning about the Schema Alias applies here - using the schema name is fine for testing, but a different name should be used for production in order to better obfuscate the actual schema name.
There’s one more integration that we can add to our environment - integrating Database Actions with APEX. This will add a link to Database Actions to the SQL Workshop tab in APEX, making it easy to jump from APEX to Database Actions without having to enter any credentials.
Note: On the APEX side, the older name “SQL Developer Web” is still referenced.
Let’s set that up.
Login to the INTERNAL workspace of APEX using the ADMIN account.
Click on the Manage Instance icon.
Click Feature Configuration.
In the SQL Workshop section, set Enable SQL Developer Web to Yes.
Click Apply Changes.
That’s all there is to it. To test, simply login to your workspace and from the SQL Workshop tab, select SQL Developer Web.
There’s a near 100% chance that you’ll need to allow popup windows for localhost. Look for a small icon in the address bar that looks like this and click on it:
Then, allow this site to open popup windows by clicking Done. The above screenshot is from Brave; other browsers will differ slightly as to how they manage popup windows.
With just a command or click, we can now unlock the power of Database Actions in our local instance. This extends the capabilities of our local environment to those who don’t need or want to use APEX alone. It is also a great complement to what APEX offers, as there are a number of useful tools in Database Actions that are simply not part of APEX.
In my initial testing, I found that the jdbc.MaxLimit value of 30 was not always sufficient. I kept getting errors like this one, which caused the ORDS container to occasionally crash:
503 The database user for the connection pool named |default|lo|, is not able to proxy to the schema named HR. This could be a configured restriction on the maximum number of database sessions or an authorization failure.
If you run into something similar, it’s simple to increase this value to fix the issue. I found that setting it to 50 made all my problems go away.
To change this parameter:
Open up a new terminal window.
Way back in the first post in this series, a new directory was created to store the ORDS configuration & secrets. This directory should have three subdirectories - ords_config, ords_secrets & images. Change to that directory in your terminal window.
Run the following command to edit the configuration file:
vi ords_config/databases/default/pool.xml
Locate the line that looks like this: <entry key="jdbc.MaxLimit">30</entry>
Change the 30 to a 50 and save the file.
Now that the change is made, we need to restart the ORDS container.
From the terminal window, enter the following:
podman stop ords
Next, create a new ORDS container with the following command:
podman run --rm --name ords -v `pwd`/ords_secrets/:/opt/oracle/variables -v `pwd`/ords_config/:/etc/ords/config/ -v `pwd`/images/:/opt/oracle/apex/24.1.0/images -p 443:8181 container-registry.oracle.com/database/ords-developer:latest &
That’s it! The new container should be running with a higher limit of database sessions.
Title Photo by charlesdeluvio on Unsplash