ORDS REST Modules with PL/SQL

October 25, 2017 at 9:00 am | Posted in Oracle Developement | Leave a comment

Here is one of the best references I’ve found for creating ORDS REST services with PL/SQL.


There are examples of the various methods (GET, POST, PUT, DELETE), as well as examples for authentication.

One thing to note is that REST modules created with PL/SQL are separate from those created in APEX. Therefore, modules created in APEX cannot be edited with PLSQL.

Creating REST modules in PL/SQL is the more robust method, as it allows you to customize it better. Also, having any sort of logic inside stored procedures and database objects is always a good idea.



ODTUG GeekAThon 2017

September 25, 2017 at 9:11 am | Posted in Oracle Developement | Leave a comment

This year, ODTUG sponsored another GeekAThon, challenging nerds to come up with cool ideas using Bluetooth Low Energy beacons. Here is my entry for this year’s contest:

BLE ID Badge for Amazon Echo

ODTUG GeekAThon 2017

Team: Texas Heat

Christoph Alexander Ruepprich, a.k.a Rocket, Christoph Martin Ruepprich


The BLE (Bluetooth Low Energy) beacon from the KScope conference badges serve as personal identifiers for the Amazon Echo. By determining the ID of the nearest badge, the Echo responds with personal data for the badge bearer.



Using the noble library for Node.js, I was able to determine the individual IDs of the two Bluetooth beacons. Using Node.js on a MacBook Pro, I wrote an Alexa skill that would scan Bluetooth signals. If a signal with one of the previously identified IDs was found the Alexa skill could respond with individualized information.



1 Laptop computer (in this case a MacBook, but a Windows or Linux PC with built in Bluetooth would work, too).

  • 2 BLE Beacons
  • 1 Amazon Echo Dot



Node.js version 8.4.0

Node.js modules:

Ngrok (https://ngrok.com/) to set up a secure tunnel to laptop. This provides a https address with a valid certificate to the localhost, and thus to the Node.js application.



There are two parts to this application: One to handle the Alexa skill, the other to do the Bluetooth scanning.

When the application starts, an express.js webserver is instantiated and the noble Bluetooth module starts scanning immediately. The scanner looks for the two identified BLE beacon IDs and keeps track of them in two variables: closeBle, for the beacon with the stronger signal, and farBle for the beacon with the weaker signal. The signal strength and other attributes of the beacons are recognized by noble, and provided in JSON format.

The webserver waits for incoming POST requests from Alexa. When a request comes in from Alexa, the application checks the ID of closeBle, and then formats the response depending who the owner is.

In this demo we have two beacons: One for the police officer, and one for the hacker. If the application sees that the police officer is closer, it responds with data pertinent to the officer. If the hacker’s badge signal is stronger, the application pretends that the beacon’s ID is not recognized and responds with a message saying that a hacking attempt was made.

Alexa Skill

The Alexa skill uses the invocation Police Headquarters. This is the name by which the skill can be invoked on the Echo. It is configured to use the ngrok URI pointing to the Node.js application on the laptop. The interaction model simply consists of an intent called briefing which is called when the police officer asks for his briefing or mission.



APEX Basic REST authentication

July 25, 2017 at 2:00 pm | Posted in Oracle Developement | 8 Comments

To prevent unauthorized access to your REST modules, APEX provides an easy, declarative way of adding basic authentication. This method utilizes APEX user accounts and RESTful Service Privileges. In order not to expose passwords, you should make sure to enable SSL/HTTPS!

First edit an existing APEX user account, and add the RESTful Services group to that user.  In our example we’ll edit the APEX user SCOTT. To do this, navigate to Manage Users and Groups in the workspace where your REST modules are, edit a user, and assign the group in the Group Assignments region:


Next create the RESTful Service Privilege by navigating to RESTful Services in the SQL Workshop. In the Tasks sidebar on the right, click the RESTful Service Privilege link.


Fill out the form and select the modules you want to protect, by shuttling them to the right in Protected Modules.


That’s it! Everything under the selected module now requires the username and password of the APEX user. You can test this with the following curl command (edit URI as needed):

curl -u scott:tiger http://servername/ords/workspace/hr/empinfo/

When testing with Postman, choose the Basic Auth from the authentication select list and enter the APEX user’s username and password.



Oracle OPC Firewall Rules

July 13, 2017 at 4:30 pm | Posted in Oracle Developement | Leave a comment

Create Firewall Rules

Managing firewall rules on Oracle OPC can be a bit confusing. In this example we want to open up ports 80 and 443 to our compute instance, in order to allow the web server there to be accessed by the public internet.

There are four elements to a firewall rule:

  1. Security List – a named container for Security Rules. A security list can have multiple rules assigned to it. Multiple security lists can be assigned to an instance.
  2. Security Application – an port range and an associated protocol.
  3. Security IP List – a comma separated list of IP address that should get access to your instance. Using specifies the public internet.
  4. Security Rule – says which port range (application) is assigned to an IP list (source) and to which security list (destination) this assignment belongs to.

To create an inbound firewall rule, create a rule for a Security Application and specify the Security IP List (in the Source filed) for which addresses can get through the ports in the Security Application. Then assign that rule to a Security List, specified in the Destination field.


Let’s go through the steps. Let’s assume we already have a compute instance with our web server on it. Log into your Oracle OCP cloud account dashboard, and click the Compute region. From there click the Open Service Console button. Here you should see your existing compute instance under the Instances tab.


Click the Network tab where you should see existing security rules, if any. In the left navigation pane are the various elements we need for the firewall rule.


Click the Security Lists link and then the Create Security List button to create a Security List. Security lists get assigned to instances and map the various ports and IP addresses to that instance:

Click the Security Applications link and then the Create Security Application button. We’ll create an application for port 443.

Click the create button again to add another security application for port 80 to allow http access:

Click the Security IP Lists link and then the Create Security IP List button to create a security list which determines the IP addresses that can get through. Using allows all IP addresses.

Now map the Port(s) to the IP list and the security list. In other words, open up port 443 to the internet for the cmr_internet security list. This is done by creating a Security Rule. This rule basically says allow port 443 to be accessed by the public web, and assign this rule to the security list cmr_public_web. Below the security list will be assigned to the instance. Note that this is an inbound policy, meaning that this is for traffic from the internet to your compute instance. If you were to reverse the source and destination values, it would become an outbound policy.

Quick note about inbound and outbound:
Inbound and outbound policies need to coincide with the policies of the Security List. A rule only works if the corresponding policy is set to Deny. In regular language this would mean: “Deny everything except what’s in the security rules.” If the policy is set to Permit, it means “Let everything through.”

Click the Security Rules link and then the Create Security Rule button. Then map the Security Application to a Source and a Destination.


Create another rule for the port 80 application and assign it to the same security list:

Now we can see that two rules are assigned to a single security list. One for HTTP and one for HTTPS. Both can be accessed via the public internet:

Finally we assign this rule to our compute instance to allow our web server there to serve pages to the public internet via HTTP and HTTPS.

Switch back to the Instances tab, click the instance you want to assign the Security List to, and click the Add to Security List button:

Repeat these steps to create a rule for SSH access on port 22, so that you can access the instance through a terminal. The final result should look like this:

At this point the firewall settings are ready to go. The compute instance can now be accessed via the internet on ports 80 and 443. You can now add or remove other security lists, or add/remove rules from a particular security list to fit your needs.


APEX Interactive Grid: JavaScript Basics Cheat Sheet

July 3, 2017 at 1:52 pm | Posted in Oracle Developement | Leave a comment

Nice cheat sheet for APEX Interactive Grid JavaScript


APEX Interactive Grid can be customized by JavaScript in the Advanced –> JavaScript Code attribute of the Grid or a Grid column.  Yup – JavaScript.

JavaScript may be out of the comfort zone for PL/SQL developers, even those who implemented extensive tabular form customization working with PL/SQL collections.  Time to say Goodbye to those collections … Welcome JavaScript!

The following is a collection of simple JavaScript lines most likely to be needed by a developer wanting to customize an Interactive Grid, or access data elements in the Grid.

This is not a comprehensive list or a complete function – just a simple collection of lines to give you an idea of the process and examples of  – a reference for – the syntax.


Taken line by line:

var $te = $(this.triggeringElement);

This line gets the triggering element – the element that caused the dynamic action (DA) to fire. Usually we…

View original post 537 more words

ORDS REST error after new installation

July 1, 2017 at 4:50 pm | Posted in Oracle Developement | 1 Comment

After installing and configuring ORDS 3.0.10, I noticed that APEX ran fine, but when testing the sample HR REST module I received the following error:

URI: http://myserver:8080/ords/cmr/hr/empinfo/

The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured


The reason was that the table ords_metadata.apex_pool_config was missing the column pool_name. To fix this, I simply had to run the ORDS validation:

java -jar ords.war validate

This added the missing column, and the error went away.

I found the answer to this on OTN.

ORDS REST: Getting Bind Variables Right

June 20, 2017 at 3:38 pm | Posted in Oracle Developement | 1 Comment

It seems every time I create a REST GET module I have to re-learn how to handle bind variables. So I decided to create a little cheat sheet.

When creating a REST module with a GET handler, you can pass variables wither through the URI or through header variables:

URI Variables:


The URI template needs to have the bind variables in curly brackets (comma separated if multiple binds):


Create a query:

select * 
  from emp
 where empno = :id
   and ename = :ename

Create a parameter for each bind variable:

I don’t think whether if the Source Type matters. HTTP Header has worked for me. I typically set the parameter name and bind variable name to upper case to avoid any problems.


Click the Set Bind Variables button to test the handler. Make sure to add a value to all parameters. If you leave a parameter empty, you’ll get an error.


Header Variables:

Create the URI without a trailing forward slash:


Create the query and parameters as before.


You’ll have to test this with a REST client like POSTman or CURL:

$ curl -H "id:7788" -H "ename:SCOTT" http://myserver:8080/ords/cmr/hr/testheader

Note when using header variables, you can leave some out without getting an error.

Happy RESTing!




APEX Interactive Grid Processing with Dynamic Actions

May 18, 2017 at 6:39 pm | Posted in Oracle Developement | Leave a comment

Here is a cool post on triggering dynamic actions from an interactive grid: http://lschilde.blogspot.com/2017/05/apex-51-interactive-grid-row-processing.html

Validating JSON in the database

April 24, 2017 at 6:04 pm | Posted in Oracle Developement | 3 Comments

Creating REST services and consuming JSON inside the Oracle database has become quite easy. The APEX_JSON package provides a number of useful procedures and functions to work with JSON. However, one thing I haven’t found is a way to validate JSON before parsing it.

To my knowledge the is not yet a PL/SQL way to check whether a JSON string is valid, i.e. to make sure that all the brackets, commas, colons, etc. are there. There is however, a column check constraint you can use in 12c that will only allow valid JSON to be inserted into a table. So here is a quick workaround for validating JSON using the check constraint: I simply take the JSON and attempt to insert it into the table. If the check constraint is violated, I’ll get an error.

I wrapped to whole thing in a function that will then simply return a boolean value for the JSON passed into it.

create table json_validator ( 
	 id   number generated always as identity primary key
	,json clob
	,constraint validate_json check (json is json format json)

create or replace function validate_json (l_json in clob)
return boolean
  l_id pls_integer;
  e_invalid_json exception;
  pragma exception_init (e_invalid_json,-2290);
  pragma autonomous_transaction;
	insert into json_validator (json) values (l_json);
	return true;
	when e_invalid_json then
	  return false;

set serveroutput on
  l_json  clob;
  l_return boolean;
  l_json := '
				{"widget": {
					"debug": "on",
					"window": {
						"title": "sample konfabulator widget",
						"name": "main_window",
						"width": 500,
						"height": 500
					"image": { 
						"src": "images/sun.png",
						"name": "sun1",
						"hoffset": 250,
						"voffset": 250,
						"alignment": "center"
					"text": {
						"data": "click here",
						"size": 36,
						"style": "bold",
						"name": "text1",
						"hoffset": 250,
						"voffset": 100,
						"alignment": "center",
						"onmouseup": "sun1.opacity = (sun1.opacity / 100) * 90;"

  l_return := validate_json(l_json => l_json );

  if l_return then 
	dbms_output.put_line('JSON is valid');
	dbms_output.put_line('JSON is NOT valid');
  end if;


How to use Font Awesome Animation Library in Applications

April 11, 2017 at 6:10 pm | Posted in Oracle Developement | Leave a comment

A very nice blog post about using Fontawesome animations by :



« Previous PageNext Page »

Entries and comments feeds.