in Programming

Playing with Oracle and Docker

I’m a software developer and I’m working with Oracle’s PL-SQL for more than 10 years now.

Every time I need a development environement I have to setup a virtual machine with Oracle installed: in the past years I moved from a full-manual solution (using Virtual Box + Ubuntu + Oracle manual setup) to an automated version (with Vagrant) but today I’m using container technology (thanks Docker).

I want to focus this article on having a quick up-and-running setup to develop PL-SQL object, assuming that you can satisfy by yourself the prerequistes.

You can look here if you want to install Docker on OS X (like I do), here for installing Docker on Ubuntu and here to setup Docker on Windows.

Once you have installed Docker, you are ready to go.

If you want to learn more about Docker I can suggest you their tutorial here.

And if you have time and want to learn how to manually install Oracle on Ubuntu you can look at here.

But for this post goal, Docker up and running is more than sufficent.

Client side, to develope you objects, I suggest you to install
SqlDeveloper wich is free Java IDE created by Oracle.

So, let’s start.

Creating Docker machine

In this article we’ll clonate an image available here.

It’s a full functioning image of Oracle xe 11g, installed over Ubuntu.

First start your Docker machine (if you are usint OS X or Linux):

docker-machine start

Then get the IP address of your Docker machine by the command:

docker-machine ip dev
192.168.99.100

And last download and run the image of the desidered container:

docker run -d -p 49160:22 -p 49161:1521 -p 46162:8080 wnameless/oracle-xe-11g

Now you would have an up and running Oracle instance, to which you can connect by Oracle SqlDeveloper:

hostname: localhost
port: 49161
sid: xe
username: system
password: oracle

You can connect also via ssh…

ssh root@92.168.99.100 -p 49160
password: admin

… or web management service:

http://192.168.99.100:46162/apex
workspace: internal
username: ADMIN
password: oracle

And that’s all!

Some examples

First create two example tables, named PEOPLE and ANIMALS

CREATE TABLE people
( 
  id number NOT NULL,
  name VARCHAR(50) NOT NULL
);
CREATE TABLE animals
( 
  id number NOT NULL,
  name VARCHAR(50) NOT NULL,
  id_owner number
);

Then you want to create two sequences to manage ID fields of each table:

CREATE SEQUENCE seq_people
 START WITH     0
 INCREMENT BY   1
 MINVALUE 0
 NOCACHE
 NOCYCLE;
CREATE SEQUENCE seq_animals
 START WITH     0
 INCREMENT BY   1
 MINVALUE 0
 NOCACHE
 NOCYCLE;

And last, create two trigger to automatically use the value of the sequences to manage ID fields for each table:

CREATE OR REPLACE TRIGGER trg_people_on_insert
BEFORE -- AFTER
INSERT -- OR UPDATE OR DELETE
ON people
FOR EACH ROW
BEGIN
  :new.id := seq_people.nextval;
  IF (:new.name is null) THEN
    :new.name := :new.id;
  END IF;
END;
CREATE OR REPLACE TRIGGER trg_animals_on_insert
BEFORE -- AFTER
INSERT -- OR UPDATE OR DELETE
ON animals
FOR EACH ROW
BEGIN
  :new.id := seq_animals.nextval;
  IF (:new.name is null) THEN
    :new.name := :new.id;
  END IF;
END;

Ok, now you have the objects you need, try to populate them:

INSERT INTO people(name) VALUES('JOHN'); 
INSERT INTO people(name) VALUES('LUKE'); 
INSERT INTO people(name) VALUES('MATTHEW'); 
INSERT INTO people(name) VALUES('MARK'); 
INSERT INTO animals(name, id_owner) VALUES ('CAT',1); 
INSERT INTO animals(name, id_owner) VALUES ('DOG',2);
INSERT INTO animals(name, id_owner) VALUES ('HORSE',3); 
INSERT INTO animals(name, id_owner) VALUES ('LION',null); 
INSERT INTO animals(name, id_owner) VALUES ('ZEBRA',null); 
COMMIT;

… and test if the datas are ok:

SELECT * FROM people;
SELECT * FROM animals;

In the end, run the following query to extract all persons without animals…

SELECT p.name people_name FROM people p left outer join animals a on p.id = a.id_owner WHERE a.id_owner is null;

… and all animals without owners…

SELECT a.name animal_name FROM people p right outer join animals a on p.id = a.id_owner WHERE p.id is null;

That’s it!