Trigger Exception propagation??

Trigger Exception propagation??

Post by Noodl » Sun, 31 Dec 1899 09:00:00



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

 
 
 

Trigger Exception propagation??

Post by Javier Morale » Sun, 31 Dec 1899 09:00:00


yes, the PL code may caught that exception, so you probably should raise an
especified exception (see raise_application_error) for you to be easier to
catch it in your PL code.



Quote:> 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


 
 
 

1. PACKAGEs; exception propagation

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.

2. Help: Database Location after installation

3. Propagation in Triggers

4. Bitwise operations and indexing

5. Ignoring trigger exceptions..

6. SB+ & Masterpack - Atlanta Ga

7. Trigger Causing Fatal Exception Help

8. Insert Trigger Issue when exception occurs in transaction

9. Exception Caused by Trigger and Primary Key

10. exception in trigger with big sql

11. No exception raised on error in Oracle Trigger