PACKAGEs; exception propagation

PACKAGEs; exception propagation

Post by ronal » Sun, 09 Jun 2002 18:49:13



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.

 
 
 

PACKAGEs; exception propagation

Post by Connor McDonal » Sun, 09 Jun 2002 19:09:30



> 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..."

 
 
 

PACKAGEs; exception propagation

Post by ronal » Sun, 09 Jun 2002 19:22:09



[...]

Thanks Connor, but -- how should I put it -- this was a generic
answer.

I am a seasoned IT professional (relatively new to Oracle
though),  and I _really_ do know everything there is to be
known about encapsulation, and "bubbling up" of exceptions.
(BTW why can't PL/SQL exceptions propagate like Java
exceptions?)

I will clarify:

1) my question about packages was directed mainly towards
development. It's too awkward having to compile the _entire_
package body each time when I make even the smallest change in
the smallest procedure in the package body.

2) my question about exceptions was directed towards
propagating exceptions from a procedure P2 to the _calling_
procedure P1. AFAIU, when there is an exception E in
procedure P2, P2 exits to host environment. Is there a
way to reraise/resend E to the calling procedure P1?

If answers to both of those question is no, then sadly
I have to state that PL/SQL needs some serious
renovation.

 
 
 

PACKAGEs; exception propagation

Post by Andy Hassal » Sun, 09 Jun 2002 21:02:36



>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 procedures back up the call stack are surrounding blocks, and the
exceptions propagate to them. It's only when you get back to the top level that
it comes out as ORA-06510: PL/SQL: unhandled user-defined exception.

create or replace package ajhexceptiontest
as
   procedure p1;
   procedure p2;
   e_exp exception;
end ajhexceptiontest;
/
create or replace package body ajhexceptiontest
as
  procedure p1 is
  begin
    p2();
  exception
    when e_exp then
      dbms_output.put_line('Exception e_exp caught in p1');
  end p1;  

  procedure p2 is
  begin
     dbms_output.put_line('Raising exception e_exp in p2');
     raise e_exp;
  end p2;

end ajhexceptiontest;
/

Package created.

Package body created.

SQL> show errors
No errors.

SQL> execute ajhexceptiontest.p2;
Raising exception e_exp in p2
BEGIN ajhexceptiontest.p2; END;

*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "TEST.AJHEXCEPTIONTEST", line 14
ORA-06512: at line 1

SQL> execute ajhexceptiontest.p1;
Raising exception e_exp in p2
Exception e_exp caught in p1

--

http://www.andyhsoftware.co.uk/space | disk usage analysis tool

 
 
 

PACKAGEs; exception propagation

Post by Sybrand Bakke » Sun, 09 Jun 2002 23:24:18




>[...]

>Thanks Connor, but -- how should I put it -- this was a generic
>answer.

>I am a seasoned IT professional (relatively new to Oracle
>though),  and I _really_ do know everything there is to be
>known about encapsulation, and "bubbling up" of exceptions.
>(BTW why can't PL/SQL exceptions propagate like Java
>exceptions?)

>I will clarify:

>1) my question about packages was directed mainly towards
>development. It's too awkward having to compile the _entire_
>package body each time when I make even the smallest change in
>the smallest procedure in the package body.

>2) my question about exceptions was directed towards
>propagating exceptions from a procedure P2 to the _calling_
>procedure P1. AFAIU, when there is an exception E in
>procedure P2, P2 exits to host environment. Is there a
>way to reraise/resend E to the calling procedure P1?

>If answers to both of those question is no, then sadly
>I have to state that PL/SQL needs some serious
>renovation.

1 recompiling a package body usually takes less than a minute. Are you
really so impatient that you consider that to be 'awkward'?
Using that word alone for me results in disbelief of your
self-qualification as 'seasoned IT-professional'. You may be an
IT-professional who seems to know everything better, see your last
remark.

2 You didn't read Connor's reply.
If you define exception in a package header you can trap them from a
calling procedure. If the calling procedure is outside the package you
can trap them in the calling procedure using
<package_name>.<exception_name> That might be 'awkward' again, but
that's the way it is.

Time to do some reading to get acquainted with PL/SQL, I guess,
probably the book of Steven Feuerstein. At least time to do some
reading *before* you have your verdict ready and start to blame a
product which many people can work with.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

 
 
 

PACKAGEs; exception propagation

Post by ronal » Mon, 10 Jun 2002 01:16:34



Quote:> 1 recompiling a package body usually takes less than a
>  minute. Are you really so impatient that you consider that
> to be 'awkward'?

The need to compile _entire_ package body, for just a
minor change in a procedure, _is_ awkward.

Right now there are 54 procedures in my package.
For each and every tiny change I have to compile
them all.

That's suboptimal. And that's the reason why dependencies,
MAKE, ant... were invented.

Quote:> 2 You didn't read Connor's reply.
> If you define exception in a package header you can trap them from a
> calling procedure. If the calling procedure is outside the package you
> can trap them in the calling procedure using
> <package_name>.<exception_name> That might be 'awkward'
> again, but that's the way it is.

What about the following scenario: I have procedures
P1 and P2 which are
standalone procedures, ie do not belong to any package.
Procedure P1 calls P2. Procedure P2 raises an Oracle
exception. Ultimately, P2 exits to host environment,
and NOT to P1.

What I would like to do, is to catch all Oracle exceptions
(raised in called procedure P2) in the calling procedure
P1, so that if P2 fails, P1 continues with processing.
But unfortunately that's not possible. Whenever I
encounter an exception in P2, calling procedure P1
exits as well.

 
 
 

PACKAGEs; exception propagation

Post by Niall Litchfiel » Mon, 10 Jun 2002 01:40:18




> > 1 recompiling a package body usually takes less than a
> >  minute. Are you really so impatient that you consider that
> > to be 'awkward'?

> The need to compile _entire_ package body, for just a
> minor change in a procedure, _is_ awkward.

> Right now there are 54 procedures in my package.
> For each and every tiny change I have to compile
> them all.

> That's suboptimal. And that's the reason why dependencies,
> MAKE, ant... were invented.

So how long does it take to execute this sub-optimal step? as long as a
minute? frankly if a minutes worth of time is awkward then what on earth you
are doing in the IT world is beyond me.

--
Niall Litchfield
Oracle DBA
Audit Commission UK

 
 
 

PACKAGEs; exception propagation

Post by Sybrand Bakke » Mon, 10 Jun 2002 02:40:40




> > 1 recompiling a package body usually takes less than a
> >  minute. Are you really so impatient that you consider that
> > to be 'awkward'?

> The need to compile _entire_ package body, for just a
> minor change in a procedure, _is_ awkward.

> Right now there are 54 procedures in my package.
> For each and every tiny change I have to compile
> them all.

> That's suboptimal. And that's the reason why dependencies,
> MAKE, ant... were invented.

> > 2 You didn't read Connor's reply.
> > If you define exception in a package header you can trap them from a
> > calling procedure. If the calling procedure is outside the package you
> > can trap them in the calling procedure using
> > <package_name>.<exception_name> That might be 'awkward'
> > again, but that's the way it is.

> What about the following scenario: I have procedures
> P1 and P2 which are
> standalone procedures, ie do not belong to any package.
> Procedure P1 calls P2. Procedure P2 raises an Oracle
> exception. Ultimately, P2 exits to host environment,
> and NOT to P1.

> What I would like to do, is to catch all Oracle exceptions
> (raised in called procedure P2) in the calling procedure
> P1, so that if P2 fails, P1 continues with processing.
> But unfortunately that's not possible. Whenever I
> encounter an exception in P2, calling procedure P1
> exits as well.

I see your ignorance of pl/sql is as big as is your inability to read and
your arrogance.
What you state in your last section is ultimately rubbish and simply NOT
true, as I explained to you before.
If you don't trap exceptions in P2 they are propagated to P1. That is
defined in any PL/SQL book and in the PL/SQL manual.
So what's wrong with you?
Are you blind?
Brainwashed by Microsoft?
Then please don't ask assistance here.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

 
 
 

PACKAGEs; exception propagation

Post by Sebastiano Pil » Tue, 11 Jun 2002 04:23:12



> 1) my question about packages was directed mainly towards
> development. It's too awkward having to compile the _entire_
> package body each time when I make even the smallest change in
> the smallest procedure in the package body.

When you change a single byte in a Java source file, do you have to
recompile the whole source file or just the line containing your change?

Sebastiano Pilla

 
 
 

PACKAGEs; exception propagation

Post by Vladimir M. Zakharyche » Tue, 11 Jun 2002 19:46:53


Ahem....

1. When you change a single line of code in your Java class that
exports some 50 methods - do you recompile this line only or the
whole class? Compare compile times of single Java class with 50
complex methods, and of single PL/SQL package body with 50
complex procedures - I doubt you'll see big difference in most
cases.
Also, DO NOT recompile the package specification each time you
change implementation of a procedure (if this change does not
alter the procedure call specification). In fact, doing this will
cascadingly invalidate all dependent packages and/or procedures
and you will have to recompile them too. You should only
recompile the package BODY as this operation does not invalidate
dependent objects even if unsuccessful.
2. Exceptions in called procedures DO propagate to CALLERS first, and
if unhandled there, they bubble up the chain until they are handled, and
an Oracle error corresponding to the initial exception will be finally thrown
to the host if none of the chain members caught and handled it. Can you
provide an example that exhibits different behavior?

--

Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.



> > 1 recompiling a package body usually takes less than a
> >  minute. Are you really so impatient that you consider that
> > to be 'awkward'?

> The need to compile _entire_ package body, for just a
> minor change in a procedure, _is_ awkward.

> Right now there are 54 procedures in my package.
> For each and every tiny change I have to compile
> them all.

> That's suboptimal. And that's the reason why dependencies,
> MAKE, ant... were invented.

> > 2 You didn't read Connor's reply.
> > If you define exception in a package header you can trap them from a
> > calling procedure. If the calling procedure is outside the package you
> > can trap them in the calling procedure using
> > <package_name>.<exception_name> That might be 'awkward'
> > again, but that's the way it is.

> What about the following scenario: I have procedures
> P1 and P2 which are
> standalone procedures, ie do not belong to any package.
> Procedure P1 calls P2. Procedure P2 raises an Oracle
> exception. Ultimately, P2 exits to host environment,
> and NOT to P1.

> What I would like to do, is to catch all Oracle exceptions
> (raised in called procedure P2) in the calling procedure
> P1, so that if P2 fails, P1 continues with processing.
> But unfortunately that's not possible. Whenever I
> encounter an exception in P2, calling procedure P1
> exits as well.

 
 
 

1. Trigger Exception propagation??

I need to integration test a simple PL/SQL program that performs two deletes.
Within the cursor loop it performs the deletes and then commits the
transaction.  The exception clause is within the loop ( just in case) and
performs a rollback of those specific delete transactions.  I realize
performing the commit within the loop is kind of non-standard.

I don't want to change the PL code just to test it.  If a put a "before delete"
trigger on one of the tables to trap a specific data point and raise the
exception there in the trigger; would that trigger exception propagate to the
running PL statement and be caught in the PL's exception clause?  

Unfortunately, I'm home with a sick child today without my docs or else I would
just logon and test my theory.

TIA,
Cliff

2. How to process an OLAP cube from T-SQL

3. Executing DTS package from ASP throws exception

4. Can I use SQL*Net with Oracle Personal 8 instead of Net8?

5. Exceptions running DTS package from C#

6. Tools to modify table structure

7. Package fails with the exception file specified..

8. Crystal Reports work with Delphi/BDE SQL ??

9. utl_http packages gives undefined exception

10. Data Propagation

11. permission propagation

12. Change Tracking w/ Schedule Propagation

13. Propagation to a Standby db