Groovy, Oracle example

Please refer to my previous posts on how to get Oracle XE installed and a demonstration of the HR Schema Sample Data.

The dynamic nature of Groovy with built-in support for Sql really shines on quick scripts like this example:

def printHeader(name, sql){
    println """\nThere are
             ${sql.firstRow("SELECT COUNT(*) TOTAL FROM ${name}".toString()).TOTAL}
             total ${name.toLowerCase()}."""
    println "\n${name}\n${"=".multiply(name.size())}"
}

import groovy.sql.Sql
sql = Sql.newInstance("jdbc:oracle:thin:@127.0.0.1:1521:orcl",
                "hr", "oracle", "oracle.jdbc.pool.OracleDataSource")


printHeader("EMPLOYEES", sql)
sql.eachRow("SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES") {
            println "${it.FIRST_NAME} ${it.LAST_NAME}"
        }

printHeader("COUNTRIES", sql)
sql.eachRow("SELECT COUNTRY_NAME FROM COUNTRIES") {
    println it.COUNTRY_NAME
}

printHeader("DEPARTMENTS", sql)
sql.eachRow("SELECT DEPARTMENT_NAME FROM DEPARTMENTS") {
    println it.DEPARTMENT_NAME
}

printHeader("REGIONS", sql)
sql.eachRow("SELECT REGION_NAME FROM REGIONS") {
    println it.REGION_NAME
}

Please note that the ojdbc jar is provided in the classpath.

Pay attention on the easiness by which we create the connection doing the needed import on the spot:

import groovy.sql.Sql
sql = Sql.newInstance("jdbc:oracle:thin:@127.0.0.1:1521:orcl",
                "hr", "oracle", "oracle.jdbc.pool.OracleDataSource")

Also the easiness and compactness by which we iterate through the result set via the help of closures and the default it reference is unparalleled in the ecosystem of JVM languages:

sql.eachRow("SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES") {
            println "${it.FIRST_NAME} ${it.LAST_NAME}"
        }

For the demo purposes I’ve created a function that is creating the printed header output. It is accepting Sql instance on the input parameters which is not advisable but I wanted to demonstrate the powerful GString injection within a String literal:

def printHeader(name, sql){
    println """\nThere are
             ${sql.firstRow("SELECT COUNT(*) TOTAL FROM ${name}".toString()).TOTAL}
             total ${name.toLowerCase()}."""
    println "\n${name}\n${"=".multiply(name.size())}"
}

Please note how we inject the table name on the SQL literal and also how we are populating the underlying of the table name header using the size of the table name string. Moreover by surrounding the literal with triple double-quotes we can expand across multiple lines without needing implicit/explicit concatenation.

The above code yields output:

There are 107 total employees.


EMPLOYEES
=========
Ellen Abel
Sundar Ande
Mozhe Atkinson
David Austin
Hermann Baer
Shelli Baida
Amit Banda
Elizabeth Bates
Sarah Bell
David Bernstein
Laura Bissot
Harrison Bloom
Alexis Bull
Anthony Cabrio
Gerald Cambrault
...

There are 25 total countries.

COUNTRIES
=========
Argentina
Australia
Belgium
Brazil
Canada
Switzerland
China
Germany
Denmark
Egypt
France
HongKong
Israel
India
Italy
Japan
Kuwait
Mexico
Nigeria
Netherlands
Singapore
United Kingdom
United States of America
Zambia
Zimbabwe

There are 27 total departments.

DEPARTMENTS
===========
Administration
Marketing
Purchasing
Human Resources
Shipping
IT
Public Relations
Sales
Executive
Finance
Accounting
Treasury
Corporate Tax
Control And Credit
Shareholder Services
Benefits
Manufacturing
Construction
Contracting
Operations
IT Support
NOC
IT Helpdesk
Government Sales
Retail Sales
Recruiting
Payroll

There are 4 total regions.

REGIONS
=======
Europe
Americas
Asia
Middle East and Africa

Spring, JdbcTemplate, Oracle example

This is a demonstration of how Spring and Oracle are playing happily together via JdbcTemplate.

Prerequisites:

Let’s start from the end: this is how the JUnit 4 test case looks like for the DAO of our domain object for all its CRUD operations armored with rollback functionality so our database data are not being altered after our transactional operations. Notice the use of matchers and the spring-context-enabling annotation of the test class:

package com.dimitrisli.springJdbcOracle.dao.impl;

import com.dimitrisli.springJdbcOracle.dao.LocationDao;
import com.dimitrisli.springJdbcOracle.model.Location;
import org.junit.Test;
import org.junit.runner.RunWith;
import static org.junit.Assert.*;
import static org.hamcrest.CoreMatchers.*;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.transaction.TransactionConfiguration;
import org.springframework.transaction.annotation.Transactional;

import javax.inject.Inject;
import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "/spring/context/applicationContext.xml")
@TransactionConfiguration(transactionManager = "jdbcTransactionManager", defaultRollback = true)
@Transactional
public class LocationDaoTest {

    @Inject private LocationDao locationDao;

    @Test
    public void testSelectAllLocations(){
    List<Location> locations = locationDao.getLocations();
    assertThat(locations.size(), is(23));
    }

    @Test
    public void testSelectOneLocation(){
      Location location = locationDao.getLocation(1000L);
      assertNotNull("test entry not found", location);
    }

    @Test
    public void testDeleteLocation(){
        assertNotNull("entry for test should be there", locationDao.getLocation(1000L));
        locationDao.deleteLocation(1000L);
        assertNull("entry wasn't successfully deleted", locationDao.getLocation(1000L));
    }

    @Test
    public void testInsertLocation(){
        Location location = new Location(1000L,"test","11111","athens","athens","IT");
        int sizeBeforeInsert = locationDao.getLocations().size();
        locationDao.createLocation(location);
        assertThat(locationDao.getLocations().size(),is(sizeBeforeInsert + 1));
    }

    @Test
    public void testUpdateLocation(){
        Location newLocation = new Location(1000L,"test","11111","athens","athens","IT");
        locationDao.updateLocation(newLocation);
        Location changedLocation = locationDao.getLocation(1000L);
        assertThat(changedLocation.getStreetAddress(), is("test"));
    }

}

The POM looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>SpringJdbcOracle</groupId>
    <artifactId>SpringJdbcOracle</artifactId>
    <version>1.0</version>


    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.3.2</version>
                <configuration>
                    <source>1.6</source>
                    <target>1.6</target>
                    <encoding>${project.build.sourceEncoding}</encoding>
                </configuration>
            </plugin>

            <!--Logging related plugin
                this plugin breaks the build if non-wanted logging frameworks are spotted in the classpath
            -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-enforcer-plugin</artifactId>
                <version>1.0.1</version>
                <executions>
                    <execution>
                        <id>enforce-versions</id>
                        <goals>
                            <goal>enforce</goal>
                        </goals>
                        <configuration>
                            <rules>
                                <bannedDependencies>
                                    <excludes>
                                        <exclude>commons-logging:commons-logging</exclude>
                                        <exclude>log4j:log4j</exclude>
                                    </excludes>
                                </bannedDependencies>
                            </rules>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <!--Spring related dependencies -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>commons-logging</groupId>
                    <artifactId>commons-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>commons-logging</groupId>
                    <artifactId>commons-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>javax.inject</groupId>
            <artifactId>javax.inject</artifactId>
            <version>1</version>
        </dependency>


        <!--Oracle jdbc driver-->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>

        <!-- DB Connection Pool -->
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>

        <!--Logging related dependencies
            Further info: http://www.slf4j.org/faq.html#excludingJCL and
                          http://blog.frankel.ch/configuring-maven-to-use-slf4j
        -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <!--scope should be runtime but applied at compile time
                to get autocompletion visibility at logback.xml-->
            <!--scope>runtime</scope-->
            <version>0.9.24</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.6.1</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>jcl-over-slf4j</artifactId>
            <version>1.7.2</version>
        </dependency>

        <!-- JUnit 4 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.10</version>
        </dependency>

        <!-- Misc -->
        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>2.2.2</version>
            <scope>runtime</scope>
        </dependency>


    </dependencies>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <spring.version>3.1.2.RELEASE</spring.version>
     </properties>


</project>

Notes:
– Note how we explicitly piping any commons logging or log4j logging through our logback slf4j wrapper
– Notice how we are declaring our Oracle driver dependency given that it’s already installed in our local Maven repo (see prerequisites section)

The applicationContext:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <import resource="classpath*:spring/database/database.xml"/>
    <context:component-scan base-package="com.dimitrisli.springJdbcOracle" />
</beans>

The imported database context above is:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:c="http://www.springframework.org/schema/c"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

    <bean  class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
           p:location="properties/database.properties" />

    <bean  id="dataSource"
           class="org.apache.commons.dbcp.BasicDataSource"
           destroy-method="close"
           p:driverClassName="${jdbc.driverClassName}"
           p:url="${jdbc.url}"
           p:username="${jdbc.username}"
           p:password="${jdbc.password}" />

    <bean  class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"
           c:dataSource-ref="dataSource"  />

    <bean  id="jdbcTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
            p:dataSource-ref="dataSource"/>
    <tx:annotation-driven transaction-manager="jdbcTransactionManager"/>

</beans>

Notes:

– We are using DBCP for our DB connection pool datasource
– We are using the parameter namespace to save on some attribute injection open-close xml characters
– We are explicitly stating NamedParameterJdbcTemplate as our jdbcTemplate and inject it with the needed datasource so we can have it available for injection conveniently in our DAOs.

The domain POJO object we are about to play with is the Location object that corresponds to the Locations table on the Oracle HR schema:


package com.dimitrisli.springJdbcOracle.model;

public class Location {

    private Long locationId;
    private String streetAddress;
    private String postalCode;
    private String city;
    private String stateProvince;
    private String countryId;

    public Location(Long locationId, String streetAddress, String postalCode, String city, String stateProvince, String countryId) {
        this.locationId = locationId;
        this.streetAddress = streetAddress;
        this.postalCode = postalCode;
        this.city = city;
        this.stateProvince = stateProvince;
        this.countryId = countryId;
    }

//getters, hashcode(), equals(), toString() ignored for brevity

The RowMapper that will provide Location objects having resultSets coming from the DB. It’s a factory method (although not static but stateless by our design) used internally from Spring per DB line result returned:

package com.dimitrisli.springJdbcOracle.orm;

import com.dimitrisli.springJdbcOracle.model.Location;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;

@Component
public class LocationRowMapper implements RowMapper<Location> {

    @Override
    public Location mapRow(ResultSet rs, int rowNum) throws SQLException {
        return  new Location(rs.getLong("LOCATION_ID"),
                             rs.getString("STREET_ADDRESS"),
                             rs.getString("POSTAL_CODE"),
                             rs.getString("CITY"),
                             rs.getString("STATE_PROVINCE"),
                             rs.getString("COUNTRY_ID"));
    }
}

Here’s the DAO interface responsible for the CRUD operations:

package com.dimitrisli.springJdbcOracle.dao;

import com.dimitrisli.springJdbcOracle.model.Location;

import java.util.List;

public interface LocationDao {

    public void createLocation(Location location);
    public List<Location> getLocations();
    public Location getLocation(Long locationId);
    public void updateLocation(Location location);
    public void deleteLocation(Long locationId);

}

and its implementation looks like this:


package com.dimitrisli.springJdbcOracle.dao.impl;

import com.dimitrisli.springJdbcOracle.dao.LocationDao;
import com.dimitrisli.springJdbcOracle.model.Location;
import com.dimitrisli.springJdbcOracle.orm.LocationRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.inject.Inject;
import java.util.HashMap;
import java.util.List;

@Repository("locationDao")
public class LocationDaoImpl implements LocationDao {

    private static final String CREATE_SQL = "INSERT INTO LOCATIONS( LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, " +
                                             "STATE_PROVINCE, COUNTRY_ID) " +
                                             "VALUES (LOCATIONS_SEQ.NEXTVAL, :streetAddress, :postalCode, :city, " +
                                             ":stateProvince, :countryId)";

    private static final String GET_ALL_SQL = "SELECT LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID " +
                                              "FROM LOCATIONS";

    private static final String GET_SQL = "SELECT LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID " +
                                          "FROM LOCATIONS WHERE LOCATION_ID = :locationId";

    private static final String DELETE_SQL = "DELETE LOCATIONS WHERE LOCATION_ID = :locationId";

    private static final String UPDATE_SQL = "UPDATE LOCATIONS SET STREET_ADDRESS = :streetAddress, POSTAL_CODE=:postalCode, " +
                                            "CITY = :city, STATE_PROVINCE = :stateProvince, COUNTRY_ID = :countryId " +
                                            "WHERE LOCATION_ID = :locationId";

    @Inject private NamedParameterJdbcOperations jdbcTemplate;
    @Inject private LocationRowMapper locationRowMapper;

    @Override
    public void createLocation(Location location) {
        SqlParameterSource params = new MapSqlParameterSource()
                .addValue("streetAddress", location.getStreetAddress())
                .addValue("postalCode", location.getPostalCode())
                .addValue("city", location.getCity())
                .addValue("stateProvince", location.getStateProvince())
                .addValue("countryId", location.getCountryId());
        jdbcTemplate.update(CREATE_SQL, params);
    }

    @Override
    public List<Location> getLocations() {
        return jdbcTemplate.query(GET_ALL_SQL, new HashMap<String, Object>(), locationRowMapper);
    }

    @Override
    public Location getLocation(Long locationId) {
        SqlParameterSource params = new MapSqlParameterSource()
                .addValue("locationId", locationId);
        List<Location> locations = jdbcTemplate.query(GET_SQL, params, locationRowMapper);
        return locations.isEmpty()?null:locations.get(0);
    }

    @Override
    public void updateLocation(Location location) {
        SqlParameterSource params = new MapSqlParameterSource()
                .addValue("locationId", location.getLocationId())
                .addValue("streetAddress", location.getStreetAddress())
                .addValue("postalCode", location.getPostalCode())
                .addValue("city", location.getCity())
                .addValue("stateProvince", location.getStateProvince())
                .addValue("countryId", location.getCountryId());
        jdbcTemplate.update(UPDATE_SQL, params);
    }

    @Override
    public void deleteLocation(Long locationId) {
        jdbcTemplate.update(DELETE_SQL, new MapSqlParameterSource("locationId",locationId));
    }
}

Notes:
– Notice how we inject the JdbcTemplate and not fetching it from this class directly
– Notice how we inject the RowMapper and we don’t anonymous-class-it from this class directly
– The CRUD operations are setup parameterized in the top of the file as private static finals
– In all the CRUD operations we are using either jdbcTemplate.update() or jdbcTemplate.query() methods

Here’s the Github repo of the project

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

Maven install ojdbc6

I really wished the Oracle driver jar was part of any (legal) publicly available Maven repo, but it’s not. So we’ll have to take matters on our hands and install it in our local repo once and for all so we can effortlessly thereafter summon it via our pom file:

       <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>

Steps:

  • Download the jdbc6.jar from the Oracle website. I tried to automate this step via a Groovy script but this pesky agreement radio-button gets in the way (which is there for a reason to be fair)
  • Supposing mvn is already setup in your path:
  • mvn install:install-file -Dfile=ojdbc6.jar -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.3 -Dpackaging=jar -DgeneratePom=true
    

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.

Introduction

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

VirtualBox-1

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.

25Apr14-1-importOvaToVirtualBox

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:

25Apr14-2-importedIntoVirtualBox

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:

25Apr14-3-loginScreen

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

When the OS boots up we are ready to go:

25Apr14-4-loggedIn

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

25Apr14-7-accessFirewall

 

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

25Apr14-8-FirewallDisabled

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

25Apr14-9-FirewallPort1521Trusted

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 (127.0.0.1) to the standard Oracle communication default 1521 TCP port to be forwarded to our Oracle VM with IP address 10.0.2.15 on the same port. The IP of the Oracle VM can be found by issuing ipconfig command on the terminal (3rd line, inet addr):

25Apr14-13-findTheIPAddr

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:

25Apr14-15-telnet

 

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):

25Apr14-11-HRDBFromIntelliJ

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

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

jdbc:oracle:thin:@//127.0.0.1:1521/PDB1

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

jdbc:oracle:thin:@//127.0.0.1:1521:orcl

Step 6c: Testing external connectivity – via SQL Developer

SQL Developer 4 @ Mac OS X Mavericks:

25Apr14-12-HRDBFromSQLDeveloperIMac

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

Conclusion

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.