Have you checked out my YouTube Channel yet? See all these posts demonstrated from end-to-end

Go To YouTube Channel
Home / Database / Docker / Oracle Database HTTPS REST Calls: Fix ORA-24247 and ORA-29024 with Network ACLs and Wallets

Oracle Database HTTPS REST Calls: Fix ORA-24247 and ORA-29024 with Network ACLs and Wallets

,

When you make an HTTPS REST call directly from Oracle Database, there are a couple of security layers that can get in the way before your request ever reaches the API.

In this guide, we'll take a practical approach and deliberately walk through those failures using Oracle AI Database 26ai Free running in Docker. We'll start with a REST call that fails, identify why it fails, fix the problem step by step, and finally get a successful HTTP 200 response.

Along the way, we'll cover:

  • Why UTL_HTTP can fail with ORA-24247
  • How Oracle Network ACLs control outbound connections
  • Why an HTTPS request can then fail with ORA-29024
  • What an Oracle Wallet actually does
  • How to create an auto-login wallet using orapki
  • How to import a trusted certificate into the wallet
  • How to use the wallet with UTL_HTTP
  • A common APEX_WEB_SERVICE ACL gotcha
  • Why UTL_HTTP.SET_WALLET can matter when database sessions are reused
Oracle Wallet + HTTPS REST Calls from a Dockerized Oracle Database
Oracle Wallet + HTTPS REST Calls from a Dockerized Oracle Database

Note: You should already have Oracle AI Database 26ai Free running in Docker and have SQLcl or another SQL client available. If you are completely new to running Oracle 26ai in Docker, check out my Oracle 26ai Docker setup video first. I'll add the video link in the Info section of this post.

What Are We Actually Trying to Do?

Our goal is simple: make an HTTPS REST request from inside Oracle Database.

For this demonstration, we'll have a small HTTPS endpoint running inside the same Oracle Database container:

Oracle Database 26ai → UTL_HTTP → HTTPS REST Endpoint

The endpoint will listen on:

https://localhost:8443/hello

The endpoint uses a self-signed certificate. This is intentional because it allows us to demonstrate the certificate trust problem clearly.

Our journey will look like this:

ORA-24247 → ORA-29024 → HTTP 200

Those three states are extremely useful because they show us that Oracle is dealing with two different security gates.

The Two Security Gates

Before an HTTPS REST request from Oracle succeeds, think about two separate questions:

  • Gate 1 – Network ACL: Is this database principal allowed to connect to this host and port?
  • Gate 2 – TLS Trust: Once Oracle reaches the endpoint, does Oracle trust the certificate presented by the server?

If the first gate fails, you get a network ACL error.

If the first gate succeeds but the certificate isn't trusted, you get a certificate validation error.

Only after both gates succeed do you finally get the HTTP response.

Step 1 – Prepare the HTTPS Test Endpoint

First, open a terminal and enter the running Oracle 26ai Docker container.

You can find your running container using:

docker ps

Then enter the container. Replace the container name if yours is different.

docker exec -it oracle26ai-apex261 bash

Create directories for our HTTPS demo server and wallet.

mkdir -p /opt/oracle/httpsdemo /opt/oracle/wallet

Create a Self-Signed Certificate

We'll create a self-signed certificate for localhost.

openssl req -x509 -nodes -newkey rsa:2048 -keyout /opt/oracle/httpsdemo/server.key -out /opt/oracle/httpsdemo/server.crt -days 365 -subj /CN=localhost -addext subjectAltName=DNS:localhost,IP:127.0.0.1

This produces two important files:

  • server.key – the private key
  • server.crt – the server certificate

The certificate is deliberately self-signed. In a typical production environment, you would normally be dealing with a certificate issued by a trusted Certificate Authority (CA), or an internal enterprise CA.

Create the HTTPS Server

Create the Python HTTPS server:

cat > /opt/oracle/httpsdemo/demo_server.py <<'PYTHON'
import http.server
import ssl
import json

class Handler(http.server.BaseHTTPRequestHandler):
def do_GET(self):
body = json.dumps({
"message": "Hello from HTTPS REST endpoint inside Oracle DB container!",
"path": self.path
}).encode()

```
    self.send_response(200)
    self.send_header('Content-Type', 'application/json')
    self.send_header('Content-Length', str(len(body)))
    self.end_headers()
    self.wfile.write(body)

def log_message(self, format, *args):
    pass
```

httpd = http.server.HTTPServer(('0.0.0.0', 8443), Handler)

httpd.socket = ssl.wrap_socket(
httpd.socket,
certfile='/opt/oracle/httpsdemo/server.crt',
keyfile='/opt/oracle/httpsdemo/server.key',
server_side=True
)

httpd.serve_forever()
PYTHON

Start the HTTPS server in the background:

nohup python3 /opt/oracle/httpsdemo/demo_server.py > /opt/oracle/httpsdemo/server.log 2>&1 & disown

Give it a moment to start and test it with curl:

sleep 1 && curl -sk https://localhost:8443/hello; echo

You should receive something similar to:

{"message": "Hello from HTTPS REST endpoint inside Oracle DB container!", "path": "/hello"}

Notice that we're using -k with curl.

That tells curl to ignore certificate validation. We're doing this only to verify that our test HTTPS server is alive.

Oracle's UTL_HTTP does not simply ignore an untrusted certificate. That's exactly what we'll demonstrate next.

Step 2 – Make the REST Call Without a Network ACL

Now let's switch to our application schema.

sqlplus itov_schema@localhost:1521/FREEPDB1

We'll make a very simple HTTPS GET request using UTL_HTTP.

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE
l_req  UTL_HTTP.req;
l_resp UTL_HTTP.resp;
BEGIN
l_req := UTL_HTTP.begin_request(
'https://localhost:8443/hello',
'GET',
'HTTP/1.1'
);

l_resp := UTL_HTTP.get_response(l_req);

DBMS_OUTPUT.put_line('HTTP STATUS: ' || l_resp.status_code);

UTL_HTTP.end_response(l_resp);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(
'ERROR STACK: ' || DBMS_UTILITY.format_error_stack
);
END;
/

The request fails.

You should see something similar to:

ERROR STACK: ORA-29273: HTTP request failed
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1189

This is our first important error:

ORA-24247: network access denied by access control list (ACL)

Oracle is stopping the request before we even get to the certificate validation stage.

Since Oracle 12c, outbound network access from database code is controlled through Network ACLs. The database principal needs permission to connect to the destination host and port.

Step 3 – Grant the Network ACL

Open another terminal and enter the Oracle container:

docker exec -it oracle26ai-apex261 bash

Connect as SYS:

sqlplus sys@localhost:1521/FREEPDB1 as sysdba

Now grant ITOV_SCHEMA permission to connect to localhost on port 8443.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'localhost',
    lower_port => 8443,
    upper_port => 8443,
    ace        => xs$ace_type(
      privilege_list => xs$name_list('connect'),
      principal_name => 'ITOV_SCHEMA',
      principal_type => xs_acl.ptype_db
    )
  );
END;
/

Now return to the first session and run the exact same UTL_HTTP request again.

This time, something interesting happens.

Step 4 – ORA-24247 Is Gone, But Now We Get ORA-29024

The error should now look like:

ERROR STACK: ORA-29273: HTTP request failed
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1189

At first glance, this might look like another failure.

But actually, the changing error tells us that we've made progress.

The Network ACL gate has now been passed.

Oracle was allowed to connect to localhost:8443, reached the HTTPS server, and started certificate validation.

That's where the second gate failed.

ORA-29024 means that Oracle could not validate the server certificate against its trusted certificate information.

And this is where the Oracle Wallet comes in.

Step 5 – Create an Oracle Wallet

We'll create a wallet inside the Docker container.

Our wallet directory is:

/opt/oracle/wallet

Create the wallet using orapki:

orapki wallet create -wallet /opt/oracle/wallet -pwd 'WalletPwd123#_' -auto_login

The -auto_login option creates an auto-login wallet.

This gives us the cwallet.sso file, which allows the Oracle process to access the wallet without requiring us to put the wallet password directly into our PL/SQL call.

You can check the wallet directory:

ls -la /opt/oracle/wallet

Step 6 – Import the Trusted Certificate

Our HTTPS server is using the certificate we created earlier:

/opt/oracle/httpsdemo/server.crt

Import that certificate into the wallet as a trusted certificate:

orapki wallet add -wallet /opt/oracle/wallet -trusted_cert -cert /opt/oracle/httpsdemo/server.crt -pwd 'WalletPwd123#_'

Now display the wallet:

orapki wallet display -wallet /opt/oracle/wallet -pwd 'WalletPwd123#_'

Under the trusted certificates, you should see something similar to:

Trusted Certificates:

Subject:        CN=localhost

We can also make sure the wallet is accessible by the Oracle operating-system user:

chmod 700 /opt/oracle/wallet
ls -la /opt/oracle/wallet

At this point, our wallet contains the trust information required for our test endpoint.

Important: In this demo we're trusting the self-signed server certificate directly. In a typical enterprise environment, you would generally import the appropriate issuing CA root and/or intermediate certificates rather than manually trusting individual server certificates.

Step 7 – Tell UTL_HTTP to Use the Wallet

Now that the wallet exists and contains the trusted certificate, we need to tell UTL_HTTP where it is.

Run the following in the ITOV_SCHEMA session:

UTL_HTTP.set_wallet(
  'file:/opt/oracle/wallet',
  ''
);

Then make the HTTPS request again.

DECLARE
  l_req   UTL_HTTP.req;
  l_resp  UTL_HTTP.resp;
  l_line  VARCHAR2(32767);
BEGIN
  UTL_HTTP.set_wallet(
    'file:/opt/oracle/wallet',
    ''
  );

l_req := UTL_HTTP.begin_request(
'https://localhost:8443/hello',
'GET',
'HTTP/1.1'
);

l_resp := UTL_HTTP.get_response(l_req);

DBMS_OUTPUT.put_line(
'HTTP STATUS: ' || l_resp.status_code
);

BEGIN
LOOP
UTL_HTTP.read_line(l_resp, l_line, TRUE);
DBMS_OUTPUT.put_line('BODY: ' || l_line);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
NULL;
END;

UTL_HTTP.end_response(l_resp);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(
'ERROR STACK: ' || DBMS_UTILITY.format_error_stack
);
END;
/

And finally, we get our successful response:

HTTP STATUS: 200
BODY: {"message": "Hello from HTTPS REST endpoint inside Oracle DB container!", "path": "/hello"}

We have successfully crossed both security gates.

Network ACL permission is present, the certificate is trusted, and Oracle can now complete the HTTPS request.

The Complete Flow

Let's summarize what just happened:

  1. We attempted the REST call without a Network ACL.
  2. Oracle returned ORA-24247.
  3. We granted the Network ACL.
  4. The error changed to ORA-29024.
  5. That told us the network connection was now reaching the TLS layer.
  6. We created an Oracle Wallet.
  7. We imported the endpoint's trusted certificate.
  8. We configured UTL_HTTP to use the wallet.
  9. The same REST call returned HTTP 200.

So the mental model is:

Network ACL → TLS Trust → HTTP Response

Bonus Gotcha #1 – APEX_WEB_SERVICE

Now let's take the same HTTPS endpoint and call it using APEX_WEB_SERVICE.

The basic call looks like this:

DECLARE
  l_resp CLOB;
BEGIN
  l_resp := APEX_WEB_SERVICE.make_rest_request(
    p_url         => 'https://localhost:8443/hello',
    p_http_method => 'GET',
    p_wallet_path => 'file:/opt/oracle/wallet',
    p_wallet_pwd  => ''
  );

DBMS_OUTPUT.put_line(
'STATUS CODE: ' || APEX_WEB_SERVICE.g_status_code
);

DBMS_OUTPUT.put_line(
'BODY: ' || l_resp
);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(
'ERROR STACK: ' || DBMS_UTILITY.format_error_stack
);
END;
/

But there is an important detail.

Even though we're executing the call from our application schema, the network ACL check can involve the execution context of the APEX package.

In this particular environment, the relevant package owner is APEX_260100.

So let's find the owner instead of assuming it.

Find the APEX Package Owner

Run:

SELECT DISTINCT owner
FROM all_objects
WHERE object_name = 'WWV_FLOW_WEB_SERVICES';

In this environment, the result is:

APEX_260100

This is a useful troubleshooting technique: when a package performs the network operation on your behalf, don't automatically assume that the schema issuing the call is the principal Oracle checks for the network ACL.

Grant the ACL to the APEX Owner

Connect with the required administrative privileges and grant the same host and port access to the APEX package owner:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'localhost',
    lower_port => 8443,
    upper_port => 8443,
    ace        => xs$ace_type(
      privilege_list => xs$name_list('connect'),
      principal_name => 'APEX_260100',
      principal_type => xs_acl.ptype_db
    )
  );
END;
/

Now run the APEX_WEB_SERVICE request again.

You should get:

STATUS CODE: 200
BODY: {"message": "Hello from HTTPS REST endpoint inside Oracle DB container!", "path": "/hello"}

This is one of those issues that can be confusing when everything appears to be configured correctly for your application schema.

If UTL_HTTP works but APEX_WEB_SERVICE returns ORA-24247, check the execution context and the relevant APEX package owner.

Bonus Gotcha #2 – SET_WALLET and Database Sessions

There is one more interesting behavior worth understanding.

Let's use another HTTPS endpoint: GitHub's API.

First, in a fresh ITOV_SCHEMA database session, grant access to GitHub:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'api.github.com',
    lower_port => 443,
    upper_port => 443,
    ace        => xs$ace_type(
      privilege_list => xs$name_list('connect'),
      principal_name => 'ITOV_SCHEMA',
      principal_type => xs_acl.ptype_db
    )
  );
END;
/

Now, without calling SET_WALLET first, make a request to GitHub:

DECLARE
  l_req   UTL_HTTP.req;
  l_resp  UTL_HTTP.resp;
  l_line  VARCHAR2(32767);
BEGIN
  l_req := UTL_HTTP.begin_request(
    'https://api.github.com/zen',
    'GET',
    'HTTP/1.1'
  );

UTL_HTTP.set_header(
l_req,
'User-Agent',
'oracle-utl-http-demo'
);

l_resp := UTL_HTTP.get_response(l_req);

DBMS_OUTPUT.put_line(
'HTTP STATUS: ' || l_resp.status_code
);

UTL_HTTP.read_line(l_resp, l_line, TRUE);

DBMS_OUTPUT.put_line(
'BODY: ' || l_line
);

UTL_HTTP.end_response(l_resp);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(
'ERROR STACK: ' || DBMS_UTILITY.format_error_stack
);
END;
/

In the tested environment, this returns:

HTTP STATUS: 200
BODY: Half measures are as bad as nothing at all.

So GitHub works.

Now, in the same database session, set our custom wallet:

BEGIN
  UTL_HTTP.set_wallet(
    'file:/opt/oracle/wallet',
    ''
  );
END;
/

Retry the exact same GitHub request.

In our test environment, it now fails with:

ERROR STACK: ORA-29273: HTTP request failed
ORA-29024: Certificate validation failure

Why?

Our custom wallet contains the certificate trust material for our local self-signed endpoint. It does not contain the trust chain required by GitHub.

The important part is that we're still in the same database session.

In this tested environment, once SET_WALLET was applied, subsequent HTTPS certificate validation used that custom wallet.

Reconnecting with a fresh database session restored the previous behavior in the test.

This is particularly interesting when connection pooling is involved.

Why Session Reuse Matters

In a simple SQLcl or SQL*Plus session, you know exactly when your session starts and ends.

Production applications are often different.

Connection pools can keep database sessions alive and reuse them for different requests.

That means session-level state can become important.

For example, imagine this sequence:

  1. Request A uses UTL_HTTP.SET_WALLET to call an internal HTTPS service.
  2. The database session goes back into the connection pool.
  3. Request B receives the same session.
  4. Request B calls a completely different HTTPS endpoint.
  5. The trust material available to that session may now affect certificate validation.

The exact behavior depends on how your application, connection pool and Oracle environment are configured, so this should be tested in your own deployment.

But the practical lesson from this demonstration is simple:

Be conscious of wallet and session state when database sessions are reused.

If multiple HTTPS integrations are expected to use the same session, a wallet containing the appropriate trust anchors for those integrations can be preferable to switching wallets casually during application execution.

Common Errors and What They Mean

Error Likely Cause What to Check
ORA-24247 No Network ACL permission Host, port and database principal
ORA-24247 from APEX ACL is missing for the relevant APEX execution context Package owner / APEX schema
ORA-29024 Certificate validation failure Wallet and trusted certificate chain
HTTPS worked, then fails after SET_WALLET Session wallet state / incomplete trust store Wallet contents and session lifecycle

The Mental Model to Remember

Whenever you make an outbound HTTPS call directly from Oracle Database, think about these two gates:

1. NETWORK ACL

Can this database principal connect to this host and port?

2. TLS TRUST

Does Oracle trust the certificate presented by the server?

3. HTTP RESPONSE

Only after both gates succeed do you get your REST response.

That gives us a very useful troubleshooting sequence:

ORA-24247 → Fix ACL → ORA-29024 → Fix Wallet → HTTP 200

Final Takeaway

Calling an HTTPS REST API directly from Oracle Database isn't simply a matter of writing UTL_HTTP.begin_request().

Oracle needs to know two things:

  • Whether the database principal is allowed to connect to the destination.
  • Whether the HTTPS certificate presented by the destination can be trusted.

Network ACLs handle the first part, while the Oracle Wallet provides the trust material needed for the TLS layer.

Once you understand those two gates, errors such as ORA-24247 and ORA-29024 become much easier to troubleshoot.

And if you're working with Oracle APEX, remember that the schema executing the call and the schema involved in the network ACL check aren't necessarily the same. In our environment, APEX_WEB_SERVICE required the ACL to be granted to APEX_260100.

Finally, if you're using connection pools, keep session-level wallet behavior in mind. A wallet configured during one request can matter to subsequent HTTPS requests using that same database session.

That's the complete journey: from ORA-24247, through ORA-29024, all the way to HTTP 200.

Thanks for reading!

If you found this useful, check out Into the Oracle-verse for more practical Oracle Database, Oracle APEX, AI, automation and MCP tutorials.

You May Also Like

No comments:

Subscribe to: Post Comments ( Atom )
Powered by Blogger.