Category Archives: Autonomous

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

June 18, 2020 bcarter Leave a comment

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.

export COMPARTMENT_NAME='Test'
export COMPUTE_NAME='TestCompute'
export COMPUTE_SHAPE='VM.Standard.E2.1.Micro'
export USER_HOME=$(eval echo ~)

export COMPARTMENT_NAME='Test'

export COMPUTE_NAME='TestCompute'

export COMPUTE_SHAPE='VM.Standard.E2.1.Micro'

export USER_HOME=$(eval echo ~)

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:

export NEW_OCID=$(the OCI command you would run to create the object and return the OCID)

1	export NEW_OCID=$(the OCI command you would run to create the object and return the OCID)

Compartment OCID

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

export COMPARTMENT_ID=$(oci iam compartment list --query "data[?name=='${COMPARTMENT_NAME}'].id | [0]" --raw-output)

1	export COMPARTMENT_ID=$(oci iam compartment list --query "data[?name=='${COMPARTMENT_NAME}'].id \| [0]" --raw-output)

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.

export AVAILABILITY_DOMAIN=$(oci iam availability-domain list --query "(data[?ends_with(name, '-3')] | [0].name) || data[0].name" --raw-output)

1	export AVAILABILITY_DOMAIN=$(oci iam availability-domain list --query "(data[?ends_with(name, '-3')] \| [0].name) \|\| data[0].name" --raw-output)

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

export VCN_ID=$(oci network vcn create -c ${COMPARTMENT_ID} --cidr-block "10.0.0.0/16" --query "data.id" --raw-output)

1	export VCN_ID=$(oci network vcn create -c ${COMPARTMENT_ID} --cidr-block "10.0.0.0/16" --query "data.id" --raw-output)

Add a Subnet to the VCN

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

export SUBNET_ID=$(oci network subnet create --vcn-id ${VCN_ID} -c ${COMPARTMENT_ID} --cidr-block "10.0.0.0/24" --query "data.id" --raw-output)

1	export SUBNET_ID=$(oci network subnet create --vcn-id ${VCN_ID} -c ${COMPARTMENT_ID} --cidr-block "10.0.0.0/24" --query "data.id" --raw-output)

Add an Internet Gateway

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

export IG_ID=$(oci network internet-gateway create -c ${COMPARTMENT_ID} --is-enabled true --vcn-id ${VCN_ID} --query "data.id" --raw-output)

1	export IG_ID=$(oci network internet-gateway create -c ${COMPARTMENT_ID} --is-enabled true --vcn-id ${VCN_ID} --query "data.id" --raw-output)

Add a Route Table

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

export RT_ID=$(oci network route-table list -c ${COMPARTMENT_ID} --vcn-id ${VCN_ID} --query "data[0].id" --raw-output)

1	export RT_ID=$(oci network route-table list -c ${COMPARTMENT_ID} --vcn-id ${VCN_ID} --query "data[0].id" --raw-output)

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.

oci network route-table update --rt-id ${RT_ID} --route-rules '[{"cidrBlock":"0.0.0.0/0","networkEntityId":"'${IG_ID}'"}]' --force

1	oci network route-table update --rt-id ${RT_ID} --route-rules '[{"cidrBlock":"0.0.0.0/0","networkEntityId":"'${IG_ID}'"}]' --force

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.

ssh-keygen -t rsa -N "" -b 2048 -C "CiCd-Compute-Instance" -f ${USER_HOME}/.ssh/id_rsa

1	ssh-keygen -t rsa -N "" -b 2048 -C "CiCd-Compute-Instance" -f ${USER_HOME}/.ssh/id_rsa

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.

export COMPUTE_OCID=$(oci compute instance launch \
 -c ${COMPARTMENT_ID} \
 --shape "${COMPUTE_SHAPE}" \
 --display-name "${COMPUTE_NAME}" \
 --image-id ocid1.image.oc1.iad.aaaaaaaahjkmmew2pjrcpylaf6zdddtom6xjnazwptervti35keqd4fdylca \
 --ssh-authorized-keys-file "${USER_HOME}/.ssh/id_rsa.pub" \
 --subnet-id ${SUBNET_ID} \
 --availability-domain "${AVAILABILITY_DOMAIN}" \
 --wait-for-state RUNNING \
 --query "data.id" \
 --raw-output)

export COMPUTE_OCID=$(oci compute instance launch \

-c ${COMPARTMENT_ID} \

--shape "${COMPUTE_SHAPE}" \

--display-name "${COMPUTE_NAME}" \

--image-id ocid1.image.oc1.iad.aaaaaaaahjkmmew2pjrcpylaf6zdddtom6xjnazwptervti35keqd4fdylca \

--ssh-authorized-keys-file "${USER_HOME}/.ssh/id_rsa.pub" \

--subnet-id ${SUBNET_ID} \

--availability-domain "${AVAILABILITY_DOMAIN}" \

--wait-for-state RUNNING \

--query "data.id" \

--raw-output)

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.

export COMPUTE_IP=$(oci compute instance list-vnics \
  --instance-id "${COMPUTE_OCID}" \
  --query 'data[0]."public-ip"' \
  --raw-output)

echo $COMPUTE_IP

export COMPUTE_IP=$(oci compute instance list-vnics \

--instance-id "${COMPUTE_OCID}" \

--query 'data[0]."public-ip"' \

--raw-output)

echo $COMPUTE_IP

Connect

Use ssh to connect to the new instance.

ssh opc@${COMPUTE_IP}

1	ssh opc@${COMPUTE_IP}

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

ssh -i /path/to/your/key/privateKeyName opc@${COMPUTE_IP}

1	ssh -i /path/to/your/key/privateKeyName opc@${COMPUTE_IP}

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.

Autonomous, Cloud, Tools

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

April 6, 2020 bcarter 3 Comments

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

OCI-CLI installed and configured
SQLcl installed

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.

oci iam compartment list

1	oci iam compartment list

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.

--query "data[]"

1	--query "data[]"

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

--query "data[?name=='Demo']"

1	--query "data[?name=='Demo']"

[
  {
    "compartment-id": "ocid1.tenancy.oc1..aaaaaaaauqbiglongguid",
    "defined-tags": {},
    "description": "For Demonstrations",
    "freeform-tags": {},
    "id": "ocid1.compartment.oc1..aaaaaaaa7a6biglongguid",
    "inactive-status": null,
    "is-accessible": null,
    "lifecycle-state": "ACTIVE",
    "name": "Demo",
    "time-created": "2019-09-26T21:14:01.870000+00:00"
  }
]

[

{

"compartment-id": "ocid1.tenancy.oc1..aaaaaaaauqbiglongguid",

"defined-tags": {},

"description": "For Demonstrations",

"freeform-tags": {},

"id": "ocid1.compartment.oc1..aaaaaaaa7a6biglongguid",

"inactive-status": null,

"is-accessible": null,

"lifecycle-state": "ACTIVE",

"name": "Demo",

"time-created": "2019-09-26T21:14:01.870000+00:00"

}

]

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

--query "data[?name=='Demo'].id"

1	--query "data[?name=='Demo'].id"

[
"ocid1.compartment.oc1..aaaaaaaa7a6biglongguid"
]

[

"ocid1.compartment.oc1..aaaaaaaa7a6biglongguid"

]

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.

--query "data[?name=='Demo'].id | [0]"

1	--query "data[?name=='Demo'].id \| [0]"

"ocid1.compartment.oc1..aaaaaaaa7a6biglongguid"

1	"ocid1.compartment.oc1..aaaaaaaa7a6biglongguid"

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

oci iam compartment list --query "data[?name=='Demo'].id | [0]" --raw-output

1	oci iam compartment list --query "data[?name=='Demo'].id \| [0]" --raw-output

ocid1.compartment.oc1..aaaaaaaa7a6biglongguid

1	ocid1.compartment.oc1..aaaaaaaa7a6biglongguid

I can use this command to set an environment variable.

export COMPARTMENT_ID=$(oci iam compartment list --query "data[?name=='Demo'].id | [0]" --raw-output)

1	export COMPARTMENT_ID=$(oci iam compartment list --query "data[?name=='Demo'].id \| [0]" --raw-output)

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:

oci iam compartment create \
   --compartment-id ocid1.tenancy.oc1..aaaaaaaauqbiglongguid \
   --name "Demo" \
   --description "For Demonstrations"

oci iam compartment create \

--compartment-id ocid1.tenancy.oc1..aaaaaaaauqbiglongguid \

--name "Demo" \

--description "For Demonstrations"

Database

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

export DB_ID=$(oci db autonomous-database list -c $COMPARTMENT_ID --query "data[?\"db-name\"=='demo'].id | [0]" --raw-output)

1	export DB_ID=$(oci db autonomous-database list -c $COMPARTMENT_ID --query "data[?\"db-name\"=='demo'].id \| [0]" --raw-output)

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.

oci db autonomous-database create \
    --compartment-id $COMPARTMENT_ID \
    --cpu-core-count 1 \
    --data-storage-size-in-tbs 1 \
    --db-name "$DB_NAME" \
    --display-name "$DB_DISPLAY_NAME" \
    --db-workload "OLTP" \
    --admin-password "T3stertester" \
    --is-free-tier True

oci db autonomous-database create \

--compartment-id $COMPARTMENT_ID \

--cpu-core-count 1 \

--data-storage-size-in-tbs 1 \

--db-name "$DB_NAME" \

--display-name "$DB_DISPLAY_NAME" \

--db-workload "OLTP" \

--admin-password "T3stertester" \

--is-free-tier True

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.

export DB_ID=$(oci db autonomous-database create \
    --compartment-id $COMPARTMENT_ID \
    --cpu-core-count 1 \
    --data-storage-size-in-tbs 1 \
    --db-name "$DB_NAME" \
    --display-name "$DB_DISPLAY_NAME" \
    --db-workload "OLTP" \
    --admin-password "T3stertester" \
    --is-free-tier  True \
    --query "data.id" --raw-output)

export DB_ID=$(oci db autonomous-database create \

--compartment-id $COMPARTMENT_ID \

--cpu-core-count 1 \

--data-storage-size-in-tbs 1 \

--db-name "$DB_NAME" \

--display-name "$DB_DISPLAY_NAME" \

--db-workload "OLTP" \

--admin-password "T3stertester" \

--is-free-tier True \

--query "data.id" --raw-output)

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.

oci db autonomous-database generate-wallet --autonomous-database-id $DB_ID --password Pw4ZipFile --file /home/bcarter/wallets/Wallet_demo.zip

1	oci db autonomous-database generate-wallet --autonomous-database-id $DB_ID --password Pw4ZipFile --file /home/bcarter/wallets/Wallet_demo.zip

Downloading file  [####################################]  100%

1	Downloading file [####################################] 100%

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.

sql /nolog
set cloudconfig $PRESENTATION_DIRECTORY/Active/LiquibaseAlwaysFree/code/wallet/Wallet_demo.zip
connect admin/T3stertester@demo_TP
select 'Yes! I connected to my Always Free ATP database!' did_it_work from dual;
exit;

sql /nolog

set cloudconfig $PRESENTATION_DIRECTORY/Active/LiquibaseAlwaysFree/code/wallet/Wallet_demo.zip

connect admin/T3stertester@demo_TP

select 'Yes! I connected to my Always Free ATP database!' did_it_work from dual;

exit;

Use a Shell Script to Automate

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

#!/bin/bash

# Set Variables
COMPARTMENT_NAME="Demo"
DB_NAME="demo"
DB_DISPLAY_NAME="DemoDb"
DB_PW="T3stertester"
WALLET_PW="Pw4ZipFile"
WALLET_ZIP="/home/bcarter/tempWallet/Wallet_${DB_NAME}.zip"

# Create an Free-Tier Autonomous Database
create_db() {
    DB_ID=$(oci db autonomous-database create \
        --compartment-id ${COMPARTMENT_ID} \
        --cpu-core-count 1 \
        --data-storage-size-in-tbs 1 \
        --db-name "${DB_NAME}" \
        --display-name "${DB_DISPLAY_NAME}" \
        --db-workload "OLTP" \
        --admin-password "${DB_PW}" \
        --is-free-tier  True \
        --wait-for-state AVAILABLE \
        --query "data.id" --raw-output)
}

# Download the Wallet .zip file
download_wallet() {
    oci db autonomous-database generate-wallet --autonomous-database-id ${DB_ID} --password ${WALLET_PW} --file ${WALLET_ZIP}
}

# Get the Compartment OCID
COMPARTMENT_ID=$(oci iam compartment list --query "data[?name=='${COMPARTMENT_NAME}'].id | [0]" --raw-output)

# Get the Database OCID
DB_ID=$(oci db autonomous-database list -c ${COMPARTMENT_ID} --query "data[?\"db-name\"=='${DB_NAME}'].id | [0]" --raw-output)

# If the Database does not exist ask to create it.
if [[ -z "${DB_ID}" ]]; then
    echo "No ${DB_NAME} Database found."
    while true; do
        read -p "Do you wish to create the ${DB_NAME} Database? " yn
        case $yn in
            [Yy]* ) create_db; break;;
            [Nn]* ) exit;;
            * ) echo "Please answer y or n.";;
        esac
    done
fi

# Download the wallet
download_wallet

# Create an SQL script to test the connection
echo "set cloudconfig ${WALLET_ZIP}
connect admin/${DB_PW}@${DB_NAME}_TP
select 'Yes! I connected to my Always Free ATP database!' did_it_work from dual;
exit;" > testConnection.sql

# Test the connection
sql /nolog @testConnection.sql

# Delete the test script
rm testConnection.sql

#!/bin/bash

# Set Variables

COMPARTMENT_NAME="Demo"

DB_NAME="demo"

DB_DISPLAY_NAME="DemoDb"

DB_PW="T3stertester"

WALLET_PW="Pw4ZipFile"

WALLET_ZIP="/home/bcarter/tempWallet/Wallet_${DB_NAME}.zip"

# Create an Free-Tier Autonomous Database

create_db() {

DB_ID=$(oci db autonomous-database create \

--compartment-id ${COMPARTMENT_ID} \

--cpu-core-count 1 \

--data-storage-size-in-tbs 1 \

--db-name "${DB_NAME}" \

--display-name "${DB_DISPLAY_NAME}" \

--db-workload "OLTP" \

--admin-password "${DB_PW}" \

--is-free-tier True \

--wait-for-state AVAILABLE \

--query "data.id" --raw-output)

}

# Download the Wallet .zip file

download_wallet() {

oci db autonomous-database generate-wallet --autonomous-database-id ${DB_ID} --password ${WALLET_PW} --file ${WALLET_ZIP}

}

# Get the Compartment OCID

COMPARTMENT_ID=$(oci iam compartment list --query "data[?name=='${COMPARTMENT_NAME}'].id | [0]" --raw-output)

# Get the Database OCID

DB_ID=$(oci db autonomous-database list -c ${COMPARTMENT_ID} --query "data[?\"db-name\"=='${DB_NAME}'].id | [0]" --raw-output)

# If the Database does not exist ask to create it.

if [[ -z "${DB_ID}" ]]; then

echo "No ${DB_NAME} Database found."

while true; do

read -p "Do you wish to create the ${DB_NAME} Database? " yn

case $yn in

[Yy]* ) create_db; break;;

[Nn]* ) exit;;

* ) echo "Please answer y or n.";;

esac

done

# Download the wallet

download_wallet

# Create an SQL script to test the connection

echo "set cloudconfig ${WALLET_ZIP}

connect admin/${DB_PW}@${DB_NAME}_TP

select 'Yes! I connected to my Always Free ATP database!' did_it_work from dual;

exit;" > testConnection.sql

# Test the connection

sql /nolog @testConnection.sql

# Delete the test script

rm testConnection.sql

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

@OraBlaineOS:OCI-CLI$ ./setup.sh
Query returned empty result, no output to show.
No demo Database found.
Do you wish to create the demo Database? y
Action completed. Waiting until the resource has entered state: ('AVAILABLE',)
Downloading file  [####################################]  100%

SQLcl: Release 18.3 Production on Fri Apr 03 14:28:11 2020

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

Operation is successfully completed.
Operation is successfully completed.
Using temp directory:/tmp/oracle_cloud_config3687856744011425249
Connected.

DID_IT_WORK
------------------------------------------------
Yes! I connected to my Always Free ATP database!


Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

@OraBlaineOS:OCI-CLI$ ./setup.sh

Query returned empty result, no output to show.

No demo Database found.

Do you wish to create the demo Database? y

Action completed. Waiting until the resource has entered state: ('AVAILABLE',)

Downloading file [####################################] 100%

SQLcl: Release 18.3 Production on Fri Apr 03 14:28:11 2020

Operation is successfully completed.

Using temp directory:/tmp/oracle_cloud_config3687856744011425249

Connected.

DID_IT_WORK

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

Yes! I connected to my Always Free ATP database!

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.4.0.0.0

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.

Autonomous, Cloud, Python

Install and Configure the Oracle Cloud Command Line Interface

March 6, 2020 bcarter 2 Comments

I love working with cloud resources. I don’t have to bog down my laptop and I don’t have to maintain all of the back-end stuff. I don’t mean to make it sound as if I think the “back-end stuff” is easy. It’s actually, I understand just enough of that “stuff” to make it go (most of the time) so it’s nice to have experts in the cloud taking care of it for me.

Some of you may be thinking “but cloud resources are expensive.” That’s true sometimes, but it depends on who’s cloud you’re using. If you’ve been following me you should already know how much I like Oracle’s Always Free Services and you should know that I love to automate anything I can. If you’d like to follow along but you don’t have an Oracle Cloud account, click that link, create an Always Free account then come back.

In this post I’ll cover how to use the Oracle Cloud Command Line Interface (OCI-CLI) to access and control your Oracle Cloud resources.

CLI Overview

Oracle’s cloud CLI is a small application you can use to control your Oracle Cloud resources. It gives you the same core functionality as you’d get using the Web Console, and some extra commands. It allows you to control your cloud account from your local console application so you can easily automate the control of your resources.

Pre-requisits

Python 3.5+

The CLI is built with Python so make sure you have Python version 3.5 or higher installed.

RSA Key Pair

You will need an RSA key attached to your cloud user in order to remotely access your account. This must be an RSA key pair in PEM format (minimum 2048 bits).

The easiest way to generate this key par is with openssl. The following commands work in Linux/Mac environments. For Windows you can use your favorite tool or execute the commands in GitBash, WSL or some other Linux shell environment.

openssl genrsa -out myPrivateKey.pem 2048
openssl rsa -pubout -in myPrivateKey.pem -out myPublicKey.pem

1 2	openssl genrsa -out myPrivateKey.pem 2048 openssl rsa -pubout -in myPrivateKey.pem -out myPublicKey.pem

The first command creates a PRIVATE KEY called ‘myPrivateKey.pem’ (name yours whatever you’d like). This is the key you will use to access remote systems. DO NOT share this key, whoever has this key can connect to those systems as you. Think of it as your admin password.

The second command uses your private key to create a PUBLIC KEY called ‘myPublicKey.pem’ (name yours whatever you’d like). This is the key you will share with remote systems. Those systems will add your PUBLIC KEY to their authorized keys list, allowing you to access their system using your private key.

Store these keys in a secure location. On Linux, the default location is usually in the ~/.ssh directory. But, if you’re creating separate keys for your projects, you can store them wherever you’d like. Just remember the location for later.

Cloud Account

You need to have access to an Oracle Cloud account with a user that is authorized to preform the tasks you intend to automate. A good way to tell if your user has the correct permissions is to log onto your account through the Web Console and create an Always Free database then terminate it.

While you’re logged into the Web Console collect some information.

Tenancy
1. In the menu, under Administration, click Tenancy Details.
2. Locate the OCID and click Copy.
3. Save this value for later.
User
1. In the menu under Identity click Users.
2. Select your user.
3. Near the bottom click ‘API Keys’ under the resource menu.
4. Click the ‘Add Public Key’ button.
5. Choose the PUBLIC key file you generated earlier.
6. Click the ‘Add’ button.
7. Your key should now show up in the ‘API Keys’ list.
8. Copy the fingerprint of your key and save it for later.
9. Near the top of the user page, locate the OCID and click Copy. Save this value for later.

Quickstart Install

You can download an execute a script that will ask you typical installation configuration questions, after which it will install and configure the OCI-CLI.

The following is current as of the publish date for this post, but you may want to review the instructions in case things change.

Linux / Mac

Open a terminal and run the following command.

bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

1	bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

Windows

Open PowerShell as Administrator.

Set-ExecutionPolicy RemoteSigned

powershell -NoProfile -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.ps1'))"

Set-ExecutionPolicy RemoteSigned

powershell -NoProfile -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.ps1'))"

In either system, answer the questions to complete the install.

Install with Python

If you don’t have the rights on your computer or you’d rather not execute the above scripts, you can install the OCI-CLI with Python.

I recommend using a virtual environment when working with python. It helps keep all of your projects clean and isolated.

python3 -m venv oci-cli-env

# Linux / Mac
source oci-cli-env/bin/activate

# Windows
oci-cli-env\Scripts\activate.bat

python3 -m venv oci-cli-env

# Linux / Mac

source oci-cli-env/bin/activate

# Windows

oci-cli-env\Scripts\activate.bat

Once your virtual environment is active, install the OCI-CLI with pip.

pip install oci-cli

1	pip install oci-cli

You can see if the install was successful by checking the version.

oci -v

oci -v

2.9.5

2.9.5

Configure the OCI-CLI

Using the values you saved from above, you can create a config file with this command.

oci setup config

1	oci setup config

You will be prompted for the following information.

Enter a location for your config [/home/bcarter/.oci/config]:
Enter a user OCID: <User OCID from above>
Enter a tenancy OCID: <Tenancy OCID from above>
Enter a region (e.g. us-ashburn-1, us-phoenix-1): us-ashburn-1
Do you want to generate a new RSA key pair? [Y/n]: n
Enter the location of your private key file: /home/bcarter/.ssh/myPrivateKey.pem
Fingerprint: <saved value>

Enter a location for your config [/home/bcarter/.oci/config]:

Enter a user OCID: <User OCID from above>

Enter a tenancy OCID: <Tenancy OCID from above>

Enter a region (e.g. us-ashburn-1, us-phoenix-1): us-ashburn-1

Do you want to generate a new RSA key pair? [Y/n]: n

Enter the location of your private key file: /home/bcarter/.ssh/myPrivateKey.pem

Fingerprint: <saved value>

The default location for the config file is typically ‘~/.oci/config’. If you use a different location you will need to remember where it is.
Enter the user and tenancy OCIDs saved from your account.
Enter the region you want to work in. The system will display some examples you can choose from.
You already generated an RSA key pair so enter ‘n’.
Enter the location of your PRIVATE key. This will not be uploaded, the CLI will use your PRIVATE key to make the connection to the cloud.
Enter the Fingerprint you saved when you uploaded your PUBLIC key.

Once it’s complete, you should see a response similar to this.

Config written to /home/bcarter/.oci/config

1	Config written to /home/bcarter/.oci/config

Your OCI-CLI should now be configured. If you open the config file it should look similar to this.

[DEFAULT]
user=ocid1.user.oc1..aaaaaaaat5nvwcna5j6aqzjcaty5eqbb6qt2jvpkanghtgdaqedqw3rynjq
fingerprint=20:3b:97:13:55:1c:5b:0d:d3:37:d8:50:4e:c5:3a:34
key_file=~/.oci/oci_api_key.pem
tenancy=ocid1.tenancy.oc1..aaaaaaaaba3pv6wkcr4jqae5f15p2b2m2yt2j6rx32uzr5vqstifsfdsq
region=us-ashburn-1

[DEFAULT]

user=ocid1.user.oc1..aaaaaaaat5nvwcna5j6aqzjcaty5eqbb6qt2jvpkanghtgdaqedqw3rynjq

fingerprint=20:3b:97:13:55:1c:5b:0d:d3:37:d8:50:4e:c5:3a:34

key_file=~/.oci/oci_api_key.pem

tenancy=ocid1.tenancy.oc1..aaaaaaaaba3pv6wkcr4jqae5f15p2b2m2yt2j6rx32uzr5vqstifsfdsq

region=us-ashburn-1

You can add other connection profiles manually by following this format or you can use the same command to add a new profile. If you re-run the command, it will ask you for a name to use for the new profile which will be added to the config file.

[DEFAULT]
user=ocid1.user.oc1..aaaaaaaat5nvwcna5j6aqzjcaty5eqbb6qt2jvpkanghtgdaqedqw3rynjq
fingerprint=20:3b:97:13:55:1c:5b:0d:d3:37:d8:50:4e:c5:3a:34
key_file=~/.oci/oci_api_key.pem
tenancy=ocid1.tenancy.oc1..aaaaaaaaba3pv6wkcr4jqae5f15p2b2m2yt2j6rx32uzr5vqstifsfdsq
region=us-ashburn-1

[ACCOUNT2]
user=ocid1.user.oc1..aaaaaaaa65vwl7zut55hiavppn4nbfwyccuecuch5tewwm32rgqvm6i34unq
fingerprint=72:00:22:7f:d3:8b:47:a4:58:05:b8:95:84:31:dd:0e
key_file=~/.ssh/other_key.pem
tenancy=ocid1.tenancy.oc1..aaaaaaaaba3pv6wkcr4jqae5f15p2b2m2yt2j6rx32uzr5vqstifsfdsq
region=us-ashburn-1

[DEFAULT]

user=ocid1.user.oc1..aaaaaaaat5nvwcna5j6aqzjcaty5eqbb6qt2jvpkanghtgdaqedqw3rynjq

fingerprint=20:3b:97:13:55:1c:5b:0d:d3:37:d8:50:4e:c5:3a:34

key_file=~/.oci/oci_api_key.pem

tenancy=ocid1.tenancy.oc1..aaaaaaaaba3pv6wkcr4jqae5f15p2b2m2yt2j6rx32uzr5vqstifsfdsq

region=us-ashburn-1

[ACCOUNT2]

user=ocid1.user.oc1..aaaaaaaa65vwl7zut55hiavppn4nbfwyccuecuch5tewwm32rgqvm6i34unq

fingerprint=72:00:22:7f:d3:8b:47:a4:58:05:b8:95:84:31:dd:0e

key_file=~/.ssh/other_key.pem

tenancy=ocid1.tenancy.oc1..aaaaaaaaba3pv6wkcr4jqae5f15p2b2m2yt2j6rx32uzr5vqstifsfdsq

region=us-ashburn-1

Quick Test

Enter the following command.

oci iam availability-domain list

1	oci iam availability-domain list

If everything is working, you should receive a response similar to this.

{
  "data": [
    {
      "compartment-id": "ocid1.tenancy.oc1..biglongguid",
      "id": "ocid1.availabilitydomain.oc1..biglongguid",
      "name": "qVbG:US-ASHBURN-AD-1"
    },
    {
      "compartment-id": "ocid1.tenancy.oc1..biglongguid",
      "id": "ocid1.availabilitydomain.oc1..biglongguid",
      "name": "qVbG:US-ASHBURN-AD-2"
    }
  ]
}

{

"data": [

{

"compartment-id": "ocid1.tenancy.oc1..biglongguid",

"id": "ocid1.availabilitydomain.oc1..biglongguid",

"name": "qVbG:US-ASHBURN-AD-1"

{

"compartment-id": "ocid1.tenancy.oc1..biglongguid",

"id": "ocid1.availabilitydomain.oc1..biglongguid",

"name": "qVbG:US-ASHBURN-AD-2"

}

]

}

Now that your OCI-CLI is installed and configured, you should familiarize yourself with the OCI-CLI documentation to learn about the many, many commands you can use to automate the control of your Oracle Cloud resources.

You can use these same commands from any system with the OCI-CLI installed, including Oracle Cloud Compute instances.

Autonomous, Cloud, CodeCard

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

January 29, 2020 bcarter Leave a comment

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.
  
  buttona1={{URL}} cc/functions/master buttona2={{URL}} cc/functions/master buttonb1={{URL}} cc/functions/master buttonb2={{URL}} cc/functions/master
  
  1
  2
  3
  4
  
  buttona1={{URL}} cc/functions/master
  buttona2={{URL}} cc/functions/master
  buttonb1={{URL}} cc/functions/master
  buttonb2={{URL}} cc/functions/master
  
  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.

CREATE USER CODE_CARD IDENTIFIED BY "setAgoodPassword";
ALTER USER CODE_CARD TEMPORARY TABLESPACE temp;

GRANT CONNECT,
CREATE SESSION,
RESOURCE,
UNLIMITED TABLESPACE,
CREATE ANY TABLE,
ALTER ANY TABLE,
CREATE ANY VIEW,
CREATE ANY SEQUENCE,
CREATE ANY PROCEDURE,
CREATE ANY TYPE,
CREATE ANY SYNONYM
TO CODE_CARD;

GRANT EXECUTE ON DBMS_LOCK TO CODE_CARD;

BEGIN
 ords_admin.enable_schema(
  p_enabled => TRUE,
  p_schema => 'CODE_CARD',
  p_url_mapping_type => 'BASE_PATH',
  p_url_mapping_pattern => 'cc',
  p_auto_rest_auth => NULL
 );
 commit;
END;

CREATE USER CODE_CARD IDENTIFIED BY "setAgoodPassword";

ALTER USER CODE_CARD TEMPORARY TABLESPACE temp;

GRANT CONNECT,

CREATE SESSION,

RESOURCE,

UNLIMITED TABLESPACE,

CREATE ANY TABLE,

ALTER ANY TABLE,

CREATE ANY VIEW,

CREATE ANY SEQUENCE,

CREATE ANY PROCEDURE,

CREATE ANY TYPE,

CREATE ANY SYNONYM

TO CODE_CARD;

GRANT EXECUTE ON DBMS_LOCK TO CODE_CARD;

BEGIN

ords_admin.enable_schema(

p_enabled => TRUE,

p_schema => 'CODE_CARD',

p_url_mapping_type => 'BASE_PATH',

p_url_mapping_pattern => 'cc',

p_auto_rest_auth => NULL

);

commit;

END;

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.

CREATE TABLE  "CODEMAKER" 
   (	"ID" NUMBER GENERATED ALWAYS AS IDENTITY, 
	"BADGEID" VARCHAR2(20), 
	"BUTTONLABEL" VARCHAR2(2), 
	"BUTTONFUNCTION" VARCHAR2(10), 
	"TEMPLATE" VARCHAR2(12), 
	"TITLE" VARCHAR2(50), 
	"SUBTITLE" VARCHAR2(100), 
	"BODYTEXT" VARCHAR2(200), 
	"ICON" VARCHAR2(200), 
	"BADGE" VARCHAR2(5), 
	"BACKGROUNDIMAGE" VARCHAR2(200), 
	"BACKGROUNDCOLOR" VARCHAR2(10), 
	"BARCODE" VARCHAR2(50), 
	"FINGERPRINT" VARCHAR2(200), 
	 CONSTRAINT "CODEMAKER_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE, 
	 CONSTRAINT "CODEMAKER_UK1" UNIQUE ("BADGEID", "BUTTONLABEL", "BUTTONFUNCTION")
  USING INDEX  ENABLE
   )
/

CREATE TABLE  "CODECARD" 
   (	"ID" NUMBER GENERATED ALWAYS AS IDENTITY, 
	"BADGEID" VARCHAR2(100), 
	"NAME" VARCHAR2(1000), 
	"CREATED" TIMESTAMP (6), 
	 CONSTRAINT "CODECARD_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE, 
	 CONSTRAINT "CODECARD_CON" UNIQUE ("BADGEID")
  USING INDEX  ENABLE
   )
/

CREATE TABLE "CODEMAKER"

( "ID" NUMBER GENERATED ALWAYS AS IDENTITY,

"BADGEID" VARCHAR2(20),

"BUTTONLABEL" VARCHAR2(2),

"BUTTONFUNCTION" VARCHAR2(10),

"TEMPLATE" VARCHAR2(12),

"TITLE" VARCHAR2(50),

"SUBTITLE" VARCHAR2(100),

"BODYTEXT" VARCHAR2(200),

"ICON" VARCHAR2(200),

"BADGE" VARCHAR2(5),

"BACKGROUNDIMAGE" VARCHAR2(200),

"BACKGROUNDCOLOR" VARCHAR2(10),

"BARCODE" VARCHAR2(50),

"FINGERPRINT" VARCHAR2(200),

CONSTRAINT "CODEMAKER_PK" PRIMARY KEY ("ID")

USING INDEX ENABLE,

CONSTRAINT "CODEMAKER_UK1" UNIQUE ("BADGEID", "BUTTONLABEL", "BUTTONFUNCTION")

USING INDEX ENABLE

)

CREATE TABLE "CODECARD"

( "ID" NUMBER GENERATED ALWAYS AS IDENTITY,

"BADGEID" VARCHAR2(100),

"NAME" VARCHAR2(1000),

"CREATED" TIMESTAMP (6),

CONSTRAINT "CODECARD_PK" PRIMARY KEY ("ID")

USING INDEX ENABLE,

CONSTRAINT "CODECARD_CON" UNIQUE ("BADGEID")

USING INDEX ENABLE

)

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.

BEGIN
 ords_admin.enable_schema(
  p_enabled => TRUE,
  p_schema => 'CODE_CARD',
  p_url_mapping_type => 'BASE_PATH',
  p_url_mapping_pattern => 'cc',
  p_auto_rest_auth => NULL
 );
 commit;
END;

BEGIN

ords_admin.enable_schema(

p_enabled => TRUE,

p_schema => 'CODE_CARD',

p_url_mapping_type => 'BASE_PATH',

p_url_mapping_pattern => 'cc',

p_auto_rest_auth => NULL

);

commit;

END;

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

  ORDS.DEFINE_MODULE(
      p_module_name    => 'functions',
      p_base_path      => '/functions/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

ORDS.DEFINE_MODULE(

p_module_name => 'functions',

p_base_path => '/functions/',

p_items_per_page => 25,

p_status => 'PUBLISHED',

p_comments => NULL);

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.

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'functions',
      p_pattern        => 'master',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

ORDS.DEFINE_TEMPLATE(

p_module_name => 'functions',

p_pattern => 'master',

p_priority => 0,

p_etag_type => 'HASH',

p_etag_query => NULL,

p_comments => NULL);

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.

ORDS.DEFINE_HANDLER(
      p_module_name    => 'functions',
      p_pattern        => 'master',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'declare
	tmp_badgeId VARCHAR2(20);
	tmp_buttonLabel VARCHAR2(2);
	tmp_buttonFunction VARCHAR2(10);
	tmp_template VARCHAR2(12);
	tmp_title VARCHAR2(50);
	tmp_subtitle VARCHAR2(100);
	tmp_bodytext VARCHAR2(200);
	tmp_icon VARCHAR2(200);
	tmp_badge VARCHAR2(5);
	tmp_backgorundImage VARCHAR2(200);
	tmp_backgroundColor VARCHAR2(10);
    tmp_fingerprint VARCHAR2(200);
	tmp_barcode VARCHAR2(50);
    tmp_function VARCHAR2(20);
begin
    select
        BADGEID, BUTTONLABEL, BUTTONFUNCTION, TEMPLATE, TITLE, SUBTITLE, BODYTEXT, ICON, BADGE, BACKGROUNDIMAGE, BACKGROUNDCOLOR, FINGERPRINT, BARCODE
    into
        tmp_badgeId, tmp_buttonLabel, tmp_buttonFunction, tmp_template, tmp_title, tmp_subtitle, tmp_bodytext, tmp_icon, tmp_badge, tmp_backgorundImage, tmp_backgroundColor, tmp_fingerprint, tmp_barcode
    from CODEMAKER
    where upper(BADGEID) = upper(:badgeid) and BUTTONLABEL = :buttonlabel and BUTTONFUNCTION = :buttonfunction;
    
    APEX_JSON.open_object;
    APEX_JSON.write(''template'', tmp_template);
    APEX_JSON.write_raw(''title'', ''"'' || tmp_title || ''"'');
    APEX_JSON.write_raw(''subtitle'', ''"'' || tmp_subtitle || ''"'');
    APEX_JSON.write_raw(''bodytext'', ''"'' ||tmp_bodytext || ''"'');
    APEX_JSON.write(''icon'', tmp_icon);
    APEX_JSON.write(''badge'', tmp_badge);
    APEX_JSON.write(''backgroundImage'', tmp_backgorundImage);
    APEX_JSON.write(''backgroundColor'', tmp_backgroundColor);
    APEX_JSON.write(''fingerprint'', tmp_fingerprint);
    APEX_JSON.write(''barcode'', tmp_barcode);
    APEX_JSON.close_object;
    
    EXCEPTION
        WHEN OTHERS THEN
        
        if :buttonfunction = ''1'' then
            tmp_function := ''Button '' ||  UPPER(:buttonlabel) || '' short press'';
        elsif :buttonfunction = ''2'' then
            tmp_function := ''Button '' ||  UPPER(:buttonlabel) ||'' long press'';
        else
           tmp_function := ''No button pressed''; 
        end if;

        APEX_JSON.open_object;
        APEX_JSON.write(''template'', ''template1'');
        APEX_JSON.write(''title'', ''Uh oh!'');
        APEX_JSON.write(''subtitle'', tmp_function);
        APEX_JSON.write(''bodytext'', ''You have not configured your Code Card'');
        APEX_JSON.write(''icon'', ''fail'');
        APEX_JSON.close_object;        
end;');

ORDS.DEFINE_HANDLER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'GET',

p_source_type => 'plsql/block',

p_mimes_allowed => '',

p_comments => NULL,

p_source =>

'declare

tmp_badgeId VARCHAR2(20);

tmp_buttonLabel VARCHAR2(2);

tmp_buttonFunction VARCHAR2(10);

tmp_template VARCHAR2(12);

tmp_title VARCHAR2(50);

tmp_subtitle VARCHAR2(100);

tmp_bodytext VARCHAR2(200);

tmp_icon VARCHAR2(200);

tmp_badge VARCHAR2(5);

tmp_backgorundImage VARCHAR2(200);

tmp_backgroundColor VARCHAR2(10);

tmp_fingerprint VARCHAR2(200);

tmp_barcode VARCHAR2(50);

tmp_function VARCHAR2(20);

begin

select

BADGEID, BUTTONLABEL, BUTTONFUNCTION, TEMPLATE, TITLE, SUBTITLE, BODYTEXT, ICON, BADGE, BACKGROUNDIMAGE, BACKGROUNDCOLOR, FINGERPRINT, BARCODE

into

tmp_badgeId, tmp_buttonLabel, tmp_buttonFunction, tmp_template, tmp_title, tmp_subtitle, tmp_bodytext, tmp_icon, tmp_badge, tmp_backgorundImage, tmp_backgroundColor, tmp_fingerprint, tmp_barcode

from CODEMAKER

where upper(BADGEID) = upper(:badgeid) and BUTTONLABEL = :buttonlabel and BUTTONFUNCTION = :buttonfunction;

APEX_JSON.open_object;

APEX_JSON.write(''template'', tmp_template);

APEX_JSON.write_raw(''title'', ''"'' || tmp_title || ''"'');

APEX_JSON.write_raw(''subtitle'', ''"'' || tmp_subtitle || ''"'');

APEX_JSON.write_raw(''bodytext'', ''"'' ||tmp_bodytext || ''"'');

APEX_JSON.write(''icon'', tmp_icon);

APEX_JSON.write(''badge'', tmp_badge);

APEX_JSON.write(''backgroundImage'', tmp_backgorundImage);

APEX_JSON.write(''backgroundColor'', tmp_backgroundColor);

APEX_JSON.write(''fingerprint'', tmp_fingerprint);

APEX_JSON.write(''barcode'', tmp_barcode);

APEX_JSON.close_object;

EXCEPTION

WHEN OTHERS THEN

if :buttonfunction = ''1'' then

tmp_function := ''Button '' || UPPER(:buttonlabel) || '' short press'';

elsif :buttonfunction = ''2'' then

tmp_function := ''Button '' || UPPER(:buttonlabel) ||'' long press'';

else

tmp_function := ''No button pressed'';

end if;

APEX_JSON.open_object;

APEX_JSON.write(''template'', ''template1'');

APEX_JSON.write(''title'', ''Uh oh!'');

APEX_JSON.write(''subtitle'', tmp_function);

APEX_JSON.write(''bodytext'', ''You have not configured your Code Card'');

APEX_JSON.write(''icon'', ''fail'');

APEX_JSON.close_object;

end;');

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

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'functions',
      p_pattern            => 'master',
      p_method             => 'GET',
      p_name               => 'X-CODECARD-BUTTON-FUNCTION',
      p_bind_variable_name => 'buttonfunction',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'functions',
      p_pattern            => 'master',
      p_method             => 'GET',
      p_name               => 'X-CODECARD-BUTTON-LABEL',
      p_bind_variable_name => 'buttonlabel',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'functions',
      p_pattern            => 'master',
      p_method             => 'GET',
      p_name               => 'X-CODECARD-ID',
      p_bind_variable_name => 'badgeid',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'GET',

p_name => 'X-CODECARD-BUTTON-FUNCTION',

p_bind_variable_name => 'buttonfunction',

p_source_type => 'HEADER',

p_param_type => 'STRING',

p_access_method => 'IN',

p_comments => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'GET',

p_name => 'X-CODECARD-BUTTON-LABEL',

p_bind_variable_name => 'buttonlabel',

p_source_type => 'HEADER',

p_param_type => 'STRING',

p_access_method => 'IN',

p_comments => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'GET',

p_name => 'X-CODECARD-ID',

p_bind_variable_name => 'badgeid',

p_source_type => 'HEADER',

p_param_type => 'STRING',

p_access_method => 'IN',

p_comments => NULL);

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.

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'functions',
      p_pattern        => 'master',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => 'application/json',
      p_comments       => NULL,
      p_source         => 
'declare
    tmpId number;
    tmpTitle varchar2(1000);
    tmpSubtitle varchar2(1000);
    tmpBody varchar2(1000);
begin

    tmpTitle := regexp_replace(:title, ''<.*?>'');
    tmpTitle := replace(tmpTitle, ''&nbsp;'', '' '');
    tmpSubtitle := regexp_replace(:subtitle, ''<.*?>'');
    tmpSubtitle := replace(tmpSubtitle, ''&nbsp;'', '' '');
    tmpBody := regexp_replace(:bodytext, ''<.*?>'');
    tmpBody := replace(tmpBody, ''&nbsp;'', '' '');

    insert into CODEMAKER (
        BADGEID, BUTTONLABEL, BUTTONFUNCTION, TEMPLATE, TITLE, SUBTITLE, BODYTEXT, ICON, BADGE, BACKGROUNDIMAGE, BACKGROUNDCOLOR, FINGERPRINT, BARCODE
    ) 
    values (
        upper(:badgeId), :buttonLabel, :buttonFunction, :template, tmpTitle, tmpSubtitle, tmpBody, :icon, :badge, :backgorundImage, :backgroundColor, :fingerprint, :barcode
    )
    returning id into tmpId;
    
    APEX_JSON.open_object;
    APEX_JSON.write(''badgeId'', upper(:badgeId));
    APEX_JSON.write(''status'', ''new'');
    APEX_JSON.write(''recordId'', tmpId);
    APEX_JSON.close_object;
    
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
        update CODEMAKER set 
        TEMPLATE = :template, 
        TITLE = tmpTitle, 
        SUBTITLE = tmpSubtitle, 
        BODYTEXT = tmpBody, 
        ICON = :icon,
        BADGE = :badge,
        BACKGROUNDIMAGE = :backgroundImage,
        BACKGROUNDCOLOR = :backgroundColor,
        FINGERPRINT = :fingerprint,
        BARCODE = :barcode
        where BADGEID = upper(:badgeId) and BUTTONLABEL = :buttonlabel and BUTTONFUNCTION = :buttonfunction
        returning id into tmpId;
        commit;

        APEX_JSON.open_object;
        APEX_JSON.write(''badgeId'', upper(:badgeId));
        APEX_JSON.write(''reacordId'', tmpId);
        APEX_JSON.close_object;
    
end;');

ORDS.DEFINE_HANDLER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'POST',

p_source_type => 'plsql/block',

p_mimes_allowed => 'application/json',

p_comments => NULL,

p_source =>

'declare

tmpId number;

tmpTitle varchar2(1000);

tmpSubtitle varchar2(1000);

tmpBody varchar2(1000);

begin

tmpTitle := regexp_replace(:title, ''<.*?>'');

tmpTitle := replace(tmpTitle, '' '', '' '');

tmpSubtitle := regexp_replace(:subtitle, ''<.*?>'');

tmpSubtitle := replace(tmpSubtitle, '' '', '' '');

tmpBody := regexp_replace(:bodytext, ''<.*?>'');

tmpBody := replace(tmpBody, '' '', '' '');

insert into CODEMAKER (

BADGEID, BUTTONLABEL, BUTTONFUNCTION, TEMPLATE, TITLE, SUBTITLE, BODYTEXT, ICON, BADGE, BACKGROUNDIMAGE, BACKGROUNDCOLOR, FINGERPRINT, BARCODE

)

values (

upper(:badgeId), :buttonLabel, :buttonFunction, :template, tmpTitle, tmpSubtitle, tmpBody, :icon, :badge, :backgorundImage, :backgroundColor, :fingerprint, :barcode

)

returning id into tmpId;

APEX_JSON.open_object;

APEX_JSON.write(''badgeId'', upper(:badgeId));

APEX_JSON.write(''status'', ''new'');

APEX_JSON.write(''recordId'', tmpId);

APEX_JSON.close_object;

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

update CODEMAKER set

TEMPLATE = :template,

TITLE = tmpTitle,

SUBTITLE = tmpSubtitle,

BODYTEXT = tmpBody,

ICON = :icon,

BADGE = :badge,

BACKGROUNDIMAGE = :backgroundImage,

BACKGROUNDCOLOR = :backgroundColor,

FINGERPRINT = :fingerprint,

BARCODE = :barcode

where BADGEID = upper(:badgeId) and BUTTONLABEL = :buttonlabel and BUTTONFUNCTION = :buttonfunction

returning id into tmpId;

commit;

APEX_JSON.open_object;

APEX_JSON.write(''badgeId'', upper(:badgeId));

APEX_JSON.write(''reacordId'', tmpId);

APEX_JSON.close_object;

end;');

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.

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'functions',
      p_pattern            => 'master',
      p_method             => 'POST',
      p_name               => 'X-CODECARD-BUTTON-FUNCTION',
      p_bind_variable_name => 'buttonFunction',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'functions',
      p_pattern            => 'master',
      p_method             => 'POST',
      p_name               => 'X-CODECARD-BUTTON-LABEL',
      p_bind_variable_name => 'buttonLabel',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'functions',
      p_pattern            => 'master',
      p_method             => 'POST',
      p_name               => 'X-CODECARD-ID',
      p_bind_variable_name => 'badgeId',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'POST',

p_name => 'X-CODECARD-BUTTON-FUNCTION',

p_bind_variable_name => 'buttonFunction',

p_source_type => 'HEADER',

p_param_type => 'STRING',

p_access_method => 'IN',

p_comments => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'POST',

p_name => 'X-CODECARD-BUTTON-LABEL',

p_bind_variable_name => 'buttonLabel',

p_source_type => 'HEADER',

p_param_type => 'STRING',

p_access_method => 'IN',

p_comments => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'POST',

p_name => 'X-CODECARD-ID',

p_bind_variable_name => 'badgeId',

p_source_type => 'HEADER',

p_param_type => 'STRING',

p_access_method => 'IN',

p_comments => NULL);

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.

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'functions',
      p_pattern        => 'register',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'functions',
      p_pattern        => 'register',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => 'application/json',
      p_comments       => NULL,
      p_source         => 
'declare
    tmpBadgeId varchar2(100);
begin
    
    insert into CODECARD (
        BADGEID, NAME, CREATED
    ) 
    values (
        UPPER(:badgeId), INITCAP(:name), systimestamp
    );
    
    APEX_JSON.open_object;
    APEX_JSON.write(''badgeId'', upper(:badgeId));
    APEX_JSON.write(''status'', ''registered'');
    APEX_JSON.close_object;
    
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
        UPDATE CODECARD set NAME = INITCAP(:name) where BADGEID = upper(:badgeId);
        APEX_JSON.open_object;
        APEX_JSON.write(''badgeId'', upper(:badgeId));
        APEX_JSON.write(''status'', ''logged'');
        APEX_JSON.close_object;
    
end;');

ORDS.DEFINE_TEMPLATE(

p_module_name => 'functions',

p_pattern => 'register',

p_priority => 0,

p_etag_type => 'HASH',

p_etag_query => NULL,

p_comments => NULL);

ORDS.DEFINE_HANDLER(

p_module_name => 'functions',

p_pattern => 'register',

p_method => 'POST',

p_source_type => 'plsql/block',

p_mimes_allowed => 'application/json',

p_comments => NULL,

p_source =>

'declare

tmpBadgeId varchar2(100);

begin

insert into CODECARD (

BADGEID, NAME, CREATED

)

values (

UPPER(:badgeId), INITCAP(:name), systimestamp

);

APEX_JSON.open_object;

APEX_JSON.write(''badgeId'', upper(:badgeId));

APEX_JSON.write(''status'', ''registered'');

APEX_JSON.close_object;

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

UPDATE CODECARD set NAME = INITCAP(:name) where BADGEID = upper(:badgeId);

APEX_JSON.open_object;

APEX_JSON.write(''badgeId'', upper(:badgeId));

APEX_JSON.write(''status'', ''logged'');

APEX_JSON.close_object;

end;');

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.

set define off;

DECLARE

BEGIN
  ORDS.DEFINE_MODULE(
      p_module_name    => 'functions',
      p_base_path      => '/functions/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'functions',
      p_pattern        => 'master',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'functions',
      p_pattern        => 'master',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'declare
	tmp_badgeId VARCHAR2(20);
	tmp_buttonLabel VARCHAR2(2);
	tmp_buttonFunction VARCHAR2(10);
	tmp_template VARCHAR2(12);
	tmp_title VARCHAR2(50);
	tmp_subtitle VARCHAR2(100);
	tmp_bodytext VARCHAR2(200);
	tmp_icon VARCHAR2(200);
	tmp_badge VARCHAR2(5);
	tmp_backgorundImage VARCHAR2(200);
	tmp_backgroundColor VARCHAR2(10);
    tmp_fingerprint VARCHAR2(200);
	tmp_barcode VARCHAR2(50);
    tmp_function VARCHAR2(20);
begin
    select
        BADGEID, BUTTONLABEL, BUTTONFUNCTION, TEMPLATE, TITLE, SUBTITLE, BODYTEXT, ICON, BADGE, BACKGROUNDIMAGE, BACKGROUNDCOLOR, FINGERPRINT, BARCODE
    into
        tmp_badgeId, tmp_buttonLabel, tmp_buttonFunction, tmp_template, tmp_title, tmp_subtitle, tmp_bodytext, tmp_icon, tmp_badge, tmp_backgorundImage, tmp_backgroundColor, tmp_fingerprint, tmp_barcode
    from CODEMAKER
    where upper(BADGEID) = upper(:badgeid) and BUTTONLABEL = :buttonlabel and BUTTONFUNCTION = :buttonfunction;
    
    APEX_JSON.open_object;
    APEX_JSON.write(''template'', tmp_template);
    APEX_JSON.write_raw(''title'', ''"'' || tmp_title || ''"'');
    APEX_JSON.write_raw(''subtitle'', ''"'' || tmp_subtitle || ''"'');
    APEX_JSON.write_raw(''bodytext'', ''"'' ||tmp_bodytext || ''"'');
    APEX_JSON.write(''icon'', tmp_icon);
    APEX_JSON.write(''badge'', tmp_badge);
    APEX_JSON.write(''backgroundImage'', tmp_backgorundImage);
    APEX_JSON.write(''backgroundColor'', tmp_backgroundColor);
    APEX_JSON.write(''fingerprint'', tmp_fingerprint);
    APEX_JSON.write(''barcode'', tmp_barcode);
    APEX_JSON.close_object;
    
    EXCEPTION
        WHEN OTHERS THEN
        
        if :buttonfunction = ''1'' then
            tmp_function := ''Button '' ||  UPPER(:buttonlabel) || '' short press'';
        elsif :buttonfunction = ''2'' then
            tmp_function := ''Button '' ||  UPPER(:buttonlabel) ||'' long press'';
        else
           tmp_function := ''No button pressed''; 
        end if;

        APEX_JSON.open_object;
        APEX_JSON.write(''template'', ''template1'');
        APEX_JSON.write(''title'', ''Uh oh!'');
        APEX_JSON.write(''subtitle'', tmp_function);
        APEX_JSON.write(''bodytext'', ''You have not configured your Code Card'');
        APEX_JSON.write(''icon'', ''fail'');
        APEX_JSON.close_object;        
end;');

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'functions',
      p_pattern            => 'master',
      p_method             => 'GET',
      p_name               => 'X-CODECARD-BUTTON-FUNCTION',
      p_bind_variable_name => 'buttonfunction',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'functions',
      p_pattern            => 'master',
      p_method             => 'GET',
      p_name               => 'X-CODECARD-BUTTON-LABEL',
      p_bind_variable_name => 'buttonlabel',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'functions',
      p_pattern            => 'master',
      p_method             => 'GET',
      p_name               => 'X-CODECARD-ID',
      p_bind_variable_name => 'badgeid',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'functions',
      p_pattern        => 'master',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => 'application/json',
      p_comments       => NULL,
      p_source         => 
'declare
    tmpId number;
    tmpTitle varchar2(1000);
    tmpSubtitle varchar2(1000);
    tmpBody varchar2(1000);
begin

    tmpTitle := regexp_replace(:title, ''<.*?>'');
    tmpTitle := replace(tmpTitle, ''&nbsp;'', '' '');
    tmpSubtitle := regexp_replace(:subtitle, ''<.*?>'');
    tmpSubtitle := replace(tmpSubtitle, ''&nbsp;'', '' '');
    tmpBody := regexp_replace(:bodytext, ''<.*?>'');
    tmpBody := replace(tmpBody, ''&nbsp;'', '' '');

    insert into CODEMAKER (
        BADGEID, BUTTONLABEL, BUTTONFUNCTION, TEMPLATE, TITLE, SUBTITLE, BODYTEXT, ICON, BADGE, BACKGROUNDIMAGE, BACKGROUNDCOLOR, FINGERPRINT, BARCODE
    ) 
    values (
        upper(:badgeId), :buttonLabel, :buttonFunction, :template, tmpTitle, tmpSubtitle, tmpBody, :icon, :badge, :backgorundImage, :backgroundColor, :fingerprint, :barcode
    )
    returning id into tmpId;
    
    APEX_JSON.open_object;
    APEX_JSON.write(''badgeId'', upper(:badgeId));
    APEX_JSON.write(''status'', ''new'');
    APEX_JSON.write(''recordId'', tmpId);
    APEX_JSON.close_object;
    
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
        update CODEMAKER set 
        TEMPLATE = :template, 
        TITLE = tmpTitle, 
        SUBTITLE = tmpSubtitle, 
        BODYTEXT = tmpBody, 
        ICON = :icon,
        BADGE = :badge,
        BACKGROUNDIMAGE = :backgroundImage,
        BACKGROUNDCOLOR = :backgroundColor,
        FINGERPRINT = :fingerprint,
        BARCODE = :barcode
        where BADGEID = upper(:badgeId) and BUTTONLABEL = :buttonlabel and BUTTONFUNCTION = :buttonfunction
        returning id into tmpId;
        commit;

        APEX_JSON.open_object;
        APEX_JSON.write(''badgeId'', upper(:badgeId));
        APEX_JSON.write(''reacordId'', tmpId);
        APEX_JSON.close_object;
    
end;');

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'functions',
      p_pattern            => 'master',
      p_method             => 'POST',
      p_name               => 'X-CODECARD-BUTTON-FUNCTION',
      p_bind_variable_name => 'buttonFunction',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'functions',
      p_pattern            => 'master',
      p_method             => 'POST',
      p_name               => 'X-CODECARD-BUTTON-LABEL',
      p_bind_variable_name => 'buttonLabel',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'functions',
      p_pattern            => 'master',
      p_method             => 'POST',
      p_name               => 'X-CODECARD-ID',
      p_bind_variable_name => 'badgeId',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'functions',
      p_pattern        => 'register',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'functions',
      p_pattern        => 'register',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => 'application/json',
      p_comments       => NULL,
      p_source         => 
'declare
    tmpBadgeId varchar2(100);
begin
    
    insert into CODECARD (
        BADGEID, NAME, CREATED
    ) 
    values (
        UPPER(:badgeId), INITCAP(:name), systimestamp
    );
    
    APEX_JSON.open_object;
    APEX_JSON.write(''badgeId'', upper(:badgeId));
    APEX_JSON.write(''status'', ''registered'');
    APEX_JSON.close_object;
    
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
        UPDATE CODECARD set NAME = INITCAP(:name) where BADGEID = upper(:badgeId);
        APEX_JSON.open_object;
        APEX_JSON.write(''badgeId'', upper(:badgeId));
        APEX_JSON.write(''status'', ''logged'');
        APEX_JSON.close_object;
    
end;');

COMMIT;

END;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

set define off;

DECLARE

BEGIN

ORDS.DEFINE_MODULE(

p_module_name => 'functions',

p_base_path => '/functions/',

p_items_per_page => 25,

p_status => 'PUBLISHED',

p_comments => NULL);

ORDS.DEFINE_TEMPLATE(

p_module_name => 'functions',

p_pattern => 'master',

p_priority => 0,

p_etag_type => 'HASH',

p_etag_query => NULL,

p_comments => NULL);

ORDS.DEFINE_HANDLER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'GET',

p_source_type => 'plsql/block',

p_mimes_allowed => '',

p_comments => NULL,

p_source =>

'declare

tmp_badgeId VARCHAR2(20);

tmp_buttonLabel VARCHAR2(2);

tmp_buttonFunction VARCHAR2(10);

tmp_template VARCHAR2(12);

tmp_title VARCHAR2(50);

tmp_subtitle VARCHAR2(100);

tmp_bodytext VARCHAR2(200);

tmp_icon VARCHAR2(200);

tmp_badge VARCHAR2(5);

tmp_backgorundImage VARCHAR2(200);

tmp_backgroundColor VARCHAR2(10);

tmp_fingerprint VARCHAR2(200);

tmp_barcode VARCHAR2(50);

tmp_function VARCHAR2(20);

begin

select

BADGEID, BUTTONLABEL, BUTTONFUNCTION, TEMPLATE, TITLE, SUBTITLE, BODYTEXT, ICON, BADGE, BACKGROUNDIMAGE, BACKGROUNDCOLOR, FINGERPRINT, BARCODE

into

tmp_badgeId, tmp_buttonLabel, tmp_buttonFunction, tmp_template, tmp_title, tmp_subtitle, tmp_bodytext, tmp_icon, tmp_badge, tmp_backgorundImage, tmp_backgroundColor, tmp_fingerprint, tmp_barcode

from CODEMAKER

where upper(BADGEID) = upper(:badgeid) and BUTTONLABEL = :buttonlabel and BUTTONFUNCTION = :buttonfunction;

APEX_JSON.open_object;

APEX_JSON.write(''template'', tmp_template);

APEX_JSON.write_raw(''title'', ''"'' || tmp_title || ''"'');

APEX_JSON.write_raw(''subtitle'', ''"'' || tmp_subtitle || ''"'');

APEX_JSON.write_raw(''bodytext'', ''"'' ||tmp_bodytext || ''"'');

APEX_JSON.write(''icon'', tmp_icon);

APEX_JSON.write(''badge'', tmp_badge);

APEX_JSON.write(''backgroundImage'', tmp_backgorundImage);

APEX_JSON.write(''backgroundColor'', tmp_backgroundColor);

APEX_JSON.write(''fingerprint'', tmp_fingerprint);

APEX_JSON.write(''barcode'', tmp_barcode);

APEX_JSON.close_object;

EXCEPTION

WHEN OTHERS THEN

if :buttonfunction = ''1'' then

tmp_function := ''Button '' || UPPER(:buttonlabel) || '' short press'';

elsif :buttonfunction = ''2'' then

tmp_function := ''Button '' || UPPER(:buttonlabel) ||'' long press'';

else

tmp_function := ''No button pressed'';

end if;

APEX_JSON.open_object;

APEX_JSON.write(''template'', ''template1'');

APEX_JSON.write(''title'', ''Uh oh!'');

APEX_JSON.write(''subtitle'', tmp_function);

APEX_JSON.write(''bodytext'', ''You have not configured your Code Card'');

APEX_JSON.write(''icon'', ''fail'');

APEX_JSON.close_object;

end;');

ORDS.DEFINE_PARAMETER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'GET',

p_name => 'X-CODECARD-BUTTON-FUNCTION',

p_bind_variable_name => 'buttonfunction',

p_source_type => 'HEADER',

p_param_type => 'STRING',

p_access_method => 'IN',

p_comments => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'GET',

p_name => 'X-CODECARD-BUTTON-LABEL',

p_bind_variable_name => 'buttonlabel',

p_source_type => 'HEADER',

p_param_type => 'STRING',

p_access_method => 'IN',

p_comments => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'GET',

p_name => 'X-CODECARD-ID',

p_bind_variable_name => 'badgeid',

p_source_type => 'HEADER',

p_param_type => 'STRING',

p_access_method => 'IN',

p_comments => NULL);

ORDS.DEFINE_HANDLER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'POST',

p_source_type => 'plsql/block',

p_mimes_allowed => 'application/json',

p_comments => NULL,

p_source =>

'declare

tmpId number;

tmpTitle varchar2(1000);

tmpSubtitle varchar2(1000);

tmpBody varchar2(1000);

begin

tmpTitle := regexp_replace(:title, ''<.*?>'');

tmpTitle := replace(tmpTitle, '' '', '' '');

tmpSubtitle := regexp_replace(:subtitle, ''<.*?>'');

tmpSubtitle := replace(tmpSubtitle, '' '', '' '');

tmpBody := regexp_replace(:bodytext, ''<.*?>'');

tmpBody := replace(tmpBody, '' '', '' '');

insert into CODEMAKER (

BADGEID, BUTTONLABEL, BUTTONFUNCTION, TEMPLATE, TITLE, SUBTITLE, BODYTEXT, ICON, BADGE, BACKGROUNDIMAGE, BACKGROUNDCOLOR, FINGERPRINT, BARCODE

)

values (

upper(:badgeId), :buttonLabel, :buttonFunction, :template, tmpTitle, tmpSubtitle, tmpBody, :icon, :badge, :backgorundImage, :backgroundColor, :fingerprint, :barcode

)

returning id into tmpId;

APEX_JSON.open_object;

APEX_JSON.write(''badgeId'', upper(:badgeId));

APEX_JSON.write(''status'', ''new'');

APEX_JSON.write(''recordId'', tmpId);

APEX_JSON.close_object;

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

update CODEMAKER set

TEMPLATE = :template,

TITLE = tmpTitle,

SUBTITLE = tmpSubtitle,

BODYTEXT = tmpBody,

ICON = :icon,

BADGE = :badge,

BACKGROUNDIMAGE = :backgroundImage,

BACKGROUNDCOLOR = :backgroundColor,

FINGERPRINT = :fingerprint,

BARCODE = :barcode

where BADGEID = upper(:badgeId) and BUTTONLABEL = :buttonlabel and BUTTONFUNCTION = :buttonfunction

returning id into tmpId;

commit;

APEX_JSON.open_object;

APEX_JSON.write(''badgeId'', upper(:badgeId));

APEX_JSON.write(''reacordId'', tmpId);

APEX_JSON.close_object;

end;');

ORDS.DEFINE_PARAMETER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'POST',

p_name => 'X-CODECARD-BUTTON-FUNCTION',

p_bind_variable_name => 'buttonFunction',

p_source_type => 'HEADER',

p_param_type => 'STRING',

p_access_method => 'IN',

p_comments => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'POST',

p_name => 'X-CODECARD-BUTTON-LABEL',

p_bind_variable_name => 'buttonLabel',

p_source_type => 'HEADER',

p_param_type => 'STRING',

p_access_method => 'IN',

p_comments => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => 'functions',

p_pattern => 'master',

p_method => 'POST',

p_name => 'X-CODECARD-ID',

p_bind_variable_name => 'badgeId',

p_source_type => 'HEADER',

p_param_type => 'STRING',

p_access_method => 'IN',

p_comments => NULL);

ORDS.DEFINE_TEMPLATE(

p_module_name => 'functions',

p_pattern => 'register',

p_priority => 0,

p_etag_type => 'HASH',

p_etag_query => NULL,

p_comments => NULL);

ORDS.DEFINE_HANDLER(

p_module_name => 'functions',

p_pattern => 'register',

p_method => 'POST',

p_source_type => 'plsql/block',

p_mimes_allowed => 'application/json',

p_comments => NULL,

p_source =>

'declare

tmpBadgeId varchar2(100);

begin

insert into CODECARD (

BADGEID, NAME, CREATED

)

values (

UPPER(:badgeId), INITCAP(:name), systimestamp

);

APEX_JSON.open_object;

APEX_JSON.write(''badgeId'', upper(:badgeId));

APEX_JSON.write(''status'', ''registered'');

APEX_JSON.close_object;

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

UPDATE CODECARD set NAME = INITCAP(:name) where BADGEID = upper(:badgeId);

APEX_JSON.open_object;

APEX_JSON.write(''badgeId'', upper(:badgeId));

APEX_JSON.write(''status'', ''logged'');

APEX_JSON.close_object;

end;');

COMMIT;

END;

Setup a button press

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

Turn your CodeCard on.
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.
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

{
    "template": "template5",
    "title": "I DID IT!!!",
    "subtitle": "My CodeCard",
    "bodytext": "is running on my Oracle Always Free ATP database!",
    "icon": "opensource",
    "backgroundColor": "white",
    "barcode": "Oracle Code"
}

{

"template": "template5",

"title": "I DID IT!!!",

"subtitle": "My CodeCard",

"bodytext": "is running on my Oracle Always Free ATP database!",

"icon": "opensource",

"backgroundColor": "white",

"barcode": "Oracle Code"

}

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

curl --location --request POST '{{URL}}/cc/functions/master' \
--header 'X-CODECARD-ID: {{CodeCardID}}' \
--header 'X-CODECARD-BUTTON-LABEL: a' \
--header 'X-CODECARD-BUTTON-FUNCTION: 1' \
--header 'Content-Type: application/json' \
--data-raw '{
    "template": "template5",
    "title": "I DID IT!!!",
    "subtitle": "My CodeCard",
    "bodytext": "is running on my Oracle Always Free ATP database!",
    "icon": "opensource",
    "backgroundColor": "white",
    "barcode": "Oracle Code"
}'

curl --location --request POST '{{URL}}/cc/functions/master' \

--header 'X-CODECARD-ID: {{CodeCardID}}' \

--header 'X-CODECARD-BUTTON-LABEL: a' \

--header 'X-CODECARD-BUTTON-FUNCTION: 1' \

--header 'Content-Type: application/json' \

--data-raw '{

"template": "template5",

"title": "I DID IT!!!",

"subtitle": "My CodeCard",

"bodytext": "is running on my Oracle Always Free ATP database!",

"icon": "opensource",

"backgroundColor": "white",

"barcode": "Oracle Code"

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

{
"badgeId":"YourBadgeId"
,"reacordId":2
}

{

"badgeId":"YourBadgeId"

,"reacordId":2

}

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

curl --location --request GET '{{URL}}/cc/functions/master' \
--header 'X-CODECARD-ID: {{CodeCardID}}' \
--header 'X-CODECARD-BUTTON-LABEL: a' \
--header 'X-CODECARD-BUTTON-FUNCTION: 1'

curl --location --request GET '{{URL}}/cc/functions/master' \

--header 'X-CODECARD-ID: {{CodeCardID}}' \

--header 'X-CODECARD-BUTTON-LABEL: a' \

--header 'X-CODECARD-BUTTON-FUNCTION: 1'

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

{
    "template": "template5",
    "title": "I DID IT!!!",
    "subtitle": "My CodeCard",
    "bodytext": "is running on my Oracle Always Free ATP database!",
    "icon": "opensource",
    "backgroundColor": "white",
    "barcode": "Oracle Code"
}

{

"template": "template5",

"title": "I DID IT!!!",

"subtitle": "My CodeCard",

"bodytext": "is running on my Oracle Always Free ATP database!",

"icon": "opensource",

"backgroundColor": "white",

"barcode": "Oracle Code"

}

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.

Autonomous, Cloud

Create an Always Free Autonomous Database on the Oracle Cloud

January 9, 2020 bcarter 3 Comments

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.

Click on the “Create an ATP database” box. (Or the “Create an ADW database” box if you want a data warehouse)
Choose the compartment you want to use.
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.
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.
In the “Choose a workload type” section, make sure the correct workload type is selected..
Since we want to create an Always Free Database, leave “Shared Infrastructure” selected in the “Choose a deployment type” section.
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)
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.
Under the “Choose a license type” section make sure “License Included” is selected.
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

Click on “Administration”.
Click on “Download Client Credentials (Wallet)”.
Create a password that will be used to access the .zip file.
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.

Click the “DB Connection” button.
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.

Start SQLcl in ‘nolog’ mode

SQL> sql /nolog SQLcl: Release 18.3 Production on Fri Jan 03 17:57:56 2020 Copyright (c) 1982, 2020, Oracle. All rights reserved.

1
2
3
4

SQL> sql /nolog
SQLcl: Release 18.3 Production on Fri Jan 03 17:57:56 2020

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

Set the location of your .zip file in the cloudconfig variable.

SQL> set cloudconfig = /home/oracle/wallets/new_wallet.zip
Operation is successfully completed.
Operation is successfully completed.
Using temp directory:/tmp/oracle_cloud_config5623188769293536689

SQL> set cloudconfig = /home/oracle/wallets/new_wallet.zip

Operation is successfully completed.

Using temp directory:/tmp/oracle_cloud_config5623188769293536689

Connect to your database using the password you set for the Admin account.

SQL> connect admin/TopSecretPW@NewDB_TP Connected.

1
2

SQL> connect admin/TopSecretPW@NewDB_TP
Connected.
Run a test query

SQL> select sysdate from dual; SYSDATE --------- 03-DEC-19

1
2
3
4

SQL> select sysdate from dual;
SYSDATE
---------
03-DEC-19

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.

Autonomous, Docker, NodeJS

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

August 27, 2019 bcarter 11 Comments

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

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

Simple Test App

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

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

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

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

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

module.exports = {

user : process.env.NODE_ORACLEDB_USER,

password : process.env.NODE_ORACLEDB_PASSWORD,

connectString : process.env.NODE_ORACLEDB_CONNECTIONSTRING,

poolMax: 2,

poolMin: 2,

poolIncrement: 0

};

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

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

async function runTest() {
  let conn;

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

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

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

runTest();

const oracledb = require('oracledb');

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

async function runTest() {

let conn;

try {

conn = await oracledb.getConnection(config);

const result = await conn.execute(

'select current_timestamp from dual'

);

console.log(result);

} catch (err) {

console.error(err);

} finally {

if (conn) {

try {

await conn.close();

} catch (err) {

console.error(err);

}

runTest();

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

npm install oracledb -s

1	npm install oracledb -s

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

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

export NODE_ORACLEDB_USER=NotMyRealUser

export NODE_ORACLEDB_PASSWORD=NotMyRealPassword

export NODE_ORACLEDB_CONNECTIONSTRING=192.168.0.44:1521/NotMyRealServiceName

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

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

@OraBlaineOS:SimpleApp$ node server.js

{

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

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

}

Create a Docker Container for the Node.js app

Create a Dockerfile

FROM oraclelinux:7-slim

# Create app directory
WORKDIR /usr/src/app

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

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

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

FROM oraclelinux:7-slim

# Create app directory

WORKDIR /usr/src/app

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

ADD *.js ./

# Update Oracle Linux

# Install Node.js

# Install the Oracle Instant Client

# Check that Node.js and NPM installed correctly

# Install the OracleDB driver

RUN yum update -y && \

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

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

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

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

yum clean all && \

node --version && \

npm --version && \

npm install oracledb && \

echo Installed

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

Build the image

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

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

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

Typical Oracle Database

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

docker run \

--name typical_node_example -it \

-e NODE_ORACLEDB_USER=$NODE_ORACLEDB_USER \

-e NODE_ORACLEDB_PASSWORD=$NODE_ORACLEDB_PASSWORD \

-e NODE_ORACLEDB_CONNECTIONSTRING=$NODE_ORACLEDB_CONNECTIONSTRING \

node_example

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

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

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

An Oracle Database inside a Docker Container

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

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

docker network create OrdsXeNet

1	docker network create OrdsXeNet

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

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

export NODE_ORACLEDB_CONNECTIONSTRING=oracleXe:1521/xepdb1

1	export NODE_ORACLEDB_CONNECTIONSTRING=oracleXe:1521/xepdb1

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

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

docker run \

--name docker_network_node_example -it \

--network=OrdsXeNet \

-e NODE_ORACLEDB_USER=$NODE_ORACLEDB_USER \

-e NODE_ORACLEDB_PASSWORD=$NODE_ORACLEDB_PASSWORD \

-e NODE_ORACLEDB_CONNECTIONSTRING=$NODE_ORACLEDB_CONNECTIONSTRING \

node_example

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

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

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

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

Oracle Autonomous Transaction Processing Cloud Database

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

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

export NODE_ORACLEDB_CONNECTIONSTRING=projects_tp

1	export NODE_ORACLEDB_CONNECTIONSTRING=projects_tp

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

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

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

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

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

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

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

-e TNS_ADMIN=/wallet

1	-e TNS_ADMIN=/wallet

The full command looks like this:

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

docker run \

--name cloud_atp_node_example -it \

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

-e NODE_ORACLEDB_USER=$NODE_ORACLEDB_USER \

-e NODE_ORACLEDB_PASSWORD=$NODE_ORACLEDB_PASSWORD \

-e NODE_ORACLEDB_CONNECTIONSTRING=$NODE_ORACLEDB_CONNECTIONSTRING \

-e TNS_ADMIN=/wallet \

-p 3000:3000 \

node_example

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

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

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

I used the same docker image for all of the examples

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

Autonomous, REST

Oracle REST Data Services on Autonomous Transaction Processing Database

July 1, 2019 bcarter Leave a comment

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

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

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

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

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

It’s going to be hard… right?

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

New User

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

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

GRANT CREATE SESSION, RESOURCE, UNLIMITED TABLESPACE TO rest_demo;

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

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

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

commit;

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

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

CREATE USER rest_demo IDENTIFIED BY NotMyRealPW;

ALTER USER rest_demo TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION, RESOURCE, UNLIMITED TABLESPACE TO rest_demo;

GRANT CREATE TABLE,

CREATE VIEW,

CREATE SEQUENCE,

CREATE PROCEDURE,

CREATE TYPE,

CREATE SYNONYM

TO rest_demo;

CREATE TABLE REST_DEMO.COOL_DATA_T (

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

NAME VARCHAR2(20) NOT NULL,

MINUTES NUMBER,

CONSTRAINT COOL_DATA_PK PRIMARY KEY (ID) ENABLE

);

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

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

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

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

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

commit;

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

CREATE TABLE REST_DEMO.GALLERY (

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

TITLE VARCHAR2(1000) NOT NULL ENABLE,

CONTENT_TYPE VARCHAR2(1000) NOT NULL ENABLE,

IMAGE BLOB NOT NULL ENABLE,

CONSTRAINT GALLERY_PK PRIMARY KEY (ID) ENABLE

)

Create a test API

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

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

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

    commit;

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

    commit;

END;

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

ORDS.ENABLE_SCHEMA(p_enabled => TRUE,

p_schema => 'REST_DEMO',

p_url_mapping_type => 'BASE_PATH',

p_url_mapping_pattern => 'api',

p_auto_rest_auth => FALSE);

commit;

ORDS.ENABLE_OBJECT(p_enabled => TRUE,

p_schema => 'REST_DEMO',

p_object => 'COOL_DATA_T',

p_object_type => 'TABLE',

p_object_alias => 'cool_data',

p_auto_rest_auth => FALSE);

commit;

END;

Get the URI

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

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

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

Test it

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

Well, that was easy.

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

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

Go try it!

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

Here are some links to help get you going:

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

Environment Variables

Preset Values

Compartment OCID

Availability Domain

Create a Virtual Cloud Network

Create a new VCN

Add a Subnet to the VCN

Add an Internet Gateway

Add a Route Table

Add a Route Rule for the Internet Gateway

RSA Key

Create the Compute Instance

Get the Public IP

Connect

Enjoy

Share this:

Prerequisites

Get the Compartment OCID

Create Compartment

Database

Download the Wallet

Test the Connection

Use a Shell Script to Automate

Explore

Share this:

CLI Overview

Pre-requisits

Python 3.5+

RSA Key Pair

Cloud Account

Quickstart Install

Linux / Mac

Windows

Install with Python

Configure the OCI-CLI

Quick Test

Share this:

Prerequisites

Database Setup

As Admin, create the CODE_CARD schema.

As code_card, create the database objects

Oracle REST Data Services API

This step was completed above

Define a module

Define a Template

GET request handler

Parameters for the bind variables

Handle POST requests

Parameters for the bind variables

Register template (Optional)

Full ORDS PL/SQL code

Setup a button press

Testing

POST

Test GET

Have Fun

Share this:

Create a new Database

Download the Oracle Credentials file

From the Details page

From the Service Console

Important Wallet Security Information

Connect to the Database

Enjoy

Share this:

Simple Test App

Create a Docker Container for the Node.js app

Typical Oracle Database

An Oracle Database inside a Docker Container

Oracle Autonomous Transaction Processing Cloud Database

I used the same docker image for all of the examples

Share this:

It’s going to be hard… right?

New User

Create a test API

Get the URI

Test it

Well, that was easy.

Go try it!

Share this: