Oracle Built−in Packages- P44: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk PACKAGE BODY printer_access IS global variables for lock name and handle printer_lockname VARCHAR2 128 printer_lock printer_lockhandle VARCHAR2 128 FUNCTION get_printer_lockhandle RETURN VARCHAR2 IS BEGIN IF printer_lockhandle IS NULL THEN lockname printer_lockname lockhandle printer_lockhandle END IF RETURN printer_lockhandle END get_printer_lockhandle END printer_access As illustrated in the example it is a good idea to call ALLOCATE_UNIQUE only once for any given lockname per session. This is why the function stashes the lockhandle in the global variable printer_lockhandle and calls ALLOCATE_UNIQUE only if this global has not been initialized. There are two reasons for using this technique efficiency and avoidance of extra COMMITs. Remember that ALLOCATE_UNIQUE will always return the same handle for a given lockname and that it always performs a COMMIT. Thus best practice for using DBMS_LOCK includes calling ALLOCATE_UNIQUE only once per named lock. Locks allocated using ALLOCATE_UNIQUE can be viewed in the Oracle data dictionary via the DBMS_LOCK_ALLOCATED view. It is good practice to avoid the possibility of lockname conflicts between applications by adopting standard naming conventions for locknames. Just as Oracle reserves names that begin with ORA you may want to prefix locknames with your own company and application identifier string. The function The REQUEST function is used to acquire a lock in the mode specified by the lockmode parameter. If the lock cannot be acquired in the requested mode within the specified time the function call completes with a nonzero return value see the parameter table . The REQUEST function is overloaded on the first parameter which is used to identify the lock by either an INTEGER identifier or by a VARCHAR2 lockhandle. The release_on_commit parameter indicates whether the lock should persist across RDBMS transactions or be .
