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

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

while testing the connection yields success:

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

and also how it appears from Oracle Modeler:

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:

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:

                     

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.

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:

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

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:

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:

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.

INSERT INTO SELECT Oracle PL/SQL faux pas

I’ve recently stumbled upon a bug that came down to the following piece of code:

INSERT INTO TABLE_A (
   SELECT ALPHA,
          GAMMA,
          BETA
   FROM TABLE_B
)
;

This is an INSERT query with respect to the column order of the table. In general the order of the columns in the table shouldn’t be important on our queries therefore the following more robust version of INSERT is guarding against ambiguity relying on the column order:

INSERT INTO TABLE_A (
   ALPHA,
   BETA,
   GAMMA
) VALUES
(  SELECT ALPHA,
          BETA,
          GAMMA
   FROM TABLE_B
)
;

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:

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

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

SELECT 1 FROM DUAL@B_DBLINK
;

Finally our block to copy the data across:

  BEGIN
  INSERT INTO OUR_TABLE
  (
    SELECT CA, CB, CC, CD
    FROM OUR_OTHER_TABLE@B_DBLINK
  );
  COMMIT;
  end;

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:

ALTER TABLE OUR_TABLE
ADD (
PKEY	NUMBER
)
;

Next, let’s create the sequence:

CREATE SEQUENCE CC_OUR_TABLE_PKEY_SEQ INCREMENT BY 1
MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCACHE
;

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

UPDATE OUR_TABLE
SET PKEY = CC_OUR_TABLE_PKEY_SEQ.NEXTVAL
;

and finally adding the primary key constraint to the column:

ALTER TABLE OUR_TABLE
ADD CONSTRAINT OUR_TABLE_PK PRIMARY KEY (PKEY)
;

And here’s the rollback process:

--UNDO PROCESS
--ALTER TABLE OUR_TABLE DROP COLUMN PKEY;
--DROP SEQUENCE CC_OUR_TABLE_PKEY_SEQ;
--COMMIT;

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