# Using Database Actions on the Oracle Container

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](https://www.thatjeffsmith.com/sql-developer-web/) or [here](https://docs.oracle.com/en/database/oracle/sql-developer-web/index.html).

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
sql system/oracle@localhost:1521/freepdb1
```

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

```sql
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:
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1733236926000/38ba4a86-9399-47a3-bb66-b37218fefe89.png align="center")

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:
    

```sql
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:
    

```sql
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:
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1733240720922/e08f55a7-8dbd-436e-b370-de54eedd4343.png align="center")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1733241350789/0d5585fb-5d6e-4094-9982-dacad6b9ab8f.png align="center")

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 “S**QL 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**.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1733242033360/8dd2b1c1-8a77-471e-968d-5e04edf2a44b.png align="center")

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:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1733242115391/13cbd80e-d6f8-4cc7-a49a-86372cf5651f.png align="center")

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](https://spendolini.blog/container-yourself), 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:
    

```plaintext
 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*](https://unsplash.com/@charlesdeluvio?utm_source=Hashnode&utm_medium=referral) *on Unsplash*