Category Archives: PL/SQL

Execute PL/SQL calls with Python and cx_Oracle

After you’ve got the hang of performing Basic CRUD operations with cx_Oracle you’re ready to start tapping into some of the real power of the Oracle Database.

Why use PL/SQL?

Python is an excellent language for most things you want your application to do, but when you’re processing data it just goes faster if you do the work where the data is.

This post will cover how to execute Oracle PL/SQL functions and procedures using Python and cx_Oracle.  I’m assuming you’re already familiar with PL/SQL if not, you can get some help from Steven Feuerstein and Bryn Llewellyn.  (Additional resources at the end.)

Prerequisites

  • Python 3
  • Oracle Database version 12+
  • Basic Oracle PL/SQL and SQL knowledge.

Setup

If you’d like to follow along with the examples you’ll need to create the following objects in a database schema that is safe to experiment in.  Make sure you have permissions to create the following objects.

To keep everything clean, I’ll be putting my PL/SQL code into a package called pet_manager.

Cleanup

To clean up the database when you are finished with the series, you need to drop the two tables and the package.  Please make sure you’re connected to the correct schema where you created the tables.

Boilerplate template

The template we will be using is:

  1. Install cx_Oracle.
  2. Import the cx_Oracle driver.
  3. Import os module used to read the environment variable.
  4. Get the connection string from the environment variable.
  5. Create the connection object.
  6. Create the cursor object.
I will include this code section with all Python examples and use the connection object “con” and the cursor object “cur” throughout the series.

For each exercise, replace the “# Your code here” line with your code.

Anonymous PL/SQL Block

I’m going to start off with the most basic process and simply execute an anonymous block of PL/SQL code to reset the database tables.

You can execute any DDL or DML statement like this, but if you’re going to run PL/SQL it’s usually best to compile it to the database.

Execute a PL/SQL Procedure

Using the code from the anonymous block I created a procedure in the PL/SQL package called reset_data.

To call this procedure from Python we use the cursor.callproc method and pass in the package.procedure name to execute.

Assuming everything works, there will not be any response.  So this works as a ‘fire and forget’ way to call database procedures.

Pass Parameters

I have a procedure in my PL/SQL package that we can use to create a new pet in the lcs_pets table.  It accepts the pet_name, owner_id and pet_type.  Using these values it will insert a new entry into the lcs_pets table.

Now on the Python side.

I prefer to set my values with variables so that my code is easier to read, so I’ll create and set pet_name, owner_id and pet_type.

Next, I’ll call the cursor.callproc method and add an array containing the values to pass in the order they are defined in the database.

If everything works there will not be any response.

You can also use keyword parameters.  This also makes your code easy to read and also makes it so you don’t need to worry about the order of the parameters.

Once again, if everything works there will not be any response.

Get PL/SQL Function Return Values

When a row is added to the lcs_pets table a new id is automatically generated.  Having this id can be useful so I created a function in my PL/SQL package that will create a new pet in the lcs_pets table, just like in the previous function, but it will return the new id.

Using Python to call a function in the database and get the return value I’ll use the cursor.callfunc method.

  1. I set the variables that I’ll use as arguments to the function.
  2. Define a new_pet_id variable and assign it the value returned from callfunc.
  3. The second argument of the callfunc method is used to define the type of the data being returned.  I’ll set it to int.  (cx_Oracle will handle the NUMBER to int conversion.)
  4. I pass in the array of values just like I did when I used callproc.
  5. Print the returned value for new_pet_id.

Out Parameters

Out parameters can be very handy when you need to pass back more than one piece of information.  I have an add_pet function in the PL/SQL package that will check to see if the pet type you’re adding needs a license or not.  The function will return the new id like before, and a ‘yes’ or ‘no’ through the out parameter.

To work with the out parameter in Python I’ll add a string variable called ‘need_license’.  It can be defined using ‘cursor.var(str)‘. Then we just add the new variable to the values array in the correct position.  This works the same when using out parameters with the callproc method.

To get the value from ‘need_license’ we call it’s getvalue() function.

Accept Argument Values

So far I’ve hard-coded the variable values in the Python code and the methods are fairly simple, so there’s a low chance of errors.  But, for most methods, we want to accept parameter values that can be passed into the Python code then on to the PL/SQL functions.  I’ll modify the Python method to accept command line arguments.

We need to import sys so that we can use sys.argv[] to grab the command line arguments and assign them to the variables.

If I run this to add a dog, I get:

Adding a fish, I get:

PL/SQL Exceptions

Now that I’m accepting outside argument values, the odds that I’ll eventually get errors with the above code is almost a certainty.  If an error happens in the Python code you can handle it as you normally would.  But, what if there’s an error thrown by the PL/SQL code?

It’s easy enough to test this.  Make the same call as before but pass in a string for the second value.

I would recommend that you handle errors as close to where they happen as you can.  In this example, you could catch the error in the PL/SQL function and either handle it or raise it.  If you don’t handle it in PL/SQL it will be passed back to cx_Oracle which will throw a cx_Oracle.DatabaseError.  At that point, you can handle it as you would when any other Error is thrown in your Python application.

Additional Resources

CI/CD for Database Developers – Export Database Objects into Version Control

With this post, I kick off a series that walks you through the process of building  database applications using a CI/CD pipeline. I will be covering:

  • Export Database Objects into Version Control (This Post)
  • Use Schema Migration
  • Unit Test PL/SQL
  • Build and Deploy
  • Automate

Links will be added for each topic as that article is published. I will use the DinoDate application (source) as my code base.

For this post, assume that I have DinoDate installed and all development to date  has been done directly in the database (source code not stored externally in files).

Since, however, it’s really not a good idea to edit and maintain code directly in the database, I’d like to switch to storing the DDL for my database schema objects in files, managed by a version control system. Let’s get going!

Export the Database Objects out to Files

I could export the code for my objects out into files by writing queries against  built-in database views like ALL_SOURCE.  I have used this method a few times, and you probably have, too. You can make it work, but it puts the onus on you to get everything right. I’d rather think about other things, so I ask myself: is there a tool that will take care of the heavy lifting on this step?

There sure is, and it’s free:  Oracle SQL Developer has a great export tool. Assuming you’ve got SQL Developer installed and you can connect to your schema, here’s what you do:

Under the Tools menu, click on Database Export.

The first step of the wizard configures the general export attributes:

  1. Choose the connection for the schema you want to export.
  2. Select the options you’d like the wizard to use when creating the export scripts.
    I like to add drop statements to my scripts.  You may also want to export grants.
  3. I want to export some of the master table data so I will leave the export data section set with defaults.
  4. Choose “Save As Separate Directories” and select the export directory.

In step 2 I left all of the default options selected.

I want all of the schema objects so I skip step 3.

I selected the tables that contain the pre-loaded master data for the application.

Everything looks good so click finish.

The export wizard also creates a master run script and opens the script in a new worksheet (I’ll come back to this later):

Now that I have everything exported, I want to get the files into version control asap.

Add the Objects to Version Control

I’ll be using Git for version control.  SQL Developer has some nice integrated tools for working with Git but for now, I’m just going to use the command line.

  1. Change to the export directory.
  2. Initialize a Git repository.
  3. Add all files to the repo.
  4. Commit the changes.
Assuming that you’re using a central Git repository, you should push the new repo up to the shared repo.  That process can differ based on which repository host you’re using so I’ll leave it up to you to consult the documentation.

With my files stored safely in Git, it’s time to

Verify / Clean up

Whenever you use any automated code generate/export tool, you should verify the results.

For example, you may want to change the generated SQL for the dinosaure_id column from

to

After you verify each file, run a code beautifier on it.  Maintaining a standard code format will make it easier to see the differences between changes.  This will make your future code reviews much easier.

Make sure you commit each file as you change it.  Don’t wait till the end to do a massive commit.

Organize

In step 1 of the wizard, I selected Save as Separate Directories so all of my object scripts have been grouped into directories by type, like this:

This is a perfectly fine way to organize your scripts.  However, I like to group mine by whether or not the scripts will be changed and re-run vs run a single time and future changes will be done with new scripts.  For example:

If I were going to run everything from scripts, I would also break down the Run_Once directory into product versions with a subdirectory for ‘create from scratch’ and one for ‘updates’, like this:

In another post, I’ll show you how to use a Schema Migration tool which makes managing schema changes much easier.

Master Build Script

Now that everything is cleaned up and organized, we need to modify the master script that was generated by the export.  It should be named with a timestamp in a format similar to this:

Generated-YYYYMMDDHH24MISS.sql.

My exported script was named Generated-20180706134514.sql.

You will want to edit this file and modify the directory for the scripts to match the changes you made above.  Also, you need to verify that the scripts are being run in the order of dependencies.  Objects should be created after the objects they depend on.

If you’re not planning to use a Schema Migration tool you’ll want to create separate master scripts for each of the create and update directories.

Test

Create a new schema that’s safe to test with, and run your master scripts.  After you get the scripts running without errors, do a schema compare to check for any differences between your new schema and the schema you exported from.

When it’s all the way you want it, you are now ready and able to work from files to build your database code.

Working From  Files

From this point, you can now follow a new and improved workflow when it comes to editing the code for your database objects:

  1. Pull the latest version of your code from the shared repository. NEVER ASSUME YOU ALREADY HAVE THE LATEST.
  2. Make your changes.
  3. Compile to the database.
  4. Commit (to your source code repository, not your transaction) often.
  5. Push your changes back up to the shared repository.

Of course, that is a very simplified workflow. You should start a discussion with your team about using more advanced methods to further automate and improve your processes.

I sincerely hope you skimmed this post because you gave up long ago on editing code directly in the database. If that is not the case, I hope this article helps you make the change. Because then the next time something goes badly wrong,  you can simply recover from Git (or your repository of choice).

No tearing your hair out. No gnashing of teeth. No self-hating recriminations. Just a quick recovery and get back to work.

And then you can move on to more interesting challenges in improving the way you write and maintain your database application code.

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

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

Work From Files

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

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

Version Control

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

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

Making frequent small commits will help keep everything running smoothly.

What about your schema objects?

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

Unit Testing

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

Unit tests typically follow these steps:

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

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

Build

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

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

How to handle the PL/SQL code

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

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

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

Automate!

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

Original by Jez Humble

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

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

Other Useful Tools

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

Getting started with Oracle Rest Data Services

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

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

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

A Short Tutorial

Setup a VM

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

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

SQL Developer

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

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

Connect to the HR schema

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

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

Test the connection and connect.

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

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

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

REST Data Services Wizard

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

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

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

For this tutorial, I’ll be using the…

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

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

         

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

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

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

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

New Module

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

To create a new module:

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

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

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

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

Click Next.

Template URI

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

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

Method Handlers

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

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

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

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

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

Click Next, review the summary and click Finish.

Get Query

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

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

Enter this query into the SQL Worksheet.

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

Post

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

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

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

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

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

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

Enter this PL/SQL into the SQL Worksheet.

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

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

Parameters

Click on the Parameters tab and enter the following values.

Colum definitions:

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

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

It’s time to….

Test the Service

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

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

GET

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

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

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

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

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

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

POST

In your REST testing tool:

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

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

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

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

Export SQL

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

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

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

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

In the window that pops up:

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

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

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

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

 

 

Getting Started With Oracle Spatial part 2

Setup

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.

Exercises:

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)

Answer

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

Answer

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

Answer

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.

Exercises:

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.

SDO_UTIL.POINT_AT_BEARING accepts:

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

Exercise:

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.

Exercise:

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.

Exercise:

Change the generate_random_location function so it accepts:

  • The island as an SDO_GEOMETRY simple polygon object.

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

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.

Exercises:

  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

Summary

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

Setup

You’ll need a copy of the git repository.

  1. Go to https://github.com/oracle/dino-date
  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.

Cleanup

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.

Exercise:

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.

Answer

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.

Exercise:

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)

Answer

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.

randomIsland

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.

Parameters:

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