Re: Can you prevent commits from a client application?
Date: Fri, 22 Aug 2008 12:34:02 -0700 (PDT)
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
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
CONSTRAINT "TRANSACTION_STOP_I" CHECK(1=2) DEFERRABLE INITIALLY DEFERRED) ; Table created.
insert into A values (1);
1 row created.
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.
1 row selected. Received on Fri Aug 22 2008 - 14:34:02 CDT