Re: Autonomous Transactions Paradoxical? Advice? Comments?

Although I have implemented logging using autonomous transactions, I haven't worried as much about whether the autonomous transactions succeeds as the usefulness of the information that is being logged.

There are at least two uses for logged error messages:

(1) During development, it helps developers spot errors and so one would want to store informaton that comes from dbms_utility.format_error_stack and dbms_utility.format_call_stack.

(2) During production, no errors should need to be logged with normal use of the system. However, if there are any, then one needs information about who and where the error came from. This means storing user information as well as the information set with dbms_application_info. Errors at this stage imply someone is trying to change the system without going through the application or that the application has not been adequately tested.

Also, it seems to make sense to log the message and then raise it again so that the user interface can handle the error with a friendly message for the user. This would imply that the java code should be advised of any error so it could modify the user interface appropriately.

As to the usefulness of autonous transactions, they certainly make logging error messages simpler to code. The error handler which you replaced apparently did not use autonomous transactions and so it had to juggle rollbacks and commits.

Frank Hubeny

Doug Cowles wrote:

> We have a situation where some java code with jdbc will be calling a
> PL/SQL package procedure which does a lot of database updates.
> The package will not have any commits in it, so that the java code can
> make the call wether to rollback everything the package procedure did
> or not. Problem is, the way the procedure was originally coded in
> it's exception handler was that if it ran into a problem, it would
> rollback itself, and then write an error record to an error table and
> then commit. Now we don't want the procedure to rollback or commit
> anything, but we need those error codes stored in the database
> somehow. So the idea came up an an autonomous transaction procedure
> that took the error strings as arguments and committed them to the
> database independently of package procedure. But upon thinking about
> it more, the autonomous transaction could fail as well, (missing
> grant, unable to allocate extent etc.,). meaning that an error code
> has to go back to the java code anyhow, leaving me right back where I
> started. So now I'm wondering a couple things.. 1) Am I being just a
> little too picky? 2) What are these things good for? (autonomous
> transactions). Any similar thoughts, experiences or comments?
> Right now, I am leaning toward bundling the errors in list object of
> some kind and passing it back to the java code so that it can write it
> to the database independently. It doesn't seem there would be any
> advantage to using the autonomous error writting procedure...
>
> - Dc.
Received on Sun Aug 06 2000 - 00:00:00 CDT