On one of these views, the update trigger needs to actually do an update and an insert on the source table: it must update the existing row to mark it as "closed", and then insert a new row to take its place. In this way the view hides the fact that the table keeps a history of changes.
[Full Article]
Note this caveat: Brian Camire, commenting on "On the false sense of security with PL/SQL based implementation of business rules - and what to do about it" by Lucas Jellema
ReplyDeleteJeff, you saved my butt on this one, couldn't figure out the commit thing until I saw your post. Thanks a million!
ReplyDelete-Jim
This was very helpful to me. Thanks for your efforts.
ReplyDeleteMinor bug: I think v_lock_status needs to be declared in procedure "release_lock".
Thanks, Kurt. I'll correct it.
ReplyDeleteHi Jeff,
ReplyDeleteWe are calling from the oracle form DB package to lock and release the lock and sometimes DBMS_LOCK.release returns sometimes 4 after releasing the lock
All I found in Oracle documentation that it means: Do not own lock specified by id or lockhandle.
I didn’t find any reason why it could happen. Looks like the session id which is trying to release lock Is not an owner but we have only one form running.
Any advice will be much appreciated.
Thanks,
Alex
Here is the code
Lock process:
---
cLockHandle:=ApplicationManager.lockprocess('MONITOR');
---
function LockProcess (cProcessName in varchar2,nTimeout in integer := 0,bReleaseOnCommit in boolean := false,nExpirationSecs in number := 0) return varchar2 is
cLockHandle varchar2(128);
nReturnValue integer := null;
nLockMode integer := 6; --exclusive mode (ULX)
begin
dbms_lock.allocate_unique(upper(substr(cProcessName,1,128)), cLockHandle,nExpirationSecs);
nReturnValue := dbms_lock.request(cLockHandle, nLockMode , nTimeout, bReleaseOnCommit); --lockmode = 6 : exclusive mode (ULX)
insert into test values ('LockProcess cLockHandle '||cLockHandle||' cProcessName '||cProcessName ) ; commit ;
if nReturnValue=0 then
return cLockHandle;
elsif nReturnValue=1 then
PdError.Throw(4008,cProcessName,'1-timeout');
elsif nReturnValue=2 then
PdError.Throw(4008,cProcessName,'2-deadlock');
elsif nReturnValue=3 then
PdError.Throw(4008,cProcessName,'3-parameter error');
elsif nReturnValue=4then
PdError.Throw(4008,cProcessName,'4-already own');
elsif nReturnValue=5 then
PdError.Throw(4008,cProcessName,'5-illegal lockhandle');
else
PdError.Throw(4008,cProcessName,'6-Unknown');
end if;
end;
Release lock:
--------------------------------------------------------------------------------------------------
procedure ReleaseLockByProcess (cProcessName in varchar2) is
cLockHandle varchar2(128);
return_value integer := null;
begin
dbms_lock.allocate_unique(upper(substr(cProcessName,1,128)), cLockHandle);
insert into test values ('ReleaseLockByProcess cLockHandle '||cLockHandle||' cProcessName '||cProcessName) ;
ReleaseLockByHandle(cLockHandle);
end;
--------------------------------------------------------------------------------------------------
procedure ReleaseLockByHandle (cLockHandle in varchar2) is
nReturnValue integer := null;
begin
if cLockHandle is not null then
nReturnValue := dbms_lock.release(cLockHandle);
if nReturnValue != 0 then
PdError.Throw(4133,to_char(nReturnValue)); -- ,'Error in releasing the lock:'||to_char(nReturnValue));
end if;
end if;
end;
Hi Alexander,
ReplyDeleteThanks for your question.
Check the sequence in which you call dbms_lock.request and dbms_lock.release. If you try to release a lock that has already been released, or which was never requested by that session in the first place, I'd expect to see a return value of 4.
Jeff
Thanks for you reply Jeff!
ReplyDelete