Oracle XE JDBC Example with Sample Data

This quick tutorial demonstrates basic JDBC connectivity to the HR database which is already setup in Oracle XE and is part of the Sample Data. This database is small, filled with data, easy to grasp and ideal to quickly put some prototype code together without worrying about DDLs and other initialisation scripts.

Please first refer to my previous post on how to get Oracle XE installed on any version of Mac OS that it also applies for any OS given that the correct version of VirtualBox gets installed.

Given that we have Oracle XE now installed and communication via TCP port 1521 is allowed we are good to go.

By using IntelliJ datasource capabilities we can quickly test connectivity to the Oracle’s Sample HR database (username: hr, password: oracle (if the DB is installed via the VM methodology described) otherwise password: hr (if the DB is natively installed) ):

Data Source Properties

while testing the connection yields success:

IntelliJ IDEA-1

.HR.EMPLOYEES - ScalaProj - [~_Dimitris_ScalaProjects_ScalaProj]

and for completeness here’s the UML of the sample database as it appears from IntelliJ:

DATABASE_1f59cfcd-b0bc-4ab3-a0ca-fd6402de2165.schema_HR - ScalaProj - [~_Dimitris_ScalaProjects_ScalaProj]

and also how it appears from Oracle Modeler:

Oracle Developer Days [Running]

Oracle Developer Days [Running]-1

How to install Oracle Database on Mac OS (Any Version)

25 Apr 2014 Update: This guide has been originally written 2 years ago describing Oracle 11gR2 DB installation. Now it has been revised for the latest Oracle 12cR1 DB.


Installing Oracle DB on any Mac OS version is not an easy task. Official support was dropped from 10g onwards around the early days of Mac OS X and since then there has been endless questions online of how to go about later versions like Leopard (10.5), Snow Leopard (10.6), Lion (10.7), Mountain Lion (10.8) or Mavericks (10.9).

I have yet to find a consistent resource online of how to natively install the database and I have also tried my hands on binaries compilation and package transfers along all the previous versions with various, inconsistent results. I have to admit that this long-winded process gets too complicated for my taste and patience.

The people at Oracle have acknowledged this problem and have provided a neat solution abstracting all the complicated noise, packing up all the required tools and needed configurations in a single image file readily to be hosted as a VM in VirtualBox.

This is old news and although VirtualBox is well documented I haven’t again found a single resource that is describing the process from top to bottom for my setup which is a Macbook Pro using WiFi therefore this detailed tutorial.

*General Note: As the creators suggest Oracle XE should be used for testing purposes only. Also the majority of passwords on the VM have been setup by default to oracle and should be changed for obvious security reasons.

Step 1: Getting VirtualBox

This is as simple as any other installation on a Mac. All we have to do is grab our x86 version (assuming an Intel CPU) for Mac from the official download page.

25 Apr 2014 Update: Currently VirtualBox is @ version 4.3.10 and doesn’t seem any different.

Step 2: Getting the VM file

This is the download page and the download instructions are clear. The ova VM file is big ~4G so better start the download process on a browser that has an integrated download manager like Firefox if you have a slow connection or are frequently experiencing dropped connections.

Apparently this VM has been initially created for a developer’s workshop but gets frequently updated with the latest and greatest of Oracle Client, Oracle SQL Developer and other tools. Various walkthroughs and other interesting tutorials are loaded-up on the VM awaiting to be discovered but all we care about is the Oracle 11gR2 DB which will be by default up and running when the VM is loaded into memory.

25 Apr 2014 Update: This is still the go-to place to get the OVA file. The difference is that the size of the file now spans ~ 5.22GB and has been prefixed with “OTN_” in the name. And of course it sports the latest Oracle 12cR1 DB along with the latest SQL Developer 4.

Step 3: Installing the Guest VM

Our host OS is apparently Mac OS and the guest VM is Oracle Linux 5 which is a branch off Red Hat Linux.

From the VirtualBox Menu we select File > Import Appliance

Then we make it point to the VM file we’ve just downloaded:

Import Virtual Applicance-2


25 Apr 2014 Update: In the picture above as mentioned in the previous section the name should be “OTN_Developer_Day_VM.ova” and the size ~ 5.22GB.


Click here for an older version of the picture above installing version 11gR2


After the import gets completed we are ready to start the VM:


Click here for an older version of the picture above installing version 11gR2


Step 4: Starting the VM

Starting up the VM is as easy as inserting oracle/oracle when we are prompted for username/password:


Click here for an older version of the picture above installing version 11gR2

When the OS boots up we are ready to go:


Click here for an older version of the picture above installing version 11gR2

Step 5a: Making Oracle DB visible  – Internet Connectivity

On the top right of the VM there should be an icon denoting Internet connectivity on the VM. If by default it is disabled it might need a kick to connect and as long as your Host OS has Internet connectivity it will connect without issues:

25Apr14-5-disconnectedByDefault                     25Apr14-6-connected

Step 5b: Making Oracle DB visible – Firewall

This is a tricky step that we didn’t need to perform 2 years ago on that flavour of OracleOS that was hosting 11gR2.

Basically the OracleOS Firewall is activated by default. So we have two solutions:

Solution 1 – Switch-off the OracleOS Firewall altogether

The Firewall is accessible via System(top left)>Administration>Firewall



On the Firewall Configuration panel click on the Disable button and then the Firewall disabled should look like this:


Solution 2 – Allow port 1521 in Firewall to be accessible remotely


Step 5c: Making Oracle DB visible – VirtualBox PortForwarding

By default VirtualBox > Devices > Network Adapters is attached to an internal NAT that should hopefully give you access to Internet.

Oracle Developer Days - Network

My setup is a MacBook Pro that is getting Internet via WiFi therefore I’ve tried to get the Bridged Adapter via WiFi to work that is initiating a more direct connection between the Host and Guest VM but to no avail. I’ve tried several things suggested out there such as reducting the MTU to 1496 or downgrading to IPv4 but without luck.

The solution lies in the Port Forwarding option under the Network Adapters picture above:

VirtualBox VM

Here we define external requests coming from localhost ( to the standard Oracle communication default 1521 TCP port to be forwarded to our Oracle VM with IP address on the same port. The IP of the Oracle VM can be found by issuing ipconfig command on the terminal (3rd line, inet addr):


Click here for an older version of the picture above installing version 11gR2

Step 6a: Testing external connectivity – via Telnet

From the Host OS (Mac OS X Mavericks) we can test that we can connect to localhost at port 1521:



Step 6b: Testing external connectivity – via IDE

Also by using IntelliJ (13.1 in this example) datasource capabilities we can successfully connect to the Oracle’s Sample HR database (username: hr, password: oracle):


Click here for an older version of the picture above installing version 11gR2

25 Apr 2014 Update: Note the correct connection jdbc url:


In prior version 11gR2 it used to be (but this does not work anymore):


Step 6c: Testing external connectivity – via SQL Developer

SQL Developer 4 @ Mac OS X Mavericks:


Click here for an older version of the picture above installing version 11gR2 sporting SQL Developer 3 at the time


That’s a quick, clean and bulletproof way of having the most recent Oracle DB running on a Guest VM not only on Mac OS but on any OS.

PS. If you have a Synology NAS like I do, you might want to check out my detailed tutorial on how to host the VirtualBox VMs on the NAS and connect to it via the supported iSCSI protocol by VirtualBox clients installed on any home/work computer.

Adding a dblink to copy data across Oracle DBs

Say for instance we need to copy a whole table’s worth of fresh data from Oracle database (db) B to db A.

First, we need to setup the dblink so that db B is visible from db A. Therefore in db A we execute:

  CONNECT TO <my_username> IDENTIFIED BY <my_password> USING '(DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <B domain or IP>)(PORT = 1521))
      (LOAD_BALANCE = yes)
       (SERVICE_NAME = <B service name>)

A quick check in db A can verify whether the dblink is actually working without any (networking) issue:


Finally our block to copy the data across:


How to add a primary key and a sequence in Oracle

First let’s create the table column that is going to serve as our primary key:


Next, let’s create the sequence:

MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCACHE

then adding this sequence to the column to serve as a primary key:


and finally adding the primary key constraint to the column:


And here’s the rollback process:


Note: I like to use SQLDeveloper as an Oracle DB IDE that helps me visualise all the Oracle DB constructs.