Category Archives: PL/SQL

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

In most ways, PL/SQL development is just like working with any other language but, sometimes it can be a little different.  If you’d like to create a Continuous Deployment Pipeline for your PL/SQL application, here is a short list of tips to help you get started.

Work From Files

Do not use the Database as your source code repository.  If you are making changes to your application in a live database, stop right now and go read PL/SQL 101: Save your source code to files by Steven Feuerstein.

Now that you’re working from files, those files should be checked into…

Version Control

There are a lot of version control applications out there, the most popular right now is probably Git.  One advantage of using Git is, you work in your own local copy of the repository making frequent commits that only you see.  If you’re working in your own personal database you could compile your changes to the database at this point.

If you’re working in a database shared with other people and you’re ready to compile your code,   git pull from the central repository to get any changes since your last pull.  Handle any merge conflicts, then git push your changes back up to the shared repository.  After the push, you can compile your code to the database.  This helps ensure that people don’t overwrite each other’s changes.

Making frequent small commits will help keep everything running smoothly.

What about your schema objects?

Your schema objects, such as tables and views, are as much a part of your application as any other component and changes to these objects should be version controlled as well.  The process of keeping track of schema changes is called Schema Migration and there are open source tools you can use such as Flyway and Liquibase.

Unit Testing

In any application it’s smart to have good test coverage, If your goal is Continuous Deployment it’s critical.  A Unit Test is when you test a single unit of code, in PL/SQL that may be a function or procedure.

Unit tests typically follow these steps:

  1. Setup the environment to simulate how it will be when the application is deployed.  This might include changing configuration settings and loading test data.
  2. Execute the code unit.
  3. Validate the results.
  4. Clean up the environment, resetting it to the state it was in before running the tests.

utPLSQL is a great tool for unit testing your PL/SQL applications.  You will write a package of tests for each ‘unit’ of your application which should test all of the required functionality and potential errors.  utPLSQL is an active open source project with an impressive set of features.  Check out the docs to get started.


Building a database application usually consists of running the change scripts in a specific order.  It’s common to create a master script that executes the others in order of how the objects depend on each other.  However, if the master script simply executes the other scripts, you will need to create additional scripts to track and verify changes, and more scripts to give you the ability to rollback the changes if/when there’s a problem.

There are build tools such as Gradle and Maven that can easily execute your scripts.  But you’ll still need to create the additional control scripts.  If you use a Schema Migration tool it should include a lot of these additional functions without having to write extra scripts.  For an example, check out Dino Date which has a Liquibase migration included.

How to handle the PL/SQL code

You could include your PL/SQL code in a Schema Migration changeset but adding schema migration notation to your PL/SQL introduces another layer of complexity and potential errors.

In the Dino Date runOnChange directory, you will find examples of setting up Liquibase changesets that watch for changes in the files of objects that you would rather keep ‘pure’.  When you run a migration, if the file has changed Liquibase will run the new version.

In a shared database environment, you should execute a schema migration after you pull/merge/push your changes into your version control system.


All of these pieces can be tied together and automated with an automation server such as Hudson or Jenkins (both are open source) to create a build pipeline.

Original by Jez Humble

A simple (maybe too simple) build pipeline using the above tools could follow these steps:

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

Other Useful Tools

  • Edition Based Redefinition[pdf] can be used to deploy applications with little to no downtime.
  • Oracle Developer Cloud Service comes with a ton of pre-configured tools to help with almost every aspect of your development process.
  • Gitora can help you version control your application if you are not able to move out to files.

Getting started with Oracle Rest Data Services

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

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

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

A Short Tutorial

Setup a VM

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

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

SQL Developer

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

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

Connect to the HR schema

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

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

Test the connection and connect.

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

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

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

REST Data Services Wizard

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

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

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

For this tutorial, I’ll be using the…

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

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


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

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

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

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

New Module

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

To create a new module:

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

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

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

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

Click Next.

Template URI

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

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

Method Handlers

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

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

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

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

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

Click Next, review the summary and click Finish.

Get Query

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

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

Enter this query into the SQL Worksheet.

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


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

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

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

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

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

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

Enter this PL/SQL into the SQL Worksheet.

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

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


Click on the Parameters tab and enter the following values.

Colum definitions:

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

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

It’s time to….

Test the Service

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

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


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

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

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

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

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

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


In your REST testing tool:

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

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

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

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

Export SQL

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

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

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

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

In the window that pops up:

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

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

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

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



Getting Started With Oracle Spatial part 2


See the previous post for setup instructions.

Is the object valid?

If you don’t have a tool with Map View or if you have problems displaying the objects (for example not enough resources to display them all) you can use the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT() function to check if they are valid. Pass your object into the function with a tolerance. It will return TRUE or FALSE.

For example, I have used this function to validate the line exercise answer from the previous post.

Create a Polygon

We want to create a home for our dinosaurs so let’s look at some examples to create polygons using different methods.

Let’s make a simple rectangle using 2 points on opposite corners.

This creates a rectangle with one corner at Lon-0, Lat-0 and the opposite corner at Lon-0.9, Lat-0.9, approximately 100KM East and 100KM North.

Let’s make another simple rectangle using points for all corners.

Notice that when we create a simple exterior polygon we define the points in counter-clockwise order, also the first and last points are the same.

This could be an OK home for our dinosaurs, it’s 100 KM by 100 KM, but living on a rectangle could be kind of boring.

Let’s make it more interesting.

This time, we’ll create an irregular shaped polygon. This is basically the same as the last rectangle polygon but we’ll be using a few more points.


1. Create a rectangle using 2 points starting at the center of the Bermuda Triangle – Latitude 26.846786, Longitude -69.322920 and having its opposite corner approximately 100KM East and 100KM North. (0.9 is approximately 100KM)


2. Create the same rectangle but this time, using 4 points to define the corners.


3. Create an irregular polygon near the center of the Bermuda Triangle using at least 8 points.


Note: when creating this type of polygon (1, 1003, 1), be careful to ensure that none of the lines cross or the object will be invalid.  Also, as with lines, if your object is “off the map”, it will be invalid.

Experiment with other polygons using different point sets.

Populate Tables

Now it’s time to start adding some data for our dinosaurs and their island.  We’ll start by building populateData.sql.  Here’s a stub to get you started.

At the start of our script we are clearing any previous changes in order to maintain a clean set of data.

We’ll be storing our data in 2 tables. Our polygons that designate an area will be stored in the DD_LOCATIONS table and we’ll keep track of where our dinosaurs are in their member record using the  DD_MEMBERS.SPTUT_LOCATION column we created in the setup.

We will be storing sdo_geometry objects. These objects must be created using the same SRID (4326) we used to populate user_sdo_geom_metadata in the setup script. We will not be going into detail on the metadata and indexes, but if you receive an error mentioning SRID, it’s probably due to mismatching the SRID.


1. Insert the polygon we previously defined into the dd_locations table with a location_name of ‘dino-island’.

2. Update our admin dinosaur (member_id = 0) and set the location to a point inside of ‘dino-island’.

New populateData.sql

Random Polygon

Most of the time you’ll be creating pre-defined objects. But, one of the reasons our dinosaurs have been able to stay undetected for so long is their island randomly changes shape every now and then.

In previous exercises, we created our polygons one point at a time in a counter-clockwise direction so we should be able to do this programmatically with some random coordinates.

On the first attempt to create a random polygon, I went with an idea to walk the edges of a square and define points a random distance in or out from the edge. While this did work, the function was a bit more complicated than I wanted, involving nested loops and multi-layered if/then statements.

I decided to go with a circular approach instead.

Helper Functions:

Here are examples of a couple useful functions.


  • A standard SDO_GEOMETRY point object
  • A radian value from 0 to 2pi. The gif here explains what a radian is perfectly.
    Note: SDO_UTIL.POINT_AT_BEARING uses 12 o’clock as 0 radian and increases clockwise.
  • A distance in M.

The function returns an SDO_GEOMETRY point object, X meters from the given point in the direction of the radian value.

Since we are creating our polygon using an MDSYS.SDO_ORDINATE_ARRAY we need to extract the x/y coordinates from our new point and add them to our array. (Remember the first and last point in the array are the same.)

SDO_UTIL.GETVERTICES can be used to extract quite a lot of data from our point object, but since we’re only interested in the x / y coordinates we will treat the output as a table and select the values.


Create a function that accepts:

  • A minimum radius. We need to ensure that our dinosaurs have land to stand on.
  • A variance percent that we will use to calculate our random edge points.
  • A center point longitude.
  • A center point latitude.

The function should return a valid SDO_GEOMETRY simple polygon object.

Change populateData.sql to use the new function for the dino-island value, centered in the Bermuda Triangle.

New generate_polygon_rad.fnc

New populateData.sql

Random Point Inside a Given Polygon

Now that we have an island lets figure out where the rest of our dinosaurs are.

We want to make sure our dinosaurs are all on the island. We could take the easy way out and just use the POINT_AT_BEARING function with a random radian and distance from 0 to the minimum limit used to create the island, but that leaves a lot of unused space out by the beaches.

Helper Functions:

Here are examples of a couple useful functions.

SDO_GEOM.SDO_INTERSECTION accepts 2 SDO_GEOMETRY objects and returns just the overlapping section. Pass in your dino-island and a line starting at the center that is long enough to extend outside of the island. You will get back a line from the center that stops right at the edge of the island.

SDO_GEOM.SDO_LENGTH accepts an SDO_GEOMETRY object and returns different information depending on the object passed in. We’re passing in a line so it will return the length of that line.

You may notice that these two functions accept a Tolerance parameter. The tolerance value determines how close two adjacent points have to be together to be considered the same point. So passing a value of 0.05 means: Points which are closer together than 5 centimeters, are considered to be one point.


Create a function that accepts:

  • The island as an SDO_GEOMETRY simple polygon object.
  • The island’s minimum radius.
  • The island’s variance percent.
  • The island’s center point longitude.
  • The island’s center point latitude.

The function should return a valid SDO_GEOMETRY point object located inside the island polygon.

New generate_random_location.fnc

Change populateData.sql to use the new function to update the dinosaurs locations using the same values as the island.

New populateData.sql

Using this information and the methods you’ve used previously, you can generate random points around the island without having to worry about dropping any dinosaurs in the ocean.

Using this specific methodology will tend to favor placing dinosaurs in the center of the island since the location always starts from the center and goes out. Feel free to try other methods to create a more random dispersion.

Random Point Inside a Given Polygon using only the Polygon

The function generate_random_location works well enough to generate a point inside a polygon if you already know something about it.

Let’s change it so it can accept just the polygon and figure out the rest of the data we need.

We still want to make sure our dinosaurs are all on the island.

Helper Functions:

Here are examples of a couple useful functions.

SDO_GEOM.SDO_MBC_CENTER accepts an SDO_GEOMETRY object and a tolerance. The function calculates the Minimum Bounding Circle, which is the smallest circle that could contain the object. It then returns an SDO_GEOMETRY point object for the calculated center of the MBC.

SDO_GEOM.SDO_MBC_RADIUS accepts an SDO_GEOMETRY object and a tolerance. The function calculates the Minimum Bounding Circle, which is the smallest circle that could contain the object. It then returns the calculated radius of the MBC.


Change the generate_random_location function so it accepts:

  • The island as an SDO_GEOMETRY simple polygon object.


New generate_random_location.fnc

Change populateData.sql so it uses the new function to update the dinosaurs locations using only the island.

New populateData.sql

Find the Dinosaurs

Run populateData.sql to generate an island and update the dinosaur locations.

Now that we have an island and we’ve scattered the dinosaurs, let’s figure out where they are.

Helper Functions:

Here are examples of a couple useful functions.

SDO_WITHIN_DISTANCE accepts two SDO_GEOMETRY objects and a parameter string. The first SDO_GEOMETRY object must be in a table and have a spatial index. The parameter string is a set of parameters defined in the linked document, for our purposes we are using distance and unit. If the two SDO_GEOMETRY objects are within the given distance the function returns ‘TRUE’.

SDO_RELATE accepts two SDO_GEOMETRY objects and a mask parameter. The mask parameter specifies the relationship you are testing. See the Usage Notes section of the linked documentation for a list of valid options.

Alternatively, if your objects are not both in tables or you are trying to determine their relationship you could use SDO_GEOM.RELATE but it is less efficient since it does not leverage a Spatial Index.


  1. Write a query that finds all of the dinosaurs within 5 KM of the beach.
  2. Write a statement that creates a smaller polygon with a minimum radius of 10 KM and centered randomly inside the island. Using the location_name of ‘asteroid-impact’ insert this polygon into the dd_locations table.
  3. Write a query that finds the dinosaurs inside the ‘asteroid-impact’ so we can warn them to move to safety.
Answer 1
Answer 2
Answer 3


There are many more functions and capabilities included with Oracle Spatial, this tutorial has barely scratched the surface.

The ‘answers’ I have included are merely suggestions and there are many other ways to complete the same tasks. Please dig into the documentation and experiment.

Please leave comments if you have any questions, corrections or suggestions.

Getting Started With Oracle Spatial part 1

In this tutorial, we’ll go over how to create some basic spatial objects such as points, lines and polygons. We’ll create some random objects and run some fun queries.

We’ll be using the Spatial and Graph Developer’s Guide as our reference.

First, let’s take a look at the SDO_GEOMETRY Object.

SDO_GTYPE is a 4 digit number that defines what type of object we’re making, using a format of DLTT

D = Dimensions of the object 2, 3 or 4 (can not mix different dimensions in the same layer)

L = Linear Referencing System. This is a more advanced topic for another day. For our examples, we will be using 0.

TT = Type of Geometry, 00 through 09

We will be using the following Geometry Types. You can find the full list here.

Value Description
DL01 Point Geometry contains one point.
DL02 Line or Curve Geometry contains one line string that can contain straight or circular arc segments, or both. (LINE and CURVE are synonymous in this context.)
DL03 Polygon or Surface Geometry contains one polygon with or without holes,1 or one surface consisting of one or more polygons. In a three-dimensional polygon, all points must be on the same plane.

SDO_SRID is a number referencing the coordinate system used, it must either be NULL or the SRID value from the SDO_COORD_REF_SYS table.  We’ll be using 4326, which is the standard Earth-based Latitude / Longitude system.

SDO_POINT is used to define the object as a point. This is only used if this object is a point, otherwise, this should be NULL and the following two parameters will define the object.

SDO_ELEM_INFO is basically a set of numbers that work like a key for using the information in the SDO_ORDINATES parameter to create the object. These numbers are grouped into one or more sets of 3 numbers or triplets. If we are creating a complex object that has more than one segment, each segment will be defined by its own triplet.

  • The first number in the triplet is the SDO_STARTING_OFFSET and indicates which number in the following SDO_ORDINATE_ARRAY is the starting point for this segment. The first element is 1.
  • The second number is the SDO_ETYPE which is used to define the type of object we are creating in this segment. For example, 2 is a straight line and 1003 is a simple polygon.
  • The third number is the SDO_INTERPRETATION and is used in one of two ways.
    • For simple objects such as a polygon, the SDO_INTERPRETATION will further define the type of polygon. 1 is a simple straight line polygon, 2 uses circular arcs, 3 is a rectangle and 4 is a circle.
    • For complex objects such as a polygon with holes in it, SDO_INTERPRETATION is used to specify how many ordinates are used for this segment.

Please see Table 2-2 Values and Semantics in SDO_ELEM_INFO for further details.

SDO_ORDINATES are pairs of coordinates used along with SDO_ELEM_INFO to define the object


You’ll need a copy of the git repository.

  1. Go to
  2. Fork the repository.
  3. Clone the repository.
  4. Follow the DinoDate install instructions.
  5. Run the following as the new DD user.

This script will add the sptut_location column to our dd_members table, setup the spatial metadata and create the spatial indexes. We will not be covering these steps in the tutorial.


This script can also be used to reset the database if something goes wrong.

We’ll need some dinosaurs to locate so let’s randomly generate a few.

Connected as dd run the following command to generate 1,000 dinosaurs.


If you’d like to restore the DD schema to its default state, run this script to remove the new column and re-create the dd_locations index.

View the Objects

For my examples, I will be using Oracle SQL Developer which includes a Map View utility to display the objects.

Create a Point

Select the following example from dual to create a point.

When you run the query you’ll see it generates an SDO_GEOMETRY type object. In SQL Developer you can select the results, right click the object and “Invoke Map View on Results Set” to display the point in the Map Viewer.

Note that there is no background map displayed, just an empty background with a point. If there were a map, this point would be just west of Africa.


Create a point at the center of the Bermuda Triangle – Latitude 26.846786, Longitude -69.322920.

If you invoke the map view for both points, you can change the marker type for one to identify which is which. If your Bermuda Triangle point is South East of 0,0 you have your Lat and Lon backward. If this is a consistent issue, you can use named parameters.


Create a Line

Select the following example from dual to create a line.

This creates a line starting at Lon-0, Lat-0 and ending at Lon-0.9, Lat-0, approximately 100KM East.


Create a line starting at the center of the Bermuda Triangle – Latitude 26.846786, Longitude -69.322920 and going approximately 100KM North. (0.9 is approximately 100KM)


Experiment with other lines. Keep in mind if your object is “off the map”, it will be invalid.

In the next post, I’ll go over creating polygons and run some fun spatial queries.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

You can find the full formatted code in this gist.

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

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

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

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.

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.

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.

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

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

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.


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.

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.

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

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.

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

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

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


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


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.


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








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.


Getting DinoDate Up and Running



  • Download Logger and extract it to dino-date/coreDatabase
  • Install Bower.
  • Install Oracle Instant Client with the sdk modules.


Database Schemas and Objects
  1. Navigate to dino-date/coreDatabase
  6. Fourth prompt is for the logger schema password
Common Client

Open a prompt and navigate to dino-date/commonClient

RESTful Tier



Open a prompt and navigate to dino-date/nodejs


Open a prompt and navigate to dino-date/python

Configure your Environment

DinoDate uses environment variables for database connection and port settings.


  • 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



Open a prompt and navigate to dino-date/python


Running Both



Please leave a comment if you run into trouble.

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.

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.

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.

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.

Cool, but…

We can do better

Let’s get rid of the CAST

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

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.