#

| Appendix C Built-In Packages |

## C.7 DBMS_LOCK

The DBMS_LOCK package provides you with access to the Oracle Lock Management (OLM) services. With OLM, you can request a lock of a particular type, assign it a name that can then be used as a handle to this lock, modify the lock, and even release the lock. A lock you create with the DBMS_LOCK package has all the functionality of a lock generated by the Oracle RDBMS, including deadlock detection and view access through SQL*DBA and the relevant virtual tables.

### C.7.1 The ALLOCATE_UNIQUE procedure

The ALLOCATE_UNIQUE procedure allocates a unique lock handle for the specified lock name. The specification is:

>     PROCEDURE DBMS_LOCK.ALLOCATE_UNIQUE
>        (lockname IN VARCHAR2,
>         lockhandle OUT VARCHAR2,
>         expiration_secs IN INTEGER DEFAULT 864000);

### C.7.2 The CONVERT function

The CONVERT function converts a lock from one type or mode to another. The specifications are:

>     FUNCTION DBMS_LOCK.CONVERT
>        (id IN INTEGER,
>         lockmode IN INTEGER,
>         timeout IN NUMBER DEFAULT MAXWAIT)
>     RETURN INTEGER;

>     FUNCTION DBMS_LOCK.CONVERT
>        (lockhandle IN VARCHAR2,
>         lockmode IN INTEGER,
>         timeout IN NUMBER DEFAULT MAXWAIT)
>     RETURN INTEGER;

The function returns the status of the attempt to change the mode, as shown below:

0

  Success.

1

  Timeout. The lock could not be converted within the specified number of seconds.

2

  Deadlock. In this case, an arbitrary session will be rolled back.

3

  Parameter error.

4

  The session does not own the lock specified by lock ID or the lock handle.

5

  Invalid lock handle. The handle was not found on the DBMS_LOCK_ALLOCATED table.

### C.7.3 The RELEASE function

The RELEASE function releases the specified lock. This specifications are:

>     FUNCTION DBMS_LOCK.RELEASE (id IN INTEGER) RETURN INTEGER;
>     FUNCTION DBMS_LOCK.RELEASE (lockhandle IN VARCHAR2) RETURN INTEGER;

In both cases, the RELEASE function returns a status with one of four values:

0

  Successful release of lock

3

  Error in the parameter passed to release

4

  Session does not own lock specified by ID or lock handle

5

  Illegal lock handle

### C.7.4 The REQUEST function

The REQUEST function requests a lock of the specified mode. The specifications are:

>     FUNCTION DBMS_LOCK.REQUEST
>        (id IN INTEGER,
>         lockmode IN INTEGER DEFAULT X_MODE,
>         timeout IN NUMBER DEFAULT MAXWAIT,
>         release_on_commit IN BOOLEAN DEFAULT FALSE)
>     RETURN INTEGER;

>     FUNCTION DBMS_LOCK.REQUEST
>        (lockhandle IN VARCHAR2,
>         lockmode IN INTEGER DEFAULT X_MODE,
>         timeout IN NUMBER DEFAULT MAXWAIT,
>         release_on_commit IN BOOLEAN DEFAULT FALSE)
>     RETURN integer;

The function returns the status of the attempt to obtain the lock; the codes are identical to those shown above for the convert function.

### C.7.5 The SLEEP procedure

The SLEEP procedure suspends the current session for a specified period of time (in seconds). The specification is:

>     PROCEDURE DBMS_LOCK.SLEEP (seconds IN NUMBER);

C.6 DBMS_LOB (PL/SQL8 Only) |  | C.8 DBMS_MAIL

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.