Oracle PL/SQL Language Pocket Reference- P28

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
31
lượt xem
10
download

Oracle PL/SQL Language Pocket Reference- P28

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle PL/SQL Language Pocket Reference- P28: This pocket guide features quick-reference information to help you use Oracle's PL/SQL language. It includes coverage of PL/SQL features in the newest version of Oracle, Oracle8i. It is a companion to Steven Feuerstein and Bill Pribyl's bestselling Oracle PL/SQL Programming. Updated for Oracle8, that large volume (nearly 1,000 pages) fills a huge gap in the Oracle market, providing developers with a single, comprehensive guide to building applications with PL/SQL and building them the right way. ...

Chủ đề:
Lưu

Nội dung Text: Oracle PL/SQL Language Pocket Reference- P28

  1. The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. Previous: C.2 Appendix C Next: C.4 DBMS_DDL DBMS_ALERT Built-In Packages C.3 Oracle AQ, the Advanced Queueing Facility Oracle8 offers the Oracle Advanced Queuing facility (Oracle AQ) which implements deferred execution of work. There are two packages you will use to implement advanced queuing: DBMS_AQ, which contains the queuing procedures themselves, and DBMS_AQADM, which lets you perform administrative tasks. They make extensive use of PL/SQL record structures, as you will see in the individual program interfaces below. For more detail on these records and how to manipulate their contents, see Oracle Built-in Packages. C.3.1 DBMS_AQ (PL/SQL 8 Only) The DBMS_AQ package provides an interface to the messaging tasks of Oracle AQ. To use these procedures, you must have been granted the new role, AQ_USER_ROLE. C.3.1.1 The ENQUEUE procedure The ENQUEUE procedure adds a message to an existing message queue. The target message queue must have had enqueuing enabled previously via the DBMS_ AQADM.START_QUEUE procedure. The specification is: PROCEDURE DBMS_AQ.ENQUEUE (q_schema IN VARCHAR2 DEFAULT NULL q_name IN VARCHAR2, corrid IN VARCHAR2 DEFAULT NULL, transactional IN BOOLEAN:= TRUE, priority IN POSITIVE DEFAULT 1, delay IN DATE DEFAULT NULL, expiration IN NATURAL:= 0, relative_msgid IN NUMBER DEFAULT NULL, seq_deviation IN CHAR DEFAULT A, exception_queue_schema IN VARCHAR2 DEFAULT NULL, exception_queue IN VARCHAR2 DEFAULT NULL, Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  3. reply_queue_schema IN VARCHAR2 DEFAULT NULL, reply_queue IN VARCHAR2 DEFAULT NULL, user_data IN any_object_type, msgid OUT RAW); C.3.1.2 The DEQUEUE procedure The DEQUEUE procedure can either remove or browse a message from an existing message queue. The target message queue must have had dequeuing enabled previously via the DBMS_AQADM. STOP_QUEUE procedure. The specification is: PROCEDURE DBMS_AQ.DEQUEUE (q_schema IN VARCHAR2 DEFAULT NULL, q_name IN VARCHAR2, msgid IN RAW DEFAULT NULL, corrid IN VARCHAR2 DEFAULT NULL, deq_mode IN CHAR DEFAULT `D', wait_time IN NATURAL DEFAULT NULL, transactional IN BOOLEAN:= true, out_msgid OUT NUMBER, out_corrid OUT VARCHAR2, priority OUT POSITIVE, delay OUT DATE, expiration OUT NATURAL, retry OUT NATURAL, exception_queue_schema OUT VARCHAR2, exception_queue OUT VARCHAR2, reply_queue_schema OUT VARCHAR2, reply_queue OUT VARCHAR2, user_data OUT any_object_type); C.3.2 DBMS_AQADM (PL/SQL 8 Only) The DBMS_AQADM package provides an interface to the administrative tasks of Oracle AQ. To use these procedures, a DBMS_AQADM user must have been granted the new role, AQ_ADMINISTRATOR_ROLE. You can verify the results of executing the DBMS_ AQADM package by querying the new Oracle AQ data dictionary views, USER_QUEUE_ TABLES and USER_QUEUES (DBA levels of these views are also available). C.3.2.1 The CREATE_QUEUE_TABLE procedure Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. The CREATE_QUEUE_TABLE procedure creates a queue table. A queue table is the named repository for a set of queues and their messages. A queue table may contain numerous queues, each of which may have many messages. But a given queue and its messages may exist in only one queue table. The specification is: PROCEDURE DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table IN VARCHAR2 ,queue_payload_type IN VARCHAR2 ,storage_clause IN VARCHAR2 DEFAULT NULL ,sort_list IN VARCHAR2 DEFAULT NULL ,multiple_consumers IN BOOLEAN DEFAULT FALSE ,message_grouping IN BINARY_INTEGER DEFAULT NONE ,comment IN VARCHAR2 DEFAULT NULL ,auto_commit IN BOOLEAN DEFAULT TRUE); C.3.2.2 The DROP_QUEUE_TABLE procedure The DROP_QUEUE_TABLE procedure drops an existing queue table. An error is returned if the queue table does not exist. The force parameter specifies whether all existing queues in the queue table are stopped and dropped automatically or manually. If manually (i.e., FALSE), then the queue administrator must stop and drop all existing queues within the queue table using the DBMS_AQADM.STOP_QUEUE and DBMS_AQADM.DROP_QUEUE procedures. The specification is: PROCEDURE DBMS_AQADM.DROP_QUEUE_TABLE (queue_table IN VARCHAR2, force IN BOOLEAN default FALSE, auto_commit IN BOOLEAN default TRUE); C.3.2.3 The CREATE_QUEUE procedure The CREATE_QUEUE procedure creates a new message queue within an existing queue table. An error is returned if the queue table does not exist. The required queue_name parameter specifies the name of the new message queue to create. All queue names must be unique within the schema. The specification is: PROCEDURE DBMS_AQADM.CREATE_QUEUE (queue_name IN VARCHAR2, queue_table IN VARCHAR2, queue_type IN BINARY_INTEGER default DBMS_AQADM. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  5. NORMAL_QUEUE, max_retries IN NUMBER default 0, retry_delay IN NUMBER default 0, retention_time IN NUMBER default 0, dependency_tracking IN BOOLEAN default FALSE, comment IN VARCHAR2 default NULL, auto_commit IN BOOLEAN default TRUE); C.3.2.4 The ALTER_QUEUE procedure The ALTER_QUEUE procedure modifies properties of an existing message queue. It returns an error if the message queue does not exist. Currently, you can alter only the maximum retries, retry delay, retention time, rentention delay and auto-commit properties; Oracle will augment this list in future releases. The specification is: PROCEDURE DBMS_AQADM.ALTER_QUEUE ( queue_name IN VARCHAR2, max_retries IN NUMBER default NULL, retry_delay IN NUMBER default NULL, retention_time IN NUMBER default NULL, auto_commit IN BOOLEAN default TRUE); C.3.2.5 The DROP_QUEUE procedure The DROP_QUEUE procedure drops an existing message queue. It returns an error if the message queue does not exist. DROP_QUEUE is not allowed unless STOP_QUEUE has been called to disable both enqueuing and dequeuing for the message queue to be dropped. If the message queue has not been stopped, then DROP_QUEUE returns an error of queue resource busy. The specification is: PROCEDURE DBMS_AQADM.DROP_QUEUE_TABLE (queue_table IN VARCHAR2, force IN BOOLEAN default FALSE, auto_commit IN BOOLEAN default TRUE); C.3.2.6 The START_QUEUE procedure The START_QUEUE procedure enables an existing message queue for enqueuing and dequeuing. It returns an error if the message queue does not exist. The default is to enable both. The specification is: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  6. PROCEDURE DBMS_AQADM.START_QUEUE ( queue_name IN VARCHAR2, enqueue IN BOOLEAN DEFAULT TRUE, dequeue IN BOOLEAN DEFAULT TRUE); C.3.2.7 The STOP_QUEUE procedure The STOP_QUEUE procedure disables an existing message queue for enqueuing and dequeuing. It returns an error if the message queue does not exist. The default is to disable both enqueuing and dequeuing. The wait parameter specifies whether to wait for outstanding transactions or to return immediately. The wait option is highly dependent on outstanding transactions. If outstanding transactions exist, then wait will either hang until the transactions complete or return an error of ORA- 24203, depending on whether the wait parameter is set to true or false. The specification is: PROCEDURE DBMS_AQADM.STOP_QUEUE (queue_name IN VARCHAR2, enqueue IN BOOLEAN DEFAULT TRUE, dequeue IN BOOLEAN DEFAULT TRUE, wait IN BOOLEAN DEFAULT TRUE); Previous: C.2 Oracle PL/SQL Next: C.4 DBMS_DDL DBMS_ALERT Programming, 2nd Edition C.2 DBMS_ALERT Book Index C.4 DBMS_DDL The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  7. Previous: C.3 Oracle AQ, Appendix C Next: C.5 DBMS_ JOB the Advanced Queueing Built-In Packages Facility C.4 DBMS_DDL The DBMS_DDL package provides access to some of the SQL DDL statements from within stored procedures. C.4.1 The ALTER_COMPILE procedure The ALTER_COMPILE procedure can be used to programmatically force a recompile of a stored object. The specification is: PROCEDURE DBMS_DDL.ALTER_COMPILE (type VARCHAR2, schema VARCHAR2, name VARCHAR2); C.4.2 The ANALYZE_OBJECT procedure A call to ANALYZE_OBJECT lets you programmatically compute statistics for the specified object. The specification is: PROCEDURE DBMS_DDL.ANALYZE_OBJECT (type VARCHAR2, schema VARCHAR2, name VARCHAR2, method VARCHAR2, estimate_rows NUMBER DEFAULT NULL, estimate_percent NUMBER DEFAULT NULL); Previous: C.3 Oracle AQ, Oracle PL/SQL Next: C.5 DBMS_ JOB the Advanced Queueing Programming, 2nd Edition Facility Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  8. C.3 Oracle AQ, the Book Index C.5 DBMS_ JOB Advanced Queueing Facility The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  9. Previous: C.4 DBMS_DDL Appendix C Next: C.6 DBMS_LOB Built-In Packages (PL/SQL8 Only) C.5 DBMS_ JOB The DBMS_ JOB package provides a way for you to schedule jobs from within the Oracle RDBMS. A job is a call to a single stored procedure. You can submit a job to run once or on a recurring basis. Once a job has been submitted, you can check on the status of the job by viewing a data dictionary table. You can also change the parameters of the job with the CHANGE procedure. When you submit a job, you must provide a string that describes that job to the DBMS_ JOB package and specify a job execution interval. C.5.1 The BROKEN procedure The Oracle Server considers a job to be broken if it has tried and failed 16 times to run the job. At this point, Oracle marks the job as broken and will no longer try to run the job until either (a) you mark the job as fixed or (b) you force the job to execute with a call to the RUN procedure. Use the BROKEN procedure to mark the job as fixed and specify the next date on which you want the job to run. The specification is: PROCEDURE DBMS_JOB.BROKEN (job IN BINARY_INTEGER, broken IN BOOLEAN, next_date IN DATE DEFAULT SYSDATE); C.5.2 The CHANGE procedure Use the CHANGE procedure to change one or all of the attributes of a job. The specification is: PROCEDURE DBMS_JOB.CHANGE (job IN BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE, interval IN VARCHAR2); C.5.3 The INTERVAL procedure Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  10. Use the INTERVAL procedure to change the interval for which a queued job is going to run. The specification is: PROCEDURE DBMS_JOB.INTERVAL (job IN BINARY_INTEGER, interval IN VARCHAR2); C.5.4 The ISUBMIT procedure The ISUBMIT procedure submits a new job with a specified job number to the queue. The difference between ISUBMIT and SUBMIT (described later in this section) is that ISUBMIT specifies a job number, whereas SUBMIT returns a job number generated by the DBMS_JOB package. The specification is: PROCEDURE DBMS_JOB.ISUBMIT (job IN BINARY_INTEGER, what IN VARCHAR2, next_date in DATE DEFAULT SYSDATE interval IN VARCHAR2 DEFAULT 'NULL', no_parse in BOOLEAN DEFAULT FALSE); C.5.5 The NEXT_DATE procedure Use the NEXT_DATE procedure to change when a queued job is going to run. The specification is: PROCEDURE DBMS_JOB.NEXT_DATE (job IN BINARY_INTEGER, next_date IN DATE); C.5.6 The REMOVE procedure Use the REMOVE procedure to remove a job from the queue. If the job has started execution, you cannot remove it from the queue. The specification is: PROCEDURE DBMS_JOB.REMOVE (job IN BINARY_INTEGER); C.5.7 The RUN procedure Use the RUN procedure to force a job to be executed immediately, regardless of the values for next_date and interval stored in the job queue. The specification is: PROCEDURE DBMS_JOB.RUN (job IN BINARY_INTEGER); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  11. C.5.8 The SUBMIT procedure The SUBMIT procedure submits jobs to the queue. The specification is: PROCEDURE DBMS_JOB.SUBMIT (job OUT BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE DEFAULT SYSDATE, interval IN VARCHAR2 DEFAULT 'NULL', no_parse IN BOOLEAN DEFAULT FALSE); C.5.9 The USER_EXPORT procedure The USER_EXPORT procedure is used to extract the job string from a job in the queue. The specification is: PROCEDURE DBMS_JOB.USER_EXPORT (job IN BINARY_INTEGER, mycall OUT VARCHAR2); C.5.10 The WHAT procedure Use the WHAT procedure to change what a queued job is going to run. The specification is: PROCEDURE DBMS_JOB.WHAT (job IN BINARY_INTEGER, what IN VARCHAR2); Previous: C.4 DBMS_DDL Oracle PL/SQL Next: C.6 DBMS_LOB Programming, 2nd Edition (PL/SQL8 Only) C.4 DBMS_DDL Book Index C.6 DBMS_LOB (PL/SQL8 Only) The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  12. Previous: C.5 DBMS_ JOB Appendix C Next: C.7 DBMS_LOCK Built-In Packages C.6 DBMS_LOB (PL/SQL8 Only) Use the DBMS_LOB package to manipulate LOBs (large objects) from within a PL/SQL program and SQL statements. With DBMS_LOB you can read and modify BLOBs (binary LOBs), CLOBs (single-byte character data), and NCLOBs (fixed-width single-byte or multibyte character data), and you can perform read-only operations on BFILEs (file-based LOBs). C.6.1 The APPEND procedure Call the APPEND procedure to append the contents of a source LOB to a destination LOB. The specifications are: PROCEDURE DBMS_LOB.APPEND (dest_lob IN OUT BLOB, src_lob IN BLOB); PROCEDURE DBMS_LOB.APPEND (dest_lob IN OUT CLOB CHARACTER SET ANY_CS, src_lob IN CLOB CHARACTER SET DEST_LOB%CHARSET); C.6.2 The COMPARE function Use the compare function to compare two LOBs in their entirety, or compare just parts of two LOBs. The specifications are: FUNCTION DBMS_LOB.COMPARE (lob_1 IN BLOB, lob_2 IN BLOB, amount IN INTEGER := 4294967295, Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  13. offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1) RETURN INTEGER; FUNCTION DBMS_LOB.COMPARE (lob_1 IN CLOB CHARACTER SET ANY_CS, lob_2 IN CLOB CHARACTER SET LOB_1%CHARSET, amount IN INTEGER := 4294967295, offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1) RETURN INTEGER; FUNCTION DBMS_LOB.COMPARE (file_1 IN BFILE, file_2 IN BFILE, amount IN INTEGER, offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1) RETURN INTEGER; C.6.3 The COPY procedure The copy procedure copies all or part of a source LOB to a destination LOB. The specifications are: PROCEDURE DBMS_LOB.COPY (dest_lob IN OUT BLOB, src_lob IN BLOB, amount IN OUT INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1); PROCEDURE DBMS_LOB.COPY (dest_lob IN OUT CLOB CHARACTER SET ANY_CS, src_lob IN CLOB CHARACTER SET DEST_LOB%CHARSET, amount IN OUT INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1); C.6.4 The ERASE procedure The erase procedure erases an entire LOB or part of a LOB. The specifications are: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  14. PROCEDURE DBMS_LOB.ERASE (lob_loc IN OUT BLOB, amount IN OUT INTEGER, offset IN INTEGER := 1); PROCEDURE DBMS_LOB.ERASE (lob_loc IN OUT CLOB CHARACTER SET ANY_CS, amount IN OUT INTEGER, offset IN INTEGER := 1); C.6.5 The FILECLOSE procedure Call the fileclose procedure to close a BFILE which has previously been opened in your session or PL/SQL block. The specification is: PROCEDURE DBMS_LOB.FILECLOSE (file_loc IN OUT BFILE); C.6.6 The FILECLOSEALL procedure The filecloseall procedure closes all BFILEs which have previously been opened in your session. The specification is: PROCEDURE DBMS_LOB.FILECLOSEALL; C.6.7 The FILEEXISTS function The fileexists function returns 1 if the file you have specified via a BFILE locator exists. The specification is: FUNCTION DBMS_LOB.FILEEXISTS (file_loc IN BFILE) RETURN INTEGER; C.6.8 The FILEGETNAME procedure Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  15. Use the filegetname procedure to translate a BFILE locator into its directory alias and filename components. The specification is: PROCEDURE DBMS_LOB.FILEGETNAME (file_loc IN BFILE, dir_alias OUT VARCHAR2, filename OUT VARCHAR2); C.6.9 The FILEISOPEN function The fileisopen function returns 1 if the BFILE is already open. The specification is: FUNCTION DBMS_LOB.FILEISOPEN (file_loc IN BFILE) RETURN INTEGER; C.6.10 The FILEOPEN procedure The fileopen procedure opens a BFILE with the specified mode. The specification is: PROCEDURE DBMS_LOB.FILEOPEN (file_loc IN OUT BFILE, open_mode IN BINARY_INTEGER := FILE_READONLY); C.6.11 The GETLENGTH function Use the getlength function to return the length of the specified LOB in bytes or characters, depending on the type of LOB. The specifications are: FUNCTION DBMS_LOB.GETLENGTH (lob_loc IN BLOB) RETURN INTEGER; FUNCTION DBMS_LOB.GETLENGTH(lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; FUNCTION DBMS_LOB.GETLENGTH (file_loc IN BFILE) RETURN INTEGER; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. C.6.12 The INSTR function The instr function returns the matching location of the nth occurrence of the specified pattern in the LOB. The specifications are: FUNCTION DBMS_LOB.INSTR (lob_loc IN BLOB, pattern IN RAW, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER; FUNCTION DBMS_LOB.INSTR (lob_loc IN CLOB CHARACTER SET ANY_CS, pattern IN VARCHAR2 CHARACTER SET LOB_LOC%CHARSET, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER; FUNCTION DBMS_LOB.INSTR (file_loc IN BFILE, pattern IN RAW, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER; C.6.13 The READ procedure Call the read procedure to read a portion of a LOB into a buffer variable. The specifications are: PROCEDURE DBMS_LOB.READ (lob_loc IN BLOB, amount IN OUT BINARY_INTEGER, offset IN INTEGER, buffer OUT RAW); PROCEDURE DBMS_LOB.READ (lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN OUT BINARY_INTEGER, offset IN INTEGER, Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  17. buffer OUT VARCHAR2 CHARACTER SET LOB_LOC%CHARSET); PROCEDURE DBMS_LOB.READ (file_loc IN BFILE, amount IN OUT BINARY_INTEGER, offset IN INTEGER, buffer OUT RAW); C.6.14 The SUBSTR function The substr function returns the specified number of bytes or characters from a LOB. The specifications are: FUNCTION DBMS_LOB.SUBSTR (lob_loc IN BLOB, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN RAW; FUNCTION DBMS_LOB.SUBSTR (lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN VARCHAR2; FUNCTION DBMS_LOB.SUBSTR (file_loc IN BFILE, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN RAW; C.6.15 The TRIM procedure Use the trim procedure to trim the LOB value to the length you specify. The specifications are: PROCEDURE DBMS_LOB.TRIM (lob_loc IN OUT BLOB, newlen IN INTEGER); PROCEDURE DBMS_LOB.TRIM Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  18. (lob_loc IN OUT CLOB CHARACTER SET ANY_CS, newlen IN INTEGER); C.6.16 The WRITE procedure Call the write procedure to write a specified number of bytes or characters from a buffer variable into a LOB at a specified position. The specifications are: PROCEDURE DBMS_LOB.WRITE (lob_loc IN OUT BLOB, amount IN OUT BINARY_INTEGER, offset IN INTEGER, buffer IN RAW); PROCEDURE DBMS_LOB.WRITE (lob_loc IN OUT CLOB CHARACTER SET ANY_CS, amount IN OUT BINARY_INTEGER, offset IN INTEGER, buffer IN VARCHAR2 CHARACTER SET LOB_LOC%CHARSET); Previous: C.5 DBMS_ JOB Oracle PL/SQL Next: C.7 DBMS_LOCK Programming, 2nd Edition C.5 DBMS_ JOB Book Index C.7 DBMS_LOCK The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  19. Previous: C.6 DBMS_LOB Appendix C Next: C.8 DBMS_MAIL (PL/SQL8 Only) 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; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  20. 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Đồng bộ tài khoản