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
change the code?
Now, after every change, I do the following:
DROP PACKAGE XXX;
CREATE PACKAGE XXX AS
and then pass all the procedures and functions in:
CREATE PACKAGE BODY XXX AS
PROCEDURE P1 IS ....
PROCEDURE P2 IS ....
PROCEDURE P3 IS ....
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.