Rem
Rem $Header: util_lock_pkgbody.sql 08-oct-2004.13:02:34 itarashc Exp $
Rem
Rem util_lock_pkgbody.sql
Rem
Rem Copyright (c) 2004, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      util_lock_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    itarashc    10/04/04 - add release_on_commit 
Rem    jsadras     04/07/04 - added comments 
Rem    jsadras     04/05/04 - made allocate_unique autonomous txn 
Rem    jsadras     04/01/04 - Problem with commit in dbms_lock.allocate_unique 
Rem    jsadras     03/24/04 - jsadras_bug-3468233 
Rem    jsadras     03/18/04 - 
Rem    jsadras     03/17/04 - New Package to provide locking functionality 
Rem    jsadras     03/11/04 - Created
Rem


CREATE OR REPLACE PACKAGE BODY MGMT_LOCK_UTIL 
AS
   -- Autonomous function to allocate handle 
   -- since dbms_lock.allocate commits
   FUNCTION allocate_handle(p_lock_type     IN VARCHAR2,
                            p_lock_value    IN VARCHAR2
                           )
   RETURN VARCHAR2
   IS
   PRAGMA autonomous_transaction ;
   l_lock_handle VARCHAR2(128) ;
   BEGIN
       dbms_lock.allocate_unique(p_lock_type||':'||p_lock_value,
                                 l_lock_handle,
                                 LOCK_EXPIRATION_SECS) ;
       RETURN(l_lock_handle) ;
   END ;

   FUNCTION  get_lock(p_lock_type     IN VARCHAR2,
                      p_lock_value    IN VARCHAR2,
                      p_lock_mode     IN NUMBER,
                      p_timeout_secs  IN NUMBER   := DEFAULT_TIMEOUT_SECS ,
                      p_exception_id  IN NUMBER   := NULL,
                      p_exception_msg IN VARCHAR2 := NULL,
                      p_release_on_commit IN  BOOLEAN := TRUE)
   RETURN VARCHAR2
   IS
   l_lock_handle varchar2(128) ;
   l_status number ;
   resource_busy EXCEPTION  ;
   PRAGMA EXCEPTION_INIT(resource_busy,-54) ;
   BEGIN
       l_lock_handle := allocate_handle(p_lock_type,p_lock_value) ;    
       l_status :=   dbms_lock.request(l_lock_handle,p_lock_mode,
                                       p_timeout_secs,p_release_on_commit)  ;
       --
       -- If we already own the lock, convert to current lock mode. if current_lock_mode
       -- equals previous mode requested then it does not do anything. 
       -- Started getting status of 4 ( already own lock) after 
       -- allocate_handle was made a autonomous function.
       --
       IF l_status = 4 THEN
            l_status :=   dbms_lock.convert(l_lock_handle,p_lock_mode,p_timeout_secs) ;
       END IF ;
       IF  l_status != 0 THEN
         IF  p_exception_id BETWEEN -20999 and -20000 THEN
           raise_application_error(p_exception_id,nvl(p_exception_msg,
                     'Cannot acquire '||p_lock_type||' lock on '||p_lock_value) );
         ELSE
           raise resource_busy ;
         END IF ;
       END IF ;
       RETURN(l_lock_handle) ;
   END ;
   
  FUNCTION   get_exclusive_lock(p_lock_type    IN VARCHAR2,
                               p_lock_value    IN VARCHAR2,
                               p_timeout_secs  IN NUMBER   := DEFAULT_TIMEOUT_SECS ,
                               p_exception_id  IN NUMBER   := NULL,
                               p_exception_msg IN VARCHAR2 := NULL,
                               p_release_on_commit IN  BOOLEAN := TRUE)        
                               
  RETURN VARCHAR2
  IS
  BEGIN
        RETURN(get_lock(p_lock_type,p_lock_value,EXCLUSIVE_MODE,
               p_timeout_secs,p_exception_id,p_exception_msg,p_release_on_commit)) ;
  END ; 

  FUNCTION  get_share_lock(p_lock_type     IN VARCHAR2,
                           p_lock_value    IN VARCHAR2,
                           p_timeout_secs  IN NUMBER   := DEFAULT_TIMEOUT_SECS ,
                           p_exception_id  IN NUMBER   := NULL,
                           p_exception_msg IN VARCHAR2 := NULL,
                           p_release_on_commit IN  BOOLEAN := TRUE)
                           
  RETURN VARCHAR2
  IS
  BEGIN
        RETURN(get_lock(p_lock_type,p_lock_value,SHARE_MODE,
               p_timeout_secs,p_exception_id,p_exception_msg,p_release_on_commit)) ;
  END ;
  
  FUNCTION  release_lock(p_lock_handle IN VARCHAR2)
  RETURN NUMBER
  IS
  BEGIN
       return(dbms_lock.release(lockhandle=>p_lock_handle))  ;
  END ;
  
END ;
/
show err