Connect to Oracle XE via SQL Developer from Windows or Mac

Accessing Oracle Database XE through SQL Developer provides a powerful, user-friendly interface for managing Oracle databases.

This guide will walk you through connecting to an Oracle XE database hosted in a Docker container on an Ubuntu server from a client machine running Windows or Mac. By the end of this guide, you’ll have a stable and secure connection between SQL Developer and your Dockerized Oracle XE instance.

Oracle SQL Developer is a free, integrated development environment that simplifies Oracle database management with a graphical interface. This article covers the essential steps to connect SQL Developer on your local machine (Windows or Mac) to an Oracle XE instance running in Docker on an Ubuntu server.

Prerequisites

To follow this guide, you’ll need:

• An Oracle XE database running in Docker on an Ubuntu server. (If you haven’t set this up yet, check out our **previous article** for a full setup guide.)
• SQL Developer installed on your Windows or Mac machine. You can download SQL Developer from the Oracle SQL Developer download page.
• Access to the Ubuntu server’s IP address and network configuration rights.

Tip: Ensure Docker and the Oracle XE container are running on the server. You can verify this with docker ps on your Ubuntu server.

Configuring Oracle XE to Allow Remote Connections

To connect remotely, we need to configure Oracle XE to accept connections from outside Docker’s network.

Step 1: Update Oracle Listener Settings

1. Access your Oracle XE container:

docker exec -it oracle-xe bash

Access Oracle Container

Step 2: Edit the listener.ora file

1. Using nano (if available): Open the file with nano:

nano /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora

2. Add the following line to enable remote access:

(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))

Save and exit by pressing Ctrl + X, then Y, and Enter.

If nano is not installed, use echo: If nano is unavailable, use echo to append the line directly:

echo "(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))" >> /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora

Step 3: Restart the Oracle XE Container

Restart the Oracle XE container to apply the configuration changes:

docker restart oracle-xe

Security Note: Allowing remote connections may expose your database to external access. Use firewall rules, VPNs, or IP whitelisting to restrict access.

Setting Up Port Forwarding on the Ubuntu Server

If your server is behind a firewall or cloud security configuration, you may need to open port 1521 to allow SQL Developer to connect.

• For UFW (Uncomplicated Firewall):

sudo ufw allow 1521/tcp

Tip: On cloud infrastructure (like AWS or Google Cloud), ensure security groups and firewall rules allow access on port 1521.

Connecting to Oracle XE via SQL Developer

Step 1: Open SQL Developer

Launch SQL Developer on your Windows or Mac machine.

Step 2: Create a New Connection in SQL Developer

1. Go to the “Connections” pane on the left and click the green + icon to add a new connection.
2. Enter the following details in the “New/Select Database Connection” dialog:

• Connection Name: Give your connection a name (e.g., “Docker Oracle XE”).
• Username: system (or another Oracle user if you have created one).
• Password: The password you set for the user in Docker.

3. Configure the Connection Settings:

• Hostname: Enter the IP address of your Ubuntu server.
• Port: 1521 (the default Oracle database port).
• Service Name: XEPDB1 (the default service name for Oracle XE).

4. Save Password if desired, and click Test to confirm the connection.

Tip: If you encounter connectivity issues, double-check the firewall and IP/port configurations.

Creating new Connection in SQL Developer

Step 3: Save and Connect

After a successful connection test, click Save and Connect to establish the connection. SQL Developer will connect to your Oracle XE instance, and you’ll see it listed under the “Connections” pane.

Validating Your Connection

Once connected, SQL Developer will display your Oracle XE instance in the “Connections” pane. You can expand the connection to explore schemas, tables, and other database objects. SQL Developer allows you to manage tables, execute SQL queries, and perform database management tasks directly from this interface.

Testing Basic Functionality

Right-click on your database connection to open a SQL Worksheet, where you can execute SQL queries and view query results. This is an excellent way to ensure everything is working as expected.

Troubleshooting Common Issues

• Connection Refused: Verify that Oracle XE is configured to listen on all addresses and that port 1521 is open on your firewall.
• Authentication Failed: Double-check your username and password. Ensure that Oracle XE allows the specified user to connect remotely.

Tip: If you still face issues, try restarting the Oracle XE container and SQL Developer.

Conclusion

By following this guide, you’ve successfully connected SQL Developer on Windows or Mac to an Oracle XE instance running in Docker on an Ubuntu server. This setup offers a convenient way to manage your Oracle XE database with a graphical interface, making it easy to run queries, update data, and monitor the database.

Call-to-Action: Was this guide helpful? Follow for more in-depth tutorials on Oracle Database, Docker, and database management with SQL Developer!