How To Connect your Node.js app in Docker to your Oracle Database

August 27, 2019 bcarter 11 Comments

In this post I’ll cover how to make a connection from a Node.js application in a Docker container to your Oracle Database in three different scenarios.

Typical Database connection.
Database inside a different Docker container.
Oracle Autonomous Transaction Processing Cloud Database.

Simple Test App

When attempting something new, I find it’s best to make sure all of the pieces that are not part of the new thing, are as simple as possible.

For my examples I will be using a small Node.js application that:

Connects to the Oracle Database.
Runs a simple query.
Prints the results to the console.

Create a new file named dbConfig.js to set the database connection information.

module.exports = {
  user          : process.env.NODE_ORACLEDB_USER,
  password      : process.env.NODE_ORACLEDB_PASSWORD,
  connectString : process.env.NODE_ORACLEDB_CONNECTIONSTRING,
  poolMax: 2,
  poolMin: 2,
  poolIncrement: 0
};

module.exports = {

user : process.env.NODE_ORACLEDB_USER,

password : process.env.NODE_ORACLEDB_PASSWORD,

connectString : process.env.NODE_ORACLEDB_CONNECTIONSTRING,

poolMax: 2,

poolMin: 2,

poolIncrement: 0

};

Create a new file named server.js used to test the connection.

const oracledb = require('oracledb');
const config = require('./dbConfig.js');

async function runTest() {
  let conn;

  try {
    conn = await oracledb.getConnection(config);

    const result = await conn.execute(
      'select current_timestamp from dual'
    );

    console.log(result);
  } catch (err) {
    console.error(err);
  } finally {
    if (conn) {
      try {
        await conn.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

runTest();

const oracledb = require('oracledb');

const config = require('./dbConfig.js');

async function runTest() {

let conn;

try {

conn = await oracledb.getConnection(config);

const result = await conn.execute(

'select current_timestamp from dual'

);

console.log(result);

} catch (err) {

console.error(err);

} finally {

if (conn) {

try {

await conn.close();

} catch (err) {

console.error(err);

}

runTest();

Install the node-oracledb driver. (If you have any questions about the node-oracledb driver, you can find the answers here.)

npm install oracledb -s

1	npm install oracledb -s

Create environment variables for the connection information. (Replace my values with yours)

export NODE_ORACLEDB_USER=NotMyRealUser
export NODE_ORACLEDB_PASSWORD=NotMyRealPassword
export NODE_ORACLEDB_CONNECTIONSTRING=192.168.0.44:1521/NotMyRealServiceName

export NODE_ORACLEDB_USER=NotMyRealUser

export NODE_ORACLEDB_PASSWORD=NotMyRealPassword

export NODE_ORACLEDB_CONNECTIONSTRING=192.168.0.44:1521/NotMyRealServiceName

Test the Node.js application. You should see something similar to the following.

@OraBlaineOS:SimpleApp$ node server.js 
{
  metaData: [ { name: 'CURRENT_TIMESTAMP' } ],
  rows: [ [ 2019-08-20T17:50:38.615Z ] ]
}

@OraBlaineOS:SimpleApp$ node server.js

{

metaData: [ { name: 'CURRENT_TIMESTAMP' } ],

rows: [ [ 2019-08-20T17:50:38.615Z ] ]

}

Create a Docker Container for the Node.js app

Create a Dockerfile

FROM oraclelinux:7-slim

# Create app directory
WORKDIR /usr/src/app

# Copy the .js files from your host machine into the new app directory
ADD *.js ./

# Update Oracle Linux
# Install Node.js
# Install the Oracle Instant Client
# Check that Node.js and NPM installed correctly
# Install the OracleDB driver
RUN yum update -y && \
  yum install -y oracle-release-el7 && \
  yum install -y oracle-nodejs-release-el7 && \
  yum install -y --disablerepo=ol7_developer_EPEL nodejs && \
  yum install -y oracle-instantclient19.3-basic.x86_64 && \
  yum clean all && \
  node --version && \
  npm --version && \
  npm install oracledb && \
  echo Installed

CMD ["node", "server.js"]

FROM oraclelinux:7-slim

# Create app directory

WORKDIR /usr/src/app

# Copy the .js files from your host machine into the new app directory

ADD *.js ./

# Update Oracle Linux

# Install Node.js

# Install the Oracle Instant Client

# Check that Node.js and NPM installed correctly

# Install the OracleDB driver

RUN yum update -y && \

yum install -y oracle-release-el7 && \

yum install -y oracle-nodejs-release-el7 && \

yum install -y --disablerepo=ol7_developer_EPEL nodejs && \

yum install -y oracle-instantclient19.3-basic.x86_64 && \

yum clean all && \

node --version && \

npm --version && \

npm install oracledb && \

echo Installed

CMD ["node", "server.js"]

Build the image

docker build --no-cache --force-rm=true -t node_example .

1	docker build --no-cache --force-rm=true -t node_example .

Now that you have an image you’ll run a container and to connect it to:

Typical Oracle Database

docker run \
--name typical_node_example -it \
-e NODE_ORACLEDB_USER=$NODE_ORACLEDB_USER \
-e NODE_ORACLEDB_PASSWORD=$NODE_ORACLEDB_PASSWORD \
-e NODE_ORACLEDB_CONNECTIONSTRING=$NODE_ORACLEDB_CONNECTIONSTRING \
node_example

docker run \

--name typical_node_example -it \

-e NODE_ORACLEDB_USER=$NODE_ORACLEDB_USER \

-e NODE_ORACLEDB_PASSWORD=$NODE_ORACLEDB_PASSWORD \

-e NODE_ORACLEDB_CONNECTIONSTRING=$NODE_ORACLEDB_CONNECTIONSTRING \

node_example

The container will run, execute the query and stop. You should see something similar to this output.

{ metaData: [ { name: 'CURRENT_TIMESTAMP' } ],
  rows: [ [ 2019-08-20T18:15:23.615Z ] ] }

1 2	{ metaData: [ { name: 'CURRENT_TIMESTAMP' } ], rows: [ [ 2019-08-20T18:15:23.615Z ] ] }

An Oracle Database inside a Docker Container

In a previous post I walked through how to setup both an ORDS instance and an Oracle XE instance in Docker Containers. Follow through the section showing how to create an Oracle XE Database in a Docker container.

When you followed the steps in that post you should have also created a Docker Network.

docker network create OrdsXeNet

1	docker network create OrdsXeNet

You’ll use that same network to connect from the Node.js container to the Oracle XE container.

First, change your NODE_ORACLEDB_CONNECTIONSTRING environment variable to use the Oracle XE container name.

export NODE_ORACLEDB_CONNECTIONSTRING=oracleXe:1521/xepdb1

1	export NODE_ORACLEDB_CONNECTIONSTRING=oracleXe:1521/xepdb1

Now when you run a new docker container you will attach it to the same Docker Network as the Oracle XE container.

docker run \
--name docker_network_node_example -it \
--network=OrdsXeNet \
-e NODE_ORACLEDB_USER=$NODE_ORACLEDB_USER \
-e NODE_ORACLEDB_PASSWORD=$NODE_ORACLEDB_PASSWORD \
-e NODE_ORACLEDB_CONNECTIONSTRING=$NODE_ORACLEDB_CONNECTIONSTRING \
node_example

docker run \

--name docker_network_node_example -it \

--network=OrdsXeNet \

-e NODE_ORACLEDB_USER=$NODE_ORACLEDB_USER \

-e NODE_ORACLEDB_PASSWORD=$NODE_ORACLEDB_PASSWORD \

-e NODE_ORACLEDB_CONNECTIONSTRING=$NODE_ORACLEDB_CONNECTIONSTRING \

node_example

The container will run, execute the query and stop. You should see something similar to this output.

{ metaData: [ { name: 'CURRENT_TIMESTAMP' } ],
  rows: [ [ 2019-08-27T16:42:16.122Z ] ] }

1 2	{ metaData: [ { name: 'CURRENT_TIMESTAMP' } ], rows: [ [ 2019-08-27T16:42:16.122Z ] ] }

Since both containers are using the same Docker network, you do not need to open the port when you run the Oracle XE container. This is useful if you’d like to keep your database private inside the Docker environment.

Oracle Autonomous Transaction Processing Cloud Database

After you create an Oracle ATP Cloud Database you’ll need to download the credentials file and extract it into a secure location.

For this database, you will be using an entry in the tnsnames.ora file to make the database connection. Change the NODE_ORACLEDB_CONNECTIONSTRING environment variable to use the tnsnames entry:

export NODE_ORACLEDB_CONNECTIONSTRING=projects_tp

1	export NODE_ORACLEDB_CONNECTIONSTRING=projects_tp

Before you run the container, you need to modify the sqlnet.ora file found in the credentials directory. Change the wallet location to the value that will be mapped inside the Docker container.

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/wallet/")))

1	WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/wallet/")))

When you run the new container for the node app you will map a volume from the directory where you extracted your credentials file to the internal container directory. Mapping a volume makes sure that there is not a copy of the credential files inside the Docker container.

-v /my/host/machine/credentials/directory:/wallet

1	-v /my/host/machine/credentials/directory:/wallet

Finally, when you run the new container, you will add an additional environment variable defining where the tns admin information is located.

-e TNS_ADMIN=/wallet

1	-e TNS_ADMIN=/wallet

The full command looks like this:

docker run \
--name cloud_atp_node_example -it \
-v /my/host/machine/credentials/directory:/wallet \
-e NODE_ORACLEDB_USER=$NODE_ORACLEDB_USER \
-e NODE_ORACLEDB_PASSWORD=$NODE_ORACLEDB_PASSWORD \
-e NODE_ORACLEDB_CONNECTIONSTRING=$NODE_ORACLEDB_CONNECTIONSTRING \
-e TNS_ADMIN=/wallet \
-p 3000:3000 \
node_example

docker run \

--name cloud_atp_node_example -it \

-v /my/host/machine/credentials/directory:/wallet \

-e NODE_ORACLEDB_USER=$NODE_ORACLEDB_USER \

-e NODE_ORACLEDB_PASSWORD=$NODE_ORACLEDB_PASSWORD \

-e NODE_ORACLEDB_CONNECTIONSTRING=$NODE_ORACLEDB_CONNECTIONSTRING \

-e TNS_ADMIN=/wallet \

-p 3000:3000 \

node_example

The container will run, execute the query and stop. You should see something similar to this output.

{ metaData: [ { name: 'CURRENT_TIMESTAMP' } ],
  rows: [ [ 2019-08-27T18:21:26.298Z ] ] }

1 2	{ metaData: [ { name: 'CURRENT_TIMESTAMP' } ], rows: [ [ 2019-08-27T18:21:26.298Z ] ] }

I used the same docker image for all of the examples

You may have noticed that you only built the Docker image once at the beginning of the post. The application in the Docker image uses environment variables to connect to the database. This allows you to run the same application in one or more containers and each container can connect to different Oracle Databases without having to rebuild the image.

Autonomous, REST

Oracle REST Data Services on Autonomous Transaction Processing Database

July 1, 2019 bcarter Leave a comment

Whenever I give a presentation, write a blog post or make a video I like to run all of my examples and demos locally and on an Oracle Cloud instance. I keep a Virtual Box machine or Docker Container ready on my laptop, just in case I can’t get to the internet, but for speed and dependability, you just can’t beat a cloud instance.

I like to have some automation scripts handy to create and destroy my environments and depending on what I’m doing that can sometimes take a while.

So when Oracle’s Autonomous Transaction Processing Database was released I was excited by how fast I could spin up a new instance. It takes a few minutes instead of around an hour for other options. Oracle wanted to make sure ATP was solid, so they initially launched with a limited feature set and new features have been making their way into Oracle’s Autonomous Transaction Processing Database almost every month.

At KScope19 it was announced that Oracle REST Data Services is now available on ATP.

I was scheduled to give my ‘Creating REST APIs Is Fast, Easy, and Secure with Oracle REST Data Services‘ talk on Wednesday and I like to live dangerously, so I decide (Tuesday afternoon) to convert my demos over to ORDS on ATP. I figured worst case, if I couldn’t figure it out I’d just use my Docker container.

It’s going to be hard… right?

I already had a couple of ATP instances that I use for stuff and things. Since I wanted to do a live demo, I decided to spin up a new instance just in case I expose anything that could be used to get in. It took under 5 minutes to fully create and start up a brand new ATP database. After that, I downloaded my Client Credentials (Wallet) and connected with SQL Developer. There are lots of examples for this part so I’ll move on to the new stuff.

New User

I try to do as little as possible as the Admin user so I created a rest_demo user with a couple of tables and a view.

CREATE USER rest_demo IDENTIFIED BY NotMyRealPW;
ALTER USER rest_demo TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION, RESOURCE, UNLIMITED TABLESPACE TO rest_demo;

GRANT CREATE TABLE,
CREATE VIEW,
CREATE SEQUENCE,
CREATE PROCEDURE,
CREATE TYPE,
CREATE SYNONYM
TO rest_demo;

CREATE TABLE REST_DEMO.COOL_DATA_T (
  ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
  NAME VARCHAR2(20) NOT NULL,
  MINUTES NUMBER,
  CONSTRAINT COOL_DATA_PK PRIMARY KEY (ID) ENABLE
);

insert into REST_DEMO.cool_data_t(name, minutes) values ('first', 24);
insert into REST_DEMO.cool_data_t(name, minutes) values ('second', 23);
insert into REST_DEMO.cool_data_t(name, minutes) values ('third', 52);
insert into REST_DEMO.cool_data_t(name, minutes) values ('fourth', 235);
insert into REST_DEMO.cool_data_t(name, minutes) values ('fifth', 874);

commit;

CREATE or replace view REST_DEMO.COOL_DATA_V as select ID, NAME, MINUTES from REST_DEMO.cool_data_t;

CREATE TABLE REST_DEMO.GALLERY (
  ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL ,
  TITLE VARCHAR2(1000) NOT NULL ENABLE,
  CONTENT_TYPE VARCHAR2(1000) NOT NULL ENABLE,
  IMAGE BLOB NOT NULL ENABLE,
  CONSTRAINT GALLERY_PK PRIMARY KEY (ID) ENABLE
)
/

CREATE USER rest_demo IDENTIFIED BY NotMyRealPW;

ALTER USER rest_demo TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION, RESOURCE, UNLIMITED TABLESPACE TO rest_demo;

GRANT CREATE TABLE,

CREATE VIEW,

CREATE SEQUENCE,

CREATE PROCEDURE,

CREATE TYPE,

CREATE SYNONYM

TO rest_demo;

CREATE TABLE REST_DEMO.COOL_DATA_T (

ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,

NAME VARCHAR2(20) NOT NULL,

MINUTES NUMBER,

CONSTRAINT COOL_DATA_PK PRIMARY KEY (ID) ENABLE

);

insert into REST_DEMO.cool_data_t(name, minutes) values ('first', 24);

insert into REST_DEMO.cool_data_t(name, minutes) values ('second', 23);

insert into REST_DEMO.cool_data_t(name, minutes) values ('third', 52);

insert into REST_DEMO.cool_data_t(name, minutes) values ('fourth', 235);

insert into REST_DEMO.cool_data_t(name, minutes) values ('fifth', 874);

commit;

CREATE or replace view REST_DEMO.COOL_DATA_V as select ID, NAME, MINUTES from REST_DEMO.cool_data_t;

CREATE TABLE REST_DEMO.GALLERY (

ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL ,

TITLE VARCHAR2(1000) NOT NULL ENABLE,

CONTENT_TYPE VARCHAR2(1000) NOT NULL ENABLE,

IMAGE BLOB NOT NULL ENABLE,

CONSTRAINT GALLERY_PK PRIMARY KEY (ID) ENABLE

)

Create a test API

Then I used the SQL Developer ‘Auto REST’ feature to REST enable the schema and a table. (PL/SQL exported below in case you want to try it.)

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'REST_DEMO',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'api',
                       p_auto_rest_auth => FALSE);

    commit;

    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'REST_DEMO',
                       p_object => 'COOL_DATA_T',
                       p_object_type => 'TABLE',
                       p_object_alias => 'cool_data',
                       p_auto_rest_auth => FALSE);

    commit;

END;

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

ORDS.ENABLE_SCHEMA(p_enabled => TRUE,

p_schema => 'REST_DEMO',

p_url_mapping_type => 'BASE_PATH',

p_url_mapping_pattern => 'api',

p_auto_rest_auth => FALSE);

commit;

ORDS.ENABLE_OBJECT(p_enabled => TRUE,

p_schema => 'REST_DEMO',

p_object => 'COOL_DATA_T',

p_object_type => 'TABLE',

p_object_alias => 'cool_data',

p_auto_rest_auth => FALSE);

commit;

END;

Get the URI

Now I just needed the URI to test my new service. Here are the steps:

Log into your Oracle Cloud Dashboard.
From the menu, select ‘Autonomous Transaction Processing’.
From the instance menu, select ‘Service Console’.

Or, Click on your Instance Name then Click the ‘Service Console’ button.
Click ‘Development’.
In the Rest Data Services box, click the “Copy URL” button.

Test it

For a simple fast test, I just used an internet browser with the following URI:
https://someIdstuff-blaineords.adb.my-region-1.oraclecloudapps.com/ords/api/cool_data

Well, that was easy.

To be honest I expected this to be a lot harder and that I’d have problems. I ran through all of the demos for my presentation and they just worked, first try.

The thing I spent the most time on was re-checking everything. Since it was so easy, I thought I had connected to one of my pre-existing cloud databases.

Go try it!

As always, don’t take my word for it, go try it yourself.

Here are some links to help get you going:

If you don’t have access to an Oracle Cloud account, you can get a trial account here.
Getting started with Oracle REST Data Services
How to secure your Oracle Rest Data Services
oracle.com/rest
Jeff Smith’s Blog

Docker, REST, Tools

Quick and Easy Setup – Oracle Xe and ORDS Using Docker

April 22, 2019 bcarter 4 Comments

In this post I’ll show you how to stand up a couple of Docker containers running Oracle Database Xe and Oracle REST Data Services.

I will assume you are familiar with using Docker. If not checkout this getting started guide.

Required Downloads

Oracle docker-images repository

Oracle provides a GitHub repository with a log of great examples. Clone repository into the directory where you want to work.

Oracle Xe

You can find more information about Oracle Xe at https://www.oracle.com/rest.

Download the installer and place it in the docker-images/OracleDatabase/SingleInstance/dockerfiles/18.4.0 directory.

I’ll be using oracle-database-xe-18c-1.0-1.x86_64.rpm .

Oracle Rest Data Services (ORDS)

You can find more information about ORDS at https://www.oracle.com/rest.

Download the installer and place it in the docker-images/OracleRestDataServices/dockerfiles directory.

I’ll be using ords-19.1.0.092.1545.zip .

ORDS runs in Java, so you’ll need a Java JRE. Download the Server JRE and place it in the docker-images/OracleJava/java-8 directory.

I’ll be using server-jre-8u211-linux-x64.tar.gz .

Docker Network

We’ll be running the database and ORDS in separate docker containers. Define a docker network that they will use to communicate.

docker network create OrdsXeNet

1	docker network create OrdsXeNet

Oracle Database Xe

Change into the docker-images/OracleDatabase/SingleInstance/dockerfiles/18.4.0 directory. Make sure the Oracle Xe install file (oracle-database-xe-18c-1.0-1.x86_64.rpm) is in this directory.

This directory contains a Dockerfile.xe ready for us to use. Take a minute and read through the file to see what it is going to do.

Build the Oracle Database Docker Image

The repository includes a shell script (buildDockerImage.sh) that you can use to build the docker image for Xe and other Oracle Databases.

It can be found in the docker-images/OracleDatabase/SingleInstance/dockerfiles directory. Instructions for using this script can be found here.

I won’t be using the shell script.

The shell script will generate and run something similar to the following command that I will use:
*Notice the dot at the end, this is not a typo.

docker build --force-rm=true \
             --no-cache=true \
             --shm-size=1G \
             --build-arg DB_EDITION=xe \
             -t oracle/database:18.4.0-xe \
             -f Dockerfile.xe .

docker build --force-rm=true \

--no-cache=true \

--shm-size=1G \

--build-arg DB_EDITION=xe \

-t oracle/database:18.4.0-xe \

-f Dockerfile.xe .

You can find an explanation of the Docker Build parameters here.

If this command doesn’t work for you, try using the shell script.

Verify that your image was created:

docker images

1	docker images

You should see something like this:

REPOSITORY                TAG                 IMAGE ID            CREATED             SIZE
oracle/database           18.4.0-xe           a0ed1af72ac3        About a minute ago  8.57GB

1 2	REPOSITORY TAG IMAGE ID CREATED SIZE oracle/database 18.4.0-xe a0ed1af72ac3 About a minute ago 8.57GB

Once you have created the docker image it’s time to

Create a Docker Container

The following command is similar to the command you’ll find on the GitHub repository, but I’ve added a ‘-d’ to run the container in detached mode and I’m not defining any data volumes so my data will not persist when I delete the container.

You can find more information on the docker run command parameters here.

docker run -d \
           --name oracleXe \
           --network=OrdsXeNet \
           -p 1521:1521 \
           -p 5500:5500 \
           -e ORACLE_PWD="Password1_One" \
        oracle/database:18.4.0-xe

docker run -d \

--name oracleXe \

--network=OrdsXeNet \

-p 1521:1521 \

-p 5500:5500 \

-e ORACLE_PWD="Password1_One" \

oracle/database:18.4.0-xe

This will take a few minutes.

You can watch the progress and troubleshoot any issues with with this command:
(You can use the container name or id.)

docker logs oracleXe

1	docker logs oracleXe

When the last line of the log looks like this, you are ready to proceed.

Completed: ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE

1	Completed: ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE

Verify that your container was created and is up:

docker ps

docker ps

You should see something like this:

CONTAINER ID        IMAGE                            COMMAND                  CREATED             STATUS                    PORTS                                                      NAMES
1d0193197193        oracle/database:18.4.0-xe        "/bin/sh -c 'exec $O…"   15 minutes ago      Up 15 minutes (healthy)   0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp, 8080/tcp   oracleXe

1 2	CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 1d0193197193 oracle/database:18.4.0-xe "/bin/sh -c 'exec $O…" 15 minutes ago Up 15 minutes (healthy) 0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp, 8080/tcp oracleXe

Now try to connect to your new Oracle Xe Database.

To connect with SQLcl:

sql system/Password1_One@localhost:1521/xepdb1

SQLcl: Release 18.4 Production on Fri Apr 19 14:30:52 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

sql system/Password1_One@localhost:1521/xepdb1

SQLcl: Release 18.4 Production on Fri Apr 19 14:30:52 2019

Connected to:

Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

Version 18.4.0.0.0

Oracle REST Data Services

Before we create the ORDS container, we need to create a Docker image with Oracle Linux 7 and a Java JRE installed. We will use this as a base image to build on top of.

Oracle Linux 7 and Java JRE Base Image

Change into the docker-images/OracleJava/java-8 directory. In there you will find a Dockerfile and the server-jre-8uXXX-linux-x64.tar.gz file you downloaded earlier.

Take a minute and read through the Dockerfile to see what it is going to do.

Run the following to build the image:
*Notice the dot at the end, this is not a typo.

docker build -t oracle/serverjre:8 .

1	docker build -t oracle/serverjre:8 .

We do not need to create a Docker container from this image. We will only need the image as a base for the ORDS image.

Verify that your image was created:

docker images

1	docker images

You should see something like this:

REPOSITORY               TAG                 IMAGE ID            CREATED             SIZE
oracle/serverjre         8                   2753451f0db2        2 seconds ago       270MB
oracle/database          18.4.0-xe           a80f034f2cf1        4 minutes ago       8.57GB

REPOSITORY TAG IMAGE ID CREATED SIZE

oracle/serverjre 8 2753451f0db2 2 seconds ago 270MB

oracle/database 18.4.0-xe a80f034f2cf1 4 minutes ago 8.57GB

Now we can create the ORDS image.

ORDS

Change into the docker-images/OracleRestDataServices/dockerfiles directory. In there you will find a Dockerfile and the ords-19.1.0.092.1545.zip file you downloaded earlier.

Take a minute and read through the Dockerfile to see what it is going to do.

Pre-Checks

Verify that the docker network we created earlier is ready:

docker network ls

1	docker network ls

You should see something like this.

NETWORK ID          NAME                DRIVER              SCOPE
b0d55a0755b9        OrdsXeNet           bridge              local

1 2	NETWORK ID NAME DRIVER SCOPE b0d55a0755b9 OrdsXeNet bridge local

Make sure your Xe database is running with the docker ps command above.

If the Xe database container is not started, start it with the following command:

docker container start oracleXe

1	docker container start oracleXe

Build the ORDS Docker Image

The repository includes a shell script (buildDockerImage.sh) that you can use to build the ORDS docker image.

It can be found in the docker-images/OracleRestDataServices/dockerfiles directory. Instructions for using this script can be found here.

If you choose to run the shell script instead of the below command, and there is not a Checksum file included for the version of ORDS you are installing, use the -i 1 command line argument to ignore the check.

The shell script will generate and run something similar to the following:
*Notice the dot at the end, this is not a typo.

docker build --force-rm=true \
             --no-cache=true \
             -t oracle/restdataservices:19.1.0 \
             -f Dockerfile .

docker build --force-rm=true \

--no-cache=true \

-t oracle/restdataservices:19.1.0 \

-f Dockerfile .

Verify that your image was created:

docker images

1	docker images

You should see something like this:

REPOSITORY                TAG                 IMAGE ID            CREATED             SIZE
oracle/restdataservices   19.1.0              67f7365e4fb4        6 seconds ago       394MB
oracle/serverjre          8                   2753451f0db2        5 minutes ago       270MB
oracle/database           18.4.0-xe           a80f034f2cf1        10 minutes ago      8.57GB

REPOSITORY TAG IMAGE ID CREATED SIZE

oracle/restdataservices 19.1.0 67f7365e4fb4 6 seconds ago 394MB

oracle/serverjre 8 2753451f0db2 5 minutes ago 270MB

oracle/database 18.4.0-xe a80f034f2cf1 10 minutes ago 8.57GB

Now we can create the ORDS container.

Create an ORDS Docker Container

Make sure that you set -e ORACLE_HOST=oracleXe to the name you used for the Oracle Xe Database container you created above. ORDS will not find your database if you use localhost. The containers are basically separate servers running on the same network --network=OrdsXeNet .

The parameter -e ORACLE_PWD="Password1_One" is the SYS/SYSTEM password you used when you created the Oracle Xe Database above.

The parameter -e ORDS_PWD="ORDS_Password1" will be used as the ORDS admin password. It does not need to be the same as the database password.

docker run -d \
           --name ORDS \
           --network=OrdsXeNet \
           -e ORACLE_HOST=oracleXe \
           -e ORACLE_SERVICE=xepdb1 \
           -e ORACLE_PWD="Password1_One" \
           -e ORDS_PWD="ORDS_Password1" \
           -p 8080:8888 \
        oracle/restdataservices:19.1.0

docker run -d \

--name ORDS \

--network=OrdsXeNet \

-e ORACLE_HOST=oracleXe \

-e ORACLE_SERVICE=xepdb1 \

-e ORACLE_PWD="Password1_One" \

-e ORDS_PWD="ORDS_Password1" \

-p 8080:8888 \

oracle/restdataservices:19.1.0

Verify that your container was created and is up:

docker ps

docker ps

You should see something like this:

CONTAINER ID        IMAGE                            COMMAND                  CREATED             STATUS                    PORTS                                                      NAMES
0bd6a3cb2b56        oracle/restdataservices:19.1.0   "/bin/sh -c $ORDS_HO…"   48 seconds ago      Up 46 seconds             0.0.0.0:8080->8888/tcp                                     ORDS
720ec365868a        oracle/database:18.4.0-xe        "/bin/sh -c 'exec $O…"   19 minutes ago      Up 19 minutes (healthy)   0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp, 8080/tcp   oracleXe

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

0bd6a3cb2b56 oracle/restdataservices:19.1.0 "/bin/sh -c $ORDS_HO…" 48 seconds ago Up 46 seconds 0.0.0.0:8080->8888/tcp ORDS

720ec365868a oracle/database:18.4.0-xe "/bin/sh -c 'exec $O…" 19 minutes ago Up 19 minutes (healthy) 0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp, 8080/tcp oracleXe

You should now have an Oracle Xe database up and running in one container and an ORDS instance running in the other.

Connect to your database and test that ORDS is properly installed and configured. A quick way to tell using Oracle SQL Developer is:

Create a new database user.
Connect as that user.
Right click on the connection.
Look towards the bottom of the menu for “REST Services”.

Have fun creating your new REST modules!

REST, Tools

How to build an Oracle Database Developer Toolbox with Vagrant

January 29, 2019 bcarter 1 Comment

I like to experiment with a lot of different things and of course, a significant number of my projects require an Oracle database and Oracle Rest Data Services (ORDS).

I often use a VirtualBox VM for my project in order to keep it’s environment “clean”.

This guide will walk through the steps to set up a VM with Oracle Xe and ORDS installed and ready to go.

Vagrant

Vagrant automates the creation of a new VM from scratch. I can use a Vagrantfile and some shell scripts to spin up a VM quickly, saving me time and hard drive space compared to keeping a bunch of VMs laying around that I would need to maintain. You can learn more on the Introduction to Vagrant page.

Prerequisites

Before we get started, install VirtualBox and Vagrant. After you’ve finished, download the following.

Downloads

Oracle Database 18c Express Edition for Linux x64
Java JRE. Get the 64bit download.
Oracle REST Data Services (ORDS)
Goto https://github.com/oracle/vagrant-boxes and either git clone the repository or download it as a zip file and extract it.

Versions

As of the date that I’m writing this post, I’m using the following versions of the above downloads. This walkthrough should work if you use these versions, if you use different versions you may have to make adjustments.

Oracle Database 18c Express Edition 18.4.0.0.0 (18c)
Java JRE – jre-8u201-linux-x64.rpm
ORDS – ords-18.4.0.354.1002.zip
https://github.com/oracle/vagrant-boxes – Latest commit eb4983d on Dec 7, 2018

Working in the host and virtual machine environments

Some of the following steps will be run inside the VM others in the Host environment. Check the title of the code blocks to make sure you’re in the correct environment.

To open an SSH connection to your new VM run the following command in the project directory created in the next step.

vagrant ssh

1	vagrant ssh

Create the Toolbox

Make a copy of the 18.4.0-XE directory.

cp -r vagrant-boxes/OracleDatabase/18.4.0-XE dbDevToolbox
cd dbDevToolbox

1 2	cp -r vagrant-boxes/OracleDatabase/18.4.0-XE dbDevToolbox cd dbDevToolbox

When you are working in the virtual machine, the /vagrant/ directory will be mapped to the dbDevToolbox directory on the host machine.

Edit dbDevToolbox/Vagrantfile

Change the name value

# define hostname
NAME = "db-dev-toolbox"

# define hostname

NAME = "db-dev-toolbox"

Open port 8080 for ORDS to use.

  # Oracle port forwarding
  config.vm.network "forwarded_port", guest: 1521, host: 1521
  config.vm.network "forwarded_port", guest: 5500, host: 5500
  config.vm.network "forwarded_port", guest: 8080, host: 8080

# Oracle port forwarding

config.vm.network "forwarded_port", guest: 1521, host: 1521

config.vm.network "forwarded_port", guest: 5500, host: 5500

config.vm.network "forwarded_port", guest: 8080, host: 8080

Create the VM and install Oracle Database XE

Run the following command to start the vagrant build process.

vagrant up

1	vagrant up

This may take a few minutes to run. When it’s finished it will generate and display the system password, you’ll want to remember it.

At this point you have a new virtual machine with Oracle Xe installed and running.

Using your preferred SQL tool, test a database connection. (localhost:1521/xepdb1)

Optional: If you would prefer to set your own password, enter the following commands in a shell. Replace ‘newPassword’ with the password you want to use.

sudo su - oracle
/home/oracle/setPassword.sh newPassword
exit

sudo su - oracle

/home/oracle/setPassword.sh newPassword

exit

Install the Jave Runtime Environment (JRE)

Switch to the root user.
Use the .rpm file to install java.
Check the java version.
Exit the root user.

sudo su -
rpm -ivh /vagrant/jre-8u201-linux-x64.rpm
java -version
exit

sudo su -

rpm -ivh /vagrant/jre-8u201-linux-x64.rpm

java -version

exit

You should see the following output.

java version “1.8.0_201”
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)

Create the file dbDevToolbox/ords_params.properties

This will be used to configure ORDS. Feel free to modify the settings for your environment. If you change the port, remember to also change it in the Vagrantfile in the above step.

For an explanation of these and other settings, you can find the documentation here.

bequeath.connect=true
db.hostname=localhost
db.port=1521
db.servicename=xepdb1
rest.services.ords.add=true
db.username=ORDS_PUBLIC_USER
user.public.password=oracle18
user.tablespace.default=SYSAUX
user.tablespace.temp=TEMP
schema.tablespace.default=SYSAUX
schema.tablespace.temp=TEMP
plsql.gateway.add=false
standalone.http.port=8080
standalone.mode=false
standalone.static.images=/opt/oracle/product/ords/images
standalone.use.https=false

bequeath.connect=true

db.hostname=localhost

db.port=1521

db.servicename=xepdb1

rest.services.ords.add=true

db.username=ORDS_PUBLIC_USER

user.public.password=oracle18

user.tablespace.default=SYSAUX

user.tablespace.temp=TEMP

schema.tablespace.default=SYSAUX

schema.tablespace.temp=TEMP

plsql.gateway.add=false

standalone.http.port=8080

standalone.mode=false

standalone.static.images=/opt/oracle/product/ords/images

standalone.use.https=false

Install ORDS

Switch to the oracle user.
Unzip ORDS into the Oracle product directory.
Copy the above properties file into the ORDS directory.
Set the LD_LIBRARY_PATH environment variable. This is needed in order to install ORDS with a bequeath connection.
Run a silent installation of ORDS using the above parameter file.
Exit the oracle user.

sudo su - oracle

unzip -q /vagrant/ords-18.4.0.354.1002.zip -d /opt/oracle/product/ords/
cp /vagrant/ords_params.properties /opt/oracle/product/ords/

echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib' >> /home/oracle/.bashrc  && source /home/oracle/.bashrc

java -Dconfig.dir=/opt/oracle/product/ords/config -jar /opt/oracle/product/ords/ords.war install simple --parameterFile /opt/oracle/product/ords/ords_params.properties

exit

sudo su - oracle

unzip -q /vagrant/ords-18.4.0.354.1002.zip -d /opt/oracle/product/ords/

cp /vagrant/ords_params.properties /opt/oracle/product/ords/

echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib' >> /home/oracle/.bashrc && source /home/oracle/.bashrc

java -Dconfig.dir=/opt/oracle/product/ords/config -jar /opt/oracle/product/ords/ords.war install simple --parameterFile /opt/oracle/product/ords/ords_params.properties

exit

Autorun ORDS on boot

Create file dbDevToolbox/oracle-ords-18-4 using the following example.

If you decide to write your own, make sure ORDS does not attempt to start until after the database is fully running. For example, in the below file I included ‘# Required-Start: oracle-xe-18c’.

#!/bin/bash
#
# chkconfig: 2345 80 05
# description: This script is responsible for the ORDS service.
#
# Required-Start: oracle-xe-18c
# processname: oracle-ords-18-4
# Red Hat or SuSE config: /etc/sysconfig/oracle-ords-18-4

# Set path if path not set
case $PATH in
    "") PATH=/bin:/usr/bin:/sbin:/etc
        export PATH ;;
esac

# Check if the root user is running this script
if [ $(id -u) != "0" ]
then
    echo "You must be root user to run the configure script. Login as root user and then run the configure script."
    exit 1
fi

NAME="Oracle REST Data Services"
JAVA=`which java`
ORDSWAR="/opt/oracle/product/ords/ords.war"
CONFIGDIR="/opt/oracle/product/ords/config"
LOGFILE=/tmp/ords_listener.log

#start stop 'this' ORDS only picked out by war file.
PID=`ps -ef | grep "$ORDSWAR" | grep -v grep |grep java| cut -c9-15`

# To start ORDS standalone
start() {
    $JAVA -Dconfig.dir=$CONFIGDIR -Xmx1024m -Xms256m -jar $ORDSWAR standalone 2>&1 > $LOGFILE &
    RETVAL=$!
    echo Started PID: $RETVAL
    echo
}

# To stop ORDS
stop() {
        if [[ "X" !=  "${PID}X" ]]; then
                echo -n "Shutting down $NAME "
                kill $PID
        else
                echo $NAME  not running
        fi
        echo
        return 0
}

restart() {
    if [ "X" = "${PID}X" ]; then
        start
    else
        stop
        start
    fi
}

case "$1" in
    start)
        start
    ;;
    stop)
        stop
    ;;
    restart)
        restart
    ;;
    *)
        echo $"Usage: $0 {start|stop|restart}"
        exit 1
    ;;
esac

exit 0

#!/bin/bash

# chkconfig: 2345 80 05

# description: This script is responsible for the ORDS service.

# Required-Start: oracle-xe-18c

# processname: oracle-ords-18-4

# Red Hat or SuSE config: /etc/sysconfig/oracle-ords-18-4

# Set path if path not set

case $PATH in

"") PATH=/bin:/usr/bin:/sbin:/etc

export PATH ;;

esac

# Check if the root user is running this script

if [ $(id -u) != "0" ]

then

echo "You must be root user to run the configure script. Login as root user and then run the configure script."

exit 1

NAME="Oracle REST Data Services"

JAVA=`which java`

ORDSWAR="/opt/oracle/product/ords/ords.war"

CONFIGDIR="/opt/oracle/product/ords/config"

LOGFILE=/tmp/ords_listener.log

#start stop 'this' ORDS only picked out by war file.

PID=`ps -ef | grep "$ORDSWAR" | grep -v grep |grep java| cut -c9-15`

# To start ORDS standalone

start() {

$JAVA -Dconfig.dir=$CONFIGDIR -Xmx1024m -Xms256m -jar $ORDSWAR standalone 2>&1 > $LOGFILE &

RETVAL=$!

echo Started PID: $RETVAL

echo

}

# To stop ORDS

stop() {

if [[ "X" != "${PID}X" ]]; then

echo -n "Shutting down $NAME "

kill $PID

else

echo $NAME not running

echo

return 0

}

restart() {

if [ "X" = "${PID}X" ]; then

start

else

stop

start

}

case "$1" in

start)

start

;;

stop)

stop

;;

restart)

restart

;;

echo $"Usage: $0 {start|stop|restart}"

exit 1

;;

esac

exit 0

Switch to root user.
Copy the above file to /etc/init.d/
Set the file permissions.
Add the configuration.
Set the configuration run levels.
Start ORDS.
Exit the root user.

sudo su -
cp /vagrant/oracle-ords-18-4 /etc/init.d/
chmod 775 /etc/init.d/oracle-ords-18-4
chkconfig --add oracle-ords-18-4
chkconfig --level 2345 oracle-ords-18-4 on
/etc/init.d/oracle-ords-18-4 start
exit

sudo su -

cp /vagrant/oracle-ords-18-4 /etc/init.d/

chmod 775 /etc/init.d/oracle-ords-18-4

chkconfig --add oracle-ords-18-4

chkconfig --level 2345 oracle-ords-18-4 on

/etc/init.d/oracle-ords-18-4 start

exit

Test

At this point, your VM should have both Oracle Database XE and ORDS installed and running. If you’re already familiar with ORDS you should create a test module to make sure everything is working.

If you’re new to ORDS you can create the file dbDevToolbox/setupHr.sql. This script will enable the HR user and create an ORDS module.

ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password;
connect hr/password@localhost:1521/xepdb1

BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'HR',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'hr',
      p_auto_rest_auth      => FALSE);

  ORDS.DEFINE_MODULE(
      p_module_name    => 'test_mod',
      p_base_path      => '/test_mod/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'test_mod',
      p_pattern        => 'test_pat',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'test_mod',
      p_pattern        => 'test_pat',
      p_method         => 'GET',
      p_source_type    => 'json/query',
      p_items_per_page =>  25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         =>
'select ''test'' from dual'
      );


  COMMIT;
END;
/
exit

ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password;

connect hr/password@localhost:1521/xepdb1

BEGIN

ORDS.ENABLE_SCHEMA(

p_enabled => TRUE,

p_schema => 'HR',

p_url_mapping_type => 'BASE_PATH',

p_url_mapping_pattern => 'hr',

p_auto_rest_auth => FALSE);

ORDS.DEFINE_MODULE(

p_module_name => 'test_mod',

p_base_path => '/test_mod/',

p_items_per_page => 25,

p_status => 'PUBLISHED',

p_comments => NULL);

ORDS.DEFINE_TEMPLATE(

p_module_name => 'test_mod',

p_pattern => 'test_pat',

p_priority => 0,

p_etag_type => 'HASH',

p_etag_query => NULL,

p_comments => NULL);

ORDS.DEFINE_HANDLER(

p_module_name => 'test_mod',

p_pattern => 'test_pat',

p_method => 'GET',

p_source_type => 'json/query',

p_items_per_page => 25,

p_mimes_allowed => '',

p_comments => NULL,

p_source =>

'select ''test'' from dual'

);

COMMIT;

END;

exit

Switch to the oracle user.
Use SQL*Plus to run the above script. Replace <YourPassword> with the sys password from the beginning of the post.
Exit the oracle user.
Use curl to test the REST service.

sudo su - oracle
/opt/oracle/product/18c/dbhomeXE/bin/sqlplus sys/<YourPassword>@localhost:1521/xepdb1 as sysdba @/vagrant/setupHr.sql
exit
curl http://localhost:8080/ords/hr/test_mod/test_pat

sudo su - oracle

/opt/oracle/product/18c/dbhomeXE/bin/sqlplus sys/<YourPassword>@localhost:1521/xepdb1 as sysdba @/vagrant/setupHr.sql

exit

curl http://localhost:8080/ords/hr/test_mod/test_pat

You should see the following output.

{"items":[{"'test'":"test"}],"first":{"$ref":"http://localhost:8080/ords/hr/test_mod/test_pat"}}

1	{"items":[{"'test'":"test"}],"first":{"$ref":"http://localhost:8080/ords/hr/test_mod/test_pat"}}

Automate

Creating a new VM with Oracle XE installed is already fully automated. Now, I’m going to show you how to roll the ORDS steps into the Vagrantfile.

The below file collects all of the above steps into a single shell script.

Create the file dbDevToolbox/scripts/installOrds.sh with the following.

#!/bin/bash
echo 'installOrds.sh: Started up'

echo 'Install Jave jre'
rpm -ivh /vagrant/jre-8u201-linux-x64.rpm

echo 'Extract ORDS'
unzip -q /vagrant/ords-18.4.0.354.1002.zip -d /opt/oracle/product/ords/

echo 'Copy ORDS properties file to ORDS directory'
cp /vagrant/ords_params.properties /opt/oracle/product/ords/

echo 'Make sure the oracle user owns ORDS'
chown -R oracle:oinstall /opt/oracle/product/ords/

echo 'Set the LD_LIBRARY_PATH environment variable'
echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib' >> /home/oracle/.bashrc

echo -n "Waiting for the database to start"
while [ $(systemctl is-active oracle-xe-18c) != "active" ]; do
  echo -n ".."
  sleep 1
done
echo ""

echo 'Install ORDS'
su -l oracle -c 'java -Dconfig.dir=/opt/oracle/product/ords/config -jar /opt/oracle/product/ords/ords.war install simple --parameterFile /opt/oracle/product/ords/ords_params.properties'

echo 'Set up ORDS to run on boot'
cp /vagrant/oracle-ords-18-4 /etc/init.d/
chmod 775 /etc/init.d/oracle-ords-18-4
chkconfig --add oracle-ords-18-4
chkconfig --level 2345 oracle-ords-18-4 on

echo 'Start ORDS'
su -l -c '/etc/init.d/oracle-ords-18-4 start'

echo 'installOrds.sh: Complete'

#!/bin/bash

echo 'installOrds.sh: Started up'

echo 'Install Jave jre'

rpm -ivh /vagrant/jre-8u201-linux-x64.rpm

echo 'Extract ORDS'

unzip -q /vagrant/ords-18.4.0.354.1002.zip -d /opt/oracle/product/ords/

echo 'Copy ORDS properties file to ORDS directory'

cp /vagrant/ords_params.properties /opt/oracle/product/ords/

echo 'Make sure the oracle user owns ORDS'

chown -R oracle:oinstall /opt/oracle/product/ords/

echo 'Set the LD_LIBRARY_PATH environment variable'

echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib' >> /home/oracle/.bashrc

echo -n "Waiting for the database to start"

while [ $(systemctl is-active oracle-xe-18c) != "active" ]; do

echo -n ".."

sleep 1

done

echo ""

echo 'Install ORDS'

su -l oracle -c 'java -Dconfig.dir=/opt/oracle/product/ords/config -jar /opt/oracle/product/ords/ords.war install simple --parameterFile /opt/oracle/product/ords/ords_params.properties'

echo 'Set up ORDS to run on boot'

cp /vagrant/oracle-ords-18-4 /etc/init.d/

chmod 775 /etc/init.d/oracle-ords-18-4

chkconfig --add oracle-ords-18-4

chkconfig --level 2345 oracle-ords-18-4 on

echo 'Start ORDS'

su -l -c '/etc/init.d/oracle-ords-18-4 start'

echo 'installOrds.sh: Complete'

Edit dbDevToolbox/Vagrantfile

Provision the new script

  # Provision everything on the first run
  config.vm.provision "shell", path: "scripts/install.sh", env:
    {
       "ORACLE_CHARACTERSET" => "AL32UTF8",
       "SYSTEM_TIMEZONE"     => SYSTEM_TIMEZONE
    }

  config.vm.provision "shell", path: "scripts/installOrds.sh", env: {}

# Provision everything on the first run

config.vm.provision "shell", path: "scripts/install.sh", env:

{

"ORACLE_CHARACTERSET" => "AL32UTF8",

"SYSTEM_TIMEZONE" => SYSTEM_TIMEZONE

}

config.vm.provision "shell", path: "scripts/installOrds.sh", env: {}

Destroy the current VM and create a new one.

WARNING: This will completely destroy the VM we created above and create a new one from scratch. If you have made any changes you’d like to keep, make sure to back them up first.

Destroy the VM.
Confirm that you want to destroy the VM.
Create a new VM.

vagrant destroy
y
vagrant up

vagrant destroy

vagrant up

Test the new machine.

Future project virtual machines

For future projects you’ll only need to:

Make a copy of the dbDevToolbox directory with a new name.
Edit the Vagrantfile and modify the NAME entry.
Run the vagrant up command.

When you’re finished with the project use vagrant destroy to clean up.

Resources

Information, Learning, REST

How to secure your ORDS service

December 10, 2018 bcarter 2 Comments

Securing your Oracle Rest Data Services module only takes a couple of minutes.

As you can see in the video, this will be a fairly short how-to.

Assumptions

For this post, I’m going to assume:

You’re able to use SQL Developer.
You already have an ORDS module created that you want to secure.
You have at least a basic understanding of Roles, Privileges, Authentication and Authorization.

Test the Existing Service

Before we begin, I’ll test the current service with a curl command.

curl -i "http://localhost:8080/ords/beacon/beacon/scan"

1	curl -i "http://localhost:8080/ords/beacon/beacon/scan"

This returns a response with a status of “HTTP/1.1 200 OK” and a JSON object with an array of items.

HTTP/1.1 200 OK
Date: Fri, 07 Dec 2018 03:02:15 GMT
Content-Type: application/json
ETag: "9xNgvloFw1ZfBwQ255WysJ1trS4t9nSdBZUqSdg170X8DfoFDkjBLboruFoXG1TcQRkzWqVx0ptMV6ldt6BU6g=="
Transfer-Encoding: chunked

{"items":[{"id":641,"scanner_id":5,"beacon_id":"myBeacon","distance":4.72,"created_on":"2017-08-27T22:19:58.082Z"},......

HTTP/1.1 200 OK

Date: Fri, 07 Dec 2018 03:02:15 GMT

Content-Type: application/json

ETag: "9xNgvloFw1ZfBwQ255WysJ1trS4t9nSdBZUqSdg170X8DfoFDkjBLboruFoXG1TcQRkzWqVx0ptMV6ldt6BU6g=="

Transfer-Encoding: chunked

{"items":[{"id":641,"scanner_id":5,"beacon_id":"myBeacon","distance":4.72,"created_on":"2017-08-27T22:19:58.082Z"},......

Secure Your Service

First up I will create a Role and a Privilege that I’ll use to secure my service.

Create a Role

Expand the schema connection and REST Data Services, if they are not already expanded.
Right click on Roles.
Click New Role…

Enter a name for the role. I’m using bc-role.
Click Apply.

Create a Privilege

Expand the schema connection and REST Data Services, if they are not already expanded.
Right click on Privileges.
Click New Privilege…

Enter a name for the privilege. I’m using bc-priv.
Select the new role ‘bc-role’.
Select the ORDS Module. I’m using my Beacon module.
Click Apply.

Be aware, this will only add security to the specific modules you select in the bottom section. If you were to add a new module later and you want it to be covered with this privilege, you need to remember to edit this privilege and select it in the ‘Protect Modules’ selector.

Alternatively, you can use the ‘Protect Resources’ tab to apply security based on a URI pattern.

For my examples, I’m working from a VirtualBox VM and I have used ‘beacon’ as my schema alias. So the base for all of my REST services in this schema would be

https://localhost:8080/ords/beacon

1	https://localhost:8080/ords/beacon

I entered the URI pattern

/*

This will protect all ORDS services for this schema. If I wanted to use this method to protect just an admin URI template in my beacon module (the full URI is https://localhost:8080/ords/beacon/beacon/admin), I would use a URI pattern like this

/beacon/admin/*

1	/beacon/admin/*

There are many more complex patterns you could use for your services, but for this post, I am only protecting the Beacon module so I am using the first example.

Test

curl -i "http://localhost:8080/ords/beacon/beacon/scan"

1	curl -i "http://localhost:8080/ords/beacon/beacon/scan"

This now returns a response with a status of “HTTP/1.1 401 Unauthorized”.

HTTP/1.1 401 Unauthorized
Content-Type: text/html
Content-Length: 13150
.....

HTTP/1.1 401 Unauthorized

Content-Type: text/html

Content-Length: 13150

.....

Access Your Service

I want to set up my ORDS module so it can be accessed by a third party application so I’ll set up OAuth 2.

Create an OAuth 2 Client

Using the OAUTH PL/SQL Package, I will create an OAuth2 client using the privilege I created above.

-- Create OAUTH client
BEGIN
  OAUTH.create_client(
    p_name            => 'BC Client',
    p_grant_type      => 'client_credentials',
    p_owner           => 'Blaine',
    p_description     => 'A client for Rest Demo',
    p_support_email   => 'blaine.carter@oracle.com',
    p_privilege_names => 'bc-priv'
  );

  COMMIT;
END;
/

-- Create OAUTH client

BEGIN

OAUTH.create_client(

p_name => 'BC Client',

p_grant_type => 'client_credentials',

p_owner => 'Blaine',

p_description => 'A client for Rest Demo',

p_support_email => 'blaine.carter@oracle.com',

p_privilege_names => 'bc-priv'

);

COMMIT;

END;

Then I will grant the new OAUTH2 client the role I created above.

-- Grant OAUTH client role
BEGIN
  OAUTH.grant_client_role(
    p_client_name => 'BC Client',
    p_role_name   => 'bc-role'
  );

  COMMIT;
END;
/

-- Grant OAUTH client role

BEGIN

OAUTH.grant_client_role(

p_client_name => 'BC Client',

p_role_name => 'bc-role'

);

COMMIT;

END;

Test

In order to generate an access token, I need to get my client id and secret from the user_ords_clients table.

SELECT id, name, client_id, client_secret
FROM   user_ords_clients;

1 2	SELECT id, name, client_id, client_secret FROM user_ords_clients;

        ID NAME                 CLIENT_ID                        CLIENT_SECRET                   
---------- -------------------- -------------------------------- --------------------------------
     10687 BC Client            RTrgeYXmVrA4IjnQtixzLw..         yPljTEn7kXX2ZfdIhPDVow..

ID NAME CLIENT_ID CLIENT_SECRET

---------- -------------------- -------------------------------- --------------------------------

10687 BC Client RTrgeYXmVrA4IjnQtixzLw.. yPljTEn7kXX2ZfdIhPDVow..

When you create an OAuth2 client, a REST service ‘/oauth/token’ is created for you that your applications can use to generate a bearer token using the above id and secret.

curl -i -k --user RTrgeYXmVrA4IjnQtixzLw..:yPljTEn7kXX2ZfdIhPDVow.. --data "grant_type=client_credentials" http://localhost:8080/ords/beacon/oauth/token

1	curl -i -k --user RTrgeYXmVrA4IjnQtixzLw..:yPljTEn7kXX2ZfdIhPDVow.. --data "grant_type=client_credentials" http://localhost:8080/ords/beacon/oauth/token

HTTP/1.1 200 OK
Date: Fri, 07 Dec 2018 03:11:58 GMT
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked

{"access_token":"xoJutP9YeUjCqN4mAgDBAw..","token_type":"bearer","expires_in":3600}

HTTP/1.1 200 OK

Date: Fri, 07 Dec 2018 03:11:58 GMT

Content-Type: application/json

X-Frame-Options: SAMEORIGIN

Transfer-Encoding: chunked

{"access_token":"xoJutP9YeUjCqN4mAgDBAw..","token_type":"bearer","expires_in":3600}

Notice that this token expires in 3600 seconds. Once it expires, you would repeat the above call to generate a new token.

Now that I have an access_token I can include it in the Authorization header to access my secure ORDS service. Notice that since this is a Bearer token I set the Authorization value to ‘Bearer <<my token>>’.

curl -i -k -H "Authorization: Bearer xoJutP9YeUjCqN4mAgDBAw.." "http://localhost:8080/ords/beacon/beacon/scan"

1	curl -i -k -H "Authorization: Bearer xoJutP9YeUjCqN4mAgDBAw.." "http://localhost:8080/ords/beacon/beacon/scan"

HTTP/1.1 200 OK
Date: Fri, 07 Dec 2018 03:22:44 GMT
Content-Type: application/json
ETag: "9xNgvloFw1ZfBwQ255WysJ1trS4t9nSdBZUqSdg170X8DfoFDkjBLboruFoXG1TcQRkzWqVx0ptMV6ldt6BU6g=="
Transfer-Encoding: chunked

{"items":[{"id":641,"scanner_id":5,"beacon_id":"myBeacon","distance":4.72,"created_on":"2017-08-27T22:19:58.082Z"},.....

HTTP/1.1 200 OK

Date: Fri, 07 Dec 2018 03:22:44 GMT

Content-Type: application/json

ETag: "9xNgvloFw1ZfBwQ255WysJ1trS4t9nSdBZUqSdg170X8DfoFDkjBLboruFoXG1TcQRkzWqVx0ptMV6ldt6BU6g=="

Transfer-Encoding: chunked

{"items":[{"id":641,"scanner_id":5,"beacon_id":"myBeacon","distance":4.72,"created_on":"2017-08-27T22:19:58.082Z"},.....

Example Usage

The following is an example of how to make secure REST calls from an external application after you’ve followed the above steps.

On the application server

Store the client_id and client_secret in environment variables.

In your application

Function get_token
  Read the client_id and client_secret from the environment variables.
  Make a GET request from your ORDS service '.../oauth/token' for a new access_token using your client_id and client_secret.
  Store the access_token and expiration time.

Function get_data
  If access_token does not exist or is expired.
    Call get_token.

  Make a GET request from your ORDS service '.../beacon/scan' using the access_token in a header parameter - "Authorization: Bearer <<Your Token Here>>".
  If the response status is 401 Unauthorized.
    If access_token exists and has expired.
      Call get_token.
      Retry GET request.
    Else
      Raise an error.

Function get_token

Read the client_id and client_secret from the environment variables.

Make a GET request from your ORDS service '.../oauth/token' for a new access_token using your client_id and client_secret.

Store the access_token and expiration time.

Function get_data

If access_token does not exist or is expired.

Call get_token.

Make a GET request from your ORDS service '.../beacon/scan' using the access_token in a header parameter - "Authorization: Bearer <<Your Token Here>>".

If the response status is 401 Unauthorized.

If access_token exists and has expired.

Call get_token.

Retry GET request.

Else

Raise an error.

Summary

Setting up security on your ORDS modules is quick and fairly easy.

OAuth2 is just one way to access your secured ORDS modules. If you’d like to setup Basic Auth checkout this post by Jeff Smith.

Console the ORDS documentation for other examples and a much more in-depth explanation.

DevOps, Learning

How to use an SSH Tunnel in Oracle Developer Cloud Service Build Jobs

October 31, 2018 bcarter Leave a comment

Oracle Developer Cloud Service is a hosted team development and delivery platform with all kinds of tools to help your team be more efficient. In this post, I will cover how to use an SSH tunnel to connect to your database in a build job.

This is current as of October 2018.

Connecting to your database through an SSH tunnel is fairly simple and you won’t have to ask your network admin to open a port in your firewall and or load balancers.

If you’re connecting to an Oracle Cloud Database it should be pre-configured to allow database connections through an SSH tunnel. If not check with your server admin for assistance.

If you’re not familiar with SSH tunnels you can find out more here.

However, if you’re reading this post you probably just want to skip to the how-to so let’s get started.

Configure your build job

Open your Developer Cloud Service project, click the ‘Build’ tab and select the build you want to work with.

Click the ‘Configure’ button

Authorized SSH Keys

You will need an SSH Key that has been authorized to connect to the database server. I recommend generating a new key that will only be used from your DevCs builds. Name the new key pair something that will let you know it’s for this DevCs project.

Helpful links:

If you generated a new key pair named DevCsProj1, you should have two files. The file without an extension ‘DevCsProj1’ is your private key and the file with .pub ‘DevCsProj1.pub’ is your public key.

Add a build environment configuration

Select the ‘Build Environment’ tab, click ‘Add Build Environment’ and choose ‘SSH Configuration’.

Open the private key (from above) in a text editor, copy everything in the file and paste it in the ‘Private Key’ file text area.
You can do the same for the ‘Public Key’ text area but it is not required for this example.
If you created your keys with a password, enter the password in the ‘Pass Phrase’ field.
For a little extra security, you could get the public key for your Database Server and enter it in the ‘SSH Server Public Key’ text area. This will ensure that your build job only connects to that server and will help protect against connecting to a different server if the IP address is re-assigned. This field is optional.
Check ‘Create SSH tunnel’.
Enter the SSH username. (This is not a database user, this is an authorized SSH user on the server.)
If you’d rather use an SSH user/password instead of an SSH Key file, you could enter the password in the password field. I leave it blank in order to only connect with the keys.
Enter the port you want to use on the DevCs side of the tunnel in ‘Local Port’.
If you leave ‘Remote Host’ empty it will default to localhost. The remote host is used on the other side of the tunnel to make the connection as if you were on that machine. Since I’m intending to connect to the database that is on the same server that I’m SSH’d into I can use localhost. If I wanted to connect to a different server from that side of the tunnel, I could enter the address for the other remote server. For example, if I had a server that was only accessible from inside of a network that includes the SSH server, I can SSH into the network and the tunnel will end on the other internal server.
If you’d like to re-use your keys for other SSH commands you can check ‘Setup files in ~/.ssh for command-line ssh tools’. It is not necessary for this example.

The ‘Connect String’ displayed at the bottom shows the ssh command DevCs will use to create the tunnel.

ssh -L localhost:1521:localhost:1521 opc@129.111.111.111

Let’s break it down:

Create a local ssh tunnel. ssh -L
On the local DevCs server map to the ‘Local Port’ value. localhost:1521 (The first one)
Once connected to the SSH server map the tunnel end to ‘Remote Host’:’Remote Port’. localhost:1521 (The second one)
Make the SSH connection as ‘Username’@’SSH Server’. opc@129.111.111.111

OK, time to use the new tunnel. I’ll use a SQLcl Builder.

Add / Edit a builder

Select the Builders tab, click Add Builder and choose SQLcl Builder.

If you’ve used a SQLcl builder before this will all be the same, except for the connect string.

Enter your database username in the ‘Username’ field.
Enter your database password in the ‘Password’ field.
If you’re connecting to a database that is using wallet credentials such as Oracle Exadata Express Cloud Service, enter the location of your Credentials file. I’m not so I will leave it empty.
Since I now have an SSH tunnel in place I will connect to the local (DevCs server) end of the tunnel and use the ‘Local Port’ value from the SSH Configuration. //localhost:1521/[servicename]. Even if I had defined a remote host and/or port other than localhost / 1521 in the tunnel configuration, I would still use localhost:[Local Port]. The tunnel takes care of the mapping on the other end.
Enter the SQL File or Inline SQL you want to run.
Save the Job Configuration.

Run it

Click the ‘Build Now’ button and when you look at the console output you will see something similar to this.

SSH is set up.
The SSH tunnel is opened.
SQLcl executed my script. (I did not enable any output in my script so none is displayed.)
SQLcl disconnects from my Database.
The SSH tunnel is closed.
The SSH environment is removed.

Once you’ve added an SSH Build Environment to your build job and tested it, you can start adding them to each Build Job you use a database connection in. After that’s done you can close port 1521 on your Database server (assuming you don’t need it for other applications).

Oracle Developer Cloud Service is constantly being improved, so let me know in the comments if this guide becomes out of date and I will update it.

Uncategorized

Join me at Oracle Open World 2018

October 3, 2018 bcarter Leave a comment

Oracle Open World is almost here!

Oracle Open World and Oracle Code One will be happening together and I will be giving sessions in both, check out this list if you’d like to join me:

Monday

9:00 a.m. – 11:00 a.m. | Moscone West – Overlook 2A (HOL) – Along with Christopher Jones and Anthony Tuininga
Python and Oracle Database: Scripting for the Future – BYOL [HOL5052]
PLEASE NOTE: YOU MUST BRING YOUR OWN LAPTOP (BYOL) TO PARTICIPATE IN THE HANDS-ON LABS.
Get hands-on, and learn the features of the Python cx_Oracle interface for Oracle Database. Learn efficient techniques for connection management, statement handling, and more. If you want to use Python and Oracle Database, this session is for you. Basic programming skills in any language are required.Please review the prerequisite information here in preparation for the lab.

Tuesday

12:30 p.m. – 12:50 p.m. | The Exchange @ Moscone South – Theater 6
AskTOM Mini Lesson: Rapid REST Enablement with Oracle REST Data Services [THT6899]
In this session see how to REST-enable a database using Oracle REST Data Services. See the steps, including “auto REST-enabling” a table, creating a specific REST service using an Oracle REST Data Services wizard, and then exporting the SQL generated by these tools, which you can include in your deploy scripts and/or use to learn how to write your own REST services using only SQL. If there is enough time the session also includes a walk-through of the process of adding security to your new REST services. Leave this session with an understanding of how to quickly create REST services on your own database.

2:15 p.m. – 3:15 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 3/4 – Along with Christopher Jones and Anthony Tuininga
Python and Oracle Database 18c: Scripting for the Future [HOL6329] Get hands-on and learn the features of the Python cx_Oracle interface for Oracle Database. In this session learn efficient techniques for connection management, statement handling, and more. If you want to use Python and Oracle Database, this session is for you. Basic programming skills in any language are required.

4:00 p.m. – 4:45 p.m. | Moscone West – Room 2010
DevOps Tools for Database Developers [DEV5055]
Are you still thinking about modernizing your database development process…someday…maybe? Do you have directories full of .sql files you use to build your application? You know that unit tests should be included as part of your build process, and you are going to start adding them…maybe next week? If you have an Oracle Cloud account, you may be surprised at what tools you already have access to and how easy it is to get started. This session takes a look at Oracle Developer Cloud Service. It discusses some of the included tools and explores mixing in some open source tools you can use to automate building and testing your applications. The sooner you get started, the more it will save you in the long run.

Thursday

2:00 p.m. – 2:45 p.m. | Moscone West – Room 3009
IoT for Oracle Database: Soldering, Python, and a Little PL/SQL [TRN4077]
Using way too much electricity and not quite sure where? Tracking usage is a great start, but analyzing data is easiest in a database. Building on the “Watch me make a Watt-watcher” project, this session begins with an overview of the steps and components used to assemble the hardware. Data ingestion to an Oracle Database Cloud is made straight forward with the Python cx_Oracle driver. Get an overview of other cx_Oracle features useful to IoT projects as well, and come away with the tools to track the data in your own projects and hopefully some new ideas. (But no electrical burns!)

My schedule could still change. If it does, I’ll update this post.

If you can’t make my sessions and would like to meet up and chat, you can leave a comment here or send me a tweet.

There’s a lot going on all week and it’s going to be an awesome conference. I hope to see you there!

Information, Learning, PL/SQL, Python

Execute PL/SQL calls with Python and cx_Oracle

July 30, 2018 bcarter 4 Comments

After you’ve got the hang of performing Basic CRUD operations with cx_Oracle you’re ready to start tapping into some of the real power of the Oracle Database.

Why use PL/SQL?

Python is an excellent language for most things you want your application to do, but when you’re processing data it just goes faster if you do the work where the data is.

This post will cover how to execute Oracle PL/SQL functions and procedures using Python and cx_Oracle. I’m assuming you’re already familiar with PL/SQL if not, you can get some help from Steven Feuerstein and Bryn Llewellyn. (Additional resources at the end.)

Prerequisites

Python 3
Oracle Database version 12+
Basic Oracle PL/SQL and SQL knowledge.

Setup

If you’d like to follow along with the examples you’ll need to create the following objects in a database schema that is safe to experiment in. Make sure you have permissions to create the following objects.

CREATE TABLE lcs_people (
 id NUMBER GENERATED BY DEFAULT AS identity,
 name VARCHAR2(20),
 age NUMBER,
 notes VARCHAR2(100)
)
/

ALTER TABLE LCS_PEOPLE
ADD CONSTRAINT PK_LCS_PEOPLE PRIMARY KEY ("ID")
/

CREATE TABLE LCS_PETS (
 id NUMBER GENERATED BY DEFAULT AS IDENTITY,
 name VARCHAR2(20),
 owner NUMBER,
 type VARCHAR2(100)
)
/

ALTER TABLE LCS_PETS ADD CONSTRAINT PK_LCS_PETS PRIMARY KEY ("ID")
/

ALTER TABLE LCS_PETS ADD CONSTRAINT FK_LCS_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "LCS_PEOPLE" ("ID")
/

INSERT INTO lcs_people (name, age, notes)
 VALUES ('Bob', 35, 'I like dogs')
/

INSERT INTO lcs_people (name, age, notes)
 VALUES ('Kim', 27, 'I like birds')
/

INSERT INTO lcs_pets (name, owner, type)
 VALUES ('Duke', 1, 'dog')
/

INSERT INTO lcs_pets (name, owner, type)
 VALUES ('Pepe', 2, 'bird')
/

COMMIT
/

CREATE TABLE lcs_people (

id NUMBER GENERATED BY DEFAULT AS identity,

name VARCHAR2(20),

age NUMBER,

notes VARCHAR2(100)

)

ALTER TABLE LCS_PEOPLE

ADD CONSTRAINT PK_LCS_PEOPLE PRIMARY KEY ("ID")

CREATE TABLE LCS_PETS (

id NUMBER GENERATED BY DEFAULT AS IDENTITY,

name VARCHAR2(20),

owner NUMBER,

type VARCHAR2(100)

)

ALTER TABLE LCS_PETS ADD CONSTRAINT PK_LCS_PETS PRIMARY KEY ("ID")

ALTER TABLE LCS_PETS ADD CONSTRAINT FK_LCS_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "LCS_PEOPLE" ("ID")

INSERT INTO lcs_people (name, age, notes)

VALUES ('Bob', 35, 'I like dogs')

INSERT INTO lcs_people (name, age, notes)

VALUES ('Kim', 27, 'I like birds')

INSERT INTO lcs_pets (name, owner, type)

VALUES ('Duke', 1, 'dog')

INSERT INTO lcs_pets (name, owner, type)

VALUES ('Pepe', 2, 'bird')

COMMIT

To keep everything clean, I’ll be putting my PL/SQL code into a package called pet_manager.

CREATE OR REPLACE PACKAGE pet_manager AS
    PROCEDURE reset_data;

    PROCEDURE add_pet (
        name_p       IN VARCHAR2,
        owner_id_p   IN NUMBER,
        pet_type_p   IN VARCHAR2
    );

    FUNCTION add_pet (
        name_p       IN VARCHAR2,
        owner_id_p   IN NUMBER,
        pet_type_p   IN VARCHAR2
    ) RETURN NUMBER;

    FUNCTION add_pet (
        name_p           IN VARCHAR2,
        owner_id_p       IN NUMBER,
        pet_type_p       IN VARCHAR2,
        need_license_p   OUT VARCHAR2
    ) RETURN NUMBER;

END;
/

CREATE OR REPLACE PACKAGE BODY pet_manager IS
  
  /*
   * Reset the example data
   */

   PROCEDURE reset_data
      AS
   BEGIN
      DELETE FROM lcs_pets;

      EXECUTE IMMEDIATE ('alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 3)');
      
      DELETE FROM lcs_people;

      EXECUTE IMMEDIATE ('alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)');
      
      INSERT INTO lcs_people (id, name, age, notes)
      VALUES (1, 'Bob', 35, 'I like dogs');

      INSERT INTO lcs_people (id, name, age, notes)
      VALUES (2, 'Kim', 27, 'I like birds');

      INSERT INTO lcs_pets (id, name, owner, type)
      VALUES (1, 'Duke', 1, 'dog');

      INSERT INTO lcs_pets (id, name, owner, type)
      VALUES (2, 'Pepe', 2, 'bird');

      COMMIT;
   END reset_data;
  
  /*
   * Add a new Pet
   */

   PROCEDURE add_pet (
      name_p       IN VARCHAR2,
      owner_id_p   IN NUMBER,
      pet_type_p   IN VARCHAR2
   )
      IS
   BEGIN
      INSERT INTO lcs_pets (
         name,
         owner,
         type
      ) VALUES (
         name_p,
         owner_id_p,
         lower(pet_type_p)
      );

      COMMIT;
   END add_pet;
  
  /*
   * Add a new Pet return new id
   */

   FUNCTION add_pet (
      name_p       IN VARCHAR2,
      owner_id_p   IN NUMBER,
      pet_type_p   IN VARCHAR2
   ) RETURN NUMBER IS
      new_pet_id   NUMBER;
   BEGIN
      INSERT INTO lcs_pets (
         name,
         owner,
         type
      ) VALUES (
         name_p,
         owner_id_p,
         lower(pet_type_p)
      ) RETURNING id INTO new_pet_id;

      COMMIT;
      RETURN new_pet_id;
   END add_pet;
  
  /*
   * Add a new Pet return new id and if it needs a license.
   */

   FUNCTION add_pet (
      name_p           IN VARCHAR2,
      owner_id_p       IN NUMBER,
      pet_type_p       IN VARCHAR2,
      need_license_p   OUT VARCHAR2
   ) RETURN NUMBER IS
      new_pet_id   NUMBER;
   BEGIN
      INSERT INTO lcs_pets (
         name,
         owner,
         type
      ) VALUES (
         name_p,
         owner_id_p,
         lower(pet_type_p)
      ) RETURNING id INTO new_pet_id;

      IF lower(pet_type_p) IN ('dog', 'cat') THEN
         need_license_p := 'yes';
      ELSE
         need_license_p := 'no';
      END IF;

      COMMIT;
      RETURN new_pet_id;
   END add_pet;

END pet_manager;
/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

CREATE OR REPLACE PACKAGE pet_manager AS

PROCEDURE reset_data;

PROCEDURE add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2

);

FUNCTION add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2

) RETURN NUMBER;

FUNCTION add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2,

need_license_p OUT VARCHAR2

) RETURN NUMBER;

END;

CREATE OR REPLACE PACKAGE BODY pet_manager IS

* Reset the example data

PROCEDURE reset_data

BEGIN

DELETE FROM lcs_pets;

EXECUTE IMMEDIATE ('alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 3)');

DELETE FROM lcs_people;

EXECUTE IMMEDIATE ('alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)');

INSERT INTO lcs_people (id, name, age, notes)

VALUES (1, 'Bob', 35, 'I like dogs');

INSERT INTO lcs_people (id, name, age, notes)

VALUES (2, 'Kim', 27, 'I like birds');

INSERT INTO lcs_pets (id, name, owner, type)

VALUES (1, 'Duke', 1, 'dog');

INSERT INTO lcs_pets (id, name, owner, type)

VALUES (2, 'Pepe', 2, 'bird');

COMMIT;

END reset_data;

* Add a new Pet

PROCEDURE add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2

)

BEGIN

INSERT INTO lcs_pets (

name,

owner,

type

) VALUES (

name_p,

owner_id_p,

lower(pet_type_p)

);

COMMIT;

END add_pet;

* Add a new Pet return new id

FUNCTION add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2

) RETURN NUMBER IS

new_pet_id NUMBER;

BEGIN

INSERT INTO lcs_pets (

name,

owner,

type

) VALUES (

name_p,

owner_id_p,

lower(pet_type_p)

) RETURNING id INTO new_pet_id;

COMMIT;

RETURN new_pet_id;

END add_pet;

* Add a new Pet return new id and if it needs a license.

FUNCTION add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2,

need_license_p OUT VARCHAR2

) RETURN NUMBER IS

new_pet_id NUMBER;

BEGIN

INSERT INTO lcs_pets (

name,

owner,

type

) VALUES (

name_p,

owner_id_p,

lower(pet_type_p)

) RETURNING id INTO new_pet_id;

IF lower(pet_type_p) IN ('dog', 'cat') THEN

need_license_p := 'yes';

ELSE

need_license_p := 'no';

END IF;

COMMIT;

RETURN new_pet_id;

END add_pet;

END pet_manager;

Cleanup

To clean up the database when you are finished with the series, you need to drop the two tables and the package. Please make sure you’re connected to the correct schema where you created the tables.

drop table lcs_pets
/

drop table lcs_people
/

drop package pet_manager
/

drop table lcs_pets

drop table lcs_people

drop package pet_manager

Boilerplate template

The template we will be using is:

Install cx_Oracle.
Import the cx_Oracle driver.
Import os module used to read the environment variable.
Get the connection string from the environment variable.
Create the connection object.
Create the cursor object.

import cx_Oracle
import os
connectString = os.getenv('db_connect')
con = cx_Oracle.connect(connectString)
cur = con.cursor()

# Your code here

import cx_Oracle

import os

connectString = os.getenv('db_connect')

con = cx_Oracle.connect(connectString)

cur = con.cursor()

# Your code here

I will include this code section with all Python examples and use the connection object “con” and the cursor object “cur” throughout the series.

For each exercise, replace the “# Your code here” line with your code.

Anonymous PL/SQL Block

I’m going to start off with the most basic process and simply execute an anonymous block of PL/SQL code to reset the database tables.

# reset data
statement = """
   BEGIN
      DELETE FROM lcs_pets;

      EXECUTE IMMEDIATE ('alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 3)');
      
      DELETE FROM lcs_people;

      EXECUTE IMMEDIATE ('alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)');
      
      INSERT INTO lcs_people (id, name, age, notes)
      VALUES (1, 'Bob', 35, 'I like dogs');

      INSERT INTO lcs_people (id, name, age, notes)
      VALUES (2, 'Kim', 27, 'I like birds');

      INSERT INTO lcs_pets (id, name, owner, type)
      VALUES (1, 'Duke', 1, 'dog');

      INSERT INTO lcs_pets (id, name, owner, type)
      VALUES (2, 'Pepe', 2, 'bird');

      COMMIT;
    END;"""
    
cur.execute(statement)

# reset data

statement = """

BEGIN

DELETE FROM lcs_pets;

EXECUTE IMMEDIATE ('alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 3)');

DELETE FROM lcs_people;

EXECUTE IMMEDIATE ('alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)');

INSERT INTO lcs_people (id, name, age, notes)

VALUES (1, 'Bob', 35, 'I like dogs');

INSERT INTO lcs_people (id, name, age, notes)

VALUES (2, 'Kim', 27, 'I like birds');

INSERT INTO lcs_pets (id, name, owner, type)

VALUES (1, 'Duke', 1, 'dog');

INSERT INTO lcs_pets (id, name, owner, type)

VALUES (2, 'Pepe', 2, 'bird');

COMMIT;

END;"""

cur.execute(statement)

 Example: python3 anon_plsql.py

 Example:

Example: python3 anon_plsql.py

Example:

You can execute any DDL or DML statement like this, but if you’re going to run PL/SQL it’s usually best to compile it to the database.

Execute a PL/SQL Procedure

Using the code from the anonymous block I created a procedure in the PL/SQL package called reset_data.

To call this procedure from Python we use the cursor.callproc method and pass in the package.procedure name to execute.

cur.callproc('pet_manager.reset_data')

1	cur.callproc('pet_manager.reset_data')

Assuming everything works, there will not be any response. So this works as a ‘fire and forget’ way to call database procedures.

 Example: python3 execute_procedure.py

 Example:

Example: python3 execute_procedure.py

Example:

Pass Parameters

I have a procedure in my PL/SQL package that we can use to create a new pet in the lcs_pets table. It accepts the pet_name, owner_id and pet_type. Using these values it will insert a new entry into the lcs_pets table.

  /*
   * Add a new Pet
   */

    PROCEDURE add_pet (
        name_p       IN VARCHAR2,
        owner_id_p   IN NUMBER,
        pet_type_p   IN VARCHAR2
    )
        IS
    BEGIN
        INSERT INTO lcs_pets (
            name,
            owner,
            type
        ) VALUES (
            name_p,
            owner_id_p,
            lower(pet_type_p)
        );

        COMMIT;
    END add_pet;

* Add a new Pet

PROCEDURE add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2

)

BEGIN

INSERT INTO lcs_pets (

name,

owner,

type

) VALUES (

name_p,

owner_id_p,

lower(pet_type_p)

);

COMMIT;

END add_pet;

Now on the Python side.

I prefer to set my values with variables so that my code is easier to read, so I’ll create and set pet_name, owner_id and pet_type.

Next, I’ll call the cursor.callproc method and add an array containing the values to pass in the order they are defined in the database.

pet_name = 'Roger'
owner_id = 1
pet_type = 'fish'

# add a new pet
cur.callproc('pet_manager.add_pet',
      [pet_name, owner_id, pet_type])

pet_name = 'Roger'

owner_id = 1

pet_type = 'fish'

# add a new pet

cur.callproc('pet_manager.add_pet',

[pet_name, owner_id, pet_type])

If everything works there will not be any response.

 Example: python3 pass_parameters.py

 Example:

Example: python3 pass_parameters.py

Example:

You can also use keyword parameters. This also makes your code easy to read and also makes it so you don’t need to worry about the order of the parameters.

# add a new pet
cur.callproc('pet_manager.add_pet',
      keywordParameters={"pet_type_p": "fish", "name_p": "Roger", "owner_id_p": 1})

# add a new pet

cur.callproc('pet_manager.add_pet',

keywordParameters={"pet_type_p": "fish", "name_p": "Roger", "owner_id_p": 1})

Once again, if everything works there will not be any response.

 Example: python3 pass_parameters.py

 Example:

Example: python3 pass_parameters.py

Example:

Get PL/SQL Function Return Values

When a row is added to the lcs_pets table a new id is automatically generated. Having this id can be useful so I created a function in my PL/SQL package that will create a new pet in the lcs_pets table, just like in the previous function, but it will return the new id.

/*
   * Add a new Pet return new id
   */

    FUNCTION add_pet (
        name_p       IN VARCHAR2,
        owner_id_p   IN NUMBER,
        pet_type_p   IN VARCHAR2
    ) RETURN NUMBER IS
        new_pet_id   NUMBER;
    BEGIN
        INSERT INTO lcs_pets (
            name,
            owner,
            type
        ) VALUES (
            name_p,
            owner_id_p,
            lower(pet_type_p)
        ) RETURNING id INTO new_pet_id;

        COMMIT;
        RETURN new_pet_id;
    END add_pet;

* Add a new Pet return new id

FUNCTION add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2

) RETURN NUMBER IS

new_pet_id NUMBER;

BEGIN

INSERT INTO lcs_pets (

name,

owner,

type

) VALUES (

name_p,

owner_id_p,

lower(pet_type_p)

) RETURNING id INTO new_pet_id;

COMMIT;

RETURN new_pet_id;

END add_pet;

Using Python to call a function in the database and get the return value I’ll use the cursor.callfunc method.

I set the variables that I’ll use as arguments to the function.
Define a new_pet_id variable and assign it the value returned from callfunc.
The second argument of the callfunc method is used to define the type of the data being returned. I’ll set it to int. (cx_Oracle will handle the NUMBER to int conversion.)
I pass in the array of values just like I did when I used callproc.
Print the returned value for new_pet_id.

pet_name = 'Roger'
owner_id = 1
pet_type = 'fish'

# add a new pet
new_pet_id = cur.callfunc('pet_manager.add_pet',
      int,
      [pet_name, owner_id, pet_type])

print (new_pet_id)

pet_name = 'Roger'

owner_id = 1

pet_type = 'fish'

# add a new pet

new_pet_id = cur.callfunc('pet_manager.add_pet',

int,

[pet_name, owner_id, pet_type])

print (new_pet_id)

 Example: python3 execute_function.py
4

 Example:

Example: python3 execute_function.py

Example:

Out Parameters

Out parameters can be very handy when you need to pass back more than one piece of information. I have an add_pet function in the PL/SQL package that will check to see if the pet type you’re adding needs a license or not. The function will return the new id like before, and a ‘yes’ or ‘no’ through the out parameter.

/*
   * Add a new Pet return new id and if it needs a license.
   */

   FUNCTION add_pet (
      name_p           IN VARCHAR2,
      owner_id_p       IN NUMBER,
      pet_type_p       IN VARCHAR2,
      need_license_p   OUT VARCHAR2
   ) RETURN NUMBER IS
      new_pet_id   NUMBER;
   BEGIN
      INSERT INTO lcs_pets (
         name,
         owner,
         type
      ) VALUES (
         name_p,
         owner_id_p,
         lower(pet_type_p)
      ) RETURNING id INTO new_pet_id;

      IF lower(pet_type_p) IN ('dog', 'cat') THEN
         need_license_p := 'yes';
      ELSE
         need_license_p := 'no';
      END IF;

      COMMIT;
      RETURN new_pet_id;
   END add_pet;

* Add a new Pet return new id and if it needs a license.

FUNCTION add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2,

need_license_p OUT VARCHAR2

) RETURN NUMBER IS

new_pet_id NUMBER;

BEGIN

INSERT INTO lcs_pets (

name,

owner,

type

) VALUES (

name_p,

owner_id_p,

lower(pet_type_p)

) RETURNING id INTO new_pet_id;

IF lower(pet_type_p) IN ('dog', 'cat') THEN

need_license_p := 'yes';

ELSE

need_license_p := 'no';

END IF;

COMMIT;

RETURN new_pet_id;

END add_pet;

To work with the out parameter in Python I’ll add a string variable called ‘need_license’. It can be defined using ‘cursor.var(str)‘. Then we just add the new variable to the values array in the correct position. This works the same when using out parameters with the callproc method.

To get the value from ‘need_license’ we call it’s getvalue() function.

pet_name = 'Roger'
owner_id = 1
pet_type = 'dog'
need_license = cur.var(str)

# add a new pet
new_pet_id = cur.callfunc('pet_manager.add_pet',
      int,
      [pet_name, owner_id, pet_type, need_license])

print ("New pet id: {}\nLicense needed: {}".format(new_pet_id, need_license.getvalue()))

pet_name = 'Roger'

owner_id = 1

pet_type = 'dog'

need_license = cur.var(str)

# add a new pet

new_pet_id = cur.callfunc('pet_manager.add_pet',

int,

[pet_name, owner_id, pet_type, need_license])

print ("New pet id: {}\nLicense needed: {}".format(new_pet_id, need_license.getvalue()))

 Example: python3 execute_function_out.py
New pet id: 5
License needed: yes

 Example:

Example: python3 execute_function_out.py

New pet id: 5

License needed: yes

Example:

Accept Argument Values

So far I’ve hard-coded the variable values in the Python code and the methods are fairly simple, so there’s a low chance of errors. But, for most methods, we want to accept parameter values that can be passed into the Python code then on to the PL/SQL functions. I’ll modify the Python method to accept command line arguments.

We need to import sys so that we can use sys.argv[] to grab the command line arguments and assign them to the variables.

import cx_Oracle
import os
import sys

connectString = os.getenv('db_connect')
con = cx_Oracle.connect(connectString)
cur = con.cursor()

pet_name = sys.argv[1]
owner_id = sys.argv[2]
pet_type = sys.argv[3]
need_license = cur.var(str)

# add a new pet
new_pet_id = cur.callfunc('pet_manager.add_pet',
      int,
      [pet_name, owner_id, pet_type, need_license])

print ("New pet id: {}\nLicense needed: {}".format(new_pet_id, need_license.getvalue()))

import cx_Oracle

import os

import sys

connectString = os.getenv('db_connect')

con = cx_Oracle.connect(connectString)

cur = con.cursor()

pet_name = sys.argv[1]

owner_id = sys.argv[2]

pet_type = sys.argv[3]

need_license = cur.var(str)

# add a new pet

new_pet_id = cur.callfunc('pet_manager.add_pet',

int,

[pet_name, owner_id, pet_type, need_license])

print ("New pet id: {}\nLicense needed: {}".format(new_pet_id, need_license.getvalue()))

If I run this to add a dog, I get:

 Example: python3 accept_input.py rover 2 dog
New pet id: 4
License needed: yes

Example: python3 accept_input.py rover 2 dog

New pet id: 4

License needed: yes

Adding a fish, I get:

 Example: python3 accept_input.py roger 1 fish
New pet id: 4
License needed: no

Example: python3 accept_input.py roger 1 fish

New pet id: 4

License needed: no

PL/SQL Exceptions

Now that I’m accepting outside argument values, the odds that I’ll eventually get errors with the above code is almost a certainty. If an error happens in the Python code you can handle it as you normally would. But, what if there’s an error thrown by the PL/SQL code?

It’s easy enough to test this. Make the same call as before but pass in a string for the second value.

 Example: python3 accept_input.py rover 2x dog
Traceback (most recent call last):
  File "accept_input.py", line 22, in <module>
    [pet_name, owner_id, pet_type, need_license])
cx_Oracle.DatabaseError: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1

Example: python3 accept_input.py rover 2x dog

Traceback (most recent call last):

File "accept_input.py", line 22, in <module>

[pet_name, owner_id, pet_type, need_license])

cx_Oracle.DatabaseError: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at line 1

I would recommend that you handle errors as close to where they happen as you can. In this example, you could catch the error in the PL/SQL function and either handle it or raise it. If you don’t handle it in PL/SQL it will be passed back to cx_Oracle which will throw a cx_Oracle.DatabaseError. At that point, you can handle it as you would when any other Error is thrown in your Python application.

Additional Resources

cx_Oracle Documentation
https://www.python.org/about/gettingstarted/
Steven Feuerstein’s Blog
Bryn Llewellyn’s Blog
Oracle DevGym
Oracle LiveSQL
If you have any problems using cx_Oracle, you can get help here.

DevOps, Learning, PL/SQL, Version Control

CI/CD for Database Developers – Export Database Objects into Version Control

July 16, 2018 bcarter Leave a comment

With this post, I kick off a series that walks you through the process of building database applications using a CI/CD pipeline. I will be covering:

Export Database Objects into Version Control (This Post)
Use Schema Migration
Unit Test PL/SQL
Build and Deploy
Automate

Links will be added for each topic as that article is published. I will use the DinoDate application (source) as my code base.

For this post, assume that I have DinoDate installed and all development to date has been done directly in the database (source code not stored externally in files).

Since, however, it’s really not a good idea to edit and maintain code directly in the database, I’d like to switch to storing the DDL for my database schema objects in files, managed by a version control system. Let’s get going!

Export the Database Objects out to Files

I could export the code for my objects out into files by writing queries against built-in database views like ALL_SOURCE. I have used this method a few times, and you probably have, too. You can make it work, but it puts the onus on you to get everything right. I’d rather think about other things, so I ask myself: is there a tool that will take care of the heavy lifting on this step?

There sure is, and it’s free: Oracle SQL Developer has a great export tool. Assuming you’ve got SQL Developer installed and you can connect to your schema, here’s what you do:

Under the Tools menu, click on Database Export.

The first step of the wizard configures the general export attributes:

Choose the connection for the schema you want to export.
Select the options you’d like the wizard to use when creating the export scripts.
I like to add drop statements to my scripts. You may also want to export grants.
I want to export some of the master table data so I will leave the export data section set with defaults.
Choose “Save As Separate Directories” and select the export directory.

In step 2 I left all of the default options selected.

I want all of the schema objects so I skip step 3.

I selected the tables that contain the pre-loaded master data for the application.

Everything looks good so click finish.

The export wizard also creates a master run script and opens the script in a new worksheet (I’ll come back to this later):

Now that I have everything exported, I want to get the files into version control asap.

Add the Objects to Version Control

I’ll be using Git for version control. SQL Developer has some nice integrated tools for working with Git but for now, I’m just going to use the command line.

Change to the export directory.
Initialize a Git repository.
Add all files to the repo.
Commit the changes.

[~]$ cd ~/Projects/Oracle/DevOpsLab/DdlExport/DD
[DD]$ git init
Initialized empty Git repository in /mnt/f5e545bf-a2d6-45c0-b7ea-29d8476a5e06/Projects/Oracle/DevOpsLab/DdlExport/DD/.git/
[DD]$ git add .
[DD]$ git commit -m "Initial Commit"
[master (root-commit) f94be76] Initial Commit
 94 files changed, 3222 insertions(+)
 create mode 100644 CONSTRAINTS/DATABASECHANGELOG.sql
 create mode 100644 CONSTRAINTS/DATABASECHANGELOGLOCK.sql
 create mode 100644 CONSTRAINTS/DD_DEQUEUE_ERRORS_T.sql
 create mode 100644 CONSTRAINTS/DD_DINOSAURS_T.sql
...

[~]$ cd ~/Projects/Oracle/DevOpsLab/DdlExport/DD

[DD]$ git init

Initialized empty Git repository in /mnt/f5e545bf-a2d6-45c0-b7ea-29d8476a5e06/Projects/Oracle/DevOpsLab/DdlExport/DD/.git/

[DD]$ git add .

[DD]$ git commit -m "Initial Commit"

[master (root-commit) f94be76] Initial Commit

94 files changed, 3222 insertions(+)

create mode 100644 CONSTRAINTS/DATABASECHANGELOG.sql

create mode 100644 CONSTRAINTS/DATABASECHANGELOGLOCK.sql

create mode 100644 CONSTRAINTS/DD_DEQUEUE_ERRORS_T.sql

create mode 100644 CONSTRAINTS/DD_DINOSAURS_T.sql

...

Assuming that you’re using a central Git repository, you should push the new repo up to the shared repo. That process can differ based on which repository host you’re using so I’ll leave it up to you to consult the documentation.

With my files stored safely in Git, it’s time to

Verify / Clean up

Whenever you use any automated code generate/export tool, you should verify the results.

For example, you may want to change the generated SQL for the dinosaure_id column from

"DINOSAUR_ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE,

1	"DINOSAUR_ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE,

dinosaur_id  NUMBER GENERATED ALWAYS AS IDENTITY,

1	dinosaur_id NUMBER GENERATED ALWAYS AS IDENTITY,

After you verify each file, run a code beautifier on it. Maintaining a standard code format will make it easier to see the differences between changes. This will make your future code reviews much easier.

Make sure you commit each file as you change it. Don’t wait till the end to do a massive commit.

Organize

In step 1 of the wizard, I selected Save as Separate Directories so all of my object scripts have been grouped into directories by type, like this:

dd
 -CONSTRAINTS
 -DATA_TABLE
 -DATA_VIEW
 -FUNCTIONS
 -INDEXES
 -PACKAGE_BODIES
 -PACKAGES
 -PROCEDURES
 -REF_CONSTRAINTS
 -SYNONYMS
 -TABLES
 -TRIGGERS
 -TYPES
 -VIEWS

-CONSTRAINTS

-DATA_TABLE

-DATA_VIEW

-FUNCTIONS

-INDEXES

-PACKAGE_BODIES

-PACKAGES

-PROCEDURES

-REF_CONSTRAINTS

-SYNONYMS

-TABLES

-TRIGGERS

-TYPES

-VIEWS

This is a perfectly fine way to organize your scripts. However, I like to group mine by whether or not the scripts will be changed and re-run vs run a single time and future changes will be done with new scripts. For example:

dd
 -Run_Once
   -CONSTRAINTS
   -DATA_TABLE
   -DATA_VIEW
   -INDEXES
   -REF_CONSTRAINTS
   -SYNONYMS
   -TABLES
   -TYPES
 -Run_On_Change
   -FUNCTIONS
   -PACKAGES
   -PACKAGE_BODIES
   -PROCEDURES
   -TRIGGERS
   -VIEWS

-Run_Once

-CONSTRAINTS

-DATA_TABLE

-DATA_VIEW

-INDEXES

-REF_CONSTRAINTS

-SYNONYMS

-TABLES

-TYPES

-Run_On_Change

-FUNCTIONS

-PACKAGES

-PACKAGE_BODIES

-PROCEDURES

-TRIGGERS

-VIEWS

If I were going to run everything from scripts, I would also break down the Run_Once directory into product versions with a subdirectory for ‘create from scratch’ and one for ‘updates’, like this:

-Run_Once
  -v4.1
    -create
      -new_table1.sql
      -new_table2.sql
    -update
      -alter_table5.sql
      -alter_table8.sql
  -v5.0
    -create
      -new_table1.sql
      -new_table2.sql
    -update
      -alter_table12.sql

-Run_Once

-v4.1

-create

-new_table1.sql

-new_table2.sql

-update

-alter_table5.sql

-alter_table8.sql

-v5.0

-create

-new_table1.sql

-new_table2.sql

-update

-alter_table12.sql

In another post, I’ll show you how to use a Schema Migration tool which makes managing schema changes much easier.

Master Build Script

Now that everything is cleaned up and organized, we need to modify the master script that was generated by the export. It should be named with a timestamp in a format similar to this:

Generated-YYYYMMDDHH24MISS.sql.

My exported script was named Generated-20180706134514.sql.

You will want to edit this file and modify the directory for the scripts to match the changes you made above. Also, you need to verify that the scripts are being run in the order of dependencies. Objects should be created after the objects they depend on.

If you’re not planning to use a Schema Migration tool you’ll want to create separate master scripts for each of the create and update directories.

Test

Create a new schema that’s safe to test with, and run your master scripts. After you get the scripts running without errors, do a schema compare to check for any differences between your new schema and the schema you exported from.

When it’s all the way you want it, you are now ready and able to work from files to build your database code.

Working From Files

From this point, you can now follow a new and improved workflow when it comes to editing the code for your database objects:

Pull the latest version of your code from the shared repository. NEVER ASSUME YOU ALREADY HAVE THE LATEST.
Make your changes.
Compile to the database.
Commit (to your source code repository, not your transaction) often.
Push your changes back up to the shared repository.

Of course, that is a very simplified workflow. You should start a discussion with your team about using more advanced methods to further automate and improve your processes.

I sincerely hope you skimmed this post because you gave up long ago on editing code directly in the database. If that is not the case, I hope this article helps you make the change. Because then the next time something goes badly wrong, you can simply recover from Git (or your repository of choice).

No tearing your hair out. No gnashing of teeth. No self-hating recriminations. Just a quick recovery and get back to work.

And then you can move on to more interesting challenges in improving the way you write and maintain your database application code.

DevOps, PL/SQL, Tools, Version Control

Tips to help PL/SQL developers get started with CI/CD

April 30, 2018 bcarter 1 Comment

In most ways, PL/SQL development is just like working with any other language but, sometimes it can be a little different. If you’d like to create a Continuous Deployment Pipeline for your PL/SQL application, here is a short list of tips to help you get started.

Work From Files

Do not use the Database as your source code repository. If you are making changes to your application in a live database, stop right now and go read PL/SQL 101: Save your source code to files by Steven Feuerstein.

Now that you’re working from files, those files should be checked into…

Version Control

There are a lot of version control applications out there, the most popular right now is probably Git. One advantage of using Git is, you work in your own local copy of the repository making frequent commits that only you see. If you’re working in your own personal database you could compile your changes to the database at this point.

If you’re working in a database shared with other people and you’re ready to compile your code, git pull from the central repository to get any changes since your last pull. Handle any merge conflicts, then git push your changes back up to the shared repository. After the push, you can compile your code to the database. This helps ensure that people don’t overwrite each other’s changes.

Making frequent small commits will help keep everything running smoothly.

What about your schema objects?

Your schema objects, such as tables and views, are as much a part of your application as any other component and changes to these objects should be version controlled as well. The process of keeping track of schema changes is called Schema Migration and there are open source tools you can use such as Flyway and Liquibase.

Unit Testing

In any application it’s smart to have good test coverage, If your goal is Continuous Deployment it’s critical. A Unit Test is when you test a single unit of code, in PL/SQL that may be a function or procedure.

Unit tests typically follow these steps:

Setup the environment to simulate how it will be when the application is deployed. This might include changing configuration settings and loading test data.
Execute the code unit.
Validate the results.
Clean up the environment, resetting it to the state it was in before running the tests.

utPLSQL is a great tool for unit testing your PL/SQL applications. You will write a package of tests for each ‘unit’ of your application which should test all of the required functionality and potential errors. utPLSQL is an active open source project with an impressive set of features. Check out the docs to get started.

Build

Building a database application usually consists of running the change scripts in a specific order. It’s common to create a master script that executes the others in order of how the objects depend on each other. However, if the master script simply executes the other scripts, you will need to create additional scripts to track and verify changes, and more scripts to give you the ability to rollback the changes if/when there’s a problem.

There are build tools such as Gradle and Maven that can easily execute your scripts. But you’ll still need to create the additional control scripts. If you use a Schema Migration tool it should include a lot of these additional functions without having to write extra scripts. For an example, check out Dino Date which has a Liquibase migration included.

How to handle the PL/SQL code

You could include your PL/SQL code in a Schema Migration changeset but adding schema migration notation to your PL/SQL introduces another layer of complexity and potential errors.

In the Dino Date runOnChange directory, you will find examples of setting up Liquibase changesets that watch for changes in the files of objects that you would rather keep ‘pure’. When you run a migration, if the file has changed Liquibase will run the new version.

In a shared database environment, you should execute a schema migration after you pull/merge/push your changes into your version control system.

Automate!

All of these pieces can be tied together and automated with an automation server such as Hudson or Jenkins (both are open source) to create a build pipeline.

Original by Jez Humble

A simple (maybe too simple) build pipeline using the above tools could follow these steps:

Developer makes a change and pushes it to the shared Git repository.
Hudson notices the repository has changed and triggers the build pipeline.
The project is pulled from Git.
Liquibase deploys the changes to a test database.
utPLSQL is trigged to run the unit tests.
Liquibase deploys the changes to the production database.

Other Useful Tools

Edition Based Redefinition[pdf] can be used to deploy applications with little to no downtime.
Oracle Developer Cloud Service comes with a ton of pre-configured tools to help with almost every aspect of your development process.
Gitora can help you version control your application if you are not able to move out to files.

Simple Test App

Create a Docker Container for the Node.js app

Typical Oracle Database

An Oracle Database inside a Docker Container

Oracle Autonomous Transaction Processing Cloud Database

I used the same docker image for all of the examples

Share this:

It’s going to be hard… right?

New User

Create a test API

Get the URI

Test it

Well, that was easy.

Go try it!

Share this:

Required Downloads

Oracle docker-images repository

Oracle Xe

Oracle Rest Data Services (ORDS)

Docker Network

Oracle Database Xe

Build the Oracle Database Docker Image

Create a Docker Container

Oracle REST Data Services

Oracle Linux 7 and Java JRE Base Image

ORDS

Pre-Checks

Build the ORDS Docker Image

Create an ORDS Docker Container

Share this:

Prerequisites

Downloads

Versions

Working in the host and virtual machine environments

Create the Toolbox

Edit dbDevToolbox/Vagrantfile

Create the VM and install Oracle Database XE

Install the Jave Runtime Environment (JRE)

Create the file dbDevToolbox/ords_params.properties

Install ORDS

Autorun ORDS on boot

Test

Automate

Future project virtual machines

Resources

Share this:

Assumptions

Test the Existing Service

Secure Your Service

Create a Role

Create a Privilege

Test

Access Your Service

Create an OAuth 2 Client

Test

Example Usage

On the application server

In your application

Summary

Share this:

Configure your build job

Authorized SSH Keys

Add a build environment configuration

Add / Edit a builder

Run it

Share this:

Oracle Open World is almost here!

Monday

Tuesday

Thursday

Share this:

Prerequisites

Setup

Cleanup

Boilerplate template

Anonymous PL/SQL Block

Execute a PL/SQL Procedure

Pass Parameters

Get PL/SQL Function Return Values

Out Parameters