Skip to main content

Using Always Free Autonomous Database to help me heal our planet


So I signed up for my Always Free Autonomous Database (AFAD) and a boatload of other cloud services. OK, what shall I do with them?

Hmmm....well....as some of you may know, I've gotten very concerned about climate change and human-caused extinctions. I started a project with Vincent Morneau called fabe - for all a beautiful earth - to help all of us reduce consumption, rescue species and reconnect to nature. Check it out at https://fab.earth. Yes, it is an APEX application and it is already running on an Oracle Database cloud service, so....what else?

You know the saying "think globally, act locally"? Well, if fabe is global, then my work on invasive species is local, very local.

When I lived in Chicago, I went out to the nearest "wild" spaces I could find and cut back buckthorn trees that didn't belong in Chicago, out-competed native trees for sunlight, and killed off those native species. I rescued trees - and the literally millions of living creatures that call those trees home.

It was just about the most fulfilling and meaningful action I've ever taken in my life.

Now that I live in North Carolina (Chapel Hill area), I have continued that tradition, but now focusing on wisteria, kudzu, English ivy, stilt grass and more. As I drive around, I identify areas that are neglected, areas overrun with invasives and whenever possible I head on over and start cutting back those plants which are wonderful - back where they evolved to grow. Sometimes I do this with permission, sometimes, well, it is an act of of non-violent (to humans, anyway) civil disobedience.

But just think: in two hours of cutting, I can rescue trees that are 50, 75 100 ft tall and being killed by wisteria. Trees that have been on this planet for decades and now will be here for decades more. All with the smallest sacrifice on my part. Joyful moments for me, for sure.

To give you a sense of the impact of my work, here is a section I just started to work on. The areas inside red show the dense grown of wisteria covering and weighing down the trees:
Once I cut the vines at the base (and some of them are 6 inches in diameter!) and they die and dry out, the "bones" of the tree are revealed:
You can see the dead wisteria vines on the left. On the right, all that sky that you can see through the trees was completely hidden by a dense, killing growth of wisteria just a few weeks ago!

Besides my individual work, I am also started up a volunteer group: Carrboro Tree Rescuers.

As I said, I have been identifying many different locations that need my loving attention (love expressed with saw, lopping shear, and pruning shear). So I've decided to build an app on my AFAD to help keep track of those locations.

I'll document the steps I took in building this application, so you can see how easy it is to leverage amazing products like Oracle Application Express, Oracle REST Data Services, SQL, PL/SQL and Oracle Database to help heal our planet and maybe even save some species from extinction.

I plan as much as possible to rely on the default features and behavior of APEX, to maximize my productivity on this side project and also showcase all that APEX does for you these days.

[And I must say that with APEX 19.2 Early Adaptor just out, I wish I could use that!]

I got started today by creating schemas and workspaces for my use in APEX. Then I decided to take advantage of the remarkable QuickSQL (brainchild of my boss, Mike Hichwa, who seriously you should be following) to ahem quickly generate the SQL I need to create the tables that will get me started.

Here's the QuickSQL script I threw together. I want to keep track of the various invasive species that plague this area, the various worksites, the species present at those sites, and a history of my activity at the sites. 
worksites /auditcols
  name vc /nn /unique
  site_type vc /check ADDRESS,INTERSECTION,DESCRIPTION,MILEMARKER,GPS /nn
  worksite_status vc /check NEW,CLEARED,INPROGRESS /nn
  address vc
  city vc
  state vc
  country vc
  postal_code vc
  description vc
  milemarker num
  gps_longitude num
  gps_latitude num

invasive_species /auditcols
   popular_name vc /nn /unique
   species_name vc /nn /unique
   impact_description vc /nn
   location_list vc
   
worksite_species /auditcols
   species_id /fk invasive_species /nn
   worksite_id /fk worksites /nn
   
rescue_teams /auditcols
   name vc /nn /unique
   description vc
   city vc
   state vc
   country vc
   postal_code vc

worksite_teams /auditcols
   rescue_team_id /fk rescue_teams /nn
   worksite_id /fk worksites /nn
   
worksite_history/auditcols
   worksite_id /fk worksites /nn
   rescue_team_id /fk rescue_teams
   activity_date d /nn
   description  vc /nn

rescue_files /auditcols
   worksite_species_id /fk worksite_species
   invasive_species_id /fk invasive_species
   worksite_hsitory_id /fk worksite_history
   file_type vc30 /check Activity,After
   mime_type vc75 /nn
   description vc255
   blob_content blob
I am sure this will change a bit before I start building the application, but it's a solid start.

Oh and did I mention? If anyone would like to help me with this  (or other similar projects), let me know!
Labels:

Comments

Post a Comment

Popular posts from this blog

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of wo...
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...
4 comments
Read more

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks. The three ways are: SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely  to run into: the error stack will be truncated at 512 bytes, and you might lose some error information. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.  Note: check out this LiveSQL script if...
5 comments
Read more