Oracle Built in Packages

Table of Contents......Page 2
BookmarkTitle:......Page 11
A.1 Installing the Guide......Page 12
A.2 Using the Guide......Page 13
BookmarkTitle:......Page 14
1.1 The Power of Built-in Packages......Page 15
1.1.1 A Kinder , More Sharing Oracle......Page 16
1.2 Built-in Packages Covered in This Book......Page 17
1.2.1 Application Development Packages......Page 20
1.2.2 Server Management Packages......Page 21
1.2.3 Distributed Database Packages......Page 22
1.3.1 What Is a Package?......Page 24
1.3.2 Controlling Access with Packages......Page 25
1.3.3 Referencing Built-in Package Elements......Page 27
1.3.4 Exception Handling and Built-in Packages......Page 28
1.3.5 Encapsulating Access to the Built-in Packages......Page 34
1.3.6 Calling Built-in Packaged Code from Oracle Developer/2000 Release 1......Page 37
1.3.7 Accessing Built-in Packaged Technology from Within SQL......Page 40
1.4 Examining Built-in Package Source Code......Page 48
1.4.1 The STANDARD Package......Page 49
1.4.2 The DBMS_STANDARD Package......Page 50
BookmarkTitle:......Page 52
2.1 Examples of Dynamic SQL......Page 53
2.2.3 DBMS_SQL Programs......Page 56
2.2.4 Types of Dynamic SQL......Page 57
2.2.6 DBMS_SQL Nonprogram Elements......Page 59
2.3.1 Processing Flow of Dynamic SQL......Page 62
2.3.2 Opening the Cursor......Page 64
2.3.3 Parsing the SQL Statement......Page 65
2.3.4 Binding Values into Dynamic SQL......Page 68
2.3.5 Defining Cursor Columns......Page 73
2.3.7 Fetching Rows......Page 76
2.3.8 Retrieving Values......Page 79
2.3.9 Closing the Cursor......Page 86
2.3.10 Checking Cursor Status......Page 87
2.3.11 Describing Cursor Columns......Page 90
2.4.2 Privileges and Execution Authority with DBMS_SQL......Page 95
2.4.3 Combining Operations......Page 97
2.4.4 Minimizing Memory for Cursors......Page 98
2.4.5 Improving the Performance of Dynamic SQL......Page 99
2.4.6 Problem-Solving Dynamic SQL Errors......Page 101
2.4.8 Executing Dynamic PL/SQL......Page 104
2.5.1 A Generic Drop_Object Procedure......Page 107
2.5.2 A Generic Foreign Key Lookup Function......Page 108
2.5.3 A Wrapper for DBMS_SQL .DESCRIBE_COLUMNS......Page 114
2.5.4 Displaying Table Contents with Method 4 Dynamic SQL......Page 117
2.5.5 Indirect Referencing in PL/SQL......Page 126
2.5.6 Array Processing with DBMS_SQL......Page 131
2.5.7 Using the RETURNING Clause in Dynamic SQL......Page 144
BookmarkTitle:......Page 148
3.1 DBMS_PIPE: Communicating Between Sessions......Page 149
3.1.1 Getting Started with DBMS_PIPE......Page 150
3.1.2 How Database Pipes Work......Page 151
3.1.3 Managing Pipes and the Message Buffer......Page 153
3.1.4 Packing and Unpacking Messages......Page 159
3.1.5 Sending and Receiving Messages......Page 168
3.1.6 Tips on Using DBMS_PIPE......Page 171
3.1.7 DBMS_PIPE Examples......Page 174
3.2.1 Getting Started with DBMS_ALERT......Page 194
3.2.2 The DBMS_ALERT Interface......Page 195
3.2.3 DBMS_ALERT Examples......Page 201
BookmarkTitle:......Page 212
4.1.1 Getting Started with DBMS_LOCK......Page 213
4.1.2 The DBMS_LOCK Interface......Page 215
4.1.3 Tips on Using DBMS_LOCK......Page 221
4.1.4 DBMS_LOCK Examples......Page 223
4.2.1 Getting Started with DBMS_TRANSACTION......Page 231
4.2.2 Advising Oracle About In-Doubt Transactions......Page 233
4.2.3 Committing Data......Page 234
4.2.4 Rolling Back Changes......Page 235
4.2.5 Setting Transaction Characteristics......Page 237
4.2.6 Cleaning Up Transaction Details......Page 239
4.2.7 Returning Transaction Identifiers......Page 241
BookmarkTitle:......Page 244
5. Oracle Advanced Queuing......Page 245
5.1.1 General Features......Page 246
5.1.2 Enqueue Features......Page 247
5.1.4 Propagation Features......Page 248
5.1.5 A Glossary of Terms......Page 250
5.1.6 Components of Oracle AQ......Page 253
5.1.8 Data Dictionary Views......Page 254
5.2.1 Installing the Oracle AQ Facility......Page 255
5.2.2 Database Initialization......Page 256
5.2.3 Authorizing Accounts to Use Oracle AQ......Page 257
5.3.1 Constants......Page 259
5.3.2 Object Names......Page 260
5.3.4 Agents Object Type......Page 261
5.3.6 Message Properties Record Type......Page 262
5.3.7 Enqueue Options Record Type......Page 264
5.3.8 Dequeue Options Record Type......Page 265
5.3.9 Oracle AQ Exceptions......Page 267
5.4.1 Enqueuing Messages......Page 269
5.4.2 Dequeuing Messages......Page 272
5.5 DBMS_AQADM: Performing AQ Administrative Tasks (Oracle8 only)......Page 278
5.5.1 Creating Queue Tables......Page 279
5.5.2 Creating and Starting Queues......Page 282
5.5.3 Managing Queue Subscribers......Page 286
5.5.4 Stopping and Dropping Queues......Page 288
5.5.5 Managing Propagation of Messages......Page 290
5.5.6 Verifying Queue Types......Page 292
5.5.7 Starting and Stopping the Queue Monitor......Page 293
5.6.1 Objects Per Queue Table......Page 294
5.6.2 Data Dictionary Objects......Page 297
5.7 Oracle AQ Examples......Page 300
5.7.1 Improving AQ Ease of Use......Page 301
5.7.2 Working with Prioritized Queues......Page 303
5.7.3 Building a Stack with AQ Using Sequence Deviation......Page 307
5.7.4 Browsing a Queue's Contents......Page 309
5.7.5 Searching by Correlation Identifier......Page 313
5.7.6 Using Time Delay and Expiration......Page 317
5.7.7 Working with Message Groups......Page 323
5.7.8 Working with Multiple Consumers......Page 326
BookmarkTitle:......Page 332
6.1.1 Getting Started with DBMS_OUTPUT......Page 333
6.1.2 Enabling and Disabling Output......Page 336
6.1.3 Writing to the DBMS_OUTPUT Buffer......Page 338
6.1.4 Retrieving Data from the DBMS_OUTPUT Buffer......Page 339
6.1.5 Tips on Using DBMS_OUTPUT......Page 342
6.1.6 DBMS_OUTPUT Examples......Page 343
6.2.1 Getting Started with UTL_FILE......Page 347
6.2.2 Opening Files......Page 353
6.2.3 Reading from Files......Page 355
6.2.4 Writing to Files......Page 356
6.2.5 Closing Files......Page 360
6.2.6 Tips on Using UTL_FILE......Page 361
6.2.7 UTL_FILE Examples......Page 368
BookmarkTitle:......Page 375
7.1.1 DBMS_APPLICATION_INFO Programs......Page 376
7.1.3 DBMS_APPLICATION_INFO Nonprogram Elements......Page 377
7.2.2 The DBMS_APPLICATION_INFO.READ_MODULE procedure......Page 379
7.2.3 The DBMS_APPLICATION_INFO.SET_ACTION procedure......Page 380
7.2.4 The DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure......Page 382
7.2.5 The DBMS_APPLICATION_INFO.SET_MODULE procedure......Page 383
7.2.6 The DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure......Page 384
7.3.1 About the register_app Package......Page 387
7.3.3 The set_stats Procedure......Page 388
7.3.4 The Information Procedures......Page 390
7.3.5 Using the register_app Package......Page 391
7.3.6 Covering DBMS_APPLICATION_INFO......Page 392
7.3.8 Session Monitoring and Three-Tier Architectures......Page 394
7.3.9 Tracking Long-Running Processes......Page 395
BookmarkTitle:......Page 399
8.1.1 DBMS_LOB Programs......Page 400
8.1.4 About the Examples......Page 402
8.2.1 LOB Datatypes......Page 405
8.2.2 BFILE Considerations......Page 408
8.2.3 Internal LOB Considerations......Page 409
8.3.1 Working with BFILEs......Page 412
8.3.2 Reading and Examining LOBs......Page 425
8.3.3 Updating BLOBs, CLOBs, and NCLOBs......Page 434
BookmarkTitle:......Page 443
9.1.1 Getting Started with DBMS_ROWID......Page 444
9.1.2 ROWID Concepts......Page 446
9.1.3 The DBMS_ROWID Interface......Page 447
9.2.1 Getting Started with UTL_RAW......Page 454
9.2.2 Raw Data Manipulation Concepts......Page 456
9.2.3 The UTL_RAW Interface......Page 457
9.2.4 UTL_REF: Referencing Objects (Oracle8.0.4)......Page 473
9.2.5 UTL_REF Interface......Page 475
9.2.6 UTL_REF Example......Page 478
BookmarkTitle:......Page 481
10.1.1 Getting Started with DBMS_UTILITY......Page 482
10.1.2 The DBMS_UTILITY Interface......Page 485
10.2.1 Getting Started with DBMS_DESCRIBE......Page 503
10.2.2 Explaining DBMS_DESCRIBE Results......Page 506
10.2.3 DBMS_DESCRIBE Example......Page 509
10.3.1 Getting Started with DBMS_DDL......Page 514
10.3.2 Compiling PL/SQL Objects......Page 515
10.3.3 Computing Statistics for an Object......Page 518
10.3.4 Setting Referenceability of Tables......Page 520
10.4.1 Getting Started with DBMS_RANDOM......Page 524
10.4.2 DBMS_RANDOM Interface......Page 525
BookmarkTitle:......Page 527
11.1.1 Getting Started with DBMS_SESSION......Page 528
11.1.2 Modifying Session Settings......Page 529
11.1.3 Obtaining Session Information......Page 533
11.1.4 Managing Session Resources......Page 535
11.1.5 DBMS_SESSION Examples......Page 540
11.2.1 Getting Started with DBMS_SYSTEM......Page 550
11.2.2 DBMS_SYSTEM Interface......Page 551
11.2.3 DBMS_SYSTEM Examples......Page 553
BookmarkTitle:......Page 557
12.1.1 Getting Started with DBMS_SPACE......Page 558
12.1.2 The DBMS_SPACE Interface......Page 559
12.1.3 DBMS_SPACE Examples......Page 563
12.2.1 Getting Started with DBMS_SHARED_POOL......Page 573
12.2.2 Pinning and Unpinning Objects......Page 574
12.2.3 Monitoring and Modifying Shared Pool Behavior......Page 576
12.2.4 DBMS_SHARED_POOL Examples......Page 578
BookmarkTitle:......Page 583
13.1.1 DBMS_JOB Programs......Page 584
13.1.2 Job Definition Parameters......Page 585
13.2.3 Miscellaneous Notes......Page 588
13.2.4 DBMS_JOB Interface......Page 589
13.2.6 Modifying Job Characteristics......Page 590
13.2.7 Removing Jobs and Changing Job Execution Status......Page 591
13.2.8 Transferring Jobs......Page 593
BookmarkTitle:......Page 596
13.2 Job Queue Architecture......Page 598
13.3.1 Job Intervals and Date Arithmetic......Page 601
13.3.2 Viewing Job Information in the Data Dictionary......Page 602
13.3.3 DBMS_IJOB: Managing Other Users' Jobs......Page 605
13.4.1 Tracking Space in Tablespaces......Page 607
13.4.2 Fixing Broken Jobs Automatically......Page 608
13.4.3 Self-Modifying and Self-Aware Jobs......Page 609
BookmarkTitle:......Page 612
14.1.1 Getting Started with DBMS_SNAPSHOT......Page 613
14.1.2 Using the I_AM_A_REFRESH Package State Variable......Page 614
14.1.3 Refreshing Snapshots......Page 616
14.1.4 Purging the Snapshot Log......Page 620
14.1.5 Reorganizing Tables......Page 621
14.1.6 Registering Snapshots......Page 623
14.2.2 Creating and Destroying Snapshot Groups......Page 627
14.2.3 Adding and Subtracting Snapshots from Snapshot Groups......Page 628
14.2.4 Altering Properties of a Snapshot Group......Page 629
14.2.5 Manually Refreshing Snapshot Groups......Page 633
14.2 DBMS_REFRESH: Managing Snapshot Groups......Page 634
14.3.1 Getting Started with DBMS_OFFLINE_SNAPSHOT......Page 636
14.3.2 DBMS_OFFLINE_SNAPSHOT Interface......Page 637
14.4.1 Getting Started with DBMS_REPCAT......Page 639
14.4.2 Creating and Dropping Snapshot Replication Groups......Page 640
14.4.3 Adding and Removing Snapshot Replication Group Objects......Page 643
14.4.4 Altering a Snapshot Replication Group's Propagation Mode......Page 645
14.4.5 Manually Refreshing a Snapshot Replication Group......Page 647
14.4.6 Switching the Master of a Snapshot Replication Group......Page 648
BookmarkTitle:......Page 651
15.1.1 Getting Started with DBMS_REPCAT_AUTH......Page 652
15.1.2 Granting and Revoking Surrogate SYS Accounts......Page 653
15.1.3 Granting and Revoking Propagator Accounts (Oracle8)......Page 654
15.2.1 Getting Started with DBMS_REPCAT_ADMIN......Page 655
15.2.2 Creating and Dropping Replication Administrator Accounts......Page 656
15.3.1 Getting Started with DBMS_REPCAT......Page 659
15.3.2 Replication Groups with DBMS_REPCAT......Page 661
15.3.3 Replicated Objects with DBMS_REPCAT......Page 666
15.3.4 Replication Support with DBMS_REPCAT......Page 675
15.3.5 Adding and Removing Master Sites with DBMS_REPCAT......Page 686
15.3.6 Maintaining the Repcatlog Queue with DBMS_REPCAT......Page 692
15.3.7 Quiescence with DBMS_REPCAT......Page 696
15.3.8 Miscellaneous DBMS_REPCAT Procedures......Page 698
15.4.1 Getting Started with DBMS_OFFLINE_OG......Page 703
15.4.2 DBMS_OFFLINE_OG Interface......Page 704
15.5.2 DBMS_RECTIFIER_DIFF Interface......Page 709
15.6.2 DBMS_REPUTIL Interface......Page 715
BookmarkTitle:......Page 717
16.1.1 DBMS_REPCAT Programs......Page 718
16.1.2 DBMS-REPCAT Exceptions......Page 720
16.1.4 Data Dictionary Views......Page 721
16.2.1 About Column Groups......Page 726
16.2.2 Creating and Dropping Column Groups......Page 727
16.2.3 Modifying Existing Column Groups......Page 730
16.3.1 About Priority Groups......Page 734
16.3.2 Creating, Maintaining, and Dropping Priority Groups......Page 735
16.3.3 Creating and Maintaining Priorities Within a Priority Group......Page 739
16.3.4 Dropping Priorities from a Priority Group......Page 744
16.4.2 Creating, Maintaining, and Dropping Site Priorities......Page 748
16.4.3 Maintaining Site Priorities......Page 751
16.5.1 About Resolution Methods......Page 759
16.6.1 About Monitoring......Page 769
BookmarkTitle:......Page 772
17.1.1 About Remote Destinations......Page 773
17.1.2 Data Dictionary Views......Page 774
17.2.1 Getting Started with DBMS_DEFER_SYS......Page 777
17.2.2 Adding and Deleting Default Destinations......Page 779
17.2.3 Copying Deferred Transactions to New Destinations......Page 780
17.2.4 Maintenance Procedures......Page 781
17.2.5 Propagating Deferred RPCs......Page 785
17.2.6 Scheduling Propagation (Oracle8 only)......Page 789
17.3.1 Getting Started with DBMS_DEFER......Page 796
17.3.2 Basic RPCs......Page 798
17.3.3 Parameterized RPCs......Page 803
17.4.1 Getting Started with DBMS_DEFER_QUERY......Page 808
BookmarkTitle:......Page 815
BookmarkTitle:......Page 818
BookmarkTitle:......Page 820
BookmarkTitle:......Page 823
BookmarkTitle:......Page 825
BookmarkTitle:......Page 827
Structure of This Book......Page 829