Category Archives: Tools

Create and use an Oracle Cloud Compute instance from the Command Line

In a previous post, I walked through how to create an Autonomous Database on the Oracle Cloud using the OCI-CLI.  In this post you’ll learn how to create a compute instance.

You can use these commands in your Oracle Cloud Shell from your Cloud Dashboard where the OCI-CLI is already setup and ready to go.

If you’d rather use your own environment you can follow these instructions to install and configure the OCI-CLI.

Environment Variables

There are some pieces of information you’ll need in order to create the compute instance.  Of course you can look this information up manually, but it’s more fun to automate as much as possible.

Preset Values

Create environment variables for the following:

  • The name of the Compartment you want to create your new Compute instance in.
  • The name for your new Compute instance.
  • The shape you want to use.  VM.Standard.E2.1.Micro is used for an Always-Fee Compute instance.
  • The absolute path for your user’s home directory.
The following commands will create the given object and use the returned OCID to create an environment variable to be used in the other steps.

For example:

Compartment OCID

The previous post demonstrates how to use the --query  parameter to get the OCID for the Compartment.

Availability Domain

You’ll need to define which Availability Domain you want to use.  The above Compute Shape is typically available in your third sub-domain, ‘xxxx:US-ASHBURN-AD-3’.

The following --query  parameter for the list command will search for the name of a sub-domain ending in ‘-3’, if one is not found it will chose the first sub-domain in the array.

Create a Virtual Cloud Network

Your Compute instance will need a VCN in order to connect to the outside world.  If you have already created a compute instance in this compartment you can re-use the existing VCN and subnet or follow these instructions to create a new one.

Create a new VCN
Add a Subnet to the VCN

Create a new subnet that your compute instance will use for connections.

Add an Internet Gateway

Create an Internet Gateway that will be used to connect from your VCN to the internet.

Add a Route Table

Create a Route Table which is a collection of rules used to route packets to the correct network entity.

Add a Route Rule for the Internet Gateway

Update your Route Table and add a rule granting internet access to your compute instance through your Internet Gateway.

RSA Key

In order to connect to your compute instance you’ll need an RSA key pair.

If you don’t already have a key pair, use the following command to generate two new files, your “key pair”.  id_rsa is your private key, do not share this.  id_rsa.pub  is your public key that you will share.  The below command will create these files in the .ssh directory inside your home directory.  You can change the directory or name if you wish.

Now that you have created a network and a key pair you can

Create the Compute Instance

If you created your key pair with a different name or in a different location than ${USER_HOME}/.ssh/id_rsa.pub , you will need to modify the --ssh-authorized-keys-filevalue below when you launch your new instance.

Most of the parameters use the values we defined above.

The parameter --image-id is the OCID for the Oracle Linux 7.8 image.

The parameter --wait-for-state RUNNING  will pause at the launch command until your instance is fully up and running.

Get the Public IP

You will need the public ip for your instance in order to establish an ssh connection.

Connect

Use ssh to connect to the new instance.

If you changed the location or the name of your private key you may need to include the private key.

Enjoy

At this point you can start configuring your new Compute instance however you’d like.

Leave a comment if something goes wrong or if you have any questions.

Create and use an Oracle Autonomous Cloud Database from the Command Line

In this post I’ll cover how to create an Oracle Autonomous Cloud Database download the Wallet credentials and connect to the database all from the command line using the OCI-CLI.

After that I’ll include an example of a shell script that I use to setup my Demo environment.

Prerequisites

Get the Compartment OCID

I like to keep all of my work compartmentalized so that I don’t run into conflicts between my (and potentially other people’s) projects.  This means I’ll need to get the OCID of the compartment I want to work in.

Rather than use the Web Console you can run this command to get a list of your available compartments.

Assuming that you already know which compartment you want to work with you can use the --query  parameter to retrieve the ID of that compartment.

The above command returns an array called data that I will use to query an object.

I’d like to retrieve only the object with a name of ‘Demo’.

Now that I have the full object, I can get the id value.

The list command will return all objects that match the query criteria in an array.  Even when there is only a single object it will be returned in an array.

Next, I pipe out the first (and only) value from the array.

Using the --raw-output  parameter, I can get the raw value without the double quotes.

I can use this command to set an environment variable.

The OCI-CLI query parameter uses the JMESPath query language.

Create Compartment

If the compartment doesn’t exist you can use OCI to create one.  For this command you will need the OCID of an existing compartment.  This will be the parent compartment.

If you want to use an existing compartment as a parent, you can use the above command to get the OCID.  Or, if you want to add the new compartment to the ROOT compartment, you can use the Tenancy OCID.

You can get the Tenancy OCID from:

  • Your OCI Config file cat ~/.oci/config.
  • The OCID of an existing compartments parent.
    This time I’m using the ?contains()  function to check compartment-id for the string ‘.tenancy.’.  Notice that the compartment-id must be double quoted because it contains a ‘-‘ and those double quotes are escaped.  \"compartment-id\" oci iam compartment list --query "data[?contains(\"compartment-id\",'.tenancy.')].\"compartment-id\" | [0]" --raw-output

Once you have the parent compartment OCID the command to create a new compartment is:

Database

I can check to see if the database already exists by using a query similar to the one I used for compartments.

If the demo database doesn’t exist I can create a new Always-Free Autonomous Cloud Database with the OCI-CLI.

The data-storage-size-in-tbs is set to 1TB which is larger than the free tier supports.  Setting --is-free-tier True  will cause the system to automatically scale it to the correct size.

The default value for ‘is-free-tier’ is False, if you do not include this parameter you will create a standard Autonomous Cloud Database.  You should check the Cost Estimator to ensure that you’re OK with the cost.

Setting --db-workload "OLTP"  will create an Autonomous Transaction Processing database, using “DW” will create a Data Warehouse.

Make sure you use a strong --admin-password , this will be the admin password for the new database.

This command will return a JSON object with the properties of the new database.

I’ll add the query and raw-output parameters to extract the ID and assign it to an environment variable.

In order to connect to my new database I will need to

Download the Wallet

Wait for your database to be in an AVAILABLE state before attempting to download the wallet.

This command will download your wallet credentials in a .zip file, just like you’d get from the web console.

The $DB_ID variable was set above for the demo database.  The –file parameter accepts the location and file name where you want to download the .zip file.  The new file will have the password set by –password.

Now that everything is in place I can

Test the Connection

  • I’ll start SQLcl without making a connection (/nolog).
  • Set cloudconfig to the location of the wallet credentials.
  • Connect with the admin password and one of the service names contained in the tnsnames.ora file included in the wallet zip file.
    The predefined service names will be in the form of <name of the database>_<performance level> .  You can find more information here.
  • Run a test query.

Use a Shell Script to Automate

The following is an example of a setup script I use for my demos.

Notice that in the create database method I added a new parameter to the OCI call --wait-for-state AVAILABLE.  Since I won’t be able to download the wallet until the database is available, I use this parameter to pause at the create step until the new Database is fully up and running.

When I run the script I get

Explore

This is just a small taste of what you can do with the OCI-CLI.  Check out the documentation for a look at the possibilities.

Leave a comment if you have a question and I will do my best to find you an answer.

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.

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

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

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:

  1. Setup the environment to simulate how it will be when the application is deployed.  This might include changing configuration settings and loading test data.
  2. Execute the code unit.
  3. Validate the results.
  4. 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:

  1. Developer makes a change and pushes it to the shared Git repository.
  2. Hudson notices the repository has changed and triggers the build pipeline.
  3. The project is pulled from Git.
  4. Liquibase deploys the changes to a test database.
  5. utPLSQL is trigged to run the unit tests.
  6. 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.

Oracle Developer Cloud Service – Use Maven Repository Artifacts in Build Jobs

Maven Repository

Oracle Developer Cloud Service (DevCs) includes a lot of great tools to help you build applications.  The Maven repository is useful for storing build dependencies and artifacts.

Maven artifacts can be uploaded to the repository after they are created by your build.  Dependencies can be manually uploaded ahead of time and pulled into the build jobs that need them.  Let’s take a look at how to work with dependency files in the Maven repository.

Upload Files

We’ll need to upload one or more files first.

  1. Click the Upload button.
  2. Drag and drop files into the box or click “select artifact files” and select the files.

My project uses Oracle JET version 4.1.0 so I’ll add it to my Maven repository.

Populate the file data.

  • GroupId: DinoDate
  • ArtifactId: oracleJet
  • Version 4.1.0
  • Packaging: zip

Click the Start Upload button.

Note: the file may be renamed in the repository.  For example, the oraclejet_4.1.0.zip file will be renamed to oraclejet-4.1.0.zip using the above data.

Now that we have a file in the repository let’s see use it in our build.

Use a Build Tool

If you’re using a build tool such as Maven or Gradle, you can get the dependency declaration for the Maven repository by clicking on the root of the project then copying it from the dependency declaration tab for your tool.

To get the file information:

  • Drill down into the folder.
  • Click on the file you need.
  • Copy the dependency declaration for the file.

At this point, you can run your build tool in a build job as normal.

But if you’re not using a build tool there is another way.

cURL

To use a tool such as cURL you need to get the URL for the file your after.

First, we’ll need the DevCs Maven repository URL for the project.

  1. Click /
  2. Copy URL from the dependency declaration panel

The repository URL will be similar to this:

Add file information to the URL:

This is the same data (plus the file name) we entered when we uploaded the file.

If you need to find the data for a file:

  • Drill down into the folder.
  • Click on the file you need.
  • Copy the information from the dependency declaration for the file.

Now fill out the rest of the URL:

Use cURL in the Build Job

The different components in DevCs already have the permissions to access each other in place so the build job can simply cURL from the Maven repository.

Add an “Execute Shell” build step and add the following (with your repository URL):

You can use any of the cURL features as you normally would, for example, to get multiple files in one call:

Why Store Dependencies in the Project?

Using the DevCs Maven Repository to store dependencies has many benefits, including:

  • It will be faster than pulling dependencies across the internet.
  • You won’t have to worry about a remote repository being offline or files being removed.
  •  Helps protect you from security problems if a remote repository is compromised.

Please leave a comment if you have questions or need additional help.

Becoming a DevOps “expert”

I’ve decided to learn more about DevOps.

I’ve always been a believer in automating repetitive tasks and letting machines do as much of “my” work as they can.  The way I learn best is (as you can tell by the name of my blog) I learn about the topic, I build something from what I’ve learned and I share my experience.

Given that DevOps is a very big topic, it will take more than one or two posts to do it justice.  This post is the first in a series of blog posts, videos and presentations that I plan to create as I learn more.  I think the best place to start is with what I “think” I know now.

What is DevOps?

A couple years ago I was using Jenkins to create a continuous delivery pipeline for a project I was working on.  I was the only one working on the project and after seeing a CD demonstration at a conference I figured I’d give it a try.  I had everything working and I was quite pleased with myself.  Then I started hearing the term DevOps and assumed it was just a term for what I was already doing.  I was partially right.

DevOps is more than just automating the software delivery process, it’s also a cultural mindset.  It’s developers and operations working together throughout the full lifecycle of a project instead of in separate silos.  Since I was working solo on that project I missed out on this aspect.  Currently, I’m not working on any project where I can experience the full cultural aspect so I plan to mentally assume different roles as I work through the learning process.

If you’d like a better definition, there is plenty of material available on the web from real experts.  I only wanted to document what DevOps means to me as I start to learn more.

My current plan.

I have been working on an open source application used for demonstrations and learning called DinoDate.  I am going to build a DevOps process around this application.  My focus will be a bit more on the database aspects of DevOps since the database isn’t always used to its full potential and sometimes even treated like a bucket of data.  I will be building this process using the Oracle Developer Cloud Service against an Oracle Cloud Database and other Oracle Cloud services, as well as other tools such as Jenkins against an Oracle Database on a VM.

Plan Steps:
  1. Define the steps to manually deploy DinoDate as is.
    1. Automate the build and deploy process which currently is, run some scripts and scp the code to an Oracle Compute instance where I have already setup Python and NodeJS.
    2. Deploy the NodeJS and Python apps to an Oracle Application Container Cloud instance.
  2. Add some open source tools to improve the process.
    • Build script using Gradle.
    • Schema object version control using Liquibase.
    • Unit tests for the PL/SQL using UTPLSQL.
  3. Automate creating the infrastructure (DB, Compute instance) from scratch then deploy, test and destroy.
  4. Reproduce the entire CD pipeline using Jenkins (or another tool) against a VM.

Once I’m satisfied with my understanding of the tools and workflow, I’ll find a project that would benefit from a DevOps environment and pester encourage them to switch to a DevOps process with an offer to act as the DevOps “expert”.

More to come.

Keep an eye out here and on my YouTube channel for how-to and ‘lessons learned’ posts that I’ll make as I go.  Feel free to post a comment if you see that I’ve already got something wrong or if you have a specific interest you’d like me to focus on as I go.

 

 

 

 

Getting started with Oracle Rest Data Services

Most applications today store data of some type, most likely that data is stored in a database.  There are many ways to get data from the application to the database and back, but one of the most popular methods is using RESTful services.  If you’re not familiar with REST think of it as an easy way to let 2 computers talk to each other.  For a more detailed explanation check out this Wikipedia page.

If you are familiar with REST you’re probably used to standing up a server and building a server side application that connects to your database and provides a REST API.

Oracle provides a simpler solution called Oracle REST Data Services or ORDS for short.  ORDS is a quick way to build a REST API directly to your database.  If you’d like a more thorough explanation, check out the ORDS site.

A Short Tutorial

Setup a VM

I’ll be using the Developer Days vm on Virtual Box for the tutorial.  This vm has the Oracle 12c Database and ORDS already installed and ready to go.

  1. Download the Database App Development VM.  I’m using the one from June 13, 2017.
  2. Create a new appliance and start it.
  3. Inside the appliance, open a terminal and enter the following commands.  Provide a password when prompted.
Now we have the VM running and we’ve created an ORDS user “ords_dev”.

SQL Developer

For these examples, I’ll be using SQL Developer version 4.2.0.

If you don’t already have SQL Developer installed you can download it here.

Connect to the HR schema

Open SQL Developer and create a connection to the HR schema.

  • Connection Name:  Anything you’d like.  I’m using Hr – VM
  • Username: hr
  • Password: oracle
  • Hostname: localhost
  • Port: 1521
  • Service name: oracle
    (Make sure you select the Service name radio button.)

Test the connection and connect.

Rest Enable The Schema
  1. Right click on the HR connection.
  2. Click REST Services.
  3. Click Enable REST Services…

  • Enable schema: checked
  • Schema alias: personnel
    (Remember this for later.)
  • Authorization required: un-checked
    For production applications, you will want to use authorization but I’m not going to cover it here.

You can click Finish or if you’d like to see the summary page you can click Next then Finish.

REST Data Services Wizard

From here SQL Developer offers a couple different ways to run the REST Data Services wizard.

One way you can work with the wizard is through the database connection.

This method does not require you to have an ORDS user, but the full ORDS URI won’t be automatically provided in the wizard so you’ll need to get that from the ORDS admin.  I’ll cover the URI below.

For this tutorial, I’ll be using the…

REST Development Panel
  1. Click the View menu item.
  2. Click REST Data Services.
  3. Click Development.

The REST Development panel (on the right) should now be in the left panel bar.

         

Connect to ORDS
  1. Click the Connect icon.
  2. Create a new connection.
  3. Populate the ORDS connection data.

This is an ORDS connection using the ORDS user we created in the VM earlier NOT the HR schema user.

Connection Name: HR-VM
Username: ords_dev
(The username is case sensitive.)
Select: http
Hostname: localhost
Port: 8080
Server Path: /ords
Schema/Workspace: /personnel
(If you used a different value when you rest enabled the schema use that value here ‘/your_alias’)

  1. Click OK in the New RESTful Services Connection panel.
  2. Select your new connection and click OK.
  3. Enter the password we created earlier: oracle
  4. Click OK.

New Module

A module is a collection of related REST services.  How the services are related is up to your imagination.  I usually think of a module like a package and the services as functions inside the package.

To create a new module:

  1. Right click on Modules.
  2. Click New Module…

The wizard will open and we can populate the data.  The purpose of my module is to manage the personnel so I’m going to name my module Manage.

Module Name: Manage
URI Prefix: manage
Check the Publish check box.

Notice that when you enter the URI Prefix the Example URI is expanded to include that value.  This is the URI I mentioned above.  If you run the wizard through the database connection the URI will include a generic value for the first part that refers to the ORDS server.  (http://localhost:8080/ords/personnel/)

Click Next.

Template URI

The template URI identifies a specific REST service endpoint.  In this case employees.  Notice that when you enter the URI Pattern the Example URI is expanded to also include that value.

Let’s break apart the URI.  First, we have the schema alias ‘personnel’ that gives us access to the HR schema.  Next, we created a module to ‘manage’ the HR schema records. Finally, we created a specific URI to handle transactions for ’employees’.

Method Handlers

Now that we’ve created the service endpoint to work with employees, we need to ‘Handle’ the different HTTP ‘Methods’ we intend to use.

A quick web search for ‘http rest methods’ will return pages of discussions on the available methods and how to “properly use them” but the short version is:

GET: Retrieve records with or without search criteria.
POST: Create records without providing the primary key.
PUT: Replace a record with a given primary key.  This can also be used to create a record if you’ve pre-assigned it a primary key.
DELETE: Remove a record with a given primary key.

We’ll start by creating a simple GET all handler.

  • Method: GET
  • Source Type: Query
  • Data Format: JSON
  • Pagination Size: 25
    We’ll leave this at the default value of 25.  It’s a good idea to define a pagination size, we don’t want to accidentally return a billion records in one call.  More on this later.

Click Next, review the summary and click Finish.

Get Query

Our GET method will return the Employee id, Hire Date, First and Last name for all employees.

If the GET employees SQL Worksheet did not automatically open, expand Manage, employees and click on GET.

Enter this query into the SQL Worksheet.

Push the new module to ORDS
  1. Right click on the Manage module.
  2. Click on Upload.

Post

To create new records we’ll want a handler for the POST method.

  1. Right click the employees URI template.
  2. Click Add Handler.
  3. Click Post.

Notice that GET is grayed out since you can only have one method handler of each type per URI template.

We use the MIME Types to define the data format that we’ll accept.  Click the green plus to add a new MIME Type and enter application/json.  Click Apply.

If the POST employees SQL Worksheet did not automatically open, expand Manage, employees and click on POST.

ORDS uses PL/SQL for methods that change data, POST, PUT and DELETE.  PL/SQL gives us a greater amount of control which in turn provides better security.

Enter this PL/SQL into the SQL Worksheet.

Notice the use of bind variables in the PL/SQL.  If the data keys coming into our REST service match our bind variables, ORDS will auto-map the values.  However, if the keys do not match or we have additional use cases, we will need to map the bind variables using the Parameters tab. For this service, we will be passing in data values with keys that match the bind variables.

Since we are creating a new record and the primary key is auto-generated, it will be useful to the end user if we return the new id.  Above, we’ve defined a new bind variable :newid to pass this value back.  There is also another bind variable :status that we’ll use to change the response status from 200 (success) to 201 (success and I created a new record).

Parameters

Click on the Parameters tab and enter the following values.

Colum definitions:

  • Name – Used by ORDS.
    • newid will be the key in the JSON object that returns the id to the user.
    • X-APEX-STATUS-CODE is a built in ORDS parameter used to set the status of the response object.
  • Bind Parameter – The bind variable used in our PL/SQL.
  • Access Method – Defines the direction in the transaction we intend to use the parameters; IN, OUT or IN/OUT.
  • Source Type is where the parameter will be used.
    • newid will be in the response body.
    • X-APEX-STATUS-CODE will be in the response header.
  • Data Type – Data type for the returned value.  When all else fails, choose STRING.
Push the modified module to ORDS
  1. Right click on the Manage module.
  2. Click on Upload.

At this point, we have created and deployed a fully functional REST API with the ability to GET all employees and POST a new employee.

It’s time to….

Test the Service

Switch to the Details tab for either the GET or the POST method handler.  At the bottom, you can copy the URI for the new REST service.

URI: http://localhost:8080/ords/personnel/manage/employees

GET

To test the GET method you could simply enter the URI into a web browser and it will return the records.  Using my test tool, I enter the URI and hit send.

I receive back a JSON object with an “items” array that has 25 employee entries in it.  Below, I’ve trimmed a few out of the middle to keep it short.

Remember, I set the Pagination Size to 25 in the GET method, so ORDS returns the first 25 records.  Notice at the bottom of the JSON object after the array there is a “first” object.  The “$ref” value will take you to the first page of records.  This is automatically added to the response by ORDS when pagination is enabled.

There is also a “next” object added by ORDS to indicate that there are more records on the server.  When you write your client side application, you would process the returned records and check to see if there is a “next” object.  If there is, you could use URI in the “$ref” object to fetch the next set of records.  You would loop through this process until the last set of records.  When you reach the last set there will not be a “next” object.

After the first page, you would start to see a “prev” object containing a “$ref” object that you can use to reverse through the records.

If you set Pagination Size to 0 the service will return every record at once and the navigation objects will not be included.

POST

In your REST testing tool:

  • Change the method to POST.
  • Add a header.
    Content-Type: application/json
  • Enter the following as the payload.
  • Send the request.

You should receive a response with a status of “201 Created” and the response body should contain the newly generated id.

Our service is deployed and the tests return the data we expect.

The wizards are a great way to quickly define REST services for your database, but you won’t want to use them when you deploy your application.  Instead, we can…

Export SQL

For mass deployment (or for people who just prefer to type everything) a SQL script is a better option.

Another difference between the REST Development panel and REST Data Services in the database connection is that you can export the SQL using the database connection tool.

Open the HR database connection and expand the REST Data Services item.  If you do not see your new service, click on the REST Data Services item and click the refresh arrows at the top of the panel.

  1. Expand Modules.
  2. Right click on Manage.
  3. Select Export…

In the window that pops up:

  1. Check the Enable Schema check box if you want to include the statement.
  2. Un-Check Privileges.
  3. Enter a filename and location.
  4. Click Apply.
  5. Open the file.

You can now include this SQL script in your application build process to deploy the REST services right alongside the rest of your database objects.

When you need a REST API to work with your database, ORDS and the SQL Developer wizards will save you a ton of time and help you create very robust and elegant solutions.

Please leave me a comment if you have trouble or find any bugs.

 

 

SQL Developer – Oracle Database connection through an SSH tunnel

I’ve been using the Oracle Cloud Database for a while now.  I connect through an SSH tunnel and I love the convenience.

Up to now, I’ve been making my tunnel in the terminal like so:

To end it I find the pid of the ssh connection and kill it.

The problem is, I often forget to kill it and/or I attempt to start it multiple times throughout the day.  This isn’t a big problem, just a minor annoyance.

Use Tools

I use Oracle’s SQL Developer to do my database work and was complaining to myself that ‘my IDE should handle this!’

Then I happened to notice the SSH item in the view menu.

viewSSH

Yes, I often complain before looking for a solution.

Add an SSH Host

In the menu, clicking View/SSH brings up the SSH panel.  So let’s add a new Host.

Right click SSH Host then click New SSH Host

SSHWindow

Fill in the Name, Host and Username, if you’re using a key file, check the box and select the file.

Check the Add a Local Port Forward box and give it a name.  Change any of the default values, if you need to, or just hit ok.

newSSH

To test it, right click on your new SSH host and click Test.

testSSH

testSuccess

Add a Connection

Create a new connection just like normal.

Change the connection type to SSH and select your new Port Forward from the list.

connectionDetails

Click Test.  Assuming everything is correct and you get a Status:  Success, click Save.

Now use your new connection as you always have and SQL Developer will handle the SSH connections.