Hi all,

I need to use dbms_lock inside a procedure to lock calling to another procedure in the code.

Following a procedure.
In the part of the "ELSE" i was asked to insert the locking.
I use first :dbms_lock.allocate_unique
second : dbms_lock.request(v_lockhandle);
third: v_release:=dbms_lock.release(v_lockhandle);

Do i use it right?
I didn't find in Metalink any example of using this feature in code pl/sql.

Thanks in advance,

Nir



PROCEDURE Relate_To_Case_Proc (
nCaseTypeID IN INTEGER,
strCommonValue IN VARCHAR2,
strCreationType IN VARCHAR2,
nAlertID IN NUMBER,
nMaxNumberOfCases IN NUMBER,
nCaseID OUT INTEGER
)
AS
nActive INTEGER;
nTotalNumbers INTEGER;
v_lockhandle VARCHAR2(150);
v_request number;
v_release number;
BEGIN
nCaseID := 0;
SELECT NUM_OF_CASES INTO nTotalNumbers
FROM CM_TOTAL_CASES;
IF nTotalNumbers >= nMaxNumberOfCases
THEN
nCaseID := -1;
RETURN;
END IF;
nActive := Is_Case_Type_Active(nCaseTypeID);
IF (nActive = 1) THEN
nCaseID := Is_Case_Exist(nCaseTypeID, strCommonValue);
IF (nCaseID > 0) THEN
Insert_Alert_In_Case(nCaseID, nAlertID, strCreationType);
ELSE

begin
dbms_lock.allocate_unique('Relate_To_Case_Proc', v_lockhandle);
while v_request <> 0 loop
v_request:= dbms_lock.request(v_lockhandle);
end loop;

-- double check
nCaseID := Is_Case_Exist(nCaseTypeID, strCommonValue);
if (nCaseID <= 0) then
nCaseID := Create_New_Case(nCaseTypeID, strCreationType, strCommonValue);
commit;
end if;
v_release:=dbms_lock.release(v_lockhandle);
EXCEPTION WHEN OTHERS THEN
v_release:=dbms_lock.release(v_lockhandle);

End;
Insert_Alert_In_Case(nCaseID, nAlertID, strCreationType);
END IF;
END IF;
END Relate_To_Case_Proc;
/