Countdown Timer Part II

Once before I had a countdown (well, countup) timer to mark the time since my last production incident (one where I screwed something up). It was a way to poke fun at myself but also a steady reminder to be careful.

In that vain, I've created a new one marking the consecutive time I've been employed! As noted in my previous post, I've had difficulty holding on to jobs this past year. Three times in fact. Fired once, laid off twice and brought back twice (by the same company).

Here's to not having to reset this effin clock in 2009!

Cheers!

SQL Objects vs. PL/SQL Tables

So I was writing a small procedure to loop through a procedure's arguments (all_arguments).

After getting it working, I then began to port it to packaged code, which is where I ran into a little problem.

Here's the initial statement:

DECLARE
  TYPE r_records IS RECORD
  (
    owner VARCHAR2(30),
    package_name VARCHAR2(30),
    procedure_name VARCHAR2(30),
    overload VARCHAR2(40),
    argument_name VARCHAR2(30),
    sequence INTEGER,
    in_out VARCHAR2(9)
  );
  TYPE t_records IS TABLE OF R_RECORDS INDEX BY BINARY_INTEGER;
  l_records T_RECORDS;
BEGIN
  SELECT
    owner, 
    package_name, 
    object_name,
    overload,
    argument_name,
    sequence, 
    in_out 
  BULK COLLECT INTO l_records
  FROM dba_arguments
  WHERE package_name = 'DBMS_UTILITY'
    AND object_name IN ( 'COMPILE_SCHEMA', 'INVALIDATE' )
  ORDER BY owner, package_name, object_name, position;
END;
/

Runs fine.

My first step to refactor was to use the TYPE declaration in the package header. But you can't do that (little rusty on pl/sql tables). So I created SQL Objects or User Defined Types (UDT).

CREATE TYPE r_procedure_arguments AS OBJECT
(
  owner VARCHAR2(30),
  package_name VARCHAR2(30),
  procedure_name VARCHAR2(128),
  overload VARCHAR2(40),
  argument_name VARCHAR2(30),
  position INTEGER,
  sequence INTEGER,
  in_out VARCHAR2(9)
);
/
show errors

CREATE TYPE t_procedure_arguments AS TABLE OF R_PROCEDURE_ARGUMENTS;
/
show errors

Then I rewrote the anonymous block to use the UDT.

CJUSTICE@ELEVEN>DECLARE
  2    l_records T_PROCEDURE_ARGUMENTS;
  3  BEGIN
  4    SELECT
  5      owner,
  6      package_name,
  7      object_name,
  8      overload,
  9      argument_name,
 10      position,
 11      sequence,
 12      in_out 
 13    BULK COLLECT INTO l_records
 14    FROM all_arguments;
 15  END;
 16  /
  FROM all_arguments;
  *
ERROR at line 14:
ORA-06550: line 14, column 3:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

not enough values? OK, let's go through it. There are 8 "columns" in the defined object and 8 in the SELECT clause. Hmmm...I tried adding some extra values at the end:

DECLARE
  l_records T_PROCEDURE_ARGUMENTS;
BEGIN
  SELECT
    owner,
    package_name,
    object_name,
    overload,
    argument_name,
    position,
    sequence,
    in_out, 1, 2, 3, 4
  BULK COLLECT INTO l_records
  FROM all_arguments;
END;
/

Same result, not enough values.

I spent the next hour toggling between gmail, facebook and meebo thinking about the problem.

Wait! You have to "cast" the values from the SELECT clause!

CJUSTICE@ELEVEN>DECLARE
  2    l_records T_PROCEDURE_ARGUMENTS;
  3  BEGIN
  4    SELECT
  5      R_PROCEDURE_ARGUMENTS( owner,
  6      package_name,
  7      object_name,
  8      overload,
  9      argument_name,
 10      position,
 11      sequence,
 12      in_out )
 13    BULK COLLECT INTO l_records
 14    FROM all_arguments;
 15  END;
 16  /

PL/SQL procedure successfully completed.

Duh.

So I post it here so next time I don't waste an hour trying to remember why I can't get it to work.

This is the LAST time...

On Friday I was furloughed...and today I was invited back. I start tomorrow.

Wow is really all I can say. Perhaps I should write a book or something? These past couple of years certainly qualify as book worthy. :)

The Tampa Timeline

I moved to Tampa a little over two years ago. To say it's been hectic would be a gross understatement. Here's the events myself and my family have been through in that short time:

November 27, 2006 - Begin work at WellCare as a contractor.
December 2, 2006 - Closed on Gainesville house
December 17, 2006 - Purchased Tampa house
December 26, 2006 - Rolled into a fulltime position at WellCare in the Datawarehouse group
January 4, 2007 - New CIO announced at WellCare
April 21, 2007 - Kate gives the Reaper the finger Summer 2007 - Awarded on-the-spot bonus, only to be *PIPped 2 days later
August 6, 2007 - Sent a "manifesto" to my CIO August 14, 2007 - Started this blog
October 24, 2007 - FBI Raids WellCare with 200 agents
January 26, 2008 - CEO and CFO resign from WellCare
April 24, 2008 - Gave my first professional presentation on Application Express
May 22, 2008 - I write a short statement on my blog that WellCare is doing layoffs
May 27, 2008 - I am fired from WellCare, with cause, for posting company "confidential" information
May 31, 2008 - I get my official offer from Revolution Money as a contractor June 15, 2008 - My paternal Grandfather passes away at 89
July 27, 2008 - I'm let go from Revolution Money
September 2, 2008 - I start as a fulltime employee at Revolution Money
December 26, 2008 - I'm furloughed from Revolution Money
December 29, 2008 - I'm asked to come back to Revolution Money.
April 8, 2009 - I resign and am then escorted from the premises.
April 10, 2009 - I start my new job.
October 16, 2009 - I get fired let go again.
November 3, 2009 - Became an Oracle ACE

What have I learned in this time?
1. Don't go into the office. If you boss says he needs a word, say you have to go to the bathroom and run for your car.
2. Leave early. If layoffs are coming and you aren't there, they can't let you go!
3. Don't check email while on vacation.
4. Life is rough sometimes, but you have to roll with it.
5. Have fun. We spend more time with those at work than we do with our families. Enjoy yourself while you are there. I got to play ping pong everyday (after 5 naturally) at Revolution Money with my Arch Nemesis. I won two times...out of a thousand games. But it was fun.

*Performance Improvement Plan