Home Page Icon
Home Page
Table of Contents for
Cover
Close
Cover
by John Beresniewicz, Charles Dye, Steven Feuerstein
Oracle Built-in Packages
Oracle Built-in Packages
SPECIAL OFFER: Upgrade this ebook with O’Reilly
Dedication
Preface
Structure of This Book
Conventions Used in This Book
Versions of Oracle
About the Disk
About PL/Vision
About Q
How to Contact Us
Acknowledgments
Steven
From Charles
From John
I. Overview
1. Introduction
The Power of Built-in Packages
A Kinder , More Sharing Oracle
Built-in Packages Covered in This Book
Part II
Part III
Part IV
Using Built-in Packages
What Is a Package?
Controlling Access with Packages
Referencing Built-in Package Elements
Exception Handling and Built-in Packages
Package-named system exception
Package-defined exception
Standard system exception
Package-specific exception
Same exception, different causes
Encapsulating Access to the Built-in Packages
Examples of encapsulation packages
Calling Built-in Packaged Code from Oracle Developer/2000 Release 1
Referencing packaged constants and variables
Handling exceptions in Oracle Developer/2000 Release 1
Accessing Built-in Packaged Technology from Within SQL
Calling a packaged function in SQL
Using a packaged procedure from within SQL
Examining Built-in Package Source Code
The STANDARD Package
The DBMS_STANDARD Package
II. Application Development Packages
2. Executing Dynamic SQL and PL/SQL
Examples of Dynamic SQL
The DBMS_SQL Interface
Processing Flow of Dynamic SQL
Opening the Cursor
The DBMS_SQL. OPEN_CURSOR function
The DBMS_SQL.IS_OPEN function
Parsing the SQL Statement
The DBMS_SQL.PARSE procedure
Parsing very long SQL statements
Binding Values into Dynamic SQL
The DBMS_SQL.BIND_VARIABLE procedure
Examples
The DBMS_SQL. BIND_ARRAY procedure
Rules for array binding
Defining Cursor Columns
The DBMS_SQL.DEFINE_COLUMN procedure
The DBMS_SQL.DEFINE_ARRAY procedure
Executing the Cursor
The DBMS_SQL.EXECUTE function
Fetching Rows
The DBMS_SQL.FETCH_ROWS function
The DBMS_SQL.EXECUTE_AND_FETCH function
Retrieving Values
The DBMS_SQL.COLUMN_VALUE procedure
The DBMS_SQL.COLUMN_VALUE_LONG procedure
The DBMS_SQL.VARIABLE_VALUE procedure
Closing the Cursor
The DBMS_SQL. CLOSE_CURSOR procedure
Checking Cursor Status
The DBMS_SQL.LAST_ERROR_POSITION function
The DBMS_SQL.LAST_ROW_COUNT function
The DBMS_SQL.LAST_ROW_ID function
The DBMS_SQL.LAST_SQL_FUNCTION_CODE function
Describing Cursor Columns
The DBMS_SQL. DESCRIBE_COLUMNS procedure
Tips on Using Dynamic SQL
Some Restrictions
Privileges and Execution Authority with DBMS_SQL
The tale of Jan
The tale of Scott
Combining Operations
Minimizing Memory for Cursors
Improving the Performance of Dynamic SQL
Problem-Solving Dynamic SQL Errors
Executing DDL in PL/SQL
Executing Dynamic PL/SQL
DBMS_SQL Examples
A Generic Drop_Object Procedure
A Generic Foreign Key Lookup Function
A Wrapper for DBMS_SQL .DESCRIBE_COLUMNS
Displaying Table Contents with Method 4 Dynamic SQL
The “in table” procedural interface
Steps for intab construction
Constructing the SELECT
Computing column length
Defining the cursor structure
Retrieving and displaying data
Build those utilities!
Full text of intab procedure
Indirect Referencing in PL/SQL
Assigning a value
Retrieving a value
Copying a value to a packaged variable
Array Processing with DBMS_SQL
Using array processing to insert
Using array processing to delete
Using array processing to update
Using array processing to fetch
Using array processing in dynamic PL/SQL
Using the RETURNING Clause in Dynamic SQL
RETURNING from a single-row insert
RETURNING from a multiple-row delete
3. Intersession Communication
DBMS_PIPE: Communicating Between Sessions
Getting Started with DBMS_PIPE
DBMS_PIPE programs
DBMS_PIPE nonprogram elements
How Database Pipes Work
Memory structures
Nontransactional communications
Pipe communications logic
Managing Pipes and the Message Buffer
The DBMS_PIPE.CREATE_PIPE function
Return values
Exceptions
Restrictions
Example
The DBMS_PIPE.REMOVE_PIPE function
Return values
Exceptions
Restrictions
Example
The DBMS_PIPE.RESET_BUFFER procedure
Example
The DBMS_PIPE.PURGE procedure
Exceptions
Restrictions
Example
The DBMS_PIPE.UNIQUE_SESSION_NAME function
Example
Packing and Unpacking Messages
The DBMS_PIPE.PACK_MESSAGE procedure
Exceptions
Restrictions
Example
The DBMS_PIPE.PACK_MESSAGE_RAW procedure
Exceptions
Restrictions
Example
The DBMS_PIPE.PACK_MESSAGE_ROWID procedure
Exceptions
Restrictions
Example
The DBMS_PIPE.UNPACK_MESSAGE procedure
Exceptions
Example
The DBMS_PIPE.UNPACK_MESSAGE_RAW procedure
Exceptions
Example
The DBMS_PIPE.UNPACK_MESSAGE_ROWID procedure
Exceptions
Example
The DBMS_PIPE.NEXT_ITEM_TYPE function
Example
Sending and Receiving Messages
The DBMS_PIPE.SEND_MESSAGE function
Exceptions
Restrictions
Example
The DBMS_PIPE.RECEIVE_MESSAGE function
Exceptions
Restrictions
Example
Tips on Using DBMS_PIPE
Defining message types and encapsulating communications logic
Separating messages
Establishing messaging protocols
Paying attention to timeouts
Using RESET_BUFFER
Sizing and removing pipes for good memory management
DBMS_PIPE Examples
Communicating with the outside world
Exploring DBMS_PIPE
The dbpipe utility package
Unpack_to_tbl and pack_from_tbl procedures
The peek procedure
The forward procedure
Implementing a server program
The pipesvr package
Message types
Pipenames
Pack/send, receive/unpack encapsulation
The server procedure
The process_request procedure
Debug procedure
Client side procedures
DBMS_ALERT: Broadcasting Alerts to Users
Getting Started with DBMS_ALERT
DBMS_ALERT programs
DBMS_ALERT nonprogram elements
The DBMS_ALERT Interface
The DBMS_ALERT. REGISTER procedure
Exceptions
Restrictions
Example
The DBMS_ALERT.REMOVE procedure
Example
The DBMS_ALERT.REMOVEALL procedure
Example
The DBMS_ALERT. SET_DEFAULTS procedure
Example
The DBMS_ALERT. SIGNAL procedure
Exceptions
Restrictions
Example
The DBMS_ALERT.WAITANY procedure
Exceptions
Restrictions
Example
The DBMS_ALERT.WAITONE procedure
Exceptions
Restrictions
Example
DBMS_ALERT Examples
The online auction
The auction schema
Auction system requirements
Integrity constraint triggers
The auction package
Place_bid procedure
Exception handling
The watch_bidding procedure
Testing the system
4. User Lock and Transaction Management
DBMS_LOCK: Creating and Managing Resource Locks
Getting Started with DBMS_LOCK
DBMS_LOCK programs
DBMS_LOCK nonprogram elements
Lock compatibility rules
The DBMS_LOCK Interface
The DBMS_LOCK.ALLOCATE_UNIQUE procedure
Restrictions
Example
The DBMS_LOCK.REQUEST function
Restrictions
Example
The DBMS_LOCK.CONVERT function
Restrictions
Example
The DBMS_LOCK.RELEASE function
Restrictions
Example
The DBMS_LOCK.SLEEP procedure
Exceptions
Restrictions
Example
Tips on Using DBMS_LOCK
Named locks or lock ids?
Issues with named locks
Performance of named locks
ALLOCATE_UNIQUE drawbacks
Optimizing named locks
REQUEST or CONVERT?
DBMS_LOCK Examples
The dblock package
The lockhandle function
get_lock_TF function
The committed_TF and release functions
Using locks to signal service availability
DBMS_TRANSACTION: Interfacing to SQL Transaction Statements
Getting Started with DBMS_TRANSACTION
DBMS_TRANSACTION programs
DBMS_TRANSACTION exceptions
Advising Oracle About In-Doubt Transactions
The DBMS_TRANSACTION.ADVISE_ROLLBACK, and ADVISE_COMMIT procedures
Example
Committing Data
The DBMS_TRANSACTION.COMMIT procedure
The DBMS_TRANSACTION.COMMIT_COMMENT procedure
The DBMS_TRANSACTION.COMMIT_FORCE procedure
Rolling Back Changes
The DBMS_TRANSACTION.ROLLBACK procedure
The DBMS_TRANSACTION.ROLLBACK_FORCE procedure
The DBMS_TRANSACTION.SAVEPOINT procedure
The DBMS_TRANSACTION.ROLLBACK_SAVEPOINT procedure
The DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT procedure
Setting Transaction Characteristics
The DBMS_TRANSACTION.READ_ONLY procedure
The DBMS_TRANSACTION.READ_WRITE procedure
The DBMS_TRANSACTION.BEGIN_DISCRETE_TRANSACTION procedure
Restrictions
Exceptions
Example
Cleaning Up Transaction Details
The DBMS_TRANSACTION.PURGE_MIXED procedure
The DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY procedure
Returning Transaction Identifiers
The DBMS_TRANSACTION.LOCAL_TRANSACTION_ID function
The DBMS_TRANSACTION.STEP_ID function
5. Oracle Advanced Queuing
Oracle AQ Concepts
General Features
Enqueue Features
Dequeue Features
Propagation Features
A Glossary of Terms
Components of Oracle AQ
Queue Monitor
Data Dictionary Views
Getting Started with Oracle AQ
Installing the Oracle AQ Facility
Database Initialization
Starting the Queue Monitor
Starting propagation processes
Setting Oracle AQ compatibility
Authorizing Accounts to Use Oracle AQ
Oracle AQ Nonprogram Elements
Constants
Administrative tasks
Operational tasks
Object Names
Queue Type Names
Agents Object Type
Recipient and Subscriber List Table Types
Message Properties Record Type
Enqueue Options Record Type
Dequeue Options Record Type
Oracle AQ Exceptions
DBMS_AQ: Interfacing to Oracle AQ (Oracle8 only)
Enqueuing Messages
The DBMS_AQ. ENQUEUE procedure
Examples
Dequeuing Messages
The DBMS_AQ.DEQUEUE procedure
Examples
Dequeue search criteria
Dequeue order
Dequeue navigation
Dequeuing with message grouping
DBMS_AQADM: Performing AQ Administrative Tasks (Oracle8 only)
Creating Queue Tables
The DBMS_AQADM. GRANT_TYPE_ACCESS procedure
The DBMS_AQADM.CREATE_QUEUE_TABLE procedure
Example
Notes on usage
Creating and Starting Queues
The DBMS_AQADM.CREATE_QUEUE procedure
Example
The DBMS_AQADM. START_QUEUE procedure
Example
The DBMS_AQADM. ALTER_QUEUE procedure
Example
Managing Queue Subscribers
The DBMS_AQADM. ADD_SUBSCRIBER procedure
Example
The DBMS_AQADM. REMOVE_SUBSCRIBER procedure
Example
The DBMS_AQADM.QUEUE_SUBSCRIBERS procedure
Example
Stopping and Dropping Queues
The DBMS_AQADM. STOP_QUEUE procedure
Example
The DBMS_AQADM.DROP_QUEUE procedure
The DBMS_AQADM.DROP_QUEUE_TABLE procedure
Example
Managing Propagation of Messages
The DBMS_AQADM.SCHEDULE_PROPAGATION procedure
Example
The DBMS_AQADM.UNSCHEDULE_PROPAGATION procedure
Verifying Queue Types
The DBMS_AQADM.VERIFY_QUEUE_TYPES procedure
Starting and Stopping the Queue Monitor
The DBMS_AQADM. START_TIME_MANAGER procedure
The DBMS_AQADM. STOP_TIME_MANAGER procedure
Oracle AQ Database Objects
Objects Per Queue Table
The database table for queue data
The queue table view
Data Dictionary Objects
The DBA_QUEUE_TABLES view
The DBA_QUEUES view
The DBA_JOBS view
The GV$AQ and V$AQ dynamic statistics views
Oracle AQ Examples
Improving AQ Ease of Use
Working with Prioritized Queues
More complex prioritization approaches
Building a Stack with AQ Using Sequence Deviation
Browsing a Queue’s Contents
A template for a show_queue procedure
Searching by Correlation Identifier
Wildcarded correlation identifiers
Tips for using the correlation identifier
Using Time Delay and Expiration
Working with Message Groups
Enqueuing messages as a group
Step 1. Create a queue table that will support message grouping
Step 2. Enqueue messages within the same transaction boundary
Dequeuing messages when part of a group
Working with Multiple Consumers
Using the subscriber list
Overriding with a recipient list
6. Generating Output from PL/SQL Programs
DBMS_OUTPUT: Displaying Output
Getting Started with DBMS_OUTPUT
DBMS_OUTPUT programs
DBMS_OUTPUT concepts
DBMS_OUTPUT exceptions
DBMS_OUTPUT nonprogram elements
Drawbacks of DBMS_OUTPUT
Enabling and Disabling Output
The DBMS_OUTPUT.ENABLE procedure
The DBMS_OUTPUT.DISABLE procedure
Enabling output in SQL*Plus
Writing to the DBMS_OUTPUT Buffer
The DBMS_OUTPUT.PUT procedure
Example
The DBMS_OUTPUT.PUT_LINE procedure
Example
The DBMS_OUTPUT.NEW_LINE procedure
Retrieving Data from the DBMS_OUTPUT Buffer
The DBMS_OUTPUT.GET_LINE procedure
Example
The DBMS_OUTPUT.GET_LINES procedure
Example
Tips on Using DBMS_OUTPUT
DBMS_OUTPUT Examples
Encapsulating DBMS_OUTPUT
Package specification for a DBMS_OUTPUT encapsulator
UTL_FILE: Reading and Writing Server-side Files
Getting Started with UTL_FILE
UTL_FILE programs
Trying out UTL_FILE
File security
Specifying file locations
UTL_FILE exceptions
UTL_FILE nonprogram elements
UTL_FILE restrictions and limitations
The UTL_FILE process flow
Opening Files
The UTL_FILE.FOPEN function
Exceptions
Example
The UTL_FILE.IS_OPEN function
Reading from Files
The UTL_FILE.GET_LINE procedure
Exceptions
Example
Writing to Files
The UTL_FILE.PUT procedure
Exceptions
The UTL_FILE.NEW_LINE procedure
Exceptions
Example
The UTL_FILE. PUT_LINE procedure
Exceptions
Example
The UTL_FILE.PUTF procedure
Exceptions
Example
The UTL_FILE. FFLUSH procedure
Exceptions
Closing Files
The UTL_FILE. FCLOSE procedure
Exceptions
The UTL_FILE. FCLOSE_ALL procedure
Exceptions
Tips on Using UTL_FILE
Handling file I/O errors
Closing unclosed files
Combining locations and filenames
Adding support for paths
You closed what?
UTL_FILE Examples
Enhancing UTL_FILE.GET_LINE
Creating a file
Testing for a file’s existence
Searching a file for a string
Getting the nth line from a file
7. Defining an Application Profile
Getting Started with DBMS_APPLICATION_INFO
DBMS_APPLICATION_INFO Programs
The V$ Virtual Tables
DBMS_APPLICATION_INFO Nonprogram Elements
DBMS_APPLICATION_INFO Interface
The DBMS_APPLICATION_INFO.READ_CLIENT_INFO procedure
Example
The DBMS_APPLICATION_INFO.READ_MODULE procedure
Example
The DBMS_APPLICATION_INFO.SET_ACTION procedure
Restrictions
Example
Recommendations for usage
The DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure
Restrictions
Example
The DBMS_APPLICATION_INFO.SET_MODULE procedure
Restrictions
Example
Recommendations for usage
The DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure
Example
DBMS_APPLICATION_INFO Examples
About the register_app Package
The action Procedure
The set_stats Procedure
The Information Procedures
Using the register_app Package
Covering DBMS_APPLICATION_INFO
Monitoring Application SQL Resource Consumption
Session Monitoring and Three-Tier Architectures
Tracking Long-Running Processes
8. Managing Large Objects
Getting Started with DBMS_LOB
DBMS_LOB Programs
DBMS_LOB Exceptions
DBMS_LOB Nonprogram Elements
About the Examples
LOB Concepts
LOB Datatypes
Internal and external LOBs
The BFILE datatype
The BLOB datatype
The CLOB datatype
The NCLOB datatype
The LOB locator
BFILE Considerations
The DIRECTORY object
Populating a BFILE locator
Internal LOB Considerations
Retaining the LOB locator
The RETURNING clause
NULL LOB locators can be a problem
NULL versus “empty” LOB locators
DBMS_LOB Interface
Working with BFILEs
The DBMS_LOB.FILEEXISTS function
Exceptions
Restrictions
Examples
The DBMS_LOB.FILEGETNAME procedure
Exceptions
Examples
The DBMS_LOB.FILEOPEN procedure
Exceptions
Examples
The DBMS_LOB.FILEISOPEN function
Exceptions
Restrictions
Examples
The DBMS_LOB.FILECLOSE procedure
Exceptions
Examples
The DBMS_LOB.FILECLOSEALL procedure
Exceptions
Examples
The DBMS_LOB.LOADFROMFILE procedure
Exceptions
Examples
Reading and Examining LOBs
The DBMS_LOB.COMPARE function
Exceptions
Restrictions
Examples
The DBMS_LOB.GETLENGTH function
Restrictions
Examples
The DBMS_LOB.READ procedure
Exceptions
Examples
The DBMS_LOB.SUBSTR function
Exceptions
Restrictions
Examples
The DBMS_LOB.INSTR function
Exceptions
Restrictions
Example
Updating BLOBs, CLOBs, and NCLOBs
The DBMS_LOB.APPEND procedure
Exceptions
Examples
The DBMS_LOB.COPY procedure
Exceptions
Example
The DBMS_LOB.ERASE procedure
Exceptions
Example
The DBMS_LOB.TRIM procedure
Exceptions
Example
The DBMS_LOB.WRITE procedure
Exceptions
Example
9. Datatype Packages
DBMS_ROWID: Working with the ROWID Pseudo-Column (Oracle8 only)
Getting Started with DBMS_ROWID
DBMS_ROWID programs
DBMS_ROWID exceptions
DBMS_ROWID nonprogram elements
ROWID Concepts
The DBMS_ROWID Interface
The DBMS_ROWID.ROWID_BLOCK_NUMBER function
Restrictions
The DBMS_ROWID.CREATE_ROWID function
Restrictions
Example
The DBMS_ROWID.ROWID_INFO procedure
Restrictions
The DBMS_ROWID.ROWID_OBJECT function
Restrictions
Example
The DBMS_ROWID.ROWID_RELATIVE_FNO function
Restrictions
The DBMS_ROWID.ROWID_ROW_NUMBER function
Restrictions
The DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO function
Restrictions
The DBMS_ROWID.ROWID_TO_EXTENDED function
Restrictions
Example
The DBMS_ROWID.ROWID_TO_RESTRICTED function
Restrictions
The DBMS_ROWID.ROWID_TYPE function
Restrictions
Example
The DBMS_ROWID. ROWID_VERIFY function
Restrictions
Example
UTL_RAW: Manipulating Raw Data
Getting Started with UTL_RAW
UTL_RAW programs
Raw Data Manipulation Concepts
Conversion and coercion
Slicing and dicing
Bit-fiddling
The UTL_RAW Interface
The UTL_RAW.BIT_AND function
Restrictions
Example
The UTL_RAW.BIT_COMPLEMENT function
Restrictions
Example
The UTL_RAW.BIT_OR function
Restrictions
Example
The UTL_RAW.BIT_XOR function
Restrictions
Example
The UTL_RAW.CAST_TO_RAW function
Restrictions
Example
The UTL_RAW.CAST_TO_VARCHAR2 function
Restrictions
Example
The UTL_RAW. COMPARE function
Restrictions
Example
The UTL_RAW.CONCAT function
Exceptions
Restrictions
Example
The UTL_RAW.CONVERT function
Exceptions
Restrictions
The UTL_RAW.COPIES function
Exceptions
Restrictions
Example
The UTL_RAW.LENGTH function
Restrictions
Example
The UTL_RAW.OVERLAY function
Exceptions
Restrictions
Example
The UTL_RAW.REVERSE function
Exceptions
Restrictions
Example
The UTL_RAW.SUBSTR function
Exceptions
Restrictions
Example
The UTL_RAW.TRANSLATE function
Exceptions
Restrictions
Example
The UTL_RAW.TRANSLITERATE function
Exceptions
Restrictions
Example
The UTL_RAW.XRANGE function
Restrictions
Example
UTL_REF: Referencing Objects (Oracle8.0.4)
Getting Started with UTL_REF
UTL_REF programs
UTL_REF exceptions
UTL_REF Interface
The UTL_REF.DELETE_OBJECT procedure
Restrictions
The UTL_REF.LOCK_OBJECT procedure
Restrictions
The UTL_REF.SELECT_OBJECT procedure
Restrictions
Example
The UTL_REF.UPDATE_OBJECT procedure
Restrictions
UTL_REF Example
10. Miscellaneous Packages
DBMS_UTILITY: Performing Miscellaneous Operations
Getting Started with DBMS_UTILITY
DBMS_UTILITY nonprogram elements
The DBMS_UTILITY Interface
The DBMS_UTILITY.ANALYZE_DATABASE procedure
Example
The DBMS_UTILITY. ANALYZE_SCHEMA procedure
Example
The DBMS_UTILITY. ANALYZE_PART_OBJECT procedure (Oracle8 Only)
Example
The DBMS_UTILITY.COMMA_TO_TABLE procedure
Example
The DBMS_UTILITY. COMPILE_SCHEMA procedure
Example
The DBMS_UTILITY. DATA_BLOCK_ADDRESS_BLOCK function
The DBMS_UTILITY. DATA_BLOCK_ADDRESS_FILE function
The DBMS_UTILITY. DB_VERSION procedure
Example
The DBMS_UTILITY. EXEC_DDL_STATEMENT procedure
The DBMS_UTILITY. FORMAT_CALL_STACK function
Example
The DBMS_UTILITY.FORMAT_ERROR_STACK function
Example
The DBMS_UTILITY. GET_HASH_VALUE function
Example
The DBMS_UTILITY. GET_PARAMETER_VALUE function
The DBMS_UTILITY. GET_TIME function
The DBMS_UTILITY. IS_PARALLEL_SERVER function
The DBMS_UTILITY. MAKE_DATA_BLOCK_ADDRESS function
Example
The DBMS_UTILITY. NAME_RESOLVE procedure
The DBMS_UTILITY.NAME_TOKENIZE procedure
The DBMS_UTILITY.PORT_STRING function
The DBMS_UTILITY.TABLE_TO_COMMA procedure
DBMS_DESCRIBE: Describing PL/SQL Program Headers
Getting Started with DBMS_DESCRIBE
DBMS_DESCRIBE program
DBMS_DESCRIBE nonprogram elements
The DBMS_DESCRIBE. DESCRIBE_PROCEDURE procedure
Exceptions
Restrictions
Explaining DBMS_DESCRIBE Results
Specifying a program name
The DESCRIBE level
How overloading is handled
DBMS_DESCRIBE Example
Features of the psdesc package
Using psdesc.args as a quality assurance tool
DBMS_DDL: Compiling and Analyzing Objects
Getting Started with DBMS_DDL
DBMS_DDL programs
Compiling PL/SQL Objects
The DBMS_DDL.ALTER_COMPILE procedure
Exceptions
Example
Computing Statistics for an Object
The DBMS_DDL. ANALYZE_OBJECT procedure
Exceptions
Setting Referenceability of Tables
The DBMS_DDL. (Oracle8 only)
Example
Exceptions
The DBMS_DDL.ALTER_TABLE_NOT_REFERENCEABLE procedure (Oracle8 only)
Exceptions
DBMS_RANDOM: Generating Random Numbers (Oracle8 Only)
Getting Started with DBMS_RANDOM
DBMS_RANDOM Interface
The DBMS_RANDOM.INITIALIZE procedure
Example
The DBMS_RANDOM.SEED procedure (Oracle8 only)
Example
The DBMS_RANDOM.RANDOM function (Oracle8 only)
Example
The DBMS_RANDOM.TERMINATE procedure (Oracle8 only)
III. Server Management Packages
11. Managing Session Information
DBMS_SESSION: Managing Session Information
Getting Started with DBMS_SESSION
DBMS_SESSION programs
Modifying Session Settings
The DBMS_SESSION. SET_LABEL procedure
Exceptions
Restrictions
Example
DBMS_SESSION.SET_MLS_LABEL_FORMAT procedure
Restrictions
Example
The DBMS_SESSION.SET_NLS procedure
Exceptions
Restrictions
Examples
The DBMS_SESSION.SET_ROLE procedure
Exceptions
Restrictions
Examples
Obtaining Session Information
The DBMS_SESSION.IS_ROLE_ENABLED function
Example
The DBMS_SESSION.UNIQUE_SESSION_ID function
Restrictions
Example
Managing Session Resources
The DBMS_SESSION. CLOSE_DATABASE_LINK procedure
Exceptions
Restrictions
Example
The DBMS_SESSION.FREE_UNUSED_USER_MEMORY procedure
The DBMS_SESSION.RESET_PACKAGE procedure
Restrictions
Example
The DBMS_SESSION. SET_CLOSE_CACHED_OPEN_CURSORS procedure
Example
The DBMS_SESSION. SET_SQL_TRACE procedure
Restrictions
Example
DBMS_SESSION Examples
Adding value to DBMS_SESSION
The unique_id function
The load_my_session_rec procedure
The sid function
The close_links procedure
The set_sql_trace procedure
The reset procedure
The memory procedure
DBMS_SYSTEM: Setting Events for Debugging
Getting Started with DBMS_SYSTEM
DBMS_SYSTEM Interface
The DBMS_SYSTEM.READ_EV procedure
Restrictions
Example
The DBMS_SYSTEM.SET_EV procedure
Restrictions
The DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procedure
Example
DBMS_SYSTEM Examples
12. Managing Server Resources
DBMS_SPACE: Obtaining Space Information
Getting Started with DBMS_SPACE
The DBMS_SPACE Interface
The DBMS_SPACE.FREE_BLOCKS procedure
Exceptions
Restrictions
Example
The DBMS_SPACE.UNUSED_SPACE procedure
Exceptions
Restrictions
Example
DBMS_SPACE Examples
The segspace package
DBMS_SHARED_POOL: Pinning Objects
Getting Started with DBMS_SHARED_POOL
Pinning and Unpinning Objects
The DBMS_SHARED_POOL.KEEP procedure
Exceptions
Restrictions
Example
The DBMS_SHARED_POOL.UNKEEP procedure
Exceptions
Restrictions
Example
Monitoring and Modifying Shared Pool Behavior
The DBMS_SHARED_POOL.SIZES procedure
Example
The DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD procedure
Exceptions
Restrictions
Example
DBMS_SHARED_POOL Examples
Pinning packages automatically
Pinning cursors into the shared pool
The DBA_KEEPSIZES view
13. Job Scheduling in the Database
Getting Started with DBMS_ JOB
DBMS_JOB Programs
Job Definition Parameters
The job parameter
The what parameter
The next_date parameter
The interval parameter
The broken parameter
Job Queue Architecture
INIT.ORA Parameters and Background Processes
JOB_QUEUE_PROCESSES
JOB_QUEUE_INTERVAL
JOB_QUEUE_KEEP_CONNECTIONS
Job Execution and the Job Execution Environment
Miscellaneous Notes
DBMS_JOB Interface
Submitting Jobs to the Job Queue
The DBMS_JOB.SUBMIT procedure
Exceptions.
Example.
The DBMS_JOB.ISUBMIT procedure
Exceptions
Example
Modifying Job Characteristics
The DBMS_JOB.CHANGE procedure
Exceptions
Restrictions
Example
The DBMS_JOB.INTERVAL procedure
Exceptions
Restrictions
Example
The DBMS_JOB.NEXT_DATE procedure
Restrictions
Example
The DBMS_JOB.WHAT procedure
Restrictions
Example
Removing Jobs and Changing Job Execution Status
The DBMS_JOB.REMOVE procedure
Restrictions
Example
The DBMS_JOB.BROKEN procedure
Restrictions
Example
The DBMS_JOB.RUN procedure
Restrictions
Example
Transferring Jobs
The DBMS_JOB.USER_EXPORT procedure
Example.
Tips on Using DBMS_JOB
Job Intervals and Date Arithmetic
Viewing Job Information in the Data Dictionary
DBMS_IJOB: Managing Other Users’ Jobs
DBMS_JOB Examples
Tracking Space in Tablespaces
Fixing Broken Jobs Automatically
Self-Modifying and Self-Aware Jobs
IV. Distributed Database Packages
14. Snapshots
DBMS_SNAPSHOT: Managing Snapshots
Getting Started with DBMS_SNAPSHOT
Using the I_AM_A_REFRESH Package State Variable
The DBMS_SNAPSHOT.I_AM_A_REFRESH function
Examples
Generating replication support
Auditing triggers
The DBMS_SNAPSHOT.SET_I_AM_A_REFRESH procedure
Example
Refreshing Snapshots
The DBMS_SNAPSHOT.REFRESH procedure
Restrictions
Examples
Read-only snapshot
Related read-only snapshots
Updateable snapshot.
Parallel refreshes
Purging the Snapshot Log
The DBMS_SNAPSHOT.PURGE.LOG procedure
Examples
Reorganizing Tables
The DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION procedure (Oracle8 only)
The DBMS_SNAPSHOT.END_TABLE_REORGANIZATION procedure (Oracle8 only)
Examples
Registering Snapshots
The DBMS_SNAPSHOT.REGISTER_SNAPSHOT procedure (Oracle 8 only)
Example
The DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT procedures (Oracle8 only)
Example
DBMS_REFRESH: Managing Snapshot Groups
Getting Started with DBMS_REFRESH
Creating and Destroying Snapshot Groups
The DBMS_REFRESH.MAKE procedure
Examples
Read-only snapshot. group
Read-only snapshot group with specialized parameters
Parallel propagation
The DBMS_REFRESH.DESTROY procedure
Example
Adding and Subtracting Snapshots from Snapshot Groups
The DBMS_REFRESH.ADD procedure
Example
The DBMS_REFRESH. SUBTRACT procedure
Example
Altering Properties of a Snapshot Group
The DBMS_REFRESH.CHANGE procedure
Manually Refreshing Snapshot Groups
The DBMS_REFRESH.REFRESH procedure
DBMS_REPCAT: Managing Snapshot Replication Groups
Getting Started with DBMS_REPCAT
DBMS_REPCAT programs
DBMS_REPCAT exceptions
Creating and Dropping Snapshot Replication Groups
The DBMS_REPCAT. CREATE_SNAPSHOT_REPGROUP procedure
Exceptions
Restrictions
The offline snapshot instantiation procedure
The DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP procedure
Exceptions
Restrictions
Example
Adding and Removing Snapshot Replication Group Objects
The DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT procedure
Exceptions.
Example
The DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT procedure
Exceptions
Restrictions
Example
Altering a Snapshot Replication Group’s Propagation Mode
The DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION procedure
Exceptions
Restrictions
Example
Manually Refreshing a Snapshot Replication Group
The DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP procedure
Exceptions
Restrictions
Example
Switching the Master of a Snapshot Replication Group
The DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER procedure
Exceptions
Restrictions
Example
15. Advanced Replication
DBMS_REPCAT_AUTH: Setting Up Administrative Accounts
Getting Started with DBMS_REPCAT_AUTH
DBMS_REPCAT_AUTH programs
DBMS_REPCAT_AUTH exceptions
Granting and Revoking Surrogate SYS Accounts
The DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT procedure
Exceptions
Example
The DBMS_REPCAT_AUTH.REVOKE_SURROGATE_REPCAT procedure
Exceptions
Example
Granting and Revoking Propagator Accounts (Oracle8)
DBMS_REPCAT_ADMIN: Setting Up More Administrator Accounts
Getting Started with DBMS_REPCAT_ADMIN
DBMS_REPCAT_ADMIN programs
DBMS_REPCAT_ADMIN exceptions
Creating and Dropping Replication Administrator Accounts
The DBMS_REPCAT_ADMIN.GRANT_ADMIN_REPGROUP procedure
Exceptions
Example
The DBMS_REPCAT_ADMIN.REVOKE_ADMIN_REPGROUP procedure
Exceptions
The DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP procedure
Exceptions
Example
The DBMS_REPCAT_ADMIN.REVOKE_ADMIN_ANY_REPGROUP procedure
Exceptions
Example
DBMS_REPCAT: Replication Environment Administration
Getting Started with DBMS_REPCAT
DBMS_REPCAT programs
Exceptions
Replication Groups with DBMS_REPCAT
The DBMS_REPCAT.CREATE_MASTER_REPGROUP procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.DROP_MASTER_REPGROUP procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT. COMMENT_ON_REPGROUP procedure
Exceptions
Restrictions
Example
Replicated Objects with DBMS_REPCAT
The DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure
Exceptions
Restrictions
Example
Adding an existing table to a replication group
Creating an object at the master definition site
Replicating a package
The DBMS_REPCAT. SET_COLUMNS procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.DROP_MASTER_REPOBJECT procedure
Exceptions
Restrictions
Examples
The DBMS_REPCAT. EXECUTE_DDL procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT. ALTER_MASTER_REPOBJECT procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT. COMMENT_ON_REPOBJECT procedure
Exceptions
Restrictions
Example
Replication Support with DBMS_REPCAT
The DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT procedure
Exceptions
Restrictions
Example
The replication support trigger
The replication support packages
Generating replication support for packages and procedures
The DBMS_REPCAT. GENERATE_REPLICATION_PACKAGE procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER procedure
Exceptions
Restrictions
Examples
Adding and Removing Master Sites with DBMS_REPCAT
The DBMS_REPCAT.ADD_MASTER_DATABASE procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.REMOVE_MASTER_DATABASES procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.COMMENT_ON_REPSITES procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT. RELOCATE_MASTERDEF procedure
Exceptions
Restrictions
Example
Maintaining the Repcatlog Queue with DBMS_REPCAT
The DBMS_REPCAT .DO_DEFERRED_REPCAT_ADMIN procedure
Exceptions
Restrictions
Example.
The DBMS_REPCAT.WAIT_MASTER_LOG procedure
Exceptions
Example
The DBMS_REPCAT. PURGE_MASTER_LOG procedure
Exceptions
Restrictions
Example
Quiescence with DBMS_REPCAT
The DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.RESUME_MASTER_ACTIVITY procedure
Exceptions
Restrictions
Example
Miscellaneous DBMS_REPCAT Procedures
The DBMS_REPCAT. REPCAT_IMPORT_CHECK procedure
Exceptions
Example
The DBMS_REPCAT. ALTER_MASTER_PROPAGATION procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT. SEND_AND_COMPARE_OLD_VALUES procedure (Oracle8 only)
Exceptions
Restrictions
DBMS_OFFLINE_OG: Performing Site Instantiation
Getting Started with DBMS_OFFLINE_OG
DBMS_OFFLINE_OG Interface
The DBMS_OFFLINE_OG.BEGIN_INSTANTIATION procedure
Exceptions
Restrictions
Example
The DBMS_OFFLINE_OG.BEGIN_LOAD procedure
Exceptions
Restrictions
The DBMS_OFFLINE_OG.END_INSTANTIATION procedure
Exceptions
Restrictions
The DBMS_OFFLINE_OG.END_LOAD procedure
Exceptions
Restrictions
The DBMS_OFFLINE_OG. RESUME_SUBSET_OF_MASTERS procedure
Exceptions
Restrictions
DBMS_RECTIFIER_DIFF: Comparing Replicated Tables
Getting Started with DBMS_RECTIFIER_DIFF
DBMS_RECTIFIER_DIFF Interface
The DBMS_RECTIFIER.DIFFERENCES procedure
Exceptions
Restrictions
Example
The DBMS_RECTIFIER_DIFF. RECTIFY procedure
Exceptions
Restrictions
Example
DBMS_REPUTIL: Enabling and Disabling Replication
Getting Started with DBMS_REPUTIL
DBMS_REPUTIL Interface
DBMS_REPUTIL.REPLICATION_OFF procedure
DBMS_REPUTIL.REPLICATION_ON procedure
16. Conflict Resolution
Getting Started with DBMS_REPCAT
DBMS_REPCAT Programs
DBMS-REPCAT Exceptions
DBMS-REPCAT Nonprogram Elements
Data Dictionary Views
Column Groups with DBMS_REPCAT
About Column Groups
Creating and Dropping Column Groups
The DBMS_REPCAT.DEFINE_COLUMN_GROUP procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.DROP_COLUMN_GROUP procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.MAKE_COLUMN_GROUP procedure
Exceptions
Restrictions
Example
Modifying Existing Column Groups
The DBMS_REPCAT.ADD_GROUPED_COLUMN procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.DROP_GROUPED_COLUMN procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.COMMENT_ON_COLUMN_GROUP procedure
Exceptions
Restrictions
Example
Priority Groups with DBMS_REPCAT
About Priority Groups
Creating, Maintaining, and Dropping Priority Groups
The DBMS_REPCAT.DEFINE_PRIORITY_GROUP procedure
Exceptions
Restrictions
Example
Creating a priority group for datatype CHAR
Creating a priority group for datatype VARCHAR
The DBMS_REPCAT.DROP_PRIORITY_GROUP procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.COMMENT_ON_PRIORITY_GROUP procedure
Exceptions
Restrictions
Example
Creating and Maintaining Priorities Within a Priority Group
The DBMS_REPCAT.ADD_PRIORITY_<datatype> procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.ALTER_PRIORITY procedure
Exceptions
Restrictions
Examples
The DBMS_REPCAT.ALTER_PRIORITY_<datatype> procedure
Exceptions
Restrictions
Example
Dropping Priorities from a Priority Group
The DBMS_REPCAT.DROP_PRIORITY procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.DROP_PRIORITY_<datatype> procedure
Exceptions
Restrictions
Example
Site Priority Groups with DBMS_REPCAT
About Site Priority Groups
Creating, Maintaining, and Dropping Site Priorities
The DBMS_REPCAT.DEFINE_SITE_PRIORITY procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.DROP_SITE_PRIORITY procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY procedure
Exceptions
Restrictions
Example
Maintaining Site Priorities
The DBMS_REPCAT.ADD_SITE_PRIORITY_SITE procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.DROP_SITE_PRIORITY_SITE procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.ALTER_SITE_PRIORITY procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE
Exceptions
Restrictions
Example
Assigning Resolution Methods with DBMS_REPCAT
About Resolution Methods
The DBMS_REPCAT.ADD_<conflicttype>_RESOLUTION
Exception
Restrictions
Examples
Examples of ADD_UPDATE_RESOLUTION
Examples of ADD_UNIQUE_RESOLUTION
Examples of ADD_DELETE_RESOLUTION
User-defined methods
The DBMS_REPCAT.DROP_<conflicttype>_RESOLUTION procedure
Exceptions
Restrictions
Example
The DBMS_REPCAT.COMMENT_ON_<conflicttype>_RESOLUTION procedure
Exceptions
Restrictions
Example
Monitoring Conflict Resolution with DBMS_REPCAT
About Monitoring
The DBMS_REPCAT.REGISTER_STATISTICS procedure
Exceptions
Example
The DBMS_REPCAT.CANCEL_STATISTICS procedure
Exceptions
Example
The DBMS_REPCAT.PURGE_STATISTICS procedure
Exceptions
Example
17. Deferred Transactions and Remote Procedure Calls
About Deferred Transactions and RPCs
About Remote Destinations
Data Dictionary Views
DBMS_DEFER_SYS: Managing Deferred Transactions
Getting Started with DBMS_DEFER_SYS
DBMS_DEFER_SYS programs
DBMS_DEFER_SYS exceptions
DBMS_DEFER_SYS nonprogram elements
Adding and Deleting Default Destinations
The DBMS_DEFER.SYS.ADD_DEFAULT_DEST procedure
Exceptions
Example
The DBMS_DEFER.SYS.DELETE_DEFAULT_DEST procedure
Example
Copying Deferred Transactions to New Destinations
The DBMS_DEFER_SYS.COPY procedure
Exceptions
Example
Maintenance Procedures
The DBMS_DEFER_SYS.DELETE_ERROR procedure
Exceptions
Examples
The DBMS_DEFER_SYS.DELETE_TRAN procedure
Exceptions
The DBMS_DEFER_SYS.DISABLED function
Exceptions
The DBMS_DEFER_SYS.EXECUTE_ERROR procedure
Exceptions
Restrictions
Example
The DBMS_DEFER_SYS.SET_DISABLED procedure
Exceptions
Restrictions
Example
Propagating Deferred RPCs
Scheduling strategies
The DBMS_DEFER_SYS.EXECUTE procedure
Exceptions
Examples
Advanced usage: using the EXECUTE parameters
The DBMS_DEFER_SYS. SCHEDULE_EXECUTION procedure
Example
The DBMS_DEFER_SYS.UNSCHEDULE_EXECUTION procedure
Exceptions
Scheduling Propagation (Oracle8 only)
The DBMS_DEFER_SYS.EXCLUDE_PUSH function (Oracle8 only)
The DBMS_DEFER_SYS.PURGE function (Oracle8 only)
Exceptions
The DBMS_DEFER_SYS.PUSH function
Exceptions
The DBMS_DEFER_SYS.SCHEDULE_PURGE procedure (Oracle8 only)
The DBMS_DEFER_SYS.SCHEDULE_PUSH procedure (Oracle8 only)
The DBMS_DEFER_SYS.UNSCHEDULE_PURGE procedure (Oracle8 only)
The DBMS_DEFER_SYS.UNSCHEDULE_PUSH procedure (Oracle8 only)
Exceptions
The DBMS_DEFER_SYS.REGISTER_PROPAGATOR procedure (Oracle8 only)
Exceptions
The DBMS_DEFER_SYS.UNREGISTER_PROPAGATOR procedure (Oracle8 only)
Exceptions
DBMS_DEFER: Building Deferred Calls
Getting Started with DBMS_DEFER
DBMS_DEFER programs
DBMS_DEFER exceptions
DBMS_DEFER nonprogram elements
Basic RPCs
The DBMS_DEFER.TRANSACTION procedure
Exceptions
Restrictions
Example
The DBMS_DEFER.CALL procedure
Exceptions
Restrictions
Example
The DBMS_DEFER.COMMIT_WORK procedure
Exceptions
Example:
Using DBMS_DEFER.TRANSACTION
Specifying nondefault destinations with TRANSACTION
Committing deferred RPC calls with COMMIT_WORK
Parameterized RPCs
The DBMS_DEFER. <datatype>_ARG procedure
Exceptions
Example
DBMS_DEFER_QUERY: Performing Diagnostics and Maintenance
Getting Started with DBMS_DEFER_QUERY
DBMS_DEFER_QUERY programs
DBMS_DEFER_QUERY nonprogram elements
The DBMS_DEFER_QUERY. GET_ARG_TYPE function
Exceptions
Example
The DBMS_DEFER_QUERY.GET_CALL_ARGS procedure
Exceptions
Example
The DBMS_DEFER_QUERY.GET_<datatype>_ARG function
Exceptions
Example
A. What’s on the Companion Disk?
Installing the Guide
Using the Guide
Index
About the Authors
Colophon
SPECIAL OFFER: Upgrade this ebook with O’Reilly
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Oracle Built-in Packages
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset