Re: How to execute a SP at Forms and commit its changes

From: DA Morgan <damorgan_at_exxesolutions.com>
Date: Sat, 15 Mar 2003 11:53:51 -0800
Message-ID: <3E7384CF.12CBCA29_at_exxesolutions.com>


ariadnedbka wrote:

> Isn't there a way to COMMIT the changes to the table before calling the
> stored procedure?
> I need to do things independently. The procedure should be called only
> after the table is changed, and it should not be called if the changes
> to the table are not commited.
>
> Ariadne
>
> Originally posted by Da Morgan
> > ariadnedbka wrote:
> >
> > > I will try to explain better.
> > > I have to insert a record in a table, let's say TABLE1.
> > > After the record is insert sucessfully at the database (same for
> > update
> > > and delete), I have to call a stored procedure (let's say SP1)
> > that will
> > > update other tables based on the values just inserted at
> > TABLE1.
> > > The procedure SP1 returns an error code to the forms.
> > > If SP1 returns without any error, I have to COMMIT its
> > changes.
> > > If SP1 returns with error, I have to ROLLBACK.
> > > But in any case I cannot touch the changes made at TABLE1 before
> > running
> > > the stored procedure.
> > > Hope this is better :-)
> > > Thanks for your help
> > > Ariadne
> > > --
> > > Posted via
> > http://dbforums.com/http://dbforums.com
> >
> > Then I would suggest an AFTER-INSERT OR UPDATE OR DELETE
> > trigger on the
> > table. All activity is a single transaction, as it should be, and the
> > problem disappears. Just make sure that when the trigger calls
> > the second
> > procedure ... that procedure does not contain any commits or
> > rollbacks ...
> > in fact it sounds more like something for a function returning a
> > Boolean,
> > True/False, than something for a procedure.
> >
> Daniel Morgan
>
> --
> Posted via http://dbforums.com

Of course there is. But it seems to me that you are stubbornly trying to force a specific method of doing something in spite of advice that it is not the correct way to do it in Oracle.

If you wish to solicit advice and ignore it that is your business and you may proceed. But please don't keep trolling for another suggestion until you get one that agrees with your preconceived notion of how things "should" be.

What you have described is a single transaction. A single transaction should only have a single commit and/or rollback. Unless, of course, the point of this exercise is to destroy Oracle's scalability, performance, and the maintainability of your code.

Daniel Morgan Received on Sat Mar 15 2003 - 20:53:51 CET

Original text of this message