Oracle Built-in Packages

Larger Cover

By Steven Feuerstein, Charles Dye, John Beresniewicz

Publisher: O'Reilly Media

Released: May 1998

Pages: 956

Oracle is the most popular database management system in use today, and PL/SQL plays a pivotal role in current and projected Oracle products and applications. PL/SQL is a programming language providing procedural extensions to the SQL relational database language and to an ever-growing number of oracle development tools. originally a rather limited tool, PL/SQL became with Oracle7 a mature and effective language for developers. now, with the introduction of Oracle8, PL/SQL has taken the next step towards becoming a fully realized programming language providing sophisticated object-oriented capabilities. Steven Feuerstein'sOracle PL/SQL Programming is a comprehensive guide to building applications with PL/SQL. That book has become the bible for PL/SQL developers who have raved about its completeness, readability, and practicality.

Built-in packages are collections of PL/SQL objects built by Oracle Corporation and stored directly in the Oracle database. The functionality of these packages is available from any programming environment that can call PL/SQL stored procedures, including Visual Basic, Oracle Developer/2000, Oracle Application Server (for web-based development), and, of course, the Oracle database itself. Built-in packages extend the capabilities and power of PL/SQL in many significant ways. for example:

DBMS_SQL executes dynamically constructed SQL statements and PL/SQL blocks of code.
DBMS_PIPE communicates between different Oracle sessions through a pipe in the RDBMS shared memory.
DBMS_JOB submits and manages regularly scheduled jobs for execution inside the database.
DBMS_LOB accesses and manipulates Oracle8's large objects (LOBs) from within PL/SQL programs.

The first edition of Oracle PL/SQL Programming contained a chapter on Oracle's built-in packages. but there is much more to say about the basic PL/SQL packages than Feuerstein could fit in his first book. In addition, now that Oracle8 has been released, there are many new Oracle8 built-in packages not described in the PL/SQL book. There are also packages extensions for specific oracle environments such as distributed database. hence this book.

Oracle Built-in Packages pulls together information about how to use the calling interface (API) to Oracle's Built-in Packages, and provides extensive examples on using the built-in packages effectively.

The windows diskette included with the book contains the companion guide, an online tool developed by RevealNet, Inc., that provides point-and-click access to the many files of source code and online documentation developed by the authors.

The table of contents follows:

Preface

Part I: Overview

1. Introduction

Part II: Application Development Packages

Executing Dynamic SQL and PL/SQL
Intersession Communication
User Lock and Transaction Management
Oracle Advanced Queuing
Generating Output from PL/SQL Programs
Defining an Application Profile
Managing Large Objects
Datatype Packages
Miscellaneous Packages

Part III: Server Management Packages

Managing Session Information
Managing Server Resources
Job Scheduling in the Database

Part IV: Distributed Database Packages

Snapshots
Advanced Replication
Conflict Resolution
Deferred Transactions and Remote Procedure Calls

Appendix. What's on the companion disk?

Overview
1. Chapter 1 Introduction
  1. The Power of Built-in Packages
  2. Built-in Packages Covered in This Book
  3. Using Built-in Packages
  4. Examining Built-in Package Source Code
Application Development Packages
1. Chapter 2 Executing Dynamic SQL and PL/SQL
  1. Examples of Dynamic SQL
  2. Getting Started with DBMS_SQL
  3. The DBMS_SQL Interface
  4. Tips on Using Dynamic SQL
  5. DBMS_SQL Examples
2. Chapter 3 Intersession Communication
  1. DBMS_PIPE: Communicating Between Sessions
  2. DBMS_ALERT: Broadcasting Alerts to Users
3. Chapter 4 User Lock and Transaction Management
  1. DBMS_LOCK: Creating and Managing Resource Locks
  2. DBMS_TRANSACTION: Interfacing to SQL Transaction Statements
4. Chapter 5 Oracle Advanced Queuing
  1. Oracle AQ Concepts
  2. Getting Started with Oracle AQ
  3. Oracle AQ Nonprogram Elements
  4. DBMS_AQ: Interfacing to Oracle AQ (Oracle8 only)
  5. DBMS_AQADM: Performing AQ Administrative Tasks (Oracle8 only)
  6. Oracle AQ Database Objects
  7. Oracle AQ Examples
5. Chapter 6 Generating Output from PL/SQL Programs
  1. DBMS_OUTPUT: Displaying Output
  2. UTL_FILE: Reading and Writing Server-side Files
6. Chapter 7 Defining an Application Profile
  1. Getting Started with DBMS_APPLICATION_INFO
  2. DBMS_APPLICATION_INFO Interface
  3. DBMS_APPLICATION_INFO Examples
7. Chapter 8 Managing Large Objects
  1. Getting Started with DBMS_LOB
  2. LOB Concepts
  3. DBMS_LOB Interface
8. Chapter 9 Datatype Packages
  1. DBMS_ROWID: Working with the ROWID Pseudo-Column (Oracle8 only)
  2. UTL_RAW: Manipulating Raw Data
  3. UTL_REF: Referencing Objects (Oracle8.0.4)
9. Chapter 10 Miscellaneous Packages
  1. DBMS_UTILITY: Performing Miscellaneous Operations
  2. DBMS_DESCRIBE: Describing PL/SQL Program Headers
  3. DBMS_DDL: Compiling and Analyzing Objects
  4. DBMS_RANDOM: Generating Random Numbers (Oracle8 Only)
Server Management Packages
1. Chapter 11 Managing Session Information
  1. DBMS_SESSION: Managing Session Information
  2. DBMS_SYSTEM: Setting Events for Debugging
2. Chapter 12 Managing Server Resources
  1. DBMS_SPACE: Obtaining Space Information
  2. DBMS_SHARED_POOL: Pinning Objects
3. Chapter 13 Job Scheduling in the Database
  1. Getting Started with DBMS_ JOB
  2. Job Queue Architecture
  3. Tips on Using DBMS_JOB
  4. DBMS_JOB Examples
Distributed Database Packages
1. Chapter 14 Snapshots
  1. DBMS_SNAPSHOT: Managing Snapshots
  2. DBMS_REFRESH: Managing Snapshot Groups
  3. DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot Instantiation
  4. DBMS_REPCAT: Managing Snapshot Replication Groups
2. Chapter 15 Advanced Replication
  1. DBMS_REPCAT_AUTH: Setting Up Administrative Accounts
  2. DBMS_REPCAT_ADMIN: Setting Up More Administrator Accounts
  3. DBMS_REPCAT: Replication Environment Administration
  4. DBMS_OFFLINE_OG: Performing Site Instantiation
  5. DBMS_RECTIFIER_DIFF: Comparing Replicated Tables
  6. DBMS_REPUTIL: Enabling and Disabling Replication
3. Chapter 16 Conflict Resolution
  1. Getting Started with DBMS_REPCAT
  2. Column Groups with DBMS_REPCAT
  3. Priority Groups with DBMS_REPCAT
  4. Site Priority Groups with DBMS_REPCAT
  5. Assigning Resolution Methods with DBMS_REPCAT
  6. Monitoring Conflict Resolution with DBMS_REPCAT
4. Chapter 17 Deferred Transactions and Remote Procedure Calls
  1. About Deferred Transactions and RPCs
  2. DBMS_DEFER_SYS: Managing Deferred Transactions
  3. DBMS_DEFER: Building Deferred Calls
  4. DBMS_DEFER_QUERY: Performing Diagnostics and Maintenance

Appendix What’s on the Companion Disk?
1. Installing the Guide
2. Using the Guide
Colophon

Title:

Oracle Built-in Packages

By:

Steven Feuerstein, Charles Dye, John Beresniewicz

Publisher:

O'Reilly Media

Formats:

Print
Ebook
Safari Books Online

Print:

May 1998

Ebook:

October 2010

Pages:

956

Print ISBN:

978-1-56592-375-1

| ISBN 10:

1-56592-375-8

Ebook ISBN:

978-1-4493-8610-8

| ISBN 10:

1-4493-8610-5

Steven Feuerstein

Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language. He is the author or coauthor of Oracle PL/SQL Programming, Oracle PL/SQL Best Practices, Oracle PL/SQL Programming: Guide to Oracle8i Features, Oracle PL/SQL Developer's Workbook, Oracle Built-in Packages, Advanced Oracle PL/SQL Programming with Packages, and several pocket reference books (all from O'Reilly & Associates). Steven is a Senior Technology Advisor with Quest Software, has been developing software since 1980, and worked for Oracle Corporation from 1987 to 1992.

View Steven Feuerstein's full profile page.
Charles Dye

Charles Dye is the database architect for Excite, Inc. (www.excite.com), where he is responsible for the design and implementation of the databases theat supply content to some of the world's busiest Web sites. Prior to joining Excite, he was the senior database administrator for The Dialog Corporation. Charles also operates a small but growing consultancy with clients in the San Francisco Bay area and Hong Kong. Once upon a time, before fleeing the East Coast for California skies, Charles taught math and physics at the Georgetown Day School in Washington DC. Charles is a frequent speaker at regional and national Oracle events such as Oracle Open World and IOUG-A Live. His favorite topics are distributed databases in general and advanced replication in particular. He also writes for the Northern California Oracle Users Group newsletter and is an active contributor to the Oracle Internet list server. Look for Charles' upcoming O'Reilly book, Oracle Distributed Systems, available later in 1998. Charles lives in Los Altos, California, with his wife Kathy, daughter Natalie, and labrador Jed. You can email him at cdye@excite.com.

View Charles Dye's full profile page.
John Beresniewicz

John Beresniewicz is currently a product analyst at Savant Corporation (www.savant-corp.com) responsible for design and development of the Q Diagnostic Center for Oracle. Prior to joining Savant, he was an Oracle DBA with eight years experience in large corporate client-server environments. Developing the Q product's server-side PL/SQL engine has given John extensive and unique experience with using Oracle built-in packages to develop large package-based applications. John is also known in the Oracle user community as a frequent speaker on DBA and PL/SQL topics and an active participant in various Oracle online discussion groups. He has given papers at numerous IOUW, ECO, and local Oracle User Groups. At ECO '97, he received the "Outstanding Speaker" award for his talk on using the PL/SQL built-in packages DBMS_PIPE and DBMS_LOCK. John lives with his wife Arlene in Gaithersburg, MD, where they enjoy going to the $2 movie theater. He can be reached by email at jberesni@savant-corp.com.

View John Beresniewicz's full profile page.

Colophon

Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. The animal appearing on the cover of Oracle Built-in Packages is a centipede. Centipedes (class Chilopoda ) are flattened, elongated, carnivorous arthropods, bearing one pair of legs per body segment. There are numerous orders of centipedes and over 2,500 species. Most of these are small, but some range up to 10 inches in length. Lifespan also varies among species, the longest being about 6 years. Centipedes generally live in moist habitats, including household basements, and are usually nocturnal or underground dwellers. Most species lay eggs in soil during the warmer months and live through the winter as adults.

Though the name implies a hundred legs, the actual number varies from 15 to well over a hundred depending on species. The first pair of legs immediately beneath the mouth is modified into a set of venom-bearing jaws with which the centipede kills its prey (live insects and other small animals) and which are only occasionally used to bite humans, resulting in minor pain and swelling. Centipedes are usually considered household pests, though they are more of a nuisance than a threat, and eat unwelcome insects and other household pests. Edie Freedman designed the cover of this book, using a 19th-century engraving from the Dover Pictorial Archive. The cover layout was produced with Quark XPress using the ITC Garamond font. Whenever possible, our books use Rep-Kover?, a durable and flexible lay-flat binding. If the page count exceeds RepKover's limit, perfect binding is used.

The inside layout was designed by Nancy Priest and implemented in FrameMaker 5.0 by Mike Sierra. The text and heading fonts are ITC Garamond Light and Garamond Book. The illustrations that appear in the book were created in Adobe Photoshop 4 by Robert Romano. This colophon was written by Nancy Kotary.

Description

Table of Contents

Product Details

About the Author

Colophon