Rollback in form not always rolling back

Oracle Forms 6.0.5.33.0 on Windows 95
[Quoted] Oracle DBMS 8.0.5.22 on HP9000

A program unit in the form executes a stored procedure. The stored procedure has no commit or rollback. When the stored procedure communicates to the program unit through the use of an OUT parameter that there was an error condition, I want to perform a rollback, insert a message into a table, and commit. Something like this:

    my_stored_procedure (in_parm1, in_parm2, out_parm1, out_parm2);     if out_parm1 != 0 then

        msg_parm := 'error in my_stored_procedure';
        rollback;
        message_logging_stored_procedure (msg_parm);
        commit;
        raise get_out;

To test, I am forcing an error in the stored procedure by passing it an invalid parameter.

When I trace through the code in the program unit by using MESSAGE, the execution flow seems just fine; messages before and after the rollback and commit are displayed. The stored procedure adds a row that I would want to roll back when there's an error. A query from SQL*Plus shows the new row not appearing after the call to the stored procedure, not appearing after the rollback, but appearing after the commit.

Probably 8 times out of 10, the rollback after the error in the stored procedure does not seem to happen. Occasionally it does.

Is there a better way to roll back an update done by a stored procedure called from a form?

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 27 2000 - 22:44:26 CEST