Oracle Built−in Packages- P1

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:5

Thêm vào BST

Báo xấu

72
lượt xem 5
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle Built−in Packages- P1: Ah, for the good old days of Version 1.0 of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms 3.0, and you went home at night content with a good day's work done.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle Built−in Packages- P1

[Appendix A] What's on the Companion Disk?
[Appendix A] What's on the Companion Disk? Table of Contents 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 .............................................................................................................................................................................85i
[Appendix A] What's on the Companion Disk? Table of Contents 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 ...........................................................................................................................................................................234ii
[Appendix A] What's on the Companion Disk? Table of Contents 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? Table of Contents 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 iv