Re: Can you prevent commits from a client application?

On Aug 23, 12:19 am, DA Morgan <damor..._at_psoug.org> wrote:
> dean wrote:
> > On Aug 15, 8:29 pm, dean <deanbrow..._at_yahoo.com> wrote:
> >> On Jul 16, 3:30 pm, Kai Rode <nik..._at_web.de> wrote:
>
> >>>> Sorry I'm not being clear here - I would like do a series of updates
> >>>> and inserts, and then once all that is finished, turn off any ability
> >>>> to make changes. In other words, a run-time and dynamic change in the
> >>>> read-only state (such as with alter session).
> >>> My idea (haven't had time to test yet): Create a table with a deferred
> >>> constraint CHECK (1=0). Insert a row into that table, then let your testers
> >>> play. If anyone tries to commit, the commit will be prevented by the failed
> >>> checking of the deferred constraint.
> >> Interesting! I'll try it and post up either way whether it works or
> >> not.
>
> >> Thanks!
>
> >> -Dean
>
> > I've implemented and been using the new (check 1=0) constraint method
> > and it appears to be a good solution that I will maintain in
> > production code. DDL statements, truncates, and other sources of
> > commits have been caught and typically the transaction is rolled back
> > if there is any attempt to commit, while the statement itself also
> > appears to fail. For example doing a truncate does the following: (1)
> > Roll back uncommitted changes, including the insert into the table
> > that contains the check. (2) Provide an error message (see below). (3)
> > Statement fails.
>
> > CREATE TABLE A (DUMMY NUMBER);
>
> > Table created.
>
> > CREATE TABLE TRANSACTION_STOP (DUMMY NUMBER NOT
> >     NULL,
> >     CONSTRAINT "TRANSACTION_STOP_I" CHECK(1=2) DEFERRABLE
> >     INITIALLY DEFERRED) ;
>
> > Table created.
>
> > insert into A values (1);
>
> > 1 row created.
>
> > commit;
>
> > Commit complete.
>
> > insert into transaction_stop values (1); -- <- contains deferred
> > check(1=0) constraint
>
> > 1 row created.
>
> > truncate table A;
> > *
> > ERROR at line 1:
> > ORA-02091: transaction rolled back
> > ORA-02290: check constraint (GC_FEB08.TRANSACTION_STOP_I) violated
>
> > select * from A; -- Test if truncate succeeded.
>
> >      DUMMY
> > ----------
> >          1
>
> > 1 row selected.
>
> To some extent you have recreated the wheel.
>
> All currently supported versions of Oracle contain DDL triggers that
> can trap is block any or all DDL.
>
> Check it out:http://www.psoug.org/reference/ddl_trigger.html
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Thanks for that. I was trying to guard against other possibilities too - such as simply calling commit from a different part of the GUI, or procedures, etc. Received on Sat Aug 23 2008 - 03:05:40 CDT