All posts by bcarter

Use ORDS to run the CodeCard backend on your own free Oracle Cloud Database

ORDS (Oracle Rest Data Services) is a fast and easy way to host a REST API you can use to take full control of your Oracle CodeCard.

In this post I’ll walk through how to create the ORDS API on your own Always Free Oracle Cloud Database.

Prerequisites

    1. Create a compartment in your Oracle Cloud Account (Optional but recommended)
    2. Create an Always Free Autonomous Database on the Oracle Cloud
    3. Go to the Service Console for your new database, click on “Development” and save the URL in the “RESTful Services and SODA” section.
    4. CodeCard Sketch changes: Howto disable fingerprint check and increase REST URI size
    5. You Went to Oracle Open World 19 and got a Code Card, now what?
      Ignore the fingerprint settings since you just disabled that in the previous step but add the following.
      Replace the below {{URL}} with the URL you saved above.
      Make sure there’s only one / between your URL and cc.  For example:
      buttona1=https://asdr34edyjtyi4j-codecard.adb.us-ashburn-1.oraclecloudapps.com/ords/cc/functions/master

When all of that is done, go to the Development section of the Service Console for your ATP instance and click “SQL Developer Web”.  Sign in as admin with the admin password you created in step 2.

Database Setup

As Admin, create the CODE_CARD schema.

Execute the following code in the worksheet.  Use a good password and remember it for later.

This will create the new CODE_CARD schema and REST enable the schema so you can use it with ORDS.  Notice the p_url_mapping_pattern is set to ‘cc’, this will be part of the REST URI in the following sections.

oraclecloudapps.com/ords/cc/
As code_card, create the database objects

Switch to the code_card schema by changing the URL In your browser.  Replace ‘admin’ with ‘cc’.
...oraclecloudapps.com/ords/admin/_sdw/?nav=worksheet  to ...oraclecloudapps.com/ords/cc/_sdw/?nav=worksheet

Log into SQL Developer Web as code_card using your new password.

In a worksheet, use the following to create the tables.

Oracle REST Data Services API

You will need two rest endpoints.   One to POST the setting for each button press and one your CodeCard will use to GET those settings.

There are multiple ways you can create an ORDS API, this post will walk through how to create the API using PL/SQL.  I will explain the settings that are most important for the CodeCard interface.  For the other settings, you can find the full documentation here.

This step was completed above

When you created the new schema above, you also REST enabled the new schema with this PL/SQL.

This command was included in the above step so that you could log into SQL Developer WEB as code_card.

The following will explain the individual PL/SQL procedures used to create the REST API.

If you want to skip the explanation, you can jump straight to the complete code included below.

Define a module

A module is a collection of related REST services.  Think of a module as a package that contains REST endpoints.

Create a module called ‘functions’.

The base path is set to ‘/functions/’, this will be part of the REST URI.

oraclecloudapps.com/ords/cc/functions/
Define a Template

A Template is the endpoint for your REST API.

The pattern is set to ‘master’, this will be the final part of the REST URI.

oraclecloudapps.com/ords/cc/functions/master
GET request handler

The CodeCard sends a GET request to the REST API to retrieve the settings assigned to a specific button (A or B) and press function (long or short).

In order to handle this request we need to define a GET handler.

Using a source type of  ‘json/collection’ would allow you to use a simple SQL query and ORDS would handle all of the JSON formatting for us.  This would be easier.

However, we want to be able to return a functioning screen layout to the CodeCard even if there’s an error.  Using a source type of ‘plsql/block’ lets you create and return a custom APEX_JSON object even if there’s an error.

Parameters for the bind variables

The PL/SQL code uses 3 bind variables that are mapped to incoming request header values.

The bind variables are mapped to the header values by defining parameters. These parameters will all be assigned to the ‘functions’ module, ‘master’ template and  ‘GET’ handler.  They will be passed ‘IN’ through the ‘HEADER’ as a ‘STRING’ type.  The value of the incoming header defined in ‘p_name’ will be mapped to value of the PL/SQL bind variable defined in ‘p_bind_variable_name’.

  • X-CODECARD-ID is the id assigned to your CodeCard.
  • X-CODECARD-BUTTON-LABEL will either be the A or B button.
  • X-CODECARD-BUTTON-FUNCTION will be 1 for a short press or 2 for a long press.
Handle POST requests

The CodeCard sends a POST request to the REST API to store the settings assigned to a specific button and press function.

In order to handle this request we need to define a POST handler.

This PL/SQL block will either create a new record or update an existing record for the incoming CodeCard id, button label and button function.

Parameters for the bind variables

The parameters for the POST handler are the same as for the GET handler.

Register template (Optional)

The register template is used by the CodeCard designer to map your name to your CodeCard id, if you choose to enter it.  Another post will detail how to install the CodeCard designer in an always free Oracle compute instance.  If you plan on implementing the designer, include this handler template and handler.  Even if you do not plan on implementing the designer, it won’t hurt anything to include these objects.

If you are piecing all of the above sections together you will need to execute a ‘COMMIT’ to save the changes to your database.  Or, you can use the following.

Full ORDS PL/SQL code

In a worksheet use the following code to create the Oracle REST Data Services API.

Setup a button press

One final piece of information you’ll need is your CodeCard’s id.

  1. Turn your CodeCard on.
  2. Press and release the A and B buttons at the same time.  Your CodeCard will display a barcode with a 12 character id string under it.
  3. Save that ID string.

Testing

POST

If you are using a tool such as Postman to test your API you will need to create a POST request with the following pieces.

  • URI:  {{URL from the prerequisite section}}/cc/functions/master
    For example: https://asdr34edyjtyi4j-codecard.adb.us-ashburn-1.oraclecloudapps.com/ords/cc/functions/master
  •  Headers
    • X-CODECARD-ID: Us the ID string from above
    • X-CODECARD-BUTTON-LABEL:  either ‘a’ or ‘b’
    • X-CODECARD-BUTTON-FUNCTION: use ‘1’ for a short press or ‘2’ for a long press
    • Content-Type: application/json
  • Body: use a JSON object like this
     

If you’d prefer to use a CURL command (replace the {{ }} sections with your values):

With either method, you should receive a response with a status of 200 and a JSON object in the body like this

Test GET

Assuming you completed the prerequisite section, you should be able to short or long press the A or B button on your CodeCard and your new template settings will be displayed on your screen.

You can also test the GET handler by sending a GET request to the same URI used above.  Use the same values for the headers, you do not need a body.

  • X-CODECARD-ID: Use the ID string from above
  • X-CODECARD-BUTTON-LABEL:  either ‘a’ or ‘b’
  • X-CODECARD-BUTTON-FUNCTION: use ‘1’ for a short press or ‘2’ for a long press

If you’d rather use CURL (replace the {{ }} sections with your values):

You should receive a JSON object like the one you sent in the body of your POST.

If the GET request works but your CodeCard doesn’t, go back to the prerequisite section and make sure you followed steps 4 and 5 correctly.

Have Fun

Once you get everything working you could experiment with changing the ORDS GET handler to do something else when a button is pressed.

  • Create a new table and log which button is pressed and if it’s a short or long press.
  • Run a query on a table and return the results in the bodytext.  (There are some size limits.)
  • Execute a stored procedure that does whatever you want it to do.
  • Make a different module/template/GET API for each button/press.

Create an Always Free Autonomous Database on the Oracle Cloud

You’ve signed up for the Oracle Cloud Free Tier at https://www.oracle.com/cloud/free/ and now you want to create an Always Free Autonomous Database.

Create a new Database

These instructions should work for both (ATP) Autonomous Transaction Processing and (ADW) Autonomous Data Warehouse Databases.

After you log into your Oracle Cloud Dashboard.

  1. Click on the “Create an ATP database” box. (Or the “Create an ADW database” box if you want a data warehouse)
  2. Choose the compartment you want to use.
  3. Fill in the “Display Name” field.  This name will be displayed in the GUI lists and drop down selection boxes in your Oracle Cloud dashboard.  You can change this later.
  4. Fill in the “Database Name” field.  This name will be used as the permanent ID for this database.  It will show up in places such as the auto-generated TNSNAMES.ORA file that you’ll download in another step.  You can not change this later, so choose wisely.
  5. In the “Choose a workload type” section, make sure the correct workload type is selected..
  6. Since we want to create an Always Free Database, leave “Shared Infrastructure” selected in the “Choose a deployment type” section.
  7. Make sure the “Always Free” selector is turned on.  This will lock in the options for an Always Free instance.  (20 GB of storage and 1 OCPU)
  8. Scroll down to the “Create administrator credentials” section and enter a Strong password for your Admin account.
    You will use the Admin account to connect to your Autonomous Database instead of SYS or System.
  9. Under the “Choose a license type” section make sure “License Included” is selected.
  10. Click the “Create Autonomous Database” button.

Your new ATP instance will be provisioned.  The orange box will turn green once it has been provisioned and is available to use.

While you’re waiting, check that you see the Always Free tag next to your database name.  If you do not see the tag, you may want to check to see what license you created the instance under and make sure you are OK with those options or terminate the instance and create a new one with the Always Free options selected.

Once the orange box turns green and your database is available, you will want to connect to it.

The Oracle Autonomous Cloud Databases are setup with a little extra security.  In order to connect you will need to use a wallet credentials file.

Download the Oracle Credentials file

There are a couple different ways to get the wallet file through the Cloud Dashboard.

From the Details page
    1. Click on the “DB Connection” button.
    2. On this form you will find a list of the auto-generated TNSNAMES entries and connection strings that you can use to connect to your database.  There is a link to the documentation explaining the difference between the entries.  For typical transactions I like to use the one ending in _TP.
    3. Click the “Download Wallet” button.
    4. Create a password that will be used to access the .zip file.
    5. Click the “Download” button.

      Remember the location where you save your wallet file, you will need it when we test the connection.
    6. Save the .zip file to a secure location.  This file is used to access your database so keep it secure.
From the Service Console

You can get to the Service Console for your Database instance either from the Details page

or from the menu for the instance in the Autonomous Transaction Processing Database list.

Once your in the service Console

  1. Click on “Administration”.
  2. Click on “Download Client Credentials (Wallet)”.
  3. Create a password that will be used to access the .zip file.
  4. Click the “Download” button.
Important Wallet Security Information

All wallets downloaded with either method will create a copy of the *SAME WALLET* even if you use different passwords for the .zip file.

You can rotate the wallet from your database instance details page.  Read the docs.

  1. Click the “DB Connection” button.
  2. Press the “Rotate Wallet” button and follow the instructions.

Connect to the Database

SQLcl is a great tool for making a quick and easy connection to the new database.

  1. Start SQLcl in ‘nolog’ mode
  2. Set the location of your .zip file in the cloudconfig variable.
  3. Connect to your database using the password you set for the Admin account.
  4. Run a test query

Enjoy

Your new Always Free Oracle Autonomous Database is running and you are able to connect.  Now you can start building your application.

If you have an questions please post them in the comments and I’ll do my best to help you find an answer.

Create an Always Free Compute VM on the Oracle Cloud

You’ve signed up for an Oracle Cloud Free Tier at https://www.oracle.com/cloud/free/ and now you want to deploy an app to a virtual machine compute instance.

Prerequisite

You will need an SSH key pair.  You can use an existing key or you can generate a new one.

Create a new Compute instance

After you log into your Oracle Cloud Dashboard.

  1. Click on the “Create a VM instance” box
  2. In the form that opens, you can give your new instance a name that makes sense for your project or use the default name
  3. Click “Show Shape, Network and Storage Options”
  4. Make sure that the Always Free options are selected for:
    1. Availability Domain
    2. Instance Type
    3. Instance Shape
  5. Choose the compartment you want to use for your cloud network objects for both:
    (If you have not created a Virtual cloud network in your selected compartment, the system will create it for you.)

    1. Virtual cloud network compartment
    2. Subnet compartment
  6. If you would like a public IP assigned when the instance is created select the option “Assign a public IP address”
  7. In the “Add SSH key” section you can add your SSH public key using one of these methods:
    1. Uploading the file with the “Choose File” button
    2. Copying and pasting the key into the text field
    3. Drag the public key file into the “Drop Files Here” box
  8. Click “Show Advanced Options”
  9. Select the Compartment you would like to use for your new Compute instance
  10. Click the “Create” button

Your new Compute instance will be provisioned.  The Orange box will turn Green when it has been provisioned and is available to use.

Look in the “Instance Information” tab, in the “Primary VNIC Information” section and get your assigned “Public IP Address”.

Test with SSH

You will want to look up the best way to open an SSH connection for your operating system.

The default user that you will connect as is “OPC”.

On Linux you can test your connection using the following command:

You should see the following question asking if you are sure you want to connect to this new instance.  This should only happen the first time you establish a connection to this instance from this machine.

Type “yes” and hit enter.

You should now be connected to your new instance and can start setting it up to deploy your application.

Create a compartment in your Oracle Cloud Account

Whenever I’m working on a project, I like to keep the related objects grouped together and separated from other projects, to make my life easier.

Oracle cloud helps you to do this with compartments and making them is pretty easy.

I’m using an Oracle Cloud Free Tier for my example, if you don’t have an account you can get one at https://www.oracle.com/cloud/free/

After you log into your Oracle Cloud Dashboard.

  1. Open the Menu
  2. Scroll down and click Identity
  3. Click on Compartments
  4. Click on the Create Compartment button
  5. Fill in Name and Description
  6. Choose a parent compartment or leave it set to the root compartment
  7. Click the ‘Create Compartment’ button

Now when you create a new object such as an Autonomous Database or a Compute instance you can keep them organized in your new compartment.

 

CodeCard Sketch changes: Howto disable fingerprint check and increase REST URI size

Pre-Requisites

  • You have followed the instructions here.
  • You are comfortable using the Arduino IDE and making changes to the Sketch.

Fingerprint Check

In the comments on the post You Went to Oracle Open World 19 and got a Code Card, now what? we talked about how to update the fingerprint of the security certificate for the REST API when it changes.

I’m not a big fan of doing things multiple times and since I’m not planning on doing anything of a sensitive nature with my CodeCard I decided I would just disable the fingerprint check altogether.

How to tell if the fingerprint check is causing your badge to stop working.

If you push one of the buttons and it hangs on the “Please wait…” screen forever, check the Serial Monitor for a message similar to this.

Warning, If you’re planning to use your CodeCard for anything you’d consider sensitive, do not make this change!

Upgrade the driver

  1. Clone or download the CodeCard repository
  2. In the Arduino IDE, open the file codecard/arduino/codecard/codecard.ino
  3. Open the Board Manager.  Tools/Board/Boards Manager
  4. Search for esp
  5. Select version 2.5.2
  6. Click Install
  7. Close the Boards Manager
Code Changes
  • Click on the httpClient.h tab
  • Replace line 74 with BearSSL::WiFiClientSecure client;

  • On line 102 add client.setInsecure();

  • Comment out the fingerprint check starting on line 112

  • Uncomment line 152 and remove line 153

  • On line 176 add secureClient.setInsecure();

  • Comment out the fingerprint check starting on line 185

Increase arrayToStore variable size

If you’re planning to change the REST calls to use a different and longer URI you will want to increase the size of the arrayToStore variables.  Currently they are set to 100, I changed mine to 200, but you can use whatever you’d like.

  • Click on the memory.h tab
  • On lines 18 and 46, change the arrayToStore variable to the new size
  • Click on the wifi.h tab
  • On line 7, change the arrayToStore variable to the new size

Troubleshooting

Sketch uses too much program storage space

I have seen this error off and on when I compile the sketch.  If you get this error, the easiest way to reduce the size of the sketch is to remove some of the larger icons in icons.h.

  • Click on icons.h
  • Find a 128x128px image that you don’t plan to use and either comment it or remove it.
  • Click on templates.h
  • Find the drawIcon128(int x, int y, String icon, long color) method and comment or remove the reference to the image you removed.
  • You may have to remove more than one image.
“Connection failed” or you receive an empty response “{}”

This one can be tricky.  The way I’ve solved this is to erase the ESP8266 flash memory.

Usually this works:

  • Connect with the Serial Monitor
  • Press and release the A and B buttons
  • Wait for the Serial Monitor to display all of the data
  • Enter “reset” and send
  • Enter “ls” and update any settings as needed

If that doesn’t work you may need to search for another way to reset the memory.  Please leave a comment if you have a better way.

 

You Went to Oracle Open World 19 and got a Code Card, now what?

We handed out several hundred Code Cards this year and I’m guessing a few of you are wondering what to do next.

If you got home and pressed one of the buttons you more than likely saw this screen.

Don’t worry, it’s not broken, you just need to get it connected to the internet.  There are a few different ways to do this.

Note: The Code Card uses a 2.4 GHz WiFi connection.

Create a WiFi Network

The easiest way to get it connected is to create a WiFi network using the credentials that are already set on the Code Card.

One way you can do this is to create guest network on your WiFi router.  Please refer to your router manual for instructions on setting this up.

Alternatively, you could create a hotspot on your phone, but make sure you’re OK with any charges from your service provider.

Edit:

At Oracle we like to keep everything patched and up to date.  After I published this post, the security certificate for the REST back end was updated.  This means you will also need to update the fingerprint settings for the four button options.

This also means that creating a WiFi SSID as in the paragraph above will only work if you also update the following four settings.  For that you’ll need to choose one of the methods below.   (I have added them to the examples below.)

Change the WiFi credentials on the card

If you’d rather just connect the Code Card to your existing WiFi network (2.4 GHz only), you can change the settings on the card.

In order to do this you need to open a serial connection to the card and send the following commands (using your WiFi credentials):

A Little Python

If you’d like to use Python:

  1. Install Python 3 if you don’t already have it.
  2. Open a terminal and install pyserial.
  3. Create a file using the following code.  I named mine ccSerial.py.
  4. Change lines 7 and 8 to use your WiFi credentials.
  5. In the terminal run the new module and follow the instructions.
    You should see something similar to the following.
Arduino IDE

If you have a Serial communication tool that you like, you should be able to use it to change the settings.

If not the Arduino IDE includes a Serial Monitor tool that work great.

  1. Do not connect your Code Card to the computer yet.
  2. Download, install and run the Arduino IDE.
  3. Click on Tools / Port.
  4. See what Ports are already in use.
  5. Connect your Code Card and turn it on.
  6. Click on Tools / Port.
  7. The newest Port should be your Code Card; select it.
  8. Click on Tools / Serial Monitor.
  9. On the bottom of the Serial Monitor, choose 115200 baud.
  10. Turn your Code Card off and on again.
  11. Press and release the A and B buttons at the same time.
    You should see something like this.
  12. In the input field at the top enter (use your SSID)
    Click Send.
    You should see something like
  13. At the top enter (use your password)
    Click Send.
    You should see something like
  14. At the top enter (use your password)
    Click Send.
    You should see something like
  15. At the top enter (use your password)
    Click Send.
    You should see something like
  16. At the top enter (use your password)
    Click Send.
    You should see something like
  17. At the top enter (use your password)
    Click Send.
    You should see something like
  18. Unplug your Code Card.
  19. Turn it off and on again.
  20. It should now connect to your network and the buttons should work.

What Else Can You Do?

There are a lot of things you can do with your Code Card.

For some ideas check out this GitHub page for some how to guides and other information.

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

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.

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

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

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

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

Create a Docker Container for the Node.js app

Create a Dockerfile

Build the image

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

Typical Oracle Database
The container will run, execute the query and stop.  You should see something similar to this output.
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.

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.

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

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

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:

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.

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.

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

The full command looks like this:

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

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.

Oracle REST Data Services on Autonomous Transaction Processing Database

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

Get the URI

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

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

    Or, Click on your Instance Name then Click the ‘Service Console’ button.
  4. Click ‘Development’.
  5. 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:

Quick and Easy Setup – Oracle Xe and ORDS Using Docker

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.

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.

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:

You should see something like this:

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.

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

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

Verify that your container was created and is up:

You should see something like this:

Now try to connect to your new Oracle Xe Database.

To connect with SQLcl:

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.

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:

You should see something like this:

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:

You should see something like this.

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:

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.

Verify that your image was created:

You should see something like this:

Now we can create the ORDS container.

Create an ORDS 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.

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.

Verify that your container was created and is up:

You should see something like this:

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:

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

Have fun creating your new REST modules!

How to build an Oracle Database Developer Toolbox with Vagrant

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

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.

Create the Toolbox

Make a copy of the 18.4.0-XE directory.

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

Open port 8080 for ORDS to use.

Create the VM and install Oracle Database XE

Run the following command to start the vagrant build process.

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.

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.

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.

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.

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’.

  • 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.

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.

  • 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.

You should see the following output.

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.

Edit dbDevToolbox/Vagrantfile

Provision the new script

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.
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