Re: Sub-Transaction within PL/SQL

From: Paolo Ghisotti <p.ghisotti_at_barilla.it>
Date: 1998/01/30
Message-ID: <6asrgu$ogj$1@news.IT.net>#1/1

N. Dirks wrote in message <34CE096C.7215_at_sundn.de>...
>Hi,
>
>for an Oracle PL/SQL-Project it would be fine
>to create sub-transactions within PL/SQL-
>Methods.
>These sub-transactions would be responsible
>for logging method-irregularities (Errors, Exceptions,
>hurt privileges, etc.) into a special logging-
>table and commiting these INSERTS without
>affecting the state of our main-transaction
>(without COMMIT !!!! on our main-transaction).
>
>Is it possible to create a sub-transaction
>within PL/SQL-Methods ? And how do I create them ?
>
>Thanks
> Nico Dirks

Hi Nico,

I think Oracle doesn't support sub-transaction. You can define savepoints and partially rollback a transaction to a defined savepoint, but COMMIT always ends and commits the whole transaction. For logging purposes you can send your messages through a PIPE (see the DBMS_PIPE package)
to a different process that starts and commits a separate transaction.

I hope that this can be useful to you.

Regards,

Paolo Ghisotti
p.ghisotti_at_barilla.it Received on Fri Jan 30 1998 - 00:00:00 CST