Erotic Problem Solving

by Michael O'Neill
Posts on oraclenerd, Twitter, Facebook

Writing code well is hard enough. Does it have to be boring too?

Business problems can be boring. This is perpetually true for the professional responsible for authoring the code to solve them who is technical-expertise-first and business-expertise-second. Emotional motivation and individual energy is a tremendous factor in the results of any problem solver.

In my career, I have repeatedly faced boring problems and successfully mucked through them with boring solutions. Sometimes though, I have faced interesting problems and unsuccessfully met them with boring solutions. Failure required a twist in my thoughts and energy to regain the upper hand on achieving a solution. Here is an example:

How do you code an effective solution with an ineffective domain model? Sophisticated social applications require a complex network model. Network models are difficult enough to document and even harder to conceptualize beyond a certain complexity. And when doubt creeps in on a modeling effort, the problem solver's all too frequent retreat is into previously reliable modeling techniques. Even if those techniques are an ill-fit at best. In a recent application, the problem ahead of me was building an intricate and robust human trust, credibility and reputation engine.

I experienced project-crippling difficulty during the initial modeling efforts. My mind was constantly circling the boring drain of popular technical trust models, like public-private keys and centralized certificate authorities. Every model fell apart eventually. My models required hierarchies that meant nothing in the real world of human trust. My models introduced single points of dependency that do not exist in human interactions.

I took a step back. I ended the boring. I turned myself on.

I imagined a small community of nonmonogamous individuals and their social sexual lives. How did they decide to hook up? How did they decide to break up? Did they like another, or like-like another? Were the states of those feelings returned? How did they decide whether to exchange bodily fluids or not? What were the layers, causes and watersheds of lust, love, trust, sacrifice, distrust? How was deceit employed? Did absolute trust exist? How did feelings of love affect everything?

I was able to configure a myriad of practical models, all intertwined based on a community of people I made up with my pencil. Right or wrong about how people work, it didn't matter. Its complexity and the energy I was able to pour into understanding the possibilities was what was valuable. It was adventure that proffered understanding of the solution required for my work. My intricate thoughts expressed in an exciting context was an excellent conversation with everyone I knew, eager every one of them to have themselves chatted up about sex. Each person I included in the discussion introduced a valuable complexity I was able to model. I remembered the what ifs shared over a pint were the reason I do what I do for a living.

I am not advocating having multiple simultaneous sexual partners to get through a modeling effort. Well, alright I am, but that is beside the point. Approaching a complicated business modeling problem required that I stop circling the boring drain and leverage the energy of thinking of all the complexities of people, emotions and how they interact - something in which I already had knowledge, intuition and interest.

Never Use RAISE_APPLICATION_ERROR Again

By Michael O’Neill
http://twitter.com/cleverideanet (professional)
http://twitter.com/oraclenude (personal)
oraclenerd articles

If you write Oracle PL/SQL, you know what RAISE_APPLICATION_ERROR is. It is an abomination of hard-coding and poor practice. If you didn't know that, I'm sorry I was the one who told you. I've written and used extensively an ultra-simple framework to eliminate RAISE_APPLICATION_ERROR from my code forever.
Here's an example (assume 11gR2) of something we all know we can do:

begin  dbms_output.put_line(1/0); end;

This will throw an unhandled ORA-01476 exception. We could write some meaningful handling of that with this:

begin 
   dbms_output.put_line(1/0);
exception
   when zero_divide
   then
      dbms_output.put_line('zero divide exception caught');
end;

This coding is elegant because Oracle has conveniently predefined an exception named ZERO_DIVIDE and a corresponding pragma for us. Unfortunately, Oracle has only 22 predefined exceptions. What happens when I do this:

declare
   d date;
begin 
   d := to_date('2010-09-30', 'YYYY-MM-DD'); -- works
   dbms_output.put_line(d);
   d := to_date('12345-09-30', 'YYYY-MM-DD'); -- fails
   dbms_output.put_line(d);
end;

This will throw an unhandled ORA-01861 exception. My option to handle this is less than meaningful because this is not a predefined exception:

declare
   d date;
begin 
   d := to_date('2010-09-30', 'YYYY-MM-DD'); -- works
   dbms_output.put_line(d);
   d := to_date('12345-09-30', 'YYYY-MM-DD'); -- fails
   dbms_output.put_line(d);
exception
   when others
   then
      case sqlcode
         when -1861
         then
            dbms_output.put_line('literal does not match exception caught');
         else
            raise;
      end case;
end;

This leads me to the inevitable desire to create my own named exception and pragma, so I could have code that looks like this instead:

declare
   d date;
begin 
   d := to_date('2010-09-30', 'YYYY-MM-DD'); -- works
   dbms_output.put_line(d);
   d := to_date('12345-09-30', 'YYYY-MM-DD'); -- fails
   dbms_output.put_line(d);
exception
   when error.ora_literal_string_mismatch
   then
      dbms_output.put_line('literal does not match exception caught');
end;

Understanding this, creating my own ERROR package with a friendly named exception and pragma for ORA-01861 leads me to the pattern of how to handle my own application exceptions, namely defining an exception and pragma.

But how does this get RAISE_APPLICATION_ERROR out of my life? Consider the ERROR abbreviated package source I use (full source: error.pks and error.pkb):

create or replace package error is
 
   package_name constant varchar2(32) := 'error'; -- in case you want to change the package name
 
   -- application exceptions and pragmas
 
   (snip)
 
   not_one_based constant string(64) := package_name || '.app_not_one_based';
   app_not_one_based exception;
   pragma exception_init(app_not_one_based, -20004);
 
   sparsity_not_allowed constant string(64) := package_name || '.app_sparsity_not_allowed';
   app_sparsity_not_allowed exception;
   pragma exception_init(app_sparsity_not_allowed, -20003);
 
   parameter_cannot_be_null constant string(64) := package_name || '.app_parameter_cannot_be_null';
   app_parameter_cannot_be_null exception;
   pragma exception_init(app_parameter_cannot_be_null, -20002);
 
   string_too_large constant string(64) := package_name || '.app_string_too_large';
   app_string_too_large exception;
   pragma exception_init(app_string_too_large, -20001);
 
   application_exception constant string(64) := package_name || '.app_application_exception';
   app_application_exception exception;
   pragma exception_init(app_application_exception, -20000);
 
   -- rdbms exceptions and pragmas
 
   (snip)
 
   literal_string_mismatch constant string(64) := package_name || '.ora_literal_string_mismatch';
   ora_literal_string_mismatch exception;
   pragma exception_init(ora_literal_string_mismatch, -1861);
 
   (snip)
 
   procedure throw(p_exception in varchar2);
 
   procedure throw
   (
      p_exception in varchar2
     ,p_message   in varchar2
   );
 
end;

You can see several user-defined exceptions and pragmas as well as the ORA_LITERAL_STRING_MISMATCH used in the previous example. The full source has more defined, but is not relevant to understanding the concept I am presenting.

Notice there is just one (overloaded) method, THROW. THROW is what I use instead of RAISE_APPLICATION_ERROR.

So, instead of this:

declare
   s string(3) := 'abc';
begin
   if (instr(s,'b') > 0)
   then
      raise_application_error(-20000, 'I hate the letter b');
   end if;
end;

I use this:

declare
   s string(3) := 'abc';
begin
   if (instr(s,'b') > 0)
   then
      error.throw(error.application_exception, 'I hate the letter b');
   end if;
end;

On its surface this doesn’t seem terribly interesting or useful. Below the surface, several powerful advantages are gained:

• A single ERROR package encapsulates a schema’s application exceptions and pragmas, giving me a consistent SQLCODEs returned to my C# code.
• No more, remembering what number to use in RAISE_APPLICATION_ERROR.
• Easier to understand code
• I can effectively organize my exceptions without sprawling them throughout a schema’s packages
• I can extend the ERROR package (and I have) to do many more things like logging or default messages for exceptions without writing that into my schema’s application packages.

How does it work? Taking a look at the body for the THROW method reveals all:

procedure throw
(
   p_exception in varchar2
  ,p_message   in varchar2
) is
begin
   begin
      begin
         execute immediate ('begin raise ' || p_exception || '; end;');
         -- exception is raised and immediately trapped
      exception
         when ora_plsql_compilation_error then
            throw(error.exception_does_not_exist, p_exception);
      end;
     
   exception
      when others then
         if sqlcode between - 20999 and - 20000
         then
            raise_application_error(sqlcode, p_message);
            -- this is the best/only use of raise_application_error
            -- and eliminates the need in application code
         else
            raise;
            -- nothing extra to do for extra for exceptions outside raise_application_error range
         end if;
   end;
exception
   when others then
      raise; -- finally, bubbles up the original throw() call
end throw;

There are few caveats I have using this development pattern. I don’t consolidate every exception I write into my ERROR package, only those exceptions that I want to bubble up unhandled to my C# code. I don’t feel it is necessary to have the same ERROR package in every application schema. In other words I don’t evolve every incarnation of my ERROR package when I’m adding exceptions and pragmas to one schema’s ERROR package. Finally, my ERROR package has a multitude more bells and whistles I’m not sharing in this post for clarity’s sake. If you are interested in a more extended version of my ERROR, let me know via Twitter (@cleverideanet)

Copyright © 2010 Michael O'Neill
Published by Permission on oraclenerd

Everything is a Bit Bucket

By: Michael O'Neill
@oraclenude
oraclenude.crisatunity.com

In response to Chet's frustration over yet another encounter with a database agnostic, I wanted to contribute my first article to the oraclenerd franchise. My thoughts seemed too long for the comment stream.

I ascribe the kernel of thought behind "the database is a bit bucket" primarily to each and every database vendor that ever existed. Every database vendor, in an effort to persuade users of competitive products to adopt their product, has participated willingly in espousing some core aspect of how "same as the other guy" their product is in addition to whatever differentiation pitch they have.

Now, the generally weak-minded and lazy developer (yes, I think the majority of developers are in fact weak-minded and lazy) latches on to the vendor's selective "sameness" claims for professional and personal reasons. (full disclosure: I am both an Oracle DBA and .NET developer)

Professionally, because they are financially invested in writing third-party code not database code. To them, the less they spend learning and understanding the particulars of things like databases, operating systems, networks, human beings, etc. the better. Personally, because there is a dominate thread in the culture of developers to dismiss the database as interesting or meaningful. It is a form of heresy to show affection towards any platform in any specificity.

This is why Java's Big Lie of "write once, run any where" swoons so many. Java's Big Lie is analogous to "the database is a bit bucket" by declaring that even the language of software code should be as absolutely interchangeable as possible - even at the expense of being cost-effective or useful. There is an unquestioned faith that decoupling everything from everything is a good thing. This faith gives us code that is as far from the simplest thing that could work from the first moment writing the code is undertaken. It is a faith I reject. That's why I'm an ORACLENERD.

P.S. I know oraclenude and oraclenerd is confusing. It's supposed to be.