Sumário Itens Encontrados: 358A. What is on the Companion Disk?A.1 Installing the GuideA.2 Using the Guide1. Introduction1.1 The Power of Built?in Packages1.1.1 A Kinder, More Sharing Oracle1.2 Built?in Packages Covered in This Book1.2.1 Application Development Packages1.2.2 Server Management Packages1.2.3 Distributed Database Packages1.3 Using Built?in Packages1.3.1 What Is a Package?1.3.2 Controlling Access with Packages1.3.3 Referencing Built?in Package Elements1.3.4 Exception Handling and Built?in Packages1.3.5 Encapsulating Access to the Built?in Packages1.3.6 Calling Built?in Packaged Code from Oracle Developer/2000 Release 11.3.7 Accessing Built?in Packaged Technology from Within SQL1.4 Examining Built?in Package Source Code1.4.1 The STANDARD Package1.4.2 The DBMS_STANDARD Package2. Executing Dynamic SQL and PL/SQL2.1 Examples of Dynamic SQL2.2 Getting Started with DBMS_SQL2.2.1 Creating the DBMS_SQL Package2.2.2 Security and Privilege Issues2.2.3 DBMS_SQL Programs2.2.4 Types of Dynamic SQL2.2.5 DBMS_SQL Exceptions2.2.6 DBMS_SQL Nonprogram Elements2.3 The DBMS_SQL Interface2.3.1 Processing Flow of Dynamic SQL2.3.2 Opening the Cursor2.3.3 Parsing the SQL Statement2.3.4 Binding Values into Dynamic SQL2.3.5 Defining Cursor Columns2.3.6 Executing the Cursor2.3.7 Fetching Rows2.3.8 Retrieving Values2.3.9 Closing the Cursor2.3.10 Checking Cursor Status2.3.11 Describing Cursor Columns[Appendix A] What is on the Companion Disk? 2.4 Tips on Using Dynamic SQL2.4.1 Some Restrictions2.4.2 Privileges and Execution Authority with DBMS_SQL2.4.3 Combining Operations2.4.4 Minimizing Memory for Cursors2.4.5 Improving the Performance of Dynamic SQL2.4.6 Problem?Solving Dynamic SQL Errors2.4.7 Executing DDL in PL/SQL2.4.8 Executing Dynamic PL/SQL2.5 DBMS_SQL Examples2.5.1 A Generic Drop_Object Procedure2.5.2 A Generic Foreign Key Lookup Function2.5.3 A Wrapper for DBMS_SQL .DESCRIBE_COLUMNS2.5.4 Displaying Table Contents with Method 4 Dynamic SQL2.5.5 Indirect Referencing in PL/SQL2.5.6 Array Processing with DBMS_SQL2.5.7 Using the RETURNING Clause in Dynamic SQL3. Intersession Communication3.1 DBMS_PIPE: Communicating Between Sessions3.1.1 Getting Started with DBMS_PIPE3.1.2 How Database Pipes Work3.1.3 Managing Pipes and the Message Buffer3.1.4 Packing and Unpacking Messages3.1.5 Sending and Receiving Messages3.1.6 Tips on Using DBMS_PIPE3.1.7 DBMS_PIPE Examples3.2 DBMS_ALERT: Broadcasting Alerts to Users3.2.1 Getting Started with DBMS_ALERT3.2.2 The DBMS_ALERT Interface3.2.3 DBMS_ALERT Examples4. User Lock and Transaction Management4.1 DBMS_LOCK: Creating and Managing Resource Locks4.1.1 Getting Started with DBMS_LOCK4.1.2 The DBMS_LOCK Interface4.1.3 Tips on Using DBMS_LOCK4.1.4 DBMS_LOCK Examples4.2 DBMS_TRANSACTION: Interfacing to SQL Transaction Statements4.2.1 Getting Started with DBMS_TRANSACTION4.2.2 Advising Oracle About In?Doubt Transactions4.2.3 Committing Data4.2.4 Rolling Back Changes4.2.5 Setting Transaction Characteristics4.2.6 Cleaning Up Transaction Details4.2.7 Returning Transaction Identifiers5. Oracle Advanced Queuing5.1 Oracle AQ Concepts5.1.1 General Features5.1.2 Enqueue Features5.1.3 Dequeue Features5.1.4 Propagation Features5.1.5 A Glossary of Terms5.1.6 Components of Oracle AQ5.1.7 Queue Monitor5.1.8 Data Dictionary Views5.2 Getting Started with Oracle AQ5.2.1 Installing the Oracle AQ Facility5.2.2 Database Initialization5.2.3 Authorizing Accounts to Use Oracle AQ5.3 Oracle AQ Nonprogram Elements5.3.1 Constants5.3.2 Object Names5.3.3 Queue Type Names5.3.4 Agents Object Type5.3.5 Recipient and Subscriber List Table Types5.3.6 Message Properties Record Type5.3.7 Enqueue Options Record Type5.3.8 Dequeue Options Record Type5.3.9 Oracle AQ Exceptions5.4 DBMS_AQ: Interfacing to Oracle AQ (Oracle8 only)5.4.1 Enqueuing Messages5.4.2 Dequeuing Messages5.5 DBMS_AQADM: Performing AQ Administrative Tasks (Oracle8 only)5.5.1 Creating Queue Tables5.5.2 Creating and Starting Queues5.5.3 Managing Queue Subscribers5.5.4 Stopping and Dropping Queues5.5.5 Managing Propagation of Messages5.5.6 Verifying Queue Types5.5.7 Starting and Stopping the Queue Monitor5.6 Oracle AQ Database Objects5.6.1 Objects Per Queue Table5.6.2 Data Dictionary Objects5.7 Oracle AQ Examples5.7.1 Improving AQ Ease of Use5.7.2 Working with Prioritized Queues5.7.3 Building a Stack with AQ Using Sequence Deviation5.7.4 Browsing a Queue is Contents5.7.5 Searching by Correlation Identifier5.7.6 Using Time Delay and Expiration5.7.7 Working with Message Groups5.7.8 Working with Multiple Consumers6. Generating Output from PL/SQL Programs6.1 DBMS_OUTPUT: Displaying Output6.1.1 Getting Started with DBMS_OUTPUT6.1.2 Enabling and Disabling Output6.1.3 Writing to the DBMS_OUTPUT Buffer6.1.4 Retrieving Data from the DBMS_OUTPUT Buffer6.1.5 Tips on Using DBMS_OUTPUT6.1.6 DBMS_OUTPUT Examples6.2 UTL_FILE: Reading and Writing Server?side Files6.2.1 Getting Started with UTL_FILE6.2.2 Opening Files6.2.3 Reading from Files6.2.4 Writing to Files6.2.5 Closing Files6.2.6 Tips on Using UTL_FILE6.2.7 UTL_FILE Examples7. Defining an Application Profile7.1 Getting Started with DBMS_APPLICATION_INFO7.1.1 DBMS_APPLICATION_INFO Programs7.1.2 The V$ Virtual Tables7.1.3 DBMS_APPLICATION_INFO Nonprogram Elements7.2 DBMS_APPLICATION_INFO Interface7.2.1 The DBMS_APPLICATION_INFO.READ_CLIENT_INFO procedure7.2.2 The DBMS_APPLICATION_INFO.READ_MODULE procedure7.2.3 The DBMS_APPLICATION_INFO.SET_ACTION procedure7.2.4 The DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure7.2.5 The DBMS_APPLICATION_INFO.SET_MODULE procedure7.2.6 The DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure7.3 DBMS_APPLICATION_INFO Examples7.3.1 About the register_app Package7.3.2 The action Procedure7.3.3 The set_stats Procedure7.3.4 The Information Procedures7.3.5 Using the register_app Package7.3.6 Covering DBMS_APPLICATION_INFO7.3.7 Monitoring Application SQL Resource Consumption7.3.8 Session Monitoring and Three?Tier Architectures7.3.9 Tracking Long?Running Processes8. Managing Large Objects8.1 Getting Started with DBMS_LOB8.1.1 DBMS_LOB Programs8.1.2 DBMS_LOB Exceptionsiv8.1.3 DBMS_LOB Nonprogram Elements8.1.4 About the Examples8.2 LOB Concepts8.2.1 LOB Datatypes8.2.2 BFILE Considerations8.2.3 Internal LOB Considerations8.3 DBMS_LOB Interface8.3.1 Working with BFILEs8.3.2 Reading and Examining LOBs8.3.3 Updating BLOBs, CLOBs, and NCLOBs9. Datatype Packages9.1 DBMS_ROWID: Working with the ROWID Pseudo?Column (Oracle8 only)9.1.1 Getting Started with DBMS_ROWID9.1.2 ROWID Concepts9.1.3 The DBMS_ROWID Interface9.2 UTL_RAW: Manipulating Raw Data9.2.1 Getting Started with UTL_RAW9.2.2 Raw Data Manipulation Concepts9.2.3 The UTL_RAW Interface9.2.4 UTL_REF: Referencing Objects (Oracle8.0.4)9.2.5 UTL_REF Interface9.2.6 UTL_REF Example10. Miscellaneous Packages10.1 DBMS_UTILITY: Performing Miscellaneous Operations10.1.1 Getting Started with DBMS_UTILITY10.1.2 The DBMS_UTILITY Interface10.2 DBMS_DESCRIBE: Describing PL/SQL Program Headers10.2.1 Getting Started with DBMS_DESCRIBE10.2.2 Explaining DBMS_DESCRIBE Results10.2.3 DBMS_DESCRIBE Example10.3 DBMS_DDL: Compiling and Analyzing Objects10.3.1 Getting Started with DBMS_DDL10.3.2 Compiling PL/SQL Objects10.3.3 Computing Statistics for an Object10.3.4 Setting Referenceability of Tables10.4 DBMS_RANDOM: Generating Random Numbers (Oracle8 Only)10.4.1 Getting Started with DBMS_RANDOM10.4.2 DBMS_RANDOM Interface11. Managing Session Information11.1 DBMS_SESSION: Managing Session Information11.1.1 Getting Started with DBMS_SESSION11.1.2 Modifying Session Settings11.1.3 Obtaining Session Information11.1.4 Managing Session Resources11.1.5 DBMS_SESSION Examples11.2 DBMS_System: Setting Events for Debugging11.2.1 Getting Started with DBMS_SYSTEM11.2.2 DBMS_SYSTEM Interface11.2.3 DBMS_SYSTEM Examples12. Managing Server Resources12.1 DBMS_SPACE: Obtaining Space Information12.1.1 Getting Started with DBMS_SPACE12.1.2 The DBMS_SPACE Interface12.1.3 DBMS_SPACE Examples12.2 DBMS_SHARED_POOL: Pinning Objects12.2.1 Getting Started with DBMS_SHARED_POOL12.2.2 Pinning and Unpinning Objects12.2.3 Monitoring and Modifying Shared Pool Behavior12.2.4 DBMS_SHARED_POOL Examples13. Job Scheduling in the Database13.1 Getting Started with DBMS_ JOB13.1.1 DBMS_JOB Programs13.1.2 Job Definition Parameters13.2.1 INIT.ORA Parameters and Background Processes13.2.2 Job Execution and the Job Execution Environment13.2.3 Miscellaneous Notes13.2.4 DBMS_JOB Interface13.2.5 Submitting Jobs to the Job Queue13.2.6 Modifying Job Characteristics13.2.7 Removing Jobs and Changing Job Execution Status13.2.8 Transferring Jobs13.2 Job Queue Architecture13.3 Tips on Using DBMS_JOB13.3.1 Job Intervals and Date Arithmetic13.3.2 Viewing Job Information in the Data Dictionary13.3.3 DBMS_IJOB: Managing Other Users Jobs13.4 DBMS_JOB Examples13.4.1 Tracking Space in Tablespaces13.4.2 Fixing Broken Jobs Automatically13.4.3 Self?Modifying and Self?Aware Jobs14. Snapshots14.1 DBMS_SNAPSHOT: Managing Snapshots14.1.1 Getting Started with DBMS_SNAPSHOT14.1.2 Using the I_AM_A_REFRESH Package State Variable14.1.3 Refreshing Snapshots14.1.4 Purging the Snapshot Log14.1.5 Reorganizing Tables14.1.6 Registering Snapshots14.2.1 Getting Started with DBMS_REFRESH14.2.2 Creating and Destroying Snapshot Groups14.2.3 Adding and Subtracting Snapshots from Snapshot Groups14.2.4 Altering Properties of a Snapshot Group14.2.5 Manually Refreshing Snapshot Groups14.2 DBMS_REFRESH: Managing Snapshot Groups14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot Instantiation14.3.1 Getting Started with DBMS_OFFLINE_SNAPSHOT14.3.2 DBMS_OFFLINE_SNAPSHOT Interface14.4 DBMS_REPCAT: Managing Snapshot Replication Groups14.4.1 Getting Started with DBMS_REPCAT14.4.2 Creating and Dropping Snapshot Replication Groups14.4.3 Adding and Removing Snapshot Replication Group Objects14.4.4 Altering a Snapshot Replication Groups Propagation Mode14.4.5 Manually Refreshing a Snapshot Replication Group14.4.6 Switching the Master of a Snapshot Replication Group15. Advanced Replication15.1 DBMS_REPCAT_AUTH: Setting Up Administrative Accounts15.1.1 Getting Started with DBMS_REPCAT_AUTH15.1.2 Granting and Revoking Surrogate SYS Accounts15.1.3 Granting and Revoking Propagator Accounts (Oracle8)15.2 DBMS_REPCAT_ADMIN: Setting Up More Administrator Accounts15.2.1 Getting Started with DBMS_REPCAT_ADMIN15.2.2 Creating and Dropping Replication Administrator Accounts15.3 DBMS_REPCAT: Replication Environment Administration15.3.1 Getting Started with DBMS_REPCAT15.3.2 Replication Groups with DBMS_REPCAT15.3.3 Replicated Objects with DBMS_REPCAT15.3.4 Replication Support with DBMS_REPCAT15.3.5 Adding and Removing Master Sites with DBMS_REPCAT15.3.6 Maintaining the Repcatlog Queue with DBMS_REPCAT15.3.7 Quiescence with DBMS_REPCAT15.3.8 Miscellaneous DBMS_REPCAT Procedures15.4 DBMS_OFFLINE_OG: Performing Site Instantiation15.4.1 Getting Started with DBMS_OFFLINE_OG15.4.2 DBMS_OFFLINE_OG Interfacevii15.5 DBMS_RECTIFIER_DIFF: Comparing Replicated Tables15.5.1 Getting Started with DBMS_RECTIFIER_DIFF15.5.2 DBMS_RECTIFIER_DIFF Interface15.6 DBMS_REPUTIL: Enabling and Disabling Replication15.6.1 Getting Started with DBMS_REPUTIL15.6.2 DBMS_REPUTIL Interface16. Conflict Resolution16.1 Getting Started with DBMS_REPCAT16.1.1 DBMS_REPCAT Programs16.1.2 DBMS?REPCAT Exceptions16.1.3 DBMS?REPCAT Nonprogram Elements16.1.4 Data Dictionary Views16.2 Column Groups with DBMS_REPCAT16.2.1 About Column Groups16.2.2 Creating and Dropping Column Groups16.2.3 Modifying Existing Column Groups16.3 Priority Groups with DBMS_REPCAT16.3.1 About Priority Groups16.3.2 Creating, Maintaining, and Dropping Priority Groups16.3.3 Creating and Maintaining Priorities Within a Priority Group16.3.4 Dropping Priorities from a Priority Group16.4 Site Priority Groups with DBMS_REPCAT16.4.1 About Site Priority Groups16.4.2 Creating, Maintaining, and Dropping Site Priorities16.4.3 Maintaining Site Priorities16.5 Assigning Resolution Methods with DBMS_REPCAT16.5.1 About Resolution Methods16.6 Monitoring Conflict Resolution with DBMS_REPCAT16.6.1 About Monitoring17. Deferred Transactions and Remote Procedure Calls17.1 About Deferred Transactions and RPCs17.1.1 About Remote Destinations17.1.2 Data Dictionary Views17.2 DBMS_DEFER_SYS: Managing Deferred Transactions17.2.1 Getting Started with DBMS_DEFER_SYS17.2.2 Adding and Deleting Default Destinations17.2.3 Copying Deferred Transactions to New Destinations17.2.4 Maintenance Procedures17.2.5 Propagating Deferred RPCs17.2.6 Scheduling Propagation (Oracle8 only)17.3 DBMS_DEFER: Building Deferred Calls17.3.1 Getting Started with DBMS_DEFER17.3.2 Basic RPCs17.3.3 Parameterized RPCs17.4 DBMS_DEFER_QUERY: Performing Diagnostics and Maintenance17.4.1 Getting Started with DBMS_DEFER_QUERYIndexPart I: OverviewPart II: Application Development PackagesPart III: Server Management PackagesPart IV: Distributed Database PackagesDedicationPrefaceStructure of This BookConventions Used in This BookVersions of Oracle