Re: PACKAGEs; exception propagation

ronald wrote:
>
> What's the best way to develop PL/SQL packages?
>
> As far as I'm concerned, developing PACKAGEs is a real pain in the *ss. Do
> you really have to pass the whole frikking body of the package in, whenever
> you
> change the code?
>
> Now, after every change, I do the following:
>
> DROP PACKAGE XXX;
>
> CREATE PACKAGE XXX AS
> ....
> END XXX;
>
> and then pass all the procedures and functions in:
>
> CREATE PACKAGE BODY XXX AS
> PROCEDURE P1 IS ....
> PROCEDURE P2 IS ....
> PROCEDURE P3 IS ....
> ...
> END XXX;
>
> This is extremely awkward and inflexible. Is there a way to have the entire
> package in consistent state, and then, when I change ONLY procedure P2, do
> something like CREATE OR REPLACE PROCEDURE
> XXX.P2 without recompiling the whole package? (I tried this, but Oracle
> considers XXX to be a schema name.)
>
> Second question: according to Oracle docs, when an exception is raised, and
> if PL/SQL cannot find a handler, the exception propagates. That is, "the
> exception reproduces itself in successive enclosing blocks until a handler
> is found or there are no more blocks to search. In the latter case, PL/SQL
> returns an UNHANDLED EXCEPTION error to the host environment".
>
> Now, if I have procedure P1 which calls procedure P2, can I propagate
> exceptions raised in P2 to P1, and not to host environment? If not,
> can somebody recommend some guidelines for dealing with sets of procedures
> and functions which call each other? Scratch tables?
> Functions returning status? Seems pretty primitive to me.

The point of packages (which I recommend for ALL plsql in a production system) is encapsulation. Thus you have for the package header

create or replace package xxx is

(and you do this only once - after all, how often do you change the calling interface to a packaged module).

Then for the actual code

create or replace package BODY xxx is

and THIS is the thing that you can change over time - and because people only get to the package code "through" the header, they need never know. Loosely speaking the package is publishing methods, and the package body implements them..

On exceptions, propagation is exactly the right thing to have. You can then catch errors at the right place. If an error is "correctable" or "ignorable" you catch the exception in the procedure that generated the error in the first place. If it cannot be handled locally, it 'bubbles' up until someone in the call chain is capable of taking care of it. If you need to share a common set of exceptions - define them all in a package header - then other routines can use them

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."