Can you prevent commits from a client application?
dean
least until a subsequent call to remove the alteration)? I'm using the
following statement to prevent commits in any procedures (ALTER
SESSION DISABLE COMMIT IN PROCEDURE), but I need a way to prevent
accidental commits from within a client application.
The reason I would like this is to allow testers to be able to
navigate around an application after a databridge has been run, and
poke around here and there in various datagrids, without risk of them
accidentally calling a function that has a commit in it.
Any thoughts appreciated.
Ana C. Dent
8eedf9...@b1g2000hsg.googlegroups.com:
Provide a READ ONLY schema
gazzag
Flashback database any use?
HTH
-g
dean
> dean <deanbrow...@yahoo.com> wrote in news:18ccb7fd-35ad-47a5-ac0d-
> 8eedf96fa...@b1g2000hsg.googlegroups.com:
Can you set read-only from a client program, at run time?
Steve Howard
Look up secure application roles, if you want to specify what a
certain application user can do under specific conditions.
dean
>
> - Show quoted text -
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).
Kai Rode
>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.
Ed Prochak
>
> 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).
This is still not clear how this will help testing.
Ed
dean
Interesting! I'll try it and post up either way whether it works or
not.
Thanks!
-Dean
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.
DA Morgan
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
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
dean
> 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.
Ana C. Dent
news:f161e0a0-d654-431d...@a1g2000hsb.googlegroups.com:
>
> 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.
>
If the application is coded to allow this, then you have bigger problems.
Are you aware that session A can not issue a COMMIT for session B?
DA Morgan
> 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.
What you just described is impossible and bring up the question of
whether you understand Oracle's transaction model. What is the basis
of your belief that such a thing is possible?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)