Getting slapped with an ora-24247 error out of nowhere is a massive pain, especially when you're just trying to send an email via UTL_SMTP or hit a web service using UTL_HTTP from your database. You've written your code, you've checked your logic, and everything looks perfect—but then you hit "run" and the database basically tells you that you don't have permission to talk to the outside world. It feels like hitting a brick wall when you're already in the home stretch of a project.
If you're seeing this error, it's not because your code is necessarily "broken" in the traditional sense. It's Oracle's way of saying that the security gates are closed. Specifically, your database user hasn't been granted the necessary privileges through an Access Control List (ACL). Since Oracle 11g, the database has been much more tight-lipped about network communication. It doesn't just let any user start reaching out to external servers without explicit permission. While this is great for security, it's a bit of a headache for developers who just want their scripts to work.
Why did this error suddenly appear?
If you've recently migrated an older database to a newer version or if you're setting up a new environment, that's usually when ora-24247 rears its head. Back in the day, Oracle was a bit like the Wild West; if you could write the code, the database would probably let you execute it. But as security became a bigger deal, Oracle introduced fine-grained access control for network services.
The error literally means "access denied by access control list." Think of an ACL as a VIP guest list at a club. Your database user is standing outside, but if their name isn't on the list for a specific "club" (which, in this case, is a specific host or IP address), the bouncer isn't letting them in. Even if you're the SYS or SYSTEM user, you sometimes still need to explicitly define these permissions to keep things orderly.
Understanding the Access Control List (ACL) logic
To fix this, we have to talk about ACLs. I know, "Access Control List" sounds like a boring dry topic from a networking textbook, but it's the heart of the solution here. An ACL is essentially a small XML file stored in the database that says, "User X is allowed to connect to Host Y on Port Z."
Before Oracle 12c, managing these was a bit clunky. You had to create the ACL, assign it to a host, and then grant privileges to a user. It felt like a lot of steps for a simple task. If you're on 12c, 19c, or 21c, things have gotten a bit smoother with the introduction of ACEs (Access Control Entries), but the underlying concept remains the same: you have to tell the database exactly who is allowed to go where.
The modern way to fix ORA-24247
If you're running a relatively modern version of Oracle (12c or higher), you don't have to jump through as many hoops as you used to. You can use the DBMS_NETWORK_ACL_ADMIN package to handle everything. Instead of creating a physical XML file manually, you use the APPEND_HOST_ACE procedure.
Here is a common scenario: you have a user named APP_USER who needs to reach out to an external API at api.example.com on port 443. To stop that ora-24247 error from ruining your day, you'd run something like this as a user with administrative rights:
sql BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => 'api.example.com', lower_port => 443, upper_port => 443, ace => xs$ace_type(privilege_list => xs$name_list('http'), principal_name => 'APP_USER', principal_type => xs_acl.ptype_db) ); END; /
What this does is pretty straightforward. It tells Oracle, "Hey, let APP_USER connect to api.example.com specifically on port 443 for HTTP-related activities." Once you run this, the error usually vanishes instantly. No restart required, no messy configuration files to edit.
Dealing with older versions (11g style)
If you happen to be working on an older system (hey, we've all been there), the process is a bit more involved. You have to create the ACL first, then assign it. It looks more like this:
- Create the ACL: You define the file and the first user on the list.
- Assign the ACL: You tell the database which host this list applies to.
- Grant more users: If others need access, you add them to the existing ACL.
It's a bit more "boilerplate" heavy, but it gets the job done. The reason most people still struggle with ora-24247 is that they might grant access to the host but forget the port, or vice versa.
Common "gotchas" that keep the error alive
You might think you've fixed it, but then the ora-24247 error pops back up. Why? Here are a few things that usually trip people up:
The "Wildcard" trap
Sometimes people try to be clever and use a wildcard like '*' for the host. While Oracle allows this in some contexts, it can be risky and sometimes doesn't behave quite how you expect if there are more specific ACLs already in place. It's always better to be specific about the host you're trying to reach.
Port ranges
If your application uses a range of ports, and you only opened port 80, but the service redirects to 443 (HTTPS), you're going to get blocked. You need to make sure the ACL covers the actual port the database is trying to communicate through. If you aren't sure, you can set lower_port and upper_port to NULL, which effectively opens all ports for that host, though security teams might give you a side-eye for that.
Case sensitivity
Oracle users can be case-sensitive. If your user is app_user but you created the ACE for APP_USER, it might not match depending on how the user was originally created. Generally, sticking to uppercase for the principal_name is the safe bet in Oracle.
The Protocol Privilege
In the APPEND_HOST_ACE example, I used 'http'. If you're trying to send an email, you'd use 'smtp'. If you're doing something involving DNS lookups, it might be 'resolve'. If you grant 'http' but try to use UTL_SMTP, the ora-24247 will still be there because you gave them the right to browse but not the right to mail.
How to check what's already configured
Before you go creating new rules, it's a good idea to see what's already there. You don't want to create overlapping or conflicting rules. You can query the data dictionary to see the current state of your network security.
Try running this: sql SELECT host, lower_port, upper_port, acl FROM dba_network_acls; And to see which users have which permissions: sql SELECT acl, principal, privilege, is_grant FROM dba_network_acl_privileges; This gives you a clear picture of the "guest list." If you see your host listed but your user isn't in the privileges view, you've found your culprit.
Wrapping things up
At the end of the day, ora-24247 is just a reminder that the database is doing its job and keeping things secure. It's annoying when you're in the middle of a flow, but once you understand the ACL/ACE logic, it's a five-minute fix.
Just remember to identify the user, the host, the port, and the protocol. Once you've got those four pieces of information, you can use DBMS_NETWORK_ACL_ADMIN to bridge the gap. Whether you're working on a legacy 11g box or a shiny new 19c cloud instance, the principle is the same: the database won't talk to strangers unless you've explicitly introduced them.
So, next time you see that error code, don't panic. Check your views, run your APPEND_HOST_ACE (or CREATE_ACL), and you'll be back to making successful network calls in no time. It's just one of those "Oracle things" that we all have to deal with eventually!