Use Oracle Spatial to locate the dinosaurs

In a previous post, we used Oracle Spatial to generate a random polygon we can use as a home for our dinosaurs.  Now, let’s have the dinosaurs using DinoDate tell us where they are so we can update the system.

Our goal is to create a function that accepts a valid polygon (the island) and returns a point at a random location inside that polygon.  We’ll call it generate_random_location.  We’ll also add in a bunch of variables we’re going to need.

create or replace function generate_random_location(p_island in sdo_geometry)
 return sdo_geometry
 v_center sdo_geometry;
 v_radius number;
 new_radian number;
 new_point sdo_geometry;
 v_lon number;
 v_lat number;
 v_x number;
 v_y number;
 new_line sdo_geometry;
 final_point sdo_geometry;
 v_length number;

As we discussed in the previous post, every so often the island the dinosaurs live on randomly changes shape and possibly location. When this happens, the dinosaurs all group up at the center of the island until it’s reformed then they move out at random from there.  (At least that’s the best “explanation” I can come up with to avoid a discussion of “true randomness” in the distribution of points.)

We’re going to use some of the same Oracle Spatial functions we used in the previous post to generate a point (x) kilometers from the island’s center in a random direction and we’ll take a look at some new functions.

In the sdo_geom package, there are some functions we can use to determine properties of the minimum bounding circle (MBC) for our polygon.  This is basically the smallest circle that our island will fit in within a given tolerance.  We’ll need to figure out the coordinates for the center of the island and its maximum radius.

v_center := sdo_geom.sdo_mbc_center(p_island, 0.005);
v_radius := sdo_geom.sdo_mbc_radius(p_island, 0.005) * 1.1;

We’ll add a little to the radius just to make sure we have a distance slightly larger than the island and we’ll get the X, Y coordinates for the center.

 /* get the island center's x y */
 select t.x, t.y into v_lon, v_lat
 from table(sdo_util.getvertices(v_center)) t;

Let’s create a random radian, generate a point in that direction at a distance of our v_radius we calculated above and get it’s X, Y coordinates.

/* random radian from 0 to 2pi */
 new_radian := dbms_random.value(0,asin(1)*4);
 /* generate a point on the new radian outside of the polygon */
 new_point := sdo_util.point_at_bearing(
 sdo_point_type(v_lon, v_lat, null), null, null),

/* get the new point's x y */
 select t.x, t.y into v_x, v_y
 from table(sdo_util.getvertices(new_point)) t;

We’re going to use this new point and the center of the island to create a line.  Then, using another function from the sdo_geom package, sdo_intersection,  we can create a line that is just the intersection of the new line and our island.  The length of this line tells us how far it is from the center of the island to the edge in the given direction.

 /* create a line from the center to the new point
 get the segment that is contained inside the polygon
 new_line := sdo_geom.sdo_intersection(
 sdo_geometry (2002, 4326, null,
 sdo_elem_info_array (1,2,1),
 sdo_ordinate_array (v_lon, v_lat,v_x, v_y)
 /* get the length of the new line */
 v_length := round(sdo_geom.sdo_length(new_line, 0.05));

Finally, we generate a new point along our new_radian at a random distance from 0 (center of the island) to v_length (edge of the island) and return that point.

 using the length of the new line,
 generate a point on the current radian with a random distance from 0 to the lenght of the line
 final_point := sdo_util.point_at_bearing(
 sdo_geometry(2001, 4326,
 sdo_point_type(v_lon, v_lat, null), null, null),

return final_point;

You can find the full formatted code in this gist.

Now let’s figure out where those dinosaurs are.

Before we get started, let’s clear out any pre-existing data.

update dd_members
 set location = null
 where location is not null;

delete from dd_locations
 where location_name = 'dino-island';


We’re going to need a couple variables to hold a new island and a list of dinosaurs.

 v_dino_island sdo_geometry;
 type t_member_location is record (member_id dd_members.member_id%type,
 new_location sdo_geometry);
 type t_member_ids is table of t_member_location
 index by pls_integer;
 l_member_ids t_member_ids;


Using the function from the previous post, we can create a new island at the center of the Bermuda Triangle, and insert it into the location table.

v_dino_island := dd.generate_polygon_rad(100000, 50, p_lat => 26.846786, p_lon => -69.322920);

insert into dd_locations (location_name, geometry)
values ('dino-island', v_dino_island);

For reasons explained here, we’re going to use a bulk collect followed by a forall update instead of a simple update statement, to populate the dinosaurs locations.

Finally, we can print out the number of changed rows and commit.

select member_id,
 bulk collect into l_member_ids
 from dd_members
 fetch next 1000 rows only;

forall indx in 1 .. l_member_ids.count
 update dd_members_t
 set location = l_member_ids (indx).new_location
 where member_id = l_member_ids (indx).member_id;


You can find the full formatted code in this gist.

Using Oracle SQL Developer, we can see our island in the Map View.

select geometry dinoisland
 from dd_locations
 where location_name = 'dino-island';

Screenshot from 2016-07-26 14-48-31

We’ll put a dot up for each dinosaur on the island.

select location
 from dd_members
 where location is not null;

Screenshot from 2016-07-26 14-50-35

Notice the points tend to cluster in the center and spread out from there.  That’s due to always using the center point as one end of our random calculation.

Please experiment with other options and share in the comments below.

Leave your comfort zone behind: head to an open conference

If you are a(n) [insert language here] programmer, you probably attend a(n) [insert language here] conference when you’re able.  That’s great, since conferences are an awesome way to improve your skills in [insert language here] .

However, I also recommend that you try to attend an “open (source, hardware, data, ….)” conference if and when you can.  The broader the scope, the better.  Odds are, there will still be something related to your [insert language here] skills that you will find useful (and can use as justification to attend, if need be) but the real value comes from semi-random association with new stuff. Stuff you’ve never seen before, maybe never thought about before.

Recently, I attended OpenWest and of course there were plenty of sessions on technologies I use regularly (NodeJS, Python, Raspberry Pi,  Docker,  Privacy/Security,  and a ton of stuff I don’t know how to do …. yet).  I attended a few of these and learned some new tricks.  I also attended some sessions that were out of my comfort zone.  I learned a bit about Bitcoin and Block Chain, tried to keep up with some Linux server discussions, was completely out of my league listening to a session on Recent Advances in Microcontrollers and, after struggling through some basic “hacker” challenges, I got to dust off my soldering skills and make a cool LED badge.

Why does this matter?  Diversity is the key to sustained growth.  This famously applies to your financial investments, but it’s also important to your growth as a technologist.  If you continue to do the same/similar thing over and over, with the same technology, you will undoubtedly get really good at it.  The potential downside, however, is that you may be perceived as that person who only has a hammer, so everything looks like a nail.

If you enjoy being the go-to person when there’s a problem to be solved in technology X and no expert in X in sight, you need to keep adding to your bag of tricks. You need to keep challenging yourself.

This doesn’t mean that you have to have all of the answers. Sometimes it’s enough to know that a better solution exists “out there” somewhere, and help get the team pointed in the right direction.

Before going to OpenWest, I had experimented with my Raspberry Pi, but only in software ways like OSMC and OpenHab.  I do have an Arduino and I’ve read through a few projects that look fun, but I’ve hesitated to get started, assuming that it’s going to take too much time to ramp up my skills and I’ll probably burn it up unless I practice my soldering skills…. a lot.

Now, I’ve learned that the hardware components are more rugged than I assumed and I’m not going to burn them up with a minor solder error and once again I’m experiencing with how much fun it is to be the “new kid” learning cool, new things.

It is definitely time to go buy a good soldering iron.

Draw a maple leaf using Oracle Spatial

Natalka asked if I can make a maple leaf with Oracle Spatial.

The most basic way to draw any polygon with Oracle Spatial is to”

  1. Plot the shape on a graph.
  2. Add the grid coordinates to a polygon SDO_GEOMETRY object. Remember to go in a counter-clockwise direction.

For a maple leaf, I searched for a bit and found a very detailed grid mapping of a maple leaf.

The points in that doc are ordered in a clockwise direction, so I reversed the order and added them to an SDO_ORDINATE_ARRAY which generates the following.


It’s a nice leaf but not quite Canadian enough.

I was unable to find a complete mapping of a leaf closer to the Canadian flag, so I found several examples that were all similar but incomplete, and did my best to guess at the missing data.

I started at the top point and worked my way counter-clockwise around the leaf points to the bottom left stem point.

Since I centered it on the 0 x axis I simply removed the negative from the x ordinates, reversed the order of the coordinates and added them to the SDO_ORDINATE_ARRAY.  Notice the start and end points are the same to close the polygon.

 0,7)) MapleLeaf
from dual;

After checking that the object is valid using .st_isvalid() I generated this leaf.


Use Oracle Spatial to create a home for the dinosaurs

In the previous post, we explored creating basic geometrical shapes with Oracle Spatial with the intention of creating an island that the dinosaurs using DinoDate can live on.

One of the main reasons our dinosaurs have stayed hidden for so long is the island they live on constantly changes shape.  Let’s build on the function we created in the previous post and add a little randomness to it.

First, let’s change the parameters of the function.

create or replace function generate_shape(
 p_lon in number default 0,
 p_lat in number default 0,
 p_sides in number default 1,
 p_radius in number default 0,
 p_start_radian in number default 0)
 return sdo_geometry

We’re going to have a random number of ‘sides’ so we can get rid of p_sides and since it will be a random shape we don’t need p_start_radian.

To control the how ‘spiky’ the island is, we’ll add a parameter that accepts a variance percent.  We’ll use this as a percentage of the given minimum radius and calculate an upper limit for the radius.

Let’s add a constant for the upper limit of our island radius and a variable to track our current radian.

create or replace function generate_polygon_rad(
 p_min_radius in number,
 p_variance_pct in number,
 p_lon in number default 0,
 p_lat in number default 0)
 return sdo_geometry
 c_upper_limit constant number := p_min_radius + p_min_radius*(p_variance_pct/100);
 v_cur_radian number := 0;

We’re only generating a polygon, this time, so we’ll extract the loop and drop most of the rest of the code.

for i in 1..p_sides loop
 select t.x, t.y
 into v_x, v_y
 from table(sdo_util.getvertices(
 sdo_point_type(p_lon, p_lat, null), null, null),
 p_radius))) t;
 /* add x and y value of new point */
 v_ordinate_array_points(v_ordinate_array_points.count-1) := v_x;
 v_ordinate_array_points(v_ordinate_array_points.count) := v_y;

 /* It's a line, we only need the start and end points*/ 
 exit when p_sides < 3;

/* decrement the current radian by c_radian_increment
 so we move in a counter clockwise direction */
 v_cur_radian := v_cur_radian - c_radian_increment;
 end loop;

We still want to make a complete polygon but we don’t know how many sides we’ll be randomly creating.  Let’s change the for loop to a while loop and we’ll loop until we exceed 2pi.

Our first point will start with a 0 radian.  Remember we consider a 0 radian to be pointing in the direction of 12 o’clock.

Instead of subtracting radians we’ll convert our radian to a negative value when we generate the point.  Either way is fine, so we’ll use this method this time to demonstrate both options.

This time, we’ll generate a radius with a random length from our p_min_radius to the c_upper_limit.

Finally, we’ll generate a new radian from .1 to .5 radians and increase the v_cur_radian.

The loop will continue until our v_cur_radian is > 2pi (asin(1)*4).

 Each itteration will add a point at a random distance from the center point
 on the current radian.
 asin(1)*4 = 2pi
 while v_cur_radian < asin(1)*4 loop
 select t.x, t.y
 into v_x, v_y
 from table(sdo_util.getvertices(
 sdo_point_type(p_lon, p_lat, null), null, null),
 Since sdo_util.point_at_bearing increments radian in a clockwise direction
 and a valid polygon must be defined in a counter-clockwise direction
 we'll change our v_cur_radian to a negative value.
 v_cur_radian * -1,
 round(dbms_random.value(p_min_radius,c_upper_limit))))) t;

/* add x and y value of new point */
 v_ordinate_array_points(v_ordinate_array_points.count-1) := v_x;
 v_ordinate_array_points(v_ordinate_array_points.count) := v_y;

/* increment the current radian by .1 to .5 radians */
 v_cur_radian := v_cur_radian + round(dbms_random.value(.1,.5),2);
 end loop;

When the loop is complete we’ll copy the starting point to the end point position to close our polygon and return a new island for our dinosaurs to live on.

Now we’ll use the new function to generate a randomly shaped polygon with a radius from 1000 to 1200 meters and we’ll put it at the center of the Bermuda Triangle.

select dd.generate_polygon_rad(p_min_radius => 1000, p_variance_pct => 20, p_lon => -69.322920, p_lat => 26.846786) from dual;


You can find the full (nicely formatted) function in this gist.

Next time we’ll add some dinosaurs to our new island.

Making Polygons with Oracle Spatial

Lately, I’ve been digging into Oracle Spatial.  It’s a lot of fun, but wow, there’s a lot to learn.

I’m working on a series of tutorials that will walk through the process of building an island for our DinoDate dinosaurs to live on.  But in the meantime, I thought we could have some fun and look at a function to generate simple shapes.

For a detailed explanation of the SDO_GEOMETRY object, you can check out the docs, or watch for my upcoming tutorial.  In this post, I’ll just add brief comments to explain the parameters.

I realize the code formatting isn’t the best on this post, so I’ve created a gist with the finished function.

create or replace function generate_shape(
 p_lon in number default 0,
 p_lat in number default 0,
 p_sides in number default 1,
 p_radius in number default 0,
 p_start_radian in number default 0)
 return sdo_geometry
 v_generated_shape sdo_geometry;

Our function accepts a latitude and longitude we’ll use to position our shape, the number of sides, a radius and a starting radian.

A Point

Accepting the defaults gives us a 1 sided object with a 0 radius, sounds like a point to me.  In fact, let’s make anything with a radius of 0 a point.

if p_radius = 0 then
 v_generated_shape := SDO_GEOMETRY(2001,
        MDSYS.SDO_POINT_TYPE(p_lon, p_lat,NULL),
  • 2001, –2 dimensions, 0 LRS, 01 is a point
  • 4326, –SRID for Earth lat/lon system
  • MDSYS.SDO_POINT_TYPE(p_lon, p_lat,NULL), –point located p_lon, p_lat
  • NULL, –not used for a point
  • NULL) –not used for a point;

Generating Shapes

When we generate the rest of our shapes, we will:

  1. Start with the given point at p_lon / p_lat.
  2. Go a distance of p_radius in meters.
  3. In a direction of p_start_radian.
  4. Get the lat / lon position of the new point.
  5. Decrement our current radian by (2pi / sides. ) 2pi = (asin(1)*4)
  6. Repeat for each corner.

If you’re like me and it’s been so long since you took a trig class that you’re a bit fuzzy on what a radian is here’s an excellent definition.  The example in that post shows a 0 radian starting in the 3 o’clock position and moving counter clockwise.  use a 0 radian at the 12 o’clock position and move clockwise.

Generating Corner Points

The function we’re using to generate corner points is sdo_util.point_at_bearing.  It considers 0 radians to be at the 12 o’clock position and moves clockwise.


  • sdo_geometry point object
  • radian from 0 to 2pi
  • radius in meters

Once we have the new point, we’ll get the x / y coordinates of the point using sdo_util.getvertices and we’ll add those values to an sdo_ordinate_array.

The sdo_ordinate_array defines the corner point for our shapes (or end points for a line) and the sdo_elem_info_array acts as a key used to define how we use those coordinates.

Once again, if you want to dig into the details look at the docs, but here I’ll add short definitions.

Let’s add some new variables to work with.

v_cur_radian number := p_start_radian;
c_radian_increment CONSTANT number := (asin(1)*4)/p_sides;
v_x number;
v_y number;
 /* array of points used to construct the polygon */
 v_ordinate_array_points sdo_ordinate_array := sdo_ordinate_array();
 each itteration will add a point at a distance of p_radius from the center point on the current radian.
 asin(1)*4 = 2pi
 for i in 1..p_sides loop
   select t.x, t.y
     into v_x, v_y
     from table(sdo_util.getvertices(
                           sdo_point_type(p_lon, p_lat, null), null, null),
         ) t;
 /* add x and y value of new point */
 v_ordinate_array_points(v_ordinate_array_points.count-1) := v_x;
 v_ordinate_array_points(v_ordinate_array_points.count) := v_y;

 /* It's a line, we only need the start and end points*/ 
 exit when p_sides < 3;

 /* decrement the current radian by c_radian_increment
 so we move in a counter clockwise direction */
 v_cur_radian := v_cur_radian - c_radian_increment;
 end loop;
A Line

A 1 sided object with a radius > 0 could be a line or a circle.  I’ve chosen to use an SRID of 4326 which is a lat/lon earth based system and is not a geodetic coordinate system, so circles are a bit more complex than I want to cover in this post.  So, a line it is and we’ll include 2 sided objects as lines also just to keep it simple.

I know, I used a bunch of fancy words and didn’t explain them.  I’m trying to keep this post simple, but if you’d like to dig into all of the details, take a look at the docs.

  /* It's a line */ 
if p_sides < 3 then
  v_generated_shape := SDO_GEOMETRY(2002,
                SDO_ORDINATE_ARRAY(p_lon, p_lat,
                     v_ordinate_array_points(1), v_ordinate_array_points(2)));
  • 2002, –2 dimensions, 0 LRS, 02 is a line
  • 4326, –SRID for Earth lat/lon system
  • NULL, — only used for a point
  • SDO_ELEM_INFO_ARRAY(1, –start with 1st ordinate
    • 2, –a straight line
    • 1), –a simple straight line
  • SDO_ORDINATE_ARRAY(p_lon, p_lat, –start coordinates
  • v_ordinate_array_points(1), v_ordinate_array_points(2))); –end coordinates

For a line, we use the p_lon, p_lat parameters as our first point and our generated values as our second point.

Polygons With 3 or More Sides

For our polygons, we use the p_lon, p_lat parameters as a center point and generate our corner points around the center.

When you define a simple polygon the exterior edge points need to be defined in a counter-clockwise direction.  If you create more complex polygons, such as a polygon with holes, the interior polygons are defined in a clockwise direct.  Check out the docks if you want to experiment with more complex objects.

When we define a closed polygon, our sdo_ordinate_array will contain a set of points for each corner.  In order to close our polygon, we add an extra point at the end that is the same as the start point.

  /* It's a polygon */
 /* add x and y value of first point as the last point of the polygon to close it */
 v_ordinate_array_points(v_ordinate_array_points.count-1) := v_ordinate_array_points(1);
 v_ordinate_array_points(v_ordinate_array_points.count) := v_ordinate_array_points(2);

 v_generated_shape := sdo_geometry(2003,
 end if;
  • 2003,  — 2-dimensional polygon
  • 4326, –SRID for Earth lat/lon system
  • NULL, — only used for a point
  • SDO_ELEM_INFO_ARRAY(1, –start with 1st ordinate
    • 1003, — simple polygon
    • 1), — simple polygon
  • v_ordinate_array_points –coordinate array

Let’s make some shapes

First, we’ll make a point at the center of the Bermuda Triangle.

select generate_shape(p_lon => -69.322920,
                      p_lat => 26.846786)
  from dual;

Using the SQL Developer Map View we can see our point.


Now let’s add a 100-meter line at 0 radians.

select generate_shape(p_lon => -69.322920,
                      p_lat => 26.846786,
                      p_sides => 1,
                      p_radius => 100)
  from dual;


We’ll move the starting radian to -.75 * pi.  We’ll also set p_sides to 2 since we treat 1 and 2 as a line.

select generate_shape(p_lon => -69.322920,
                      p_lat => 26.846786,
                      p_sides => 2,
                      p_radius => 100,
                      p_start_radian => (asin(1)*4) * -.75)
  from dual;


From here on, we’ll leave this point and line on the view for reference and we’ll just increment the p_sides parameter.

p_sides => 3


p_sides => 4


p_sides => 5


p_sides => 6

p_sides => 7


p_sides => 8


p_sides => 9


p_sides => 10


I think that’s enough examples.  I hope you’ve found this useful or at least a little fun.

Feel free to leave questions or suggestions in the comments.


SQL Developer – Oracle Database connection through an SSH tunnel

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

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

ssh -i ~/.ssh/myKey -f oracle@ -L 1521:

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

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

Use Tools

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

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


Yes, I often complain before looking for a solution.

Add an SSH Host

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

Right click SSH Host then click New SSH Host


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

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


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



Add a Connection

Create a new connection just like normal.

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


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

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

Getting DinoDate Up and Running

You’ve downloaded DinoDate, the premier dating website for dinosaurs. Now lets get it up and running!


  • Download Logger and extract it to dino-date/coreDatabase
  • Check Logger’s create_user.sql file. If you see an exit statement at the very end , remove it.
  • Install Bower.
  • Install Oracle Instant Client with the sdk modules.


Database Schemas and Objects
IMPORTANT – Please make sure you’re using a database instance in which you can safely create  schemas named DD, DD_NON_EBR and DD_LOGGER.
  1. Navigate to dino-date/coreDatabase
  2. Run dd_master_install.sql from an account connected “as sysdba”.
  3. First prompt is for the directory into which you extracted Logger, e.g. Logger_3.1.1
  4. Second prompt is what you want to name the schema that holds the Logger database objects.
  5. Third and fourth prompts are for your Tablespace and Temporary Tablespace.
  6. Fourth prompt is for the logger schema password
Common Client

Open a prompt and navigate to dino-date/commonClient

bower install
RESTful Tier

Currently there are RESTful APIs written in both NodeJS and Python.  You can choose to run one or both.


Open a prompt and navigate to dino-date/nodejs

npm install

Open a prompt and navigate to dino-date/python

pip install cx_Oracle
pip install bottle

Configure your Environment

DinoDate uses environment variables for database connection and port settings.

Create the following environment variables using the correct values for your system.

  • dd_connectString=localhost:1521/orcl
  • dd_user=dd
  • dd_password=dd
  • dd_port=8888
  • dd_python_port=8080
  • dd_node_port=3000

Run DinoDate


Open a prompt and navigate to dino-date/nodejs

node server.js

DinoDate (NodeJS) will be listening on the dd_node_port port you defined above.


Open a prompt and navigate to dino-date/python


DinoDate (Python) will be listening on the dd_python_port port you defined above.

Running Both

You can run both versions at the same time, provided you specified different ports.

This will allow you to switch between languages by changing the ports and view the code examples specifically for each language.

Please leave a comment if you run into trouble.

DinoDate – a demonstration platform for Oracle Database

I’ve heard people talk about how “SQL databases are dinosaurs.”  I’m sure they don’t mean it as a compliment, but if you think about it, well, why wouldn’t it be?


Dinosaurs were the dominant terrestrial vertebrates for 135 million years.  It took a giant asteroid to stop them from ruling the earth forever (or for a few million more years, anyway).

Here at Oracle, we admire dinosaurs so much, we launched an expedition to see if we could find any.  And what do you know? We found a remote island that doesn’t appear on any maps….populated entirely with dinosaurs!

Who would have thought?

Instead of building an amusement park on the island, we decided to stealthily observe the dinosaurs, so we can better understand them. Here’s what we’ve learned so far:

  • Dinosaurs are busy: they spend a lot of time searching for food and trying to avoid becoming food.
  • Dinosaurs are very shy: that’s why they have gone undetected for so long.

Unfortunately, being busy and shy is a bad combination when it comes to finding a spouse and propagating the species. So the Oracle Developer Advocates team decide to help them out.

Just last week, I flew to this secret, hidden island and presented to our unsuspecting users, for the first time ever, DinoDate: the premier online dating service for dinosaurs.

OK, so, not really. We didn’t really find an island of dinosaurs. We aren’t really going to be able to help dinosaurs find their perfect match.

But we hope you agree with us that DinoDate is more entertaining than another human resources app built on to top of the employees and departments tables.

DinoDate as a Teaching Platform

Oracle Database is packed full of features that can be of great assistance to application developers, whether building those applications in Oracle Application Express, JavaScript, Python or any number of other scripting languages.

Such features include SQL itself, PL/SQL, Oracle Spatial, Advanced Queueing, Oracle Text and more.

Yet many developers are not aware of these features or greatly underutilize them in their application development.

We believe that the results of this underutilization are applications that are not as fast as they could be, not as secure as they could be, and not as easy to maintain as they could be.

So we want to help application developers learn about – and learn how to use – all these great features. And DinoDate will be one of our delivery vehicles for this learning process.

DinoDate will provide examples of advanced Oracle Database features such as spatial queries, advanced queuing and text searches.  We will continue to add new examples and features, such as analytical functions, over time. And we will not only use these features, but show you how and why we are using them.


DinoDate Architecture: diversity is key to survival

We’ve designed DinoDate so that we can show how Oracle Database appdev features can be leveraged from  many different programming languages, starting with Python, NodeJS and PHP and adding others later.  This will benefit those who want to learn a new language by allowing them to see how the feature works in each language.

Each feature will be implemented in each language while attempting to follow recommended practices.  Since the database interface takes place at the server application level, each language specific application will provide a restful interface instead of creating views.

AngularJS is used to provide a common shared front end.  If we add a language that is unable to support the restful interface we’ll create custom views.

At the time of this post, we have the AngularJS and Python pieces in place, parts of NodeJS working and the legacy PHP code included.  The plan is to finish with NodeJS then refactor PHP.

After that we have a few things in mind, but the main plan is to choose the next stack based on your interest.  Please leave comments about where you’d like us to go next.

I’m sure there are a lot of people much better at each of these languages than we are, so if you see something we did incorrectly let us know and we’ll get the changes incorporated.

Please download the project and after you get it up and running, feel free to add comments below for any improvements we can make.

This has been a fun project so far, and I’m just getting started. I look forward to learning more – from the technology and from you – as we expand DinoDate.

When a problem turns into cool code

The goal

Generate some random data for an application.  The value we’re focusing on here, is a short “about me” paragraph.

What we have

  • Around 40 pre-populated rows.
  • Free reign to do as we want.

First solution

Create a collection of unique words from existing records by splitting the existing about me strings on spaces, removing special characters and building a unique list of words using an associative array.

FUNCTION build_word_list RETURN nt_type IS
  TYPE aa_type IS TABLE OF VARCHAR2(500)
  words_aa aa_type;
  word_list nt_type;
  l_idx VARCHAR2(200);
  c_special_chars CONSTANT VARCHAR2(50) := ';:*~!#$%^()_-+=&#1234567890}]{{/?.,><'||CHR(9)||CHR(10)||CHR(13);
  FOR about_list IN
    (SELECT dbms_lob.substr(about_yourself) about
     FROM dd_members
     WHERE member_id < 41)
    FOR word_list IN
     (SELECT regexp_substr(about_list.about,'[^ ]+', 1, level) word
      FROM dual
      CONNECT BY regexp_substr(about_list.about, '[^ ]+', 1, level) IS NOT NULL)
              'I')) := 1;
      --remove non alphabet characters except '
  l_idx := words_aa.first;

    word_list(word_list.count + 1) := l_idx;
    l_idx :=;

  RETURN word_list;
END build_word_list;

This gets run once when the package initializes.  After that the word_list is retained in memory.

As each new record is created we generate the about value as a random number of random words from the word list.

 new_about CLOB;
 num_words NUMBER;
 num_words := dbms_random.value(10, 101);
 --About will have between 10 and 100 words
 FOR indx IN 1..num_words LOOP
  new_about := new_about ||
   word_list(dbms_random.value(1, word_list.count)) ||
   ' ';
 RETURN rtrim(new_about);
END generate_about;

The problem

Even though the words are real, the values are gibberish.

We decided to create a table of “generic” sentences and build the new ‘about me’ value by randomly selecting sentences.

With this change, we can drop the build_word_list function and just do a simple bulk collect from the sentences table.  Then we can change generate about to use the sentences list instead of a word list.

SELECT sentence_text
 BULK COLLECT INTO about_sentences_list
 FROM dd_sentences;
 new_about CLOB;
 num_sentences NUMBER;
 num_sentences := dbms_random.value(3, 11);
  --About will have between 3 and 10 sentences 
 FOR indx IN 1..num_sentences LOOP
 new_about := new_about ||
   about_sentence_list(dbms_random.value(1, num_sentences.count)) ||
   ' ';
 RETURN rtrim(new_about);
END generate_about;

This works fine except, now that we have a much sorter list to choose from, we wind up with a lot of duplicate sentences.  We’d like to only have unique sentences.


Associative Array
  • Create an associative array to keep track of the the used indexes.
  • Generate the index number of the sentence to use separately.
  • If the new index number is already used regenerate the index.

The risk here is a long running process if we repeatedly hit the same random number.  It’s a low risk, but it’s there.

Copy the Collection and delete used items

Make a copy of the master collection and collection.delete(x) as the sentences are used.

Of course the problem is, deleting an item doesn’t collapse the collection and we’ll get an exception if we hit an empty index.  There are several ways to get around this, but now our code is getting long.

Copy the Collection using the Table() function

If we make a copy of the master collection by selecting from it with the Table() function, we can make a collection that is randomized.  Also, if we use FETCH NEXT we can just get the number of rows needed.

FUNCTION generate_about
 new_about CLOB;
 num_sentences NUMBER;
 unused_sentences_list about_sentences_type;
 random_sentence_index NUMBER;
 l_idx NUMBER;
 /* Will have between 3 and 10 sentences */
 num_sentences := floor(DBMS_RANDOM.VALUE (3, 11));
 /* copy the chosen number of rows at random from the sentences list */
     FROM TABLE( about_sentences_list )
     FETCH NEXT num_sentences ROWS ONLY
   ) AS about_sentences_type)
 INTO unused_sentences_list
 FROM dual;
 l_idx:= unused_sentences_list.FIRST;
   new_about := new_about ||
      unused_sentences_list(l_idx) ||
      ' ';
   l_idx:= unused_sentences_list.NEXT(l_idx);
 RETURN RTRIM (new_about);
END generate_about;

Cool, but…

We can do better

Let’s get rid of the CAST

 BULK COLLECT INTO unused_sentences_list
 FROM TABLE (about_sentences_list)
 FETCH NEXT num_sentences ROWS ONLY;

Now, let’s use an analytic function and get rid of the new collection and the loop that goes with it.

FUNCTION generate_about
 new_about CLOB;
 num_sentences NUMBER;

 /* Will have between 3 and 10 sentences */
 num_sentences := floor(DBMS_RANDOM.VALUE (3, 11));
 SELECT LISTAGG(column_value, ' ')
 INTO new_about
 FROM (SELECT column_value
       FROM TABLE (about_sentences_list)
       FETCH NEXT num_sentences ROWS ONLY);

 RETURN new_about;
END generate_about;

Note, that we used a nested SELECT statement.  This allows us to still use FETCH NEXT to limit the number of sentences we send to LISTAGG.

We could have selected directly from the sentences table, but that would mean an extra trip back to the database which may add up when generating 10,000 rows.

In the end

We added a small table of sentences, removed an extra function and generated semi-coherent sounding profiles.

And we look cool doing it.


Use Open Source to hone programming skills

I’ve been writing software for many years. And I’ve realized lately that the more I engaged with (wrote in, integrated with, etc.) open source technologies, the better the code I write gets. Which got me wondering: correlation or causation?

Reading code makes you better

I learned early on in my programming career that the more code I read, the better my code became.  I learned that when I had to maintain other people’s code, simple and clean almost always beat fancy/complex code – even if there were comments.  On the other hand, when I took enough time to understand the complex code, I usually learned new tricks.  Either way, I improved.

This led me to push for code reviews in shops where we weren’t doing them.  And when there ‘wasn’t enough time’ to do code reviews officially, I would browse the repositories and read the code on my own.  Of course, back then I was limited to the company source from small teams.

Going beyond the syntax

While you inevitable had to wrestle with the syntax of any programming language, that’s the most trivial aspect of learning how to fully leverage that language.  The syntax of a language tends to be pretty static, and if you get it wrong your compiler will complain.  Deeper lessons involve what kind of problems a language is best suite to solve (“right tool for the right job”), and how best to write code in that language so it is efficient and maintainable.

There are many ways to pick up a new language; courses, tutorials, mentors, books and more.  I often use combinations of these options when I learn a new language.  One thing I noticed is they are frequently very similar when it comes to the non-syntax elements.  Obviously following recommended practices.

When you read real deployed code from other people you get something more.  You learn patterns and practices beyond what’s in normal structured learning.  The “correct” way to do something in a language is not always what works best.  You see the edge cases, one offs and unexpected integrations.  You will see solutions to these issues, both good and bad, but if you really think about it, this is where “recommended practices” are born.  Today’s pattern is tomorrows anti-pattern.

You may have strong feelings about things such as ‘always comment’, ‘commas go at the end’, ‘indent x spaces’ and of course you’re ‘right.’  I’ve had very strong feelings about those and other aspects of coding.

As I read other peoples code sometimes I would get angry that ‘they were doing it wrong.’ As I read more, I started to understand that there were situations common in other peoples code that I had not encountered in mine and my way could be unnecessarily more difficult.   I not only changed some of my opinions, but I am learning to be more flexible.

Open Source is Everywhere

As the open source movement grew, so did the amount of code available to read and learn from.  With sites like Gitlab, GitHub and  BitBucket we can pull down fully functioning applications to not only read, but tinker with.  I rarely encounter something I want to learn that doesn’t have at least some open source code available to play with.

I remember starting out with a new language and stressing over simple things such as directory structure and naming conventions.  Now I go look at a few different open source projects and I can start to piece together common approaches.  I rarely stress over these types of things any more.

Nowadays there is so much code available, both good and bad.  When you’re learning, you don’t really know which is which.  Just keep reading and you’ll learn to tell the difference.  Reading ‘bad’ code helps you understand why it’s ‘bad.’  The key is not being afraid to try whatever you think looks correct, admit when you get something wrong, fix it and move on.

Bad Code is Bad, or is it?

Some would say “there is more ‘bad code’ out there than ‘good code.'” Here’s a sub-reddit dedicated to bad code.

I’ve written plenty of good and bad code over the years.  When I look at my older code I often wonder how I could have written such crap. This really means I’m still learning.  If I can look at my old code and think it looks great, it means I’m not growing.

So how can we learn from bad code?

The more bad code you read the better you will be at spotting it.

As you’re learning and searching for examples, you will find and use a lot of code that doesn’t quite work.  Remember, just because it doesn’t work for your situation, that doesn’t make it bad.  Learning how to make it work makes you better.

How do you know it’s bad?

People love to criticize. Read the comments, if you see a lot of ‘WTFs‘ you may be looking at bad code, figure out why it’s bad. Please don’t be one of those people who just leave a “this code sucks” comment.  Don’t assume you know all of the requirements for the bad code, there may be a valid reason for the way it’s written.  If you can see why it’s bad try to leave a constructive comment. Or…

Don’t leave it bad.

Put in a pull request that makes the code better.  Fix the syntax, use a better method, add comments or fix the indentation; these are all great ways to help.  Add a good explanation for why you are recommending a change.

I learn more when I help someone else learn than I do on my own.  If I think I understand a new topic I go looking for someone to explain it to, this makes it stick in my head and I quickly find out if I’m writing bad code.

Give Back

Remember open source works best when there is participation.  Code changes are welcome in most projects, but there are lots of ways to contribute.

Test open source code (you know you want to!) and file bug reports; help complete the documentation set; write tutorials and how-to examples; participate in the conversations – or simply help spread the word.  Everything makes a difference, and the more fingers in the pie, the better!


Here are a few links to help get you started.  Some I’ve worked with, some I haven’t yet had time to dive into. If you find something useful – or something that needs correction in this post, please don’t hesitate to share it.