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.

Enabling Database Actions

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.

REST-Enabling a Schema via SQLcl

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.

1. Open a new terminal window.

2. Connect to the freepdb1 container as system with the following command:

sql system/oracle@localhost:1521/freepdb1

3. Run the following commands to create the new HR schema:

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.

4. Open a new browser and navigate to the following URL:
   https://localhost/ords

5. Click the Go button under the card labeled SQL Developer Web.

6. 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.

7. From the same terminal window, enter and run the following:

grant inherit privileges on user SYSTEM to ORDS_METADATA
/

The next and final step is to actually issue the ords.enable_schema API call.

8. Next, enter and run the following:

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.

9. Switch back to the browser that was just used to try to login to Database Actions.

10. 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.

REST-Enabling a Schema via APEX

If you already have an APEX workspace created, you can easily REST-enable your schema from APEX in just seconds. Here’s how:

1. Open a new browser and login to your workspace.

2. Click the SQL Workshop icon.

3. Click the RESTful Services icon.

4. Click Register Schema with ORDS.

5. On the popup region, de-select Install Sample Service.

6. Click Save Schema Attributes.

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.

Integrating Database Actions with APEX

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.

1. Login to the INTERNAL workspace of APEX using the ADMIN account.

2. Click on the Manage Instance icon.

3. Click Feature Configuration.

4. In the SQL Workshop section, set Enable SQL Developer Web to Yes.

5. 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.

Summary

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.

Appendix

Increasing the jdbc.MaxLimit Parameter

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:

1. Open up a new terminal window.

2. 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.

3. Run the following command to edit the configuration file:
   vi ords_config/databases/default/pool.xml

4. Locate the line that looks like this: <entry key="jdbc.MaxLimit">30</entry>

5. Change the 30 to a 50 and save the file.

Now that the change is made, we need to restart the ORDS container.

6. From the terminal window, enter the following:
   podman stop ords

7. 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