Dimitrios Kechagias

I recently added Oracle Database support to SQL::Inserter (check it out if you'd like simple to use, high-performance inserting to SQL databases). I had not used an Oracle Database since my uni days 20 years ago, so I had to set one up to test it.

Even though Oracle provides a free development DB, the process is not as simple as Postgres/MySQL etc., so I thought I'd document it for future reference.

There are basically two ways you can go, with Oracle providing instructions either for a VirtualBox VM, or Docker. For the purposes of this article, we'll use VirtualBox. If you prefer Docker, you can follow Oracle's instructions and skip the next section.

Setting up the Oracle VM

Oracle provides instructions for setting up a VM with their latest 23c Database.

To sum up, you download and install VirtualBox, as well as the 23c VM image (.ova).

Launch VirtualBox, go to File->Import Appliance and select the .ova file that you just downloaded. You can leave the defaults for the import.

Start the Oracle Linux based VM.

Setting up Perl DBI/DBD::Oracle

Open a terminal on the Oracle VM. You have sudo powers (password is oracle), but first we'll need to set up the ORACLE_SID and ORACLE_HOME environment variables. To determine what they should be do:

> cat /etc/oratab

You are looking for an uncommented line with three parts separated by colons, on this VM it is: FREE:/opt/oracle/product/23c/dbhomeFree:Y, with the first part being the SID, the second being the HOME. We can add these to our .bashrc:

 > echo 'export ORACLE_SID=FREE' >> ~/.bashrc
 > echo 'export ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree' >> ~/.bashrc
 > echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib' >> ~/.bashrc
 > source ~/.bashrc

We can now install the required libaio and cpanm - using the latter to install the CPAN packages:

> sudo yum install -y libaio-devel perl-App-cpanminus
> sudo -E bash -c 'cpanm -n DBI DBD::Oracle'

The -E parameter passes the env variables we set up above. We are all set to start development.

Note that this Oracle Linux VM comes with Perl 5.26, if you want a more recent version, you could use use Perlbrew.

Connecting to the DB using Perl/DBI

The html page that opens on the first launch of the Oracle VM gives you the db and user names already set up. Using those, you can connect to the DB through Perl/DBI with the DBD::Oracle driver:

#!/usr/bin/env perl
use strict;
use warnings;

use DBI;

# Connect to the pre-created PDB 'freepdb1'
my $dbname = "freepdb1";
my $user   = "hr";
my $pass   = "oracle";
my $dbh    = DBI->connect("dbi:Oracle:$dbname", $user, $pass);

# Or connect to the CDB 'free'
my $user = "system";
my $pass = "oracle";
my $sid  = "free";
my $dbh  = DBI->connect("dbi:Oracle:host=localhost;sid=$sid", $user, $pass);