Re: Rollback in form not always rolling back

djsnyder_at_my-deja.com wrote in message <8tcpf8$9ha$1_at_nnrp1.deja.com>...
>Oracle Forms 6.0.5.33.0 on Windows 95
>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;
> end if;
>
>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?

Rollback is PL/SQL and is mapped to 'Clear_Form' without any parameters. Perhaps the reason its not working is that the Form doesn't recognise there is anything to rollback.

Try using:
Forms_DDL('ROLLBACK');
Alternatively, make the form believe there is a change outstanding by harmlessly changing a base table item, eg. :block.item := :block.item;

A cleaner approach for larger systems is to make error handling independent of the form, so that errors are logged asynchronously, eg. via an Oracle pipe. Received on Mon Oct 30 2000 - 04:42:22 CET