CREATE OR REPLACE PACKAGE locking AS
	PROCEDURE acquire_lock(aResourceName IN VARCHAR2, read_only IN BOOLEAN, timeout IN NUMBER DEFAULT 60);
	PROCEDURE release_lock(aResourceName IN VARCHAR2);
END;
/

show errors;

CREATE OR REPLACE PACKAGE BODY locking AS

	PROCEDURE acquire_lock(aResourceName IN VARCHAR2, read_only IN BOOLEAN, timeout IN NUMBER DEFAULT 60)
	AS
		lock_handle VARCHAR2(100);
		lock_mode   INTEGER;
		lock_result INTEGER;

	BEGIN
		IF read_only THEN
			lock_mode := 4;
		ELSE
			lock_mode := 6;
		END IF;

		-- generate or reuse a lock handle for given named resource
		dbms_lock.allocate_unique(aResourceName, lock_handle);

		-- try to acquire the lock
		lock_result := dbms_lock.request(lockhandle        => lock_handle,
		                                 lockmode          => lock_mode,
		                                 timeout           => timeout,
		                                 release_on_commit => false);


		IF lock_result=0 THEN  -- no body locked 
			
			dbms_output.put_line(aResourceName||' successfully acquired ('||lock_handle||').');

		ELSIF lock_result=1 THEN
			raise_application_error(-20001, 'Timesout.');
		ELSIF lock_result=2 THEN
			raise_application_error(-20002, 'Deadlock!');
		ELSIF lock_result=3 THEN
			raise_application_error(-20003, 'Invalid parameters on call dbms_lock.request()!');
		ELSIF lock_result=4 THEN
			raise_application_error(-20004, 'Already locked!');
		ELSIF lock_result=5 THEN
			raise_application_error(-20005, 'Illegal lock handle!');
		ELSE
			raise_application_error(-20006, 'Unknown return code: '||lock_result);
		END IF;

	END;

	PROCEDURE release_lock(aResourceName IN VARCHAR2)
	AS
		userNestingLevel NUMBER;
		lock_handle VARCHAR2(100);
		lock_result INTEGER;
	BEGIN
		-- generate or reuse a lock handle for given named resource
		dbms_lock.allocate_unique(aResourceName, lock_handle);

		lock_result := dbms_lock.release(lock_handle);

		IF lock_result=0 THEN
			dbms_output.put_line(lock_handle||' successfully released.');
		ELSIF lock_result=3 THEN
			raise_application_error(-20003, 'Invalid parameters on call dbms_lock.request()!');
		ELSIF lock_result=4 THEN
			raise_application_error(-20004, 'Not owned by caller!');
		ELSIF lock_result=5 THEN
			raise_application_error(-20005, 'Illegal lock handle!');
		ELSE
			raise_application_error(-20006, 'Unknown return code: '||lock_result);
		END IF;

	END;
END;
/

show errors;