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.
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):
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 email@example.com -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!
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;