SUMMARY

SEARCH

Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.

BOOK ORACLE BUILT-IN PACKAGES

SUMMARY

370

A. What is on the Companion Disk?
A.1 Installing the Guide
A.2 Using the Guide
1. Introduction
1.1 The Power of Built?in Packages
1.1.1 A Kinder, More Sharing Oracle
1.2 Built?in Packages Covered in This Book
1.2.1 Application Development Packages
1.2.2 Server Management Packages
1.2.3 Distributed Database Packages
1.3 Using Built?in Packages
1.3.1 What Is a Package?
1.3.2 Controlling Access with Packages
1.3.3 Referencing Built?in Package Elements
1.3.4 Exception Handling and Built?in Packages
1.3.5 Encapsulating Access to the Built?in Packages
1.3.6 Calling Built?in Packaged Code from Oracle Developer/2000 Release 1
1.3.7 Accessing Built?in Packaged Technology from Within SQL
1.4 Examining Built?in Package Source Code
1.4.1 The STANDARD Package
1.4.2 The DBMS_STANDARD Package
2. Executing Dynamic SQL and PL/SQL
2.1 Examples of Dynamic SQL
2.2 Getting Started with DBMS_SQL
2.2.1 Creating the DBMS_SQL Package
2.2.2 Security and Privilege Issues
2.2.3 DBMS_SQL Programs
2.2.4 Types of Dynamic SQL
2.2.5 DBMS_SQL Exceptions
2.2.6 DBMS_SQL Nonprogram Elements
2.3 The DBMS_SQL Interface
2.3.1 Processing Flow of Dynamic SQL
2.3.2 Opening the Cursor
2.3.3 Parsing the SQL Statement
2.3.4 Binding Values into Dynamic SQL
2.3.5 Defining Cursor Columns
2.3.6 Executing the Cursor
2.3.7 Fetching Rows
2.3.8 Retrieving Values
2.3.9 Closing the Cursor
2.3.10 Checking Cursor Status
2.3.11 Describing Cursor Columns
[Appendix A] What is on the Companion Disk?
2.4 Tips on Using Dynamic SQL
2.4.1 Some Restrictions
2.4.2 Privileges and Execution Authority with DBMS_SQL
2.4.3 Combining Operations
2.4.4 Minimizing Memory for Cursors
2.4.5 Improving the Performance of Dynamic SQL
2.4.6 Problem?Solving Dynamic SQL Errors
2.4.7 Executing DDL in PL/SQL
2.4.8 Executing Dynamic PL/SQL
2.5 DBMS_SQL Examples
2.5.1 A Generic Drop_Object Procedure
2.5.2 A Generic Foreign Key Lookup Function
2.5.3 A Wrapper for DBMS_SQL .DESCRIBE_COLUMNS
2.5.4 Displaying Table Contents with Method 4 Dynamic SQL
2.5.5 Indirect Referencing in PL/SQL
2.5.6 Array Processing with DBMS_SQL
2.5.7 Using the RETURNING Clause in Dynamic SQL
3. Intersession Communication
3.1 DBMS_PIPE: Communicating Between Sessions
3.1.1 Getting Started with DBMS_PIPE
3.1.2 How Database Pipes Work
3.1.3 Managing Pipes and the Message Buffer
3.1.4 Packing and Unpacking Messages
3.1.5 Sending and Receiving Messages
3.1.6 Tips on Using DBMS_PIPE
3.1.7 DBMS_PIPE Examples
3.2 DBMS_ALERT: Broadcasting Alerts to Users
3.2.1 Getting Started with DBMS_ALERT
3.2.2 The DBMS_ALERT Interface
3.2.3 DBMS_ALERT Examples
4. User Lock and Transaction Management
4.1 DBMS_LOCK: Creating and Managing Resource Locks
4.1.1 Getting Started with DBMS_LOCK
4.1.2 The DBMS_LOCK Interface
4.1.3 Tips on Using DBMS_LOCK
4.1.4 DBMS_LOCK Examples
4.2 DBMS_TRANSACTION: Interfacing to SQL Transaction Statements
4.2.1 Getting Started with DBMS_TRANSACTION
4.2.2 Advising Oracle About In?Doubt Transactions
4.2.3 Committing Data
4.2.4 Rolling Back Changes
4.2.5 Setting Transaction Characteristics
4.2.6 Cleaning Up Transaction Details
4.2.7 Returning Transaction Identifiers
[Appendix A] What is on the Companion Disk?
5. Oracle Advanced Queuing
5.1 Oracle AQ Concepts
5.1.1 General Features
5.1.2 Enqueue Features
5.1.3 Dequeue Features
5.1.4 Propagation Features
5.1.5 A Glossary of Terms
5.1.6 Components of Oracle AQ
5.1.7 Queue Monitor
5.1.8 Data Dictionary Views
5.2 Getting Started with Oracle AQ
5.2.1 Installing the Oracle AQ Facility
5.2.2 Database Initialization
5.2.3 Authorizing Accounts to Use Oracle AQ
5.3 Oracle AQ Nonprogram Elements
5.3.1 Constants
5.3.2 Object Names
5.3.3 Queue Type Names
5.3.4 Agents Object Type
5.3.5 Recipient and Subscriber List Table Types
5.3.6 Message Properties Record Type
5.3.7 Enqueue Options Record Type
5.3.8 Dequeue Options Record Type
5.3.9 Oracle AQ Exceptions
5.4 DBMS_AQ: Interfacing to Oracle AQ (Oracle8 only)
5.4.1 Enqueuing Messages
5.4.2 Dequeuing Messages
5.5 DBMS_AQADM: Performing AQ Administrative Tasks (Oracle8 only)
5.5.1 Creating Queue Tables
5.5.2 Creating and Starting Queues
5.5.3 Managing Queue Subscribers
5.5.4 Stopping and Dropping Queues
5.5.5 Managing Propagation of Messages
5.5.6 Verifying Queue Types
5.5.7 Starting and Stopping the Queue Monitor
5.6 Oracle AQ Database Objects
5.6.1 Objects Per Queue Table
5.6.2 Data Dictionary Objects
5.7 Oracle AQ Examples
5.7.1 Improving AQ Ease of Use
5.7.2 Working with Prioritized Queues
5.7.3 Building a Stack with AQ Using Sequence Deviation
5.7.4 Browsing a Queue is Contents
5.7.5 Searching by Correlation Identifier
5.7.6 Using Time Delay and Expiration
5.7.7 Working with Message Groups
[Appendix A] What is on the Companion Disk?
5.7.8 Working with Multiple Consumers
6. Generating Output from PL/SQL Programs
6.1 DBMS_OUTPUT: Displaying Output
6.1.1 Getting Started with DBMS_OUTPUT
6.1.2 Enabling and Disabling Output
6.1.3 Writing to the DBMS_OUTPUT Buffer
6.1.4 Retrieving Data from the DBMS_OUTPUT Buffer
6.1.5 Tips on Using DBMS_OUTPUT
6.1.6 DBMS_OUTPUT Examples
6.2 UTL_FILE: Reading and Writing Server?side Files
6.2.1 Getting Started with UTL_FILE
6.2.2 Opening Files
6.2.3 Reading from Files
6.2.4 Writing to Files
6.2.5 Closing Files
6.2.6 Tips on Using UTL_FILE
6.2.7 UTL_FILE Examples
7. Defining an Application Profile
7.1 Getting Started with DBMS_APPLICATION_INFO
7.1.1 DBMS_APPLICATION_INFO Programs
7.1.2 The V$ Virtual Tables
7.1.3 DBMS_APPLICATION_INFO Nonprogram Elements
7.2 DBMS_APPLICATION_INFO Interface
7.2.1 The DBMS_APPLICATION_INFO.READ_CLIENT_INFO procedure
7.2.2 The DBMS_APPLICATION_INFO.READ_MODULE procedure
7.2.3 The DBMS_APPLICATION_INFO.SET_ACTION procedure
7.2.4 The DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure
7.2.5 The DBMS_APPLICATION_INFO.SET_MODULE procedure
7.2.6 The DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure
7.3 DBMS_APPLICATION_INFO Examples
7.3.1 About the register_app Package
7.3.2 The action Procedure
7.3.3 The set_stats Procedure
7.3.4 The Information Procedures
7.3.5 Using the register_app Package
7.3.6 Covering DBMS_APPLICATION_INFO
7.3.7 Monitoring Application SQL Resource Consumption
7.3.8 Session Monitoring and Three?Tier Architectures
7.3.9 Tracking Long?Running Processes
8. Managing Large Objects
8.1 Getting Started with DBMS_LOB
8.1.1 DBMS_LOB Programs
8.1.2 DBMS_LOB Exceptions
[Appendix A] What is on the Companion Disk?
iv
8. Managing Large Objects
8.1.3 DBMS_LOB Nonprogram Elements
8.1.4 About the Examples
8.2 LOB Concepts
8.2.1 LOB Datatypes
8.2.2 BFILE Considerations
8.2.3 Internal LOB Considerations
8.3 DBMS_LOB Interface
8.3.1 Working with BFILEs
8.3.2 Reading and Examining LOBs
8.3.3 Updating BLOBs, CLOBs, and NCLOBs
9. Datatype Packages
9.1 DBMS_ROWID: Working with the ROWID Pseudo?Column (Oracle8 only)
9.1.1 Getting Started with DBMS_ROWID
9.1.2 ROWID Concepts
9.1.3 The DBMS_ROWID Interface
9.2 UTL_RAW: Manipulating Raw Data
9.2.1 Getting Started with UTL_RAW
9.2.2 Raw Data Manipulation Concepts
9.2.3 The UTL_RAW Interface
9.2.4 UTL_REF: Referencing Objects (Oracle8.0.4)
9.2.5 UTL_REF Interface
9.2.6 UTL_REF Example
10. Miscellaneous Packages
10.1 DBMS_UTILITY: Performing Miscellaneous Operations
10.1.1 Getting Started with DBMS_UTILITY
10.1.2 The DBMS_UTILITY Interface
10.2 DBMS_DESCRIBE: Describing PL/SQL Program Headers
10.2.1 Getting Started with DBMS_DESCRIBE
10.2.2 Explaining DBMS_DESCRIBE Results
10.2.3 DBMS_DESCRIBE Example
10.3 DBMS_DDL: Compiling and Analyzing Objects
10.3.1 Getting Started with DBMS_DDL
10.3.2 Compiling PL/SQL Objects
10.3.3 Computing Statistics for an Object
10.3.4 Setting Referenceability of Tables
10.4 DBMS_RANDOM: Generating Random Numbers (Oracle8 Only)
10.4.1 Getting Started with DBMS_RANDOM
10.4.2 DBMS_RANDOM Interface
[Appendix A] What is on the Companion Disk?
11. Managing Session Information
11.1 DBMS_SESSION: Managing Session Information
11.1.1 Getting Started with DBMS_SESSION
11.1.2 Modifying Session Settings
11.1.3 Obtaining Session Information
11.1.4 Managing Session Resources
11.1.5 DBMS_SESSION Examples
11.2 DBMS_System: Setting Events for Debugging
11.2.1 Getting Started with DBMS_SYSTEM
11.2.2 DBMS_SYSTEM Interface
11.2.3 DBMS_SYSTEM Examples
12. Managing Server Resources
12.1 DBMS_SPACE: Obtaining Space Information
12.1.1 Getting Started with DBMS_SPACE
12.1.2 The DBMS_SPACE Interface
12.1.3 DBMS_SPACE Examples
12.2 DBMS_SHARED_POOL: Pinning Objects
12.2.1 Getting Started with DBMS_SHARED_POOL
12.2.2 Pinning and Unpinning Objects
12.2.3 Monitoring and Modifying Shared Pool Behavior
12.2.4 DBMS_SHARED_POOL Examples
13. Job Scheduling in the Database
13.1 Getting Started with DBMS_ JOB
13.1.1 DBMS_JOB Programs
13.1.2 Job Definition Parameters
13.2.1 INIT.ORA Parameters and Background Processes
13.2.2 Job Execution and the Job Execution Environment
13.2.3 Miscellaneous Notes
13.2.4 DBMS_JOB Interface
13.2.5 Submitting Jobs to the Job Queue
13.2.6 Modifying Job Characteristics
13.2.7 Removing Jobs and Changing Job Execution Status
13.2.8 Transferring Jobs
13.2 Job Queue Architecture
13.3 Tips on Using DBMS_JOB
13.3.1 Job Intervals and Date Arithmetic
13.3.2 Viewing Job Information in the Data Dictionary
13.3.3 DBMS_IJOB: Managing Other Users Jobs
13.4 DBMS_JOB Examples
13.4.1 Tracking Space in Tablespaces
13.4.2 Fixing Broken Jobs Automatically
13.4.3 Self?Modifying and Self?Aware Jobs
[Appendix A] What is on the Companion Disk?
14. Snapshots
14.1 DBMS_SNAPSHOT: Managing Snapshots
14.1.1 Getting Started with DBMS_SNAPSHOT
14.1.2 Using the I_AM_A_REFRESH Package State Variable
14.1.3 Refreshing Snapshots
14.1.4 Purging the Snapshot Log
14.1.5 Reorganizing Tables
14.1.6 Registering Snapshots
14.2.1 Getting Started with DBMS_REFRESH
14.2.2 Creating and Destroying Snapshot Groups
14.2.3 Adding and Subtracting Snapshots from Snapshot Groups
14.2.4 Altering Properties of a Snapshot Group
14.2.5 Manually Refreshing Snapshot Groups
14.2 DBMS_REFRESH: Managing Snapshot Groups
14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot Instantiation
14.3.1 Getting Started with DBMS_OFFLINE_SNAPSHOT
14.3.2 DBMS_OFFLINE_SNAPSHOT Interface
14.4 DBMS_REPCAT: Managing Snapshot Replication Groups
14.4.1 Getting Started with DBMS_REPCAT
14.4.2 Creating and Dropping Snapshot Replication Groups
14.4.3 Adding and Removing Snapshot Replication Group Objects
14.4.4 Altering a Snapshot Replication Groups Propagation Mode
14.4.5 Manually Refreshing a Snapshot Replication Group
14.4.6 Switching the Master of a Snapshot Replication Group
15. Advanced Replication
15.1 DBMS_REPCAT_AUTH: Setting Up Administrative Accounts
15.1.1 Getting Started with DBMS_REPCAT_AUTH
15.1.2 Granting and Revoking Surrogate SYS Accounts
15.1.3 Granting and Revoking Propagator Accounts (Oracle8)
15.2 DBMS_REPCAT_ADMIN: Setting Up More Administrator Accounts
15.2.1 Getting Started with DBMS_REPCAT_ADMIN
15.2.2 Creating and Dropping Replication Administrator Accounts
15.3 DBMS_REPCAT: Replication Environment Administration
15.3.1 Getting Started with DBMS_REPCAT
15.3.2 Replication Groups with DBMS_REPCAT
15.3.3 Replicated Objects with DBMS_REPCAT
15.3.4 Replication Support with DBMS_REPCAT
15.3.5 Adding and Removing Master Sites with DBMS_REPCAT
15.3.6 Maintaining the Repcatlog Queue with DBMS_REPCAT
15.3.7 Quiescence with DBMS_REPCAT
15.3.8 Miscellaneous DBMS_REPCAT Procedures
15.4 DBMS_OFFLINE_OG: Performing Site Instantiation
15.4.1 Getting Started with DBMS_OFFLINE_OG
15.4.2 DBMS_OFFLINE_OG Interface
[Appendix A] What is on the Companion Disk?
vii
15.5 DBMS_RECTIFIER_DIFF: Comparing Replicated Tables
15.5.1 Getting Started with DBMS_RECTIFIER_DIFF
15.5.2 DBMS_RECTIFIER_DIFF Interface
15.6 DBMS_REPUTIL: Enabling and Disabling Replication
15.6.1 Getting Started with DBMS_REPUTIL
15.6.2 DBMS_REPUTIL Interface
16. Conflict Resolution
16.1 Getting Started with DBMS_REPCAT
16.1.1 DBMS_REPCAT Programs
16.1.2 DBMS?REPCAT Exceptions
16.1.3 DBMS?REPCAT Nonprogram Elements
16.1.4 Data Dictionary Views
16.2 Column Groups with DBMS_REPCAT
16.2.1 About Column Groups
16.2.2 Creating and Dropping Column Groups
16.2.3 Modifying Existing Column Groups
16.3 Priority Groups with DBMS_REPCAT
16.3.1 About Priority Groups
16.3.2 Creating, Maintaining, and Dropping Priority Groups
16.3.3 Creating and Maintaining Priorities Within a Priority Group
16.3.4 Dropping Priorities from a Priority Group
16.4 Site Priority Groups with DBMS_REPCAT
16.4.1 About Site Priority Groups
16.4.2 Creating, Maintaining, and Dropping Site Priorities
16.4.3 Maintaining Site Priorities
16.5 Assigning Resolution Methods with DBMS_REPCAT
16.5.1 About Resolution Methods
16.6 Monitoring Conflict Resolution with DBMS_REPCAT
16.6.1 About Monitoring
17. Deferred Transactions and Remote Procedure Calls
17.1 About Deferred Transactions and RPCs
17.1.1 About Remote Destinations
17.1.2 Data Dictionary Views
17.2 DBMS_DEFER_SYS: Managing Deferred Transactions
17.2.1 Getting Started with DBMS_DEFER_SYS
17.2.2 Adding and Deleting Default Destinations
17.2.3 Copying Deferred Transactions to New Destinations
17.2.4 Maintenance Procedures
17.2.5 Propagating Deferred RPCs
17.2.6 Scheduling Propagation (Oracle8 only)
[Appendix A] What is on the Companion Disk?
17.3 DBMS_DEFER: Building Deferred Calls
17.3.1 Getting Started with DBMS_DEFER
17.3.2 Basic RPCs
17.3.3 Parameterized RPCs
17.4 DBMS_DEFER_QUERY: Performing Diagnostics and Maintenance
17.4.1 Getting Started with DBMS_DEFER_QUERY
Index
Part I: Overview
Part II: Application Development Packages
Part III: Server Management Packages
Part IV: Distributed Database Packages
Part I: Overview
Part II: Application Development Packages
Part III: Server Management Packages
Part IV: Distributed Database Packages
Dedication
Preface
Structure of This Book
Conventions Used in This Book
Versions of Oracle