How'd You Do That

  A while back my employer (www.insum.ca) had an internal competition (a hackathon) and I gathered a two others to a team where we called ourselves The Good (Monty), The Bad (Me), and The Ugly (Adrian).  I personally though I was more appropriate to the final credit due to Adrian's unusually youthful appearance, but he won the gun fight for the role and we'll speak no more of my humiliation.

  During the event I proposed that we attempt to integrate a javascript library into APEX. It's been done before but this one was special.  Draw2D (www.draw2d.org) is a canvas-based library that brings a lot to the table including the visualization of process data into a visio-like charts that can be stored in, and retrieved from, the oracle database.

  Since that day we have shown it off to a few groups and now I am releasing this into the wilds of apex.oracle.com for those that have asked me when they could play with it a bit more than just looking.

  MapIt can be found here https://goo.gl/v0wTq4 and anyone can play with it.  Though, I will need to clean out the tables on occasion.

   This is not merely and end result but a precursor to a personal project of mine to bring a more useful workflow engine to the Application Express community.  We'll have more on that project in the future.

Til then, Enjoy!

Breaking Rules and Telling Stories

  Anyone who reads this space, or follows we via Twitter and other media outlets generally get the point that I rarely get political where I present a professional line of thought, but I do tend to tell personal stories. 

  “The only difference between a sea-story and a fairy-tale is that the Fairy-tale starts out ‘Once upon a time…’, and the sea-story starts out with ‘No Shit man, this really happened….’”  -- call it a Sailor’s proverb

  At age 19, I was assigned to the US Naval Mobile Construction Battalion Forty (NMCB-40).  And I left to join them in Rota, Spain for a seven-month deployment.  When I got there I was already restless and decided to volunteer for a detachment known as AIRDET.  It was only after that people told me that you don’t volunteer for that one.  I was added as an alternate, and the night before they deployed I was told to grab my gear because one of our guys had his appendix burst.  The next morning, I was on a C130 with 80 other Seabees, headed for Tunisia.

  The North Detachment, was tasked with assisting in the building up road drainage infrastructure nead Tunis, while the Southern crew, my group, were sent to the desert near Gabes.  During this time, we added a number of things to the Tunisian landscape.  One I believe I can still see on Google Earth. 

  We worked and trained with the Tunisian marines during this time and helped them to understand how some of the construction operations worked and could be improved.  While working one of these days I was sitting down and splicing some cable and came face to face with an African Horned Viper.  This thing literally came out from under the tire I was sitting, between my legs, and didn’t seem pleased at all.  Two young Tunisians saw this too and sprang into action faster than I could have imagined and sliced the viper up with their shovels before either the snake or I could react.  Afterwards their Lieutenant ‘Mohammad’ explained to me that the viper was deadly, and I told him that I fully approved of the action taken. 

  Later in this mission I was injured in a gasoline explosion that left me hospitalized for three weeks.  That’s another story but the take away here is that when it happened two people reached me first.  One of our guys Eric Peterson, and a Tunisian Marine, one of the young men that save my life before.  They helped me away from the fire and then onto an Ambulance back to camp.  In the next 30-45 minutes a helicopter from a Tunisian airbase in Gabes arrived to take me to their hospital for triage treatment.  A remarkable feat since they had only seven of these vehicles and they were all down for maintenance awaiting parts when the call went out.  The mechanics at the airbase made one working helo from seven broken ones fully knowing that an American soldier was injured.  A pair of Tunisian pilots with a cobbled-together aircraft risked their lives flying this machine, to save my own. 

  At the age of 41 this whole incident is now decades old, I have been through lots of counseling and still today suffer from PTSD from the fire itself.  But this is a relevant story for me.  I owe my life to two men that I can still recall their faces but not their names, and countless others that worked in concert and at the risk of death to save the life of an American Soldier.   Without that I would never have met my wife, had my sons or enjoyed the next 22 years in safety and health.

  My point in this story is to highlight a fault in our current political environment.  I owe my life to countless Muslim men and women that I would trust it with them again if I had to.  The rhetoric in the US had come to a point where the actions of radical elements are being used as a prod to hurt and alienate an entire group of people, even our own citizens.  We have a candidate for the US Presidency that advocates a blanket abuse of civil rights.  Rights that I gave 8 years of my life and almost my life in service to protect.  All of us in this democratic society are given one voice, one vote, to show the World what matters to us.  I will never tell another citizen how to vote but I will say this, even if you have grasped my leaning, I consider this ‘Right’ an obligation.  If you do not vote and use your voice at every opportunity, then why call yourself a citizen.  The obligation of every citizen in the US (or any other democratic society) is to be informed in the issues at hand and cast your vote every time.  When no best, or even good, choice presents itself; then your obligation is to choose the lesser of two evils and strive toward the best path possible.

If we knew what we were doing, it wouldn't be research.

We all have our side projects.  Some are as big as the normal projects we work on professionally, while others can be that little function that is elusive.  In some ways it's that little thing that itches us time and again. Yet, we scratch that itch to get a breather and sort of recharge the creative side of our work.

For some time now there have been a number of places where I had been instrumenting code for anything from performance to functional issues and I had thought (like several before me) that it would be nice to tell what program was running when the log was created.  Not surprising that Oracle has a couple of items that can help

• $$PLSQL_UNIT - Will tell you what package, procedure, function, or object method your log came from, it won't tell you what program within the package has executed.
• $$PLSQL_LINE - Tells you at what line the value, or log in my case, was recorded

What I was after is that subprogram reference inside of the package and I did some research and found a lot of code.  Not all of it is super performant, and some of it just does not work.

For methods that others have used or expressed here is a short list of references:

• https://community.oracle.com/thread/1042794?tstart=0
• https://community.oracle.com/thread/1051771?tstart=0
• https://community.oracle.com/thread/2487174?tstart=0

After these and several other attempts I decided that I should look into it myself and find a better way. and believe me.

(Demodivator copyright © www.despair.com)

In various attempts I found that my code was as big or slow as the other examples.  I'm a big fan of the Data Dictionary, and this is where most of my attempts were focused.  There are great resources in those views.

•  ALL_PROCEDURES - Gives a full list of public procedures, functions and those sub-programs in a package.
• ALL_ARGUEMENTS - Give a full list of the parameters for anything found in ALL_PROCEDURES
• ALL_IDENTIFIERS - If the previous views are a gold mine of information this one became the mother-lode.  It shows a variety of information on the package, beyond what is public.  If you spend some time and learn how to read and parse the data there is so much you can understand and then do.

I won't go into the all the attempts to get this information but, instead will simply show the best solution I have to date.  It's not perfect but it is fast and mostly accurate.

create or replace function f_get_package_program
(p_plsql_unit in varchar2
,p_plsql_line in number)
return varchar2
as
  l_retvar varchar2(1000);
begin
 
  select  ident
  into    l_retvar
  from(
    SELECT  a.OBJECT_NAME||'.'||a.NAME||':'||LINE ident
    from    SYS.ALL_IDENTIFIERS a
    where   object_type = 'PACKAGE BODY'
      and   object_name = p_plsql_unit
      and   type in ('PACKAGE','PROCEDURE','FUNCTION')
      and   usage = 'DEFINITION'
      and   line <= p_plsql_line
    order by line desc)
  where rownum = 1;
 
  return l_retvar;

end f_get_package_program;


Suffice it to say, wandering in the forest sometimes reveals a much shorter path

Cheers!