Insert (Crud) using Perl and DBD::ORACLE

In this post, we’re going to take a look at the C in CRUD: Create.

We will be using the DBD::Oracle driver to create some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.

Helper Function

I will be using a helper function get_all_rows(). This is a select statement used to verify that the inserts worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.

sub get_all_rows {
 my $label = $_[0];
 # Query all rows
 my $con = DBI->connect( 'dbi:Oracle:', $connectString, '') || 
    die "Database connection not made: $DBI::errstr";
 $con->{RaiseError} = 1; # set the connection to raise errors
 my $sth = $con->prepare("select id, name, age, notes from lcs_people order by id");
 $sth->execute;

 #Adding some space around the results to make better screenshots.
 print "\n $label: \n";
 while (my @row = $sth->fetchrow_array){
   print " @row\n";
 }
 print "\n";

 $con->disconnect;
}

Add this function to the top of your file.

Resetting the data

To keep the examples clean and precise, I will reset the data at times.

Create a new file called reset_data.perl with the following code and then run it whenever you would like to reset the data.

use strict;
use DBI;
my $connectString=$ENV{'db_connect'};
my $con = DBI->connect( 'dbi:Oracle:', $connectString, '');
$con->{RaiseError} = 1; # set the connection to raise errors

# Delete rows
my $sth = $con->prepare("delete from lcs_pets");
$sth->execute;

# Reset Identity Coulmn
my $sth = $con->prepare("alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 3)");
$sth->execute;

# Delete rows
my $sth = $con->prepare("delete from lcs_people");
$sth->execute;

# Reset Identity Coulmn
my $sth = $con->prepare("alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)");
$sth->execute;

# Insert default rows
my @ids = (1, 2);
my @names = ("Bob", "Kim");
my @ages = (35, 27);
my @notes = ("I like dogs", "I like birds");

my $sth = $con->prepare("INSERT INTO lcs_people(id, name, age, notes) VALUES (?, ?, ?, ?)");
my $tuples = $sth->execute_array(
 { ArrayTupleStatus => \my @tuple_status }, \@ids, \@names, \@ages, \@notes,);
if ($tuples) {
 print "Successfully inserted $tuples records\n";
} else {
 print "Insert failed\n";
}

# Insert default rows
my @ids = (1, 2);
my @names = ("Duke", "Pepe");
my @owners = (1, 2);
my @types = ("dog", "bird");

my $sth = $con->prepare("INSERT INTO lcs_pets(id, name, owner, type) VALUES (?, ?, ?, ?)");
my $tuples = $sth->execute_array(
 { ArrayTupleStatus => \my @tuple_status }, \@ids, \@names, \@owners, \@types,);
if ($tuples) {
 print "Successfully inserted $tuples records\n";
} else {
 print "Insert failed\n";
}

$con->disconnect;
Boilerplate template

The template we will be using is:

use strict;
use DBI;
my $connectString=$ENV{'db_connect'};

sub get_all_rows {
 my $label = $_[0];
 # Query all rows
 my $con = DBI->connect( 'dbi:Oracle:', $connectString, '') ||
 die "Database connection not made: $DBI::errstr";
 $con->{RaiseError} = 1; # set the connection to raise errors
 my $sth = $con->prepare("select id, name, age, notes from lcs_people order by id");
 $sth->execute;

 print "\n $label: \n";
 while (my @row = $sth->fetchrow_array){
   print " @row\n";
 }
 print "\n";

 $con->disconnect;
}

my $con = DBI->connect( 'dbi:Oracle:', $connectString, '') || die "Database connection not made: $DBI::errstr";
$con->{RaiseError} = 1; # set the connection to raise errors

get_all_rows('Original Data');

# Your code here

get_all_rows('New Data');

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

Simple insert

We will perform a simple insert that adds a single record into the lcs_people table.  These are the steps performed in the code snippet below.

  • Prepare a SQL INSERT statement, specifying the table and columns to insert the data.
  • Bind the three parameters to their values.  (See the R part of this series for an explanation of bind variables)
  • Execute the statement.
my $sth = $con->prepare("insert into lcs_people(name, age, notes) values (:name, :age, :notes)");
$sth->bind_param( ":name","Sandy");
$sth->bind_param( ":age",31);
$sth->bind_param( ":notes","I like horses");
$sth->execute;

When I run this code in my Perl session, I see:

 Original Data: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds

 New Data: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds
 3 Sandy 31 I like horses

You’ll notice in the bullet points above, I did not commit.  The DBD::Oracle driver is set to auto commit by default.  If you plan to process multiple dependent transactions you may want to disable AutoCommit.

What is a transaction?

When you execute Data Manipulation Language or DML statements, such as the insert I use in this post, those changes are only visible to your current connection or session.

Those changes will not be visible to other sessions (even another session connected to the same schema in which the changes were made) until you commit your changes. That step makes it “permanent” in the database, and available for everyone else to see (and possibly change in a future transaction).  This also allows you to roll back a series of uncommitted transactions if one of the later transactions fails and it would cause data problems for the previous transactions.

Extra Fun 1 & 2

1.  Insert more than 1 row .

Using data for ‘Rob’, 37, ‘I like snakes’ and ‘Cheryl’, 41, ‘I like monkeys’ Your results should be:

 Original Data: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds
 3 Sandy 31 I like horses

 New Data: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds
 3 Sandy 31 I like horses
 4 Rob 37 I like snakes
 5 Cheryl 41 I like monkeys
Answer

2.  Verify that a second connection cannot see your changes till after the commit.

Using data for ‘Suzy’, 31, ‘I like rabbits’ and assuming that you did the previous exercise your results should be:

 Original Data: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds
 3 Sandy 31 I like horses
 4 Rob 37 I like snakes
 5 Cheryl 41 I like monkeys

 New connection after insert: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds
 3 Sandy 31 I like horses
 4 Rob 37 I like snakes
 5 Cheryl 41 I like monkeys

 Same connection: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds
 3 Sandy 31 I like horses
 4 Rob 37 I like snakes
 5 Cheryl 41 I like monkeys
 6 Suzy 31 I like rabbits

 New connection after commit: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds
 3 Sandy 31 I like horses
 4 Rob 37 I like snakes
 5 Cheryl 41 I like monkeys
 6 Suzy 31 I like rabbits

 New Data: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds
 3 Sandy 31 I like horses
 4 Rob 37 I like snakes
 5 Cheryl 41 I like monkeys
 6 Suzy 31 I like rabbits

Notice that after the insert, the connection that made the insert can see Suzy but the second connection can’t.

After the commit, both connections see Suzy.

Answer
Reset the data

Now is a good time to run reset_data.perl.

Using Identity Columns

You may have noticed that the id column is not passed in, but is automatically set sequentially.  Prior to Oracle Database 12c, this was accomplished using a sequence and a trigger.

In 12c, this can be accomplished by using an Identity Column.

 CREATE TABLE lcs_people (
 id NUMBER GENERATED BY DEFAULT AS identity,
 ....
 You can find more information on identity columns here(pdf).
Returning data after an insert
 Sometimes we need to perform additional operations after an insert using data generated by the database, such as the identity column above.  For example, let’s add a person and a pet for them.

We could run an insert then select the value back using the name.  But if the name is not unique we’ll have a problem.  This is where the RETURNING clause is helpful.

We will perform an insert that adds a single record into the lcs_people table. Then using the returned id we will add a pet.  These are the steps performed in the code snippet below.

  • Prepare a SQL INSERT statement, specifying the table and columns to insert the people data.
  • Bind the three “values” parameters to their values.
  • Bind the id parameters to a new variable $new_id using bind_param_inout .
  • Execute the statement returning the id into new_id.
  • Prepare a SQL INSERT statement, specifying the table and columns to insert the pet data.
  • Bind the id parameter to the $new_id value.
  • Execute the statement.
  • Print the new_id value.
  • Prepare a SQL statement.
  • Bind the owner parameter to the $new_id value.
  • Execute the statement.
  • Print the results with a little decoration text.
my $sth = $con->prepare("insert into lcs_people(name, age, notes) values (:name, :age, :notes) returning id into :id");
$sth->bind_param( ":name","Sandy");
$sth->bind_param( ":age",31);
$sth->bind_param( ":notes","I like horses");
$sth->bind_param_inout(":id", \my $new_id, 99);
$sth->execute;

my $sth = $con->prepare("insert into lcs_pets (name, owner, type) values (:name, :owner, :type)");
$sth->bind_param( ":name","Big Red");
$sth->bind_param( ":owner", $new_id);
$sth->bind_param( ":type","horse");
$sth->execute;

print " Our new value is: $new_id\n";

$sth = $con->prepare("select name, owner, type from lcs_pets where owner = :owner");
$sth->bind_param(":owner", $new_id);
$sth->execute;

print "\n Sandy\'s pets:\n";
while (my @row = $sth->fetchrow_array){
 print " @row\n";
}
print "\n";

When I run this code in my Perl session, I see:

 Original Data: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds

 Our new value is: 3

 Sandy's pets:
 Big Red 3 horse

 New Data: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds
 3 Sandy 31 I like horses

Notice the new value, the owner in Sandy’s pets and Sandy’s id in the New Data are all 3 .

Extra Fun 3

3.  Insert Sandy again but return her id and name.

Your results should be:

 Original Data: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds
 3 Sandy 31 I like horses

 Our new id is: 23 name: Sandy

 New Data: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds
 3 Sandy 31 I like horses
 4 Sandy 31 I like horses

Notice that (3, ‘Sandy’..) is still there along with our new (4, ‘Sandy’..) but the returned id is 4.  It should return the new id each time you run it.

Answer
 Reset the data

Now is a good time to run reset_data.perl.

Insert more than 1 row

As mentioned above, when you want to insert multiple rows, running multiple insert statements is inefficient and makes multiple trips to the database so instead, we will use execute_array.  In some databases, execute_array is simply a shortcut that will call execute for each record.  However, if your database is capable of bulk processing like Oracle is, the driver will create a much more efficient bulk transaction.

We will perform an insert that adds two records into the lcs_people table.  These are the steps performed in the code snippet below.

  • Create an array for the each column populated with the data for that column.  The longest array will be used to determine the number of transactions if there are any shorter arrays they will be padded with NULL.
  • Prepare a SQL INSERT statement, specifying the table and columns to insert the people data.  Notice we’re using positional bind variables this time.
  • Use execute_array to execute the  statement.  We aren’t accessing any of the execute_array attributes “{}.” Bind the three arrays in order of use.
  • Print the number of records inserted using $tuples, if $tuples is unknown the transaction failed.
my @names = ("Sandy", "Suzy");
my @ages = (31, 29);
my @notes = ("I like horses", "I like rabbits");

my $sth = $con->prepare("INSERT INTO lcs_people(name, age, notes) VALUES (?, ?, ?)");
my $tuples = $sth->execute_array({}, \@names, \@ages, \@notes,);
if ($tuples) {
 print " Successfully inserted $tuples records\n";
} else {
 print " Insert failed\n";
}

When I run this code in my Perl session, I see:

 Original Data: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds

 Successfully inserted 2 records

 New Data: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds
 3 Sandy 31 I like horses
 4 Suzy 29 I like rabbits
Some things you could try
  • Loop through an array of people and insert each one returning its id.  Using that id add multiple pets with execute_array.
  • Create a large array of people.  Time the difference between looping through single inserts and using execute_array.

Series sections

Initial Setup
Create records
Retrieve records
Update records (Coming soon)
Delete records (Coming soon)

Select (cRud) using Ruby-OCI8

In this post, we’re going to take a look at the R in CRUD: Retrieve.

We will be using the ruby-oci8 driver to retrieve some data from the database tables, using the connection object created in the Initial Setup section of the first post in this series.

Simple query

We will perform a simple query that pulls all of the records in no particular order.  Here are the steps we’ll follow in the code snippet below.

  1. Create the connection object.  We will use this object to perform our database operations.
  2. Define the SQL SELECT statement, specifying the columns desired from the table.
  3. Create a cursor by parsing the statement.
  4. Execute the cursor.
  5. Fetch and loop through the rows.
  6. Print each row.
# Query all rows
con = OCI8.new(connectString)
statement = 'select id, name, age, notes from lcs_people'
cursor = con.parse(statement)
cursor.exec
cursor.fetch() {|row|
 print row
}

When I run this code in my Ruby session, I see:

[#<BigDecimal:15bcb00,'0.1E1',9(18)>, "Bob", #<BigDecimal:15bc790,'0.35E2',9(18)>, "I like dogs"][#<BigDecimal:15bd1b8,'0.2E1',9(18)>, "Kim", #<BigDecimal:193bf60,'0.27E2',9(18)>, "I like birds"]

This is not real pretty, so from here on we’ll use printf.

printf "Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]

Much better:

select1pretty

Id: 1, Name: Bob, Age: 35, Notes: I like dogs
Id: 2, Name: Kim, Age: 27, Notes: I like birds

Extra Fun 1

Modify the statement to order by age.  When you’re done the results should be:

Id: 2, Name: Kim, Age: 27, Notes: I like birds
Id: 1, Name: Bob, Age: 35, Notes: I like dogs
Answer
Select specific rows

Now suppose I only want to see the data for Kim. I want, therefore, to restrict the rows returned by the SELECT. This is done with a WHERE clause. There are several ways to do this.

We could just put the where clause in the statement and it would work.

statement = "select id, name, age, notes from lcs_people where name = 'Kim'"

However, we want to choose the name at run time and store it in a variable called person_name.  You could accept the value in as an argument passed into a function, but we’ll just set a variable to keep it simple.

person_name = 'Kim'

It is possible to simply concatenate the value into the statement.

statement = "select id, name, age, notes from lcs_people where name = '#{person_name}'"

This is very dangerous and opens our code to a SQL Injection attack.  You can follow that link for more information, but we won’t be going into detail in this series.  Just know that you should, generally, never allow end user input to be fed directly into a dynamic SQL statement.

A much safer way to pass external values into the SQL statement is by using bind variables with prepared statements.

You have a couple different options:

Positional:
statement = "select id, name, age, notes from lcs_people where name = :1 and age = :2"
cursor = con.parse(statement)
cursor.bind_param(1, 'Bob')
cursor.bind_param(2, 35)

statement = "select id, name, age, notes from lcs_people where name = :2 and age = :1"
cursor = con.parse(statement)
cursor.bind_param(1, 'Bob')
cursor.bind_param(2, 35)

Notice the :1 and :2 are switched in the two examples. With a positional statement, the labels do not matter, it could just as well have been :1 and :something. What matters is the first :variable in the statement will be assigned the value of the bind_param with the index of 1, the second the value of index 2 and so on.

Positional bind parameters will us an integer as the key in bind_param().

Named:
statement = "select id, name, age, notes from lcs_people where name = :name and age = :age"
cursor = con.parse(statement)
cursor.bind_param('name', 'Bob')
cursor.bind_param('age', 35)

statement = "select id, name, age, notes from lcs_people where name = :age and age = :name"
cursor = con.parse(statement)
cursor.bind_param('age', 'Bob')
cursor.bind_param('name', 35)

With this method, the :name variable will be assigned the value of ‘name’ in the provided key value set.

In the second example, I switched the labels in the SQL, and I also had to switch the keys in the bind_parameter calls so the correct value is still used.

Notice, in both examples, that we do not wrap the bind variable for the name with quotes.  This is handled automatically when the statement is prepared for execution.

Example:
  1. Create the connection object.
  2. Create the person_name variable and assign it a value of ‘Kim’.
  3. Define the SQL SELECT statement, specifying the columns desired from the table.
  4. Create a cursor by parsing the statement.
  5. Bind the value of person_name to :name.
  6. Execute the cursor.
  7. Fetch and loop through the rows.
  8. Print each row.
# Query for Kim
con = OCI8.new(connectString)

person_name = 'Kim'
statement = "select id, name, age, notes from lcs_people where name=:name"
cursor = con.parse(statement)
cursor.bind_param('name', person_name)
cursor.exec
cursor.fetch() {|row|
 printf "Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
}

This will return only the data for Kim:

Id: 2, Name: Kim, Age: 27, Notes: I like birds
Extra Fun 2

Modify the statement and variable to get the people older than 30.  When you’re done the results should be:

Id: 1, Name: Bob, Age: 35, Notes: I like dogs
Answer

In this section, we took a look at some basic query functionality.  When you experiment with more complex queries, if you run into problems leave a comment here or on twitter and we’ll find an answer together.

Some things you could try
  • Join the lcs_people and lcs_pets table to get the people and their pets
  • Only retrieve the person’s name and age
  • Change the order to display in descending order.

Hint – If you have trouble getting a query to run in your code, try running it in SQL Plus or another database console tool.  This will help determine if the problem is with the query or the code.

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Select (cRud) using Perl and DBD::ORACLE

In this post, we’re going to take a look at the R in CRUD: Retrieve.

We will be using the DBD::Oracle driver to retrieve some data from the database tables, using the connection object created in the Initial Setup section of the first post in this series.

Simple query

We will perform a simple query that pulls all of the records in no particular order.  Here are the steps we’ll follow in the code snippet below.

  1. Create the connection object and set it to raise errors.  We will use this object to perform our database operations.
  2. Prepare the SQL SELECT statement, specifying the columns desired from the table.
  3. Execute the statement.
  4. Fetch the results and dump them to $fh.
  5. Disconnect.
# Query all rows
my $con = DBI->connect( 'dbi:Oracle:', $connectString, '');
$con->{RaiseError} = 1; # set the connection to raise errors

my $sth = $con->prepare("select id, name, age, notes from lcs_people");
$sth->execute;
DBI::dump_results($sth);

$con->disconnect;

When I run this code in my Perl session, I see:

'1', 'Bob', '35', 'I like dogs'
'2', 'Kim', '27', 'I like birds'
2 rows
Extra Fun 1

Modify the statement to order by age.  When you’re done the results should be:

'2', 'Kim', '27', 'I like birds'
'1', 'Bob', '35', 'I like dogs'
2 rows
Answer
Select specific rows

Now suppose I only want to see the data for Kim. I want, therefore, to restrict the rows returned by the SELECT. This is done with a WHERE clause. There are several ways to do this.

We could just put the where clause in the statement and it would work.

my $sth = $con->prepare("select id, name, age, notes from lcs_people where name = 'Kim'");

However, we want to choose the name at run time and store it in a variable called person_name.  You could accept the value in as an argument passed into a function, but we’ll just set a variable to keep it simple.

my $person_name = 'Kim';

It is possible to simply concatenate the value into the statement.

my $sth = $con->prepare("select id, name, age, notes from lcs_people where name= '${person_name}'");

This is very dangerous and opens our code to a SQL Injection attack.  You can follow that link for more information, but we won’t be going into detail in this series.  Just know that you should, generally, never allow end user input to be fed directly into a dynamic SQL statement.

A much safer way to pass external values into the SQL statement is by using bind variables with prepared statements.

You have a couple different options:

Placeholders:
my $sth = $con->prepare("select id, name, age, notes from lcs_people where name = ? and age = ?");
$sth->bind_param(1,'Bob');
$sth->bind_param(2, 35);

my $sth = $con->prepare("select id, name, age, notes from lcs_people where name = ? and age = ?");
$sth->bind_param(2, 35);
$sth->bind_param(1,'Bob');

Notice the bind_param(1,  and bind_param(2, are switched in the two examples.  With a placeholders statement, you use a ? to indicate where the bind variable value goes, then when you assign the bind_param you indicate which placeholder to assign the value to.

Named:
my $sth = $con->prepare("select id, name, age, notes from lcs_people where name = :name and age = :age");
$sth->bind_param( ":name",'Bob');
$sth->bind_param( ":age", 35);

my $sth = $con->prepare("select id, name, age, notes from lcs_people where name = :name and age = :age");
$sth->bind_param( ":age", 35);
$sth->bind_param( ":name",'Bob');

With this method, the :name variable will be assigned the value of ‘name’ in the provided key value set.

Notice, in both examples, that we do not wrap the bind variable for the name with quotes.  This is handled automatically when the statement is prepared for execution.

Example:
  1. Create the connection object and set it to raise errors.
  2. Assign ‘Kim’ to person_name.
  3. Prepare the SQL statement using a bind variable.
  4. Bind the value of $person_name to :name.
  5. Execute the statement.
  6. Fetch the results and dump them to $fh.
  7. Disconnect.
# Query for Kim
my $con = DBI->connect( 'dbi:Oracle:', $connectString, '');
$con->{RaiseError} = 1; # set the connection to raise errors

my $person_name = 'Kim';
my $sth = $con->prepare("select id, name, age, notes from lcs_people where name=:name ");
$sth->bind_param( ":name",$person_name);

$sth->execute;
DBI::dump_results($sth);

This will return only the data for Kim:

'2', 'Kim', '27', 'I like birds'
1 rows
Extra Fun 2

Modify the statement and variable to get the people older than 30.  When you’re done the results should be:

'1', 'Bob', '35', 'I like dogs'
1 rows
Answer

In this section, we took a look at some basic query functionality.  When you experiment with more complex queries, if you run into problems leave a comment here or on twitter and we’ll find an answer together.

Some things you could try
  • Join the lcs_people and lcs_pets table to get the people and their pets
  • Only retrieve the person’s name and age
  • Change the order to display in descending order.

Hint – If you have trouble getting a query to run in your code, try running it in SQL Plus or another database console tool.  This will help determine if the problem is with the query or the code.

Series sections

Initial Setup
Create records
Retrieve records
Update records (Coming soon)
Delete records (Coming soon)

Basic CRUD operations using Perl and DBD::Oracle

In this series, we’re going to take a look at performing CRUD (Create Retrieve Update Delete) operations using the DBD::Oracle driver.

A good ORM will handle most of your needs.

An ORM tool can handle many of the repetitive processes when interfacing with a database.  Your project might call for an ORM tool such as DBIx::Class.  No doubt about it,  a good ORM can come in very handy.  An ORM application will typically have a function for passing in raw SQL if needed, so you may not need to go straight to the driver.

Why learn to use the driver directly?

An ORM brings its own, different complexity to a project.  It may be overkill for some projects.  There are also times when a specific task is just different enough that an ORM may not be able to help, or its application to your requirements become so complex that your code becomes difficult to maintain.

And if you’re like me, it’s hard to be satisfied with a black box approach.  You want to know more about how your tools work and you want to have options, just in case.

Martin Fowler said “Mapping to a relational database involves lots of repetitive, boiler-plate code.  A framework that allows me to avoid 80% of that is worthwhile even if it is only 80%.”

When you have enough knowledge to implement direct CRUD operations, you are in a better position to choose the right tool for the right job.

Common Setup

All examples in this series will use the same database objects and connection information.

Creating the Database Objects

The following can be used to setup the initial tables we’ll use.  Please make sure you’re connected to a schema in which you can safely execute commands like these.

CREATE TABLE LCS_PEOPLE (
 id NUMBER GENERATED BY DEFAULT AS identity,
 name VARCHAR2(20),
 age NUMBER,
 notes VARCHAR2(100)
)
/

ALTER TABLE LCS_PEOPLE
ADD CONSTRAINT PK_LCS_PEOPLE PRIMARY KEY ("ID")
/

CREATE TABLE LCS_PETS (
 id NUMBER GENERATED BY DEFAULT AS IDENTITY,
 name VARCHAR2(20),
 owner NUMBER,
 type VARCHAR2(100)
)
/

ALTER TABLE LCS_PETS ADD CONSTRAINT PK_LCS_PETS PRIMARY KEY ("ID")
/

ALTER TABLE LCS_PETS ADD CONSTRAINT FK_LCS_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "LCS_PEOPLE" ("ID")
/

INSERT INTO LCS_PEOPLE (name, age, notes)
 VALUES ('Bob', 35, 'I like dogs')
/

INSERT INTO LCS_PEOPLE (name, age, notes)
 VALUES ('Kim', 27, 'I like birds')
/

INSERT INTO LCS_PETS (name, owner, type)
 VALUES ('Duke', 1, 'dog')
/

INSERT INTO LCS_PETS (name, owner, type)
 VALUES ('Pepe', 2, 'bird')
/

COMMIT
/
 Making the Connection
  1. use strict
  2. use the DBD::Oracle driver.
  3. Get the connection string from the environment variable.
  4. Create the connection object.
  5. Set the connection to raise errors.
use strict;
use DBI;
my $connectString=$ENV{'db_connect'};
my $con = DBI->connect( 'dbi:Oracle:', $connectString, '') || die "Database connection not made: $DBI::errstr";
$con->{RaiseError} = 1; # set the connection to raise errors

We will include this code section with all examples and use the connection object “con” throughout the series.

Cleanup

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

drop table LCS_PETS
/

drop table LCS_PEOPLE
/
Guide to Perl DBD::Oracle CRUD Series

Initial Setup
Create records
Retrieve records
Update records (Coming soon)
Delete records (Coming soon)

Basic CRUD operations using Ruby-OCI8

In this series, we’re going to take a look at performing CRUD (Create Retrieve Update Delete) operations using the ruby-oci8 driver.

A good ORM will handle most of your needs.

An ORM tool can handle many of the repetitive processes when interfacing with a database.  Your project might call for an ORM tool such as ActiveRecord.  No doubt about it,  a good ORM can come in very handy.  An ORM application will typically have a function for passing in raw SQL if needed, so you may not need to go straight to the driver.

Why learn to use the driver directly?

An ORM brings its own, different complexity to a project.  It may be overkill for some projects.  There are also times when a specific task is just different enough that an ORM may not be able to help, or its application to your requirements become so complex that your code becomes difficult to maintain.

And if you’re like me, it’s hard to be satisfied with a black box approach.  You want to know more about how your tools work and you want to have options, just in case.

Martin Fowler said “Mapping to a relational database involves lots of repetitive, boiler-plate code.  A framework that allows me to avoid 80% of that is worthwhile even if it is only 80%.”

When you have enough knowledge to implement direct CRUD operations, you are in a better position to choose the right tool for the right job.

Common Setup

All examples in this series will use the same database objects and connection information.

Creating the Database Objects

The following can be used to setup the initial tables we’ll use.  Please make sure you’re connected to a schema in which you can safely execute commands like these.

CREATE TABLE LCS_PEOPLE (
 id NUMBER GENERATED BY DEFAULT AS identity,
 name VARCHAR2(20),
 age NUMBER,
 notes VARCHAR2(100)
)
/

ALTER TABLE LCS_PEOPLE
ADD CONSTRAINT PK_LCS_PEOPLE PRIMARY KEY ("ID")
/

CREATE TABLE LCS_PETS (
 id NUMBER GENERATED BY DEFAULT AS IDENTITY,
 name VARCHAR2(20),
 owner NUMBER,
 type VARCHAR2(100)
)
/

ALTER TABLE LCS_PETS ADD CONSTRAINT PK_LCS_PETS PRIMARY KEY ("ID")
/

ALTER TABLE LCS_PETS ADD CONSTRAINT FK_LCS_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "LCS_PEOPLE" ("ID")
/

INSERT INTO LCS_PEOPLE (name, age, notes)
 VALUES ('Bob', 35, 'I like dogs')
/

INSERT INTO LCS_PEOPLE (name, age, notes)
 VALUES ('Kim', 27, 'I like birds')
/

INSERT INTO LCS_PETS (name, owner, type)
 VALUES ('Duke', 1, 'dog')
/

INSERT INTO LCS_PETS (name, owner, type)
 VALUES ('Pepe', 2, 'bird')
/

COMMIT
/
 Making the Connection
  1. require the ruby-oci8 driver.
  2. Get the connection string from the environment variable.
  3. Create the connection object.
require 'oci8'
connectString = ENV['db_connect'] 
con = OCI8.new(connectString)

We will include this code section with all examples and use the “con” connection object “con” throughout the series.

Cleanup

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

drop table LCS_PETS
/

drop table LCS_PEOPLE
/
Guide to Ruby-OCI8 CRUD Series

Initial Setup
Create records (Coming soon)
Retrieve records
Update records (Coming soon)
Delete records (Coming soon)

Use Oracle Spatial to locate the dinosaurs

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

return final_point;
end;

You can find the full formatted code in this gist.

Now let’s figure out where those dinosaurs are.

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

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

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

commit;

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

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

begin

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

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

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

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

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

select member_id,
 generate_random_location(v_dino_island)
 bulk collect into l_member_ids
 from dd_members
 fetch next 1000 rows only;

forall indx in 1 .. l_member_ids.count
 update dd_members_t
 set location = l_member_ids (indx).new_location
 where member_id = l_member_ids (indx).member_id;
 
 dbms_output.put_line(sql%rowcount);

commit;
end;

You can find the full formatted code in this gist.

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

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

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

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

select location
 from dd_members
 where location is not null;

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

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

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

Leave your comfort zone behind: head to an open conference

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

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

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

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

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

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

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

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

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

Draw a maple leaf using Oracle Spatial

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

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

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

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

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

MapleLeaf1

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

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

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

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

select MDSYS.SDO_GEOMETRY(2003,
 4326,
 NULL,
 MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
 MDSYS.SDO_ORDINATE_ARRAY(0,7,
 -1.2,4.4,
 -3,5,
 -2,1,
 -4,3,
 -4.3,1.5,
 -7,2.5,
 -5.5,0,
 -7,-1,
 -3.3,-3.4,
 -4,-5,
 -.2,-4.2,
 -.2,-8,
 .2,-8,
 .2,-4.2,
 4,-5,
 3.3,-3.4,
 7,-1,
 5.5,0,
 7,2.5,
 4.3,1.5,
 4,3,
 2,1,
 3,5,
 1.2,4.4,
 0,7)) MapleLeaf
from dual;

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

MapleLeaf2

Use Oracle Spatial to create a home for the dinosaurs

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

A Point

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

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

Generating Shapes

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

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

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

Generating Corner Points

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

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.

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

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

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

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

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

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

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

Polygons With 3 or More Sides

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

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

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

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

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

Let’s make some shapes

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

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

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

start_point

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

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

line_0_radian

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

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

line_neg_three_quarter_pi

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

p_sides => 3

triangle

p_sides => 4

diamond

p_sides => 5

pentagon

p_sides => 6

p_sides => 7

seven_sides

p_sides => 8

octagon

p_sides => 9

nine_sides

p_sides => 10

ten_sides

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.

 

Repeat