Oracle 9i. Supplied PL/SQL Packages and Types Reference

1506 Pages·2001·9.322 MB·English

by  Bradshaw D. K.

Oracle9i
Supplied PL/SQL Packages and Types Reference
Release 1 (9.0.1)
July2001
PartNo. A89852-02
Supplied PL/SQL Packages and Types Reference, Release 1 (9.0.1)
Part No. A89852-02
Copyright © 2001, Oracle Corporation. All rights reserved.
Primary Author: D.K. Bradshaw
Contributing Authors: Mark Bauer, Michelle Cyran
Contributors: D. Alpern, G. Arora, L. Barton, N. Bhatt, S. Chandrasekar, T. Chang, G. Claborn, R.
Decker, A. Downing, J. Draaijer, S. Ehrsam, A. Ganesh, J. Gosselin, R. Govindarajan, B. Goyal, S. Harris,
B. Himatsingka, C. Iyer, H. Jakobsson, A. Jasuja, M. Jungerman, P. Justus, A. Kalra, P. Lane, B. Lee, J. Liu,
P. Locke, A. Logan, N. Mallavarupu, J. Mallory, R. Mani, S. Mavris, A. Mozes, J. Muller, C. Murray, K.
Muthukkaruppan, S. Muthulingam, R. Pang, D. Raphaely, S. Ray, A. Rhee, K. Rich, V. Schupmann, J.
Sharma, R. Sujithan, A. Swaminathan, K. Tarkhanov, A. Tsukerman A. To, R. Urbano, S. Urman, S.
Vivian, D. Voss, W. Wang, D. Wong, L. Wu
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
The information contained in this document is subject to change without notice. If you find any problems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and LogMiner, Oracle Alert, Oracle Call Interface, Oracle Developer,
Oracle MultiProtocol Interchange, Oracle Open Gateways, Oracle Real Application Clusters, Oracle
Procedural Gateway, Oracle Spatial, Oracle8, Oracle8i, PL/SQL, and SQL are trademarks or registered
trademarks of Oracle Corporation. Other names may be trademarks of their respective owners.
Contents
Send Us Your Comments................................................................................................................ xvii
Preface.......................................................................................................................................................... xix
Audience................................................................................................................................................ xx
Related Documentation....................................................................................................................... xx
Conventions.......................................................................................................................................... xxi
Documentation Accessibility............................................................................................................ xxiii
What’s New in Supplied PL/SQL Packages and Types?.............................................. xxv
Oracle9i Release 1 (9.0.1) New Features in Supplied PL/SQL Packages and Types............... xxvi
Oracle8i Release 2 (8.1.6) New Features in Supplied PL/SQL Packages................................. xxvii
Oracle8i Release 1 (8.1.5) New Features in Supplied PL/SQL Packages................................. xxvii
1 Introduction
Package Overview.............................................................................................................................. 1-2
Abbreviations for Datetime and Interval Datatypes................................................................... 1-7
Summary of Oracle Supplied PL/SQL Packages ......................................................................... 1-7
Summary of Subprograms in Supplemental Packages............................................................. 1-14
2 DBMS_ALERT
Security, Constants, and Errors for DBMS_ALERT..................................................................... 2-2
Using Alerts......................................................................................................................................... 2-3
Summary of DBMS_ALERT Subprograms................................................................................... 2-4
iii
3 DBMS_APPLICATION_INFO
Privileges.............................................................................................................................................. 3-2
Summary of DBMS_APPLICATION_INFO Subprograms........................................................ 3-2
4 DBMS_AQ
Java Classes.......................................................................................................................................... 4-2
Enumerated Constants....................................................................................................................... 4-2
Data Structures for DBMS_AQ........................................................................................................ 4-2
Summary of Subprograms.............................................................................................................. 4-16
5 DBMS_AQADM
Enumerated Constants....................................................................................................................... 5-2
Summary of DBMS_AQADM Subprograms................................................................................ 5-2
6 DBMS_AQELM
Summary of DBMS_AQELM Subprograms................................................................................. 6-2
7 DBMS_BACKUP_RESTORE
Filename Normalization for Oracle on Windows NT Platforms............................................... 7-2
8 DBMS_DDL
Summary of DBMS_DDL Subprograms........................................................................................ 8-2
9 DBMS_DEBUG
Using DBMS_DEBUG....................................................................................................................... 9-2
Usage Notes.......................................................................................................................................... 9-5
Types and Constants........................................................................................................................... 9-6
Error Codes, Exceptions, and Variables........................................................................................ 9-11
Common and Debug Session Sections......................................................................................... 9-12
OER Breakpoints............................................................................................................................... 9-13
Summary of DBMS_DEBUG Subprograms................................................................................ 9-14
iv
10 DBMS_DEFER
Summary of DBMS_DEFER Subprograms................................................................................. 10-2
11 DBMS_DEFER_QUERY
Summary of DBMS_DEFER_QUERY Subprograms................................................................. 11-2
12 DBMS_DEFER_SYS
Summary of DBMS_DEFER_SYS Subprograms........................................................................ 12-2
13 DBMS_DESCRIBE
Security, Types, and Errors for DBMS_DESCRIBE................................................................... 13-2
Summary of DBMS_DESCRIBE Subprograms.......................................................................... 13-2
14 DBMS_DISTRIBUTED_TRUST_ADMIN
Requirements..................................................................................................................................... 14-2
Summary of DBMS_DISTRIBUTED_TRUST_ADMIN Subprograms................................. 14-2
15 DBMS_FGA
Summary of DBMS_FGA Subprogram........................................................................................ 15-2
16 DBMS_FLASHBACK
DBMS_FLASHBACK Error Messages.......................................................................................... 16-3
DBMS_FLASHBACK Example...................................................................................................... 16-3
Summary of DBMS_FLASHBACK Subprograms..................................................................... 16-6
17 DBMS_HS_PASSTHROUGH
Security............................................................................................................................................... 17-2
Summary of DBMS_HS_PASSTHROUGH Subprograms....................................................... 17-2
18 DBMS_IOT
Summary of DBMS_IOT Subprograms....................................................................................... 18-2
v
19 DBMS_JOB
Requirements..................................................................................................................................... 19-2
Using the DBMS_JOB Package with Oracle Real Application Clusters............................... 19-2
Summary of DBMS_JOB Subprograms....................................................................................... 19-3
20 DBMS_LDAP
Exception Summary.......................................................................................................................... 20-2
Summary of Data Types................................................................................................................... 20-3
Summary of DBMS_LDAP Subprograms................................................................................... 20-4
21 DBMS_LIBCACHE
Requirements..................................................................................................................................... 21-2
Summary of DBMS_LIBCACHE Subprograms......................................................................... 21-2
22 DBMS_LOB
LOB Locators for DBMS_LOB....................................................................................................... 22-2
Datatypes, Constants, and Exceptions for DBMS_LOB............................................................ 22-2
Security for DBMS_LOB................................................................................................................. 22-4
Rules and Limitations for DBMS_LOB........................................................................................ 22-5
Temporary LOBs............................................................................................................................... 22-9
Summary of DBMS_LOB Subprograms.................................................................................... 22-13
23 DBMS_LOCK
Requirements, Security, and Constants for DBMS_LOCK...................................................... 23-2
Summary of DBMS_LOCK Subprograms................................................................................... 23-3
24 DBMS_LOGMNR
DBMS_LOGMNR Constants......................................................................................................... 24-2
Extracting Data Values From Redo Log Files.............................................................................. 24-3
Example of Using DBMS_LOGMNR........................................................................................... 24-3
Summary of DBMS_LOGMNR Subprograms........................................................................... 24-4
vi
25 DBMS_LOGMNR_CDC_PUBLISH
Publishing Change Data................................................................................................................. 25-2
Summary of DBMS_LOGMNR_CDC_PUBLISH Subprograms............................................ 25-2
26 DBMS_LOGMNR_CDC_SUBSCRIBE
Subscribing to Change Data........................................................................................................... 26-2
Summary of DBMS_LOGMNR_CDC_SUBSCRIBE Subprograms....................................... 26-2
27 DBMS_LOGMNR_D
Extracting a Dictionary to the Redo Log Files............................................................................. 27-2
Extracting a Dictionary to a Flat File............................................................................................. 27-2
Examples of Using DBMS_LOGMNR_D.BUILD...................................................................... 27-3
Summary of DBMS_LOGMNR_D Subprograms...................................................................... 27-3
28 DBMS_METADATA
Summary of DBMS_METADATA Subprograms....................................................................... 28-2
29 DBMS_MVIEW
Summary of DBMS_MVIEW Subprograms............................................................................... 29-2
30 DBMS_OBFUSCATION_TOOLKIT
Overview of Key Management...................................................................................................... 30-3
Summary of DBMS_OBFUSCATION Subprograms................................................................ 30-5
31 DBMS_ODCI
Summary of DBMS_ODCI Subprograms.................................................................................... 31-2
32 DBMS_OFFLINE_OG
Summary of DBMS_OFFLINE_OG Subprograms.................................................................... 32-2
33 DBMS_OFFLINE_SNAPSHOT
Summary of DBMS_OFFLINE_SNAPSHOT Subprograms.................................................... 33-2
vii
34 DBMS_OLAP
Requirements..................................................................................................................................... 34-2
Error Messages................................................................................................................................... 34-2
Summary of DBMS_OLAP Subprograms................................................................................... 34-6
DBMS_OLAP Interface Views..................................................................................................... 34-21
35 DBMS_ORACLE_TRACE_AGENT
Security............................................................................................................................................... 35-2
Summary of DBMS_ORACLE_TRACE_AGENT Subprograms............................................. 35-2
36 DBMS_ORACLE_TRACE_USER
Summary of DBMS_ORACLE_TRACE_USER Subprograms................................................. 36-2
37 DBMS_OUTLN
Requirements and Security for DBMS_OUTLN........................................................................ 37-2
Summary of DBMS_OUTLN Subprograms................................................................................ 37-2
38 DBMS_OUTLN_EDIT
Summary of DBMS_OUTLN_EDIT Subprograms.................................................................... 38-2
39 DBMS_OUTPUT
Security, Errors, and Types for DBMS_OUTPUT....................................................................... 39-2
Using DBMS_OUTPUT................................................................................................................... 39-2
Summary of DBMS_OUTPUT Subprograms............................................................................. 39-3
40 DBMS_PCLXUTIL
Using DBMS_PCLXUTIL................................................................................................................ 40-2
Limitations......................................................................................................................................... 40-3
Summary of DBMS_PCLUTTL Subprograms............................................................................ 40-3
41 DBMS_PIPE
Public Pipes, Private Pipes, and Pipe Uses.................................................................................. 41-2
viii
Security, Constants, and Errors...................................................................................................... 41-4
Summary of DBMS_PIPE Subprograms...................................................................................... 41-4
42 DBMS_PROFILER
Using DBMS_PROFILER................................................................................................................ 42-2
Requirements..................................................................................................................................... 42-3
Security............................................................................................................................................... 42-5
Exceptions........................................................................................................................................... 42-6
Error Codes......................................................................................................................................... 42-6
Summary of DBMS_PROFILER Subprograms.......................................................................... 42-7
43 DBMS_RANDOM
Requirements..................................................................................................................................... 43-2
Summary of DBMS_RANDOM Subprograms........................................................................... 43-2
44 DBMS_RECTIFIER_DIFF
Summary of DBMS_RECTIFIER_DIFF Subprograms.............................................................. 44-2
45 DBMS_REDEFINITION
Summary of DBMS_REDEFINITION Subprograms................................................................ 45-2
46 DBMS_REFRESH
Summary of DBMS_REFRESH Subprograms............................................................................ 46-2
47 DBMS_REPAIR
Security, Enumeration Types, and Exceptions............................................................................ 47-2
Summary of DBMS_REPAIR Subprograms................................................................................ 47-4
48 DBMS_REPCAT
Summary of DBMS_REPCAT Subprograms............................................................................... 48-2
ix
49 DBMS_REPCAT_ADMIN
Summary of DBMS_REPCAT_ADMIN Subprograms............................................................. 49-2
50 DBMS_REPCAT_INSTANTIATE
Summary of DBMS_REPCAT_INSTANTIATE Subprograms................................................. 50-2
51 DBMS_REPCAT_RGT
Summary of DBMS_REPCAT_RGT Subprograms.................................................................... 51-2
52 DBMS_REPUTIL
Summary of DBMS_REPUTIL Subprograms............................................................................. 52-2
53 DBMS_RESOURCE_MANAGER
Requirements..................................................................................................................................... 53-2
Summary of DBMS_RESOURE_MANAGER Subprograms................................................... 53-2
54 DBMS_RESOURCE_MANAGER_PRIVS
Summary of DBMS_RESOURCE_MANAGER_PRIVS Subprograms.................................. 54-2
55 DBMS_RESUMABLE
Summary of DBMS_RESUMABLE Subprograms..................................................................... 55-2
56 DBMS_RLS
Dynamic Predicates.......................................................................................................................... 56-2
Security............................................................................................................................................... 56-3
Usage Notes........................................................................................................................................ 56-3
Summary of DBMS_RLS Subprograms....................................................................................... 56-3
57 DBMS_ROWID
Usage Notes........................................................................................................................................ 57-2
Requirements..................................................................................................................................... 57-3
ROWID Types.................................................................................................................................... 57-3
x