One head, plus one body (slightly damaged)

We all know that two heads are better than one. Apparently a head without a body can be more useful than one with a broken body.

This train of thought was prompted by an interesting question on the PL/SQL Challenge earlier this week (October 6, 2011 - to be precise). The question posed the puzzle about what happens when you reference a constant defined in the specification of a PL/SQL package if there was an invalid body.

My initial thought was that it is quite valid to have a package specification without a body, and you can safely references types and constants in that specification. I incorrectly assumed that an invalid body wouldn't be a problem.

One reason why it fails is that a body can contain an initialisation section. That is you can have:

CREATE OR REPLACE PACKAGE plch_pkg

IS

   g_stuff   VARCHAR2 (10) := 'Stuff';

   PROCEDURE show_dummy;

END plch_pkg;

/

CREATE OR REPLACE PACKAGE BODY plch_pkg

IS

   PROCEDURE show_dummy

   IS

   BEGIN

      DBMS_OUTPUT.put_line ('Dummy');

   END;

   

BEGIN

  dbms_output.put_line('Start it up');

END plch_pkg;

/

exec dbms_output.put_line(plch_pkg.g_stuff);

This would output both "Start it up" and "Stuff". But of course it can only do that if the body is valid and can be executed by Oracle, which is why Oracle has to try to recompile an invalid package body and must throw an error if it fails.