Oracle Built−in Packages- P1

[Appendix A] What's on the Companion Disk?

A. What's on the Companion Disk?..................................................................................................................2 A.1 Installing the Guide...........................................................................................................................2 ...............................................................................................................................................................................3 A.2 Using the Guide................................................................................................................................3 ...............................................................................................................................................................................4

1. Introduction.....................................................................................................................................................5 1.1 The Power of Built−in Packages.......................................................................................................5 1.1.1 A Kinder , More Sharing Oracle........................................................................................6 ...............................................................................................................................................................................7 1.2 Built−in Packages Covered in This Book..........................................................................................7 1.2.1 Application Development Packages................................................................................10 1.2.2 Server Management Packages.........................................................................................11 1.2.3 Distributed Database Packages........................................................................................12 .............................................................................................................................................................................14 1.3 Using Built−in Packages..................................................................................................................14 1.3.1 What Is a Package?..........................................................................................................14 1.3.2 Controlling Access with Packages...................................................................................15 1.3.3 Referencing Built−in Package Elements.........................................................................17 1.3.4 Exception Handling and Built−in Packages....................................................................18 1.3.5 Encapsulating Access to the Built−in Packages..............................................................24 1.3.6 Calling Built−in Packaged Code from Oracle Developer/2000 Release 1......................27 1.3.7 Accessing Built−in Packaged Technology from Within SQL.........................................30 .............................................................................................................................................................................38 1.4 Examining Built−in Package Source Code......................................................................................38 1.4.1 The STANDARD Package..............................................................................................39 1.4.2 The DBMS_STANDARD Package.................................................................................40 .............................................................................................................................................................................42

2. Executing Dynamic SQL and PL/SQL.......................................................................................................43 2.1 Examples of Dynamic SQL.............................................................................................................43 .............................................................................................................................................................................46 2.2 Getting Started with DBMS_SQL...................................................................................................46 2.2.1 Creating the DBMS_SQL Package..................................................................................46 2.2.2 Security and Privilege Issues...........................................................................................46 2.2.3 DBMS_SQL Programs....................................................................................................46 2.2.4 Types of Dynamic SQL...................................................................................................47 2.2.5 DBMS_SQL Exceptions..................................................................................................49 2.2.6 DBMS_SQL Nonprogram Elements...............................................................................49 .............................................................................................................................................................................52 2.3 The DBMS_SQL Interface..............................................................................................................52 2.3.1 Processing Flow of Dynamic SQL..................................................................................52 2.3.2 Opening the Cursor..........................................................................................................54 2.3.3 Parsing the SQL Statement..............................................................................................55 2.3.4 Binding Values into Dynamic SQL.................................................................................58 2.3.5 Defining Cursor Columns................................................................................................63 2.3.6 Executing the Cursor.......................................................................................................66 2.3.7 Fetching Rows.................................................................................................................66 2.3.8 Retrieving Values............................................................................................................69 2.3.9 Closing the Cursor...........................................................................................................76 2.3.10 Checking Cursor Status.................................................................................................77 2.3.11 Describing Cursor Columns...........................................................................................80

i .............................................................................................................................................................................85

[Appendix A] What's on the Companion Disk?

2.4 Tips on Using Dynamic SQL...........................................................................................................85 2.4.1 Some Restrictions............................................................................................................85 2.4.2 Privileges and Execution Authority with DBMS_SQL...................................................85 2.4.3 Combining Operations.....................................................................................................87 2.4.4 Minimizing Memory for Cursors.....................................................................................88 2.4.5 Improving the Performance of Dynamic SQL.................................................................89 2.4.6 Problem−Solving Dynamic SQL Errors..........................................................................91 2.4.7 Executing DDL in PL/SQL..............................................................................................94 2.4.8 Executing Dynamic PL/SQL...........................................................................................94 .............................................................................................................................................................................97 2.5 DBMS_SQL Examples....................................................................................................................97 2.5.1 A Generic Drop_Object Procedure..................................................................................97 2.5.2 A Generic Foreign Key Lookup Function.......................................................................98 2.5.3 A Wrapper for DBMS_SQL .DESCRIBE_COLUMNS...............................................104 2.5.4 Displaying Table Contents with Method 4 Dynamic SQL............................................107 2.5.5 Indirect Referencing in PL/SQL....................................................................................116 2.5.6 Array Processing with DBMS_SQL..............................................................................121 2.5.7 Using the RETURNING Clause in Dynamic SQL........................................................134 ...........................................................................................................................................................................138

3. Intersession Communication......................................................................................................................139 3.1 DBMS_PIPE: Communicating Between Sessions........................................................................139 3.1.1 Getting Started with DBMS_PIPE................................................................................140 3.1.2 How Database Pipes Work............................................................................................141 3.1.3 Managing Pipes and the Message Buffer......................................................................143 3.1.4 Packing and Unpacking Messages.................................................................................149 3.1.5 Sending and Receiving Messages..................................................................................158 3.1.6 Tips on Using DBMS_PIPE..........................................................................................161 3.1.7 DBMS_PIPE Examples.................................................................................................164 ...........................................................................................................................................................................184 3.2 DBMS_ALERT: Broadcasting Alerts to Users.............................................................................184 3.2.1 Getting Started with DBMS_ALERT............................................................................184 3.2.2 The DBMS_ALERT Interface.......................................................................................185 3.2.3 DBMS_ALERT Examples............................................................................................191 ...........................................................................................................................................................................202

4. User Lock and Transaction Management................................................................................................203 4.1 DBMS_LOCK: Creating and Managing Resource Locks.............................................................203 4.1.1 Getting Started with DBMS_LOCK..............................................................................203 4.1.2 The DBMS_LOCK Interface.........................................................................................205 4.1.3 Tips on Using DBMS_LOCK........................................................................................211 4.1.4 DBMS_LOCK Examples..............................................................................................213 ...........................................................................................................................................................................221 4.2 DBMS_TRANSACTION: Interfacing to SQL Transaction Statements.......................................221 4.2.1 Getting Started with DBMS_TRANSACTION............................................................221 4.2.2 Advising Oracle About In−Doubt Transactions............................................................223 4.2.3 Committing Data............................................................................................................224 4.2.4 Rolling Back Changes...................................................................................................225 4.2.5 Setting Transaction Characteristics...............................................................................227 4.2.6 Cleaning Up Transaction Details...................................................................................229 4.2.7 Returning Transaction Identifiers..................................................................................231

ii ...........................................................................................................................................................................234

[Appendix A] What's on the Companion Disk?

5. Oracle Advanced Queuing.........................................................................................................................235 5.1 Oracle AQ Concepts......................................................................................................................236 5.1.1 General Features............................................................................................................236 5.1.2 Enqueue Features...........................................................................................................237 5.1.3 Dequeue Features...........................................................................................................238 5.1.4 Propagation Features......................................................................................................238 5.1.5 A Glossary of Terms......................................................................................................240 5.1.6 Components of Oracle AQ............................................................................................243 5.1.7 Queue Monitor...............................................................................................................244 5.1.8 Data Dictionary Views...................................................................................................244 ...........................................................................................................................................................................245 5.2 Getting Started with Oracle AQ.....................................................................................................245 5.2.1 Installing the Oracle AQ Facility...................................................................................245 5.2.2 Database Initialization...................................................................................................246 5.2.3 Authorizing Accounts to Use Oracle AQ......................................................................247 ...........................................................................................................................................................................249 5.3 Oracle AQ Nonprogram Elements.................................................................................................249 5.3.1 Constants........................................................................................................................249 5.3.2 Object Names.................................................................................................................250 5.3.3 Queue Type Names........................................................................................................251 5.3.4 Agents Object Type.......................................................................................................251 5.3.5 Recipient and Subscriber List Table Types...................................................................252 5.3.6 Message Properties Record Type..................................................................................252 5.3.7 Enqueue Options Record Type......................................................................................254 5.3.8 Dequeue Options Record Type......................................................................................255 5.3.9 Oracle AQ Exceptions...................................................................................................257 ...........................................................................................................................................................................259 5.4 DBMS_AQ: Interfacing to Oracle AQ (Oracle8 only)..................................................................259 5.4.1 Enqueuing Messages......................................................................................................259 5.4.2 Dequeuing Messages.....................................................................................................262 ...........................................................................................................................................................................268 5.5 DBMS_AQADM: Performing AQ Administrative Tasks (Oracle8 only)....................................268 5.5.1 Creating Queue Tables...................................................................................................269 5.5.2 Creating and Starting Queues........................................................................................272 5.5.3 Managing Queue Subscribers........................................................................................276 5.5.4 Stopping and Dropping Queues.....................................................................................278 5.5.5 Managing Propagation of Messages..............................................................................280 5.5.6 Verifying Queue Types..................................................................................................282 5.5.7 Starting and Stopping the Queue Monitor.....................................................................283 ...........................................................................................................................................................................284 5.6 Oracle AQ Database Objects.........................................................................................................284 5.6.1 Objects Per Queue Table...............................................................................................284 5.6.2 Data Dictionary Objects.................................................................................................287 ...........................................................................................................................................................................290 5.7 Oracle AQ Examples.....................................................................................................................290 5.7.1 Improving AQ Ease of Use............................................................................................291 5.7.2 Working with Prioritized Queues..................................................................................293 5.7.3 Building a Stack with AQ Using Sequence Deviation..................................................297 5.7.4 Browsing a Queue's Contents........................................................................................299 5.7.5 Searching by Correlation Identifier...............................................................................303 5.7.6 Using Time Delay and Expiration.................................................................................307 5.7.7 Working with Message Groups.....................................................................................313

iii

[Appendix A] What's on the Companion Disk?

5.7.8 Working with Multiple Consumers...............................................................................316 ...........................................................................................................................................................................322

6. Generating Output from PL/SQL Programs...........................................................................................323 6.1 DBMS_OUTPUT: Displaying Output...........................................................................................323 6.1.1 Getting Started with DBMS_OUTPUT.........................................................................323 6.1.2 Enabling and Disabling Output......................................................................................326 6.1.3 Writing to the DBMS_OUTPUT Buffer.......................................................................328 6.1.4 Retrieving Data from the DBMS_OUTPUT Buffer......................................................329 6.1.5 Tips on Using DBMS_OUTPUT...................................................................................332 6.1.6 DBMS_OUTPUT Examples..........................................................................................333 ...........................................................................................................................................................................337 6.2 UTL_FILE: Reading and Writing Server−side Files.....................................................................337 6.2.1 Getting Started with UTL_FILE....................................................................................337 6.2.2 Opening Files.................................................................................................................343 6.2.3 Reading from Files.........................................................................................................345 6.2.4 Writing to Files..............................................................................................................346 6.2.5 Closing Files..................................................................................................................350 6.2.6 Tips on Using UTL_FILE..............................................................................................351 6.2.7 UTL_FILE Examples....................................................................................................358 ...........................................................................................................................................................................365

7. Defining an Application Profile.................................................................................................................366 7.1 Getting Started with DBMS_APPLICATION_INFO...................................................................366 7.1.1 DBMS_APPLICATION_INFO Programs....................................................................366 7.1.2 The V$ Virtual Tables...................................................................................................367 7.1.3 DBMS_APPLICATION_INFO Nonprogram Elements...............................................367 ...........................................................................................................................................................................369 7.2 DBMS_APPLICATION_INFO Interface.....................................................................................369 7.2.1 The DBMS_APPLICATION_INFO.READ_CLIENT_INFO procedure.....................369 7.2.2 The DBMS_APPLICATION_INFO.READ_MODULE procedure.............................369 7.2.3 The DBMS_APPLICATION_INFO.SET_ACTION procedure...................................370 7.2.4 The DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure.........................372 7.2.5 The DBMS_APPLICATION_INFO.SET_MODULE procedure.................................373 7.2.6 The DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure..............374 ...........................................................................................................................................................................377 7.3 DBMS_APPLICATION_INFO Examples....................................................................................377 7.3.1 About the register_app Package....................................................................................377 7.3.2 The action Procedure.....................................................................................................378 7.3.3 The set_stats Procedure.................................................................................................378 7.3.4 The Information Procedures..........................................................................................380 7.3.5 Using the register_app Package.....................................................................................381 7.3.6 Covering DBMS_APPLICATION_INFO.....................................................................382 7.3.7 Monitoring Application SQL Resource Consumption..................................................384 7.3.8 Session Monitoring and Three−Tier Architectures.......................................................384 7.3.9 Tracking Long−Running Processes...............................................................................385 ...........................................................................................................................................................................389

8. Managing Large Objects............................................................................................................................390 8.1 Getting Started with DBMS_LOB.................................................................................................390 8.1.1 DBMS_LOB Programs..................................................................................................390 8.1.2 DBMS_LOB Exceptions...............................................................................................392