Oracle Built-in Packages

Overview

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...

See more details below
Paperback (BK&DISK)
$37.90
BN.com price
(Save 24%)$49.95 List Price
Other sellers (Paperback)
  • All (24) from $1.99   
  • New (3) from $34.47   
  • Used (21) from $1.99   
Oracle Built-in Packages

Available on NOOK devices and apps  
  • NOOK Devices
  • NOOK HD/HD+ Tablet
  • NOOK
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac
  • NOOK Study
  • NOOK for Web

Want a NOOK? Explore Now

NOOK Book (eBook)
$22.99
BN.com price
(Save 42%)$39.99 List Price

Overview

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's
Oracle 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?

A complete reference to all of the built-ins, including the new packages available with Oracle8, this book provides extensive examples and comes with a disk containing 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.

Read More Show Less

Product Details

  • ISBN-13: 9781565923751
  • Publisher: O'Reilly Media, Incorporated
  • Publication date: 5/28/1998
  • Edition description: BK&DISK
  • Edition number: 1
  • Pages: 956
  • Sales rank: 1,220,587
  • Product dimensions: 7.03 (w) x 9.21 (h) x 1.84 (d)

Meet the Author

Steven Feuerstein is considered to be one of the world's leading experts on the Oracle PL/SQL language, having written ten books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (all published by O'Reilly Media). He has also published his first book for kids: http://stevenfeuerstein.com/vivianvultur​e. Steven currently serves as PL/SQL Evangelist for Oracle Corporation. He has been developing software since 1980, spent five years with Oracle (1987-1992), and was PL/SQL Evangelist for Quest Software (and then Dell) from January 2001 to February 2014. He was an Oracle ACE Director (had to give up the prestigious title when he rejoined Oracle) and writes regularly for Oracle Magazine, which named him the PL/SQL Developer of the Year in both 2002 and 2006. He is also the first recipient of ODTUG's Lifetime Achievement Award (2009). Steven's latest initiative is the PL/SQL Challenge ( http://www.plsqlchallenge.com ), a daily quiz for PL/SQL developers; hundreds of programmers play each day. Check it out and join the fun! You can also catch up on his latest, mostly non-PLSQL rants at http://feuerthoughts.blogspot.com.

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.

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.

Read More Show Less

Read an Excerpt

Chapter 9: Datatype Packages

UTL_REF: Referencing Objects (Oracle8.1 Only)

The UTL_REF package provides a PL/SQL interface that allows you to select and modify objects (instances of an object type) in an object table without having to specify or know about the underlying database table. With UTL_REF, you only need a reference to the object in order to identify it in the database and perform the desired operations. With UTL_REF, you can do any of the following:

  • Select or retrieve an object from the database
  • Lock an object so that no other session can make changes to the object
  • Select and lock an object in a single operation (similar to SELECT FOR UPDATE)
  • Update the contents of an object
  • Delete an object

You will typically use UTL_REF programs when you have references to an object and one of the following is true:

  • You do not want to have to resort to an SQL statement to perform the needed action.
  • You do not even know the name of the table that contains the object, and therefore cannot rely on SQL to get your job done.

Before getting into the details, let's start with an initial example of how you might use the UTL_REF packages.

You will be able to use UTL_REF programs only to select or modify objects in an object table. An object table is a table in which each row of the table is an object. Here are the steps one might take to create an object table.

First, create an object type:

CREATE TYPE hazardous_site_t IS OBJECT (
name VARCHAR2(100),
location VARCHAR2(100),
dixoin_level NUMBER,
pcb_level NUMBER,
METHOD FUNCTION cleanup_time RETURN NUMBER);

Nowyou can create a table of these objects:

CREATE TABLE hazardous_sites OF hazardous_site_t;

As you will see in the headers for the UTL_REF programs, Oracle has provided a special parameter-passing syntax called ANY. This syntax allows us to pass references and objects of any object type in and out of the programs. This behavior is not otherwise available in Oracle8 built-in packages or the code that you yourself can write using object types.

Getting Started with UTL_REF

The UTL_REF package is created when the Oracle8.1 (or later) database is installed. The utlref.sql script (found in the built-in packages source code directory, as described in Chapter 1) contains the source code for this package's specification. The script is called by catproc.sql, which is normally run immediately after the database is created. The script creates the public synonym UTL_REF for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of the package.

Every program in this package runs as "owner." This means that programs in the UTL_REF package operate within the privileges of the session running those programs. You will be able to select and modify only objects to which your session has been granted the necessary privileges.

UTL_REF programs

Table 9-5 lists the programs defined for the UTL_REF packages.

UTL_REF does not declare any nonprogram elements.

UTL_REF exceptions

UTL_REF does not declare any exceptions. However, you may encounter any of the following Oracle exceptions when running the UTL_REF programs:

ORA-00942
Insufficient privileges. You must have the appropriate privileges on the under- lying database table.
ORA-01031
Insufficient privileges. You attempted to update an object table on which you have only SELECT privileges. You must have the appropriate privileges on the underlying database table.
ORA-08177
Cannot serialize access for this transaction. You have tried to change data after the start of a serialized transaction.
ORA-00060
Deadlock detected while waiting for resource. Your session and another ses- sion are waiting for a resource locked by the other. You will need to wait or ROLLBACK. ORA-01403
No data found. The REF is NULL or otherwise not associated with an object in the database.

UTL_REF Interface

This section describes the programs available through the UTL_REF package. A single, extended example at the end of the chapter shows how you might be able to take advantage of the UTL_REF programs in your own applications.

The UTL_REF.DELETE_OBJECT procedure
Use the DELETE_OBJECT procedure to delete an object (actually, the row containing that object) specified by the given reference. The header is,

PROCEDURE UTL_REF.DELETE_(reference IN REF ANY);

where reference identifies the object.

This program effectively substitutes for the following kind of SQL statement:

DELETE FROM the_underlying_object_table t
WHERE REF (t) = reference;

In contrast to this SQL statement, with DELETE_OBJECT you will not need to specify the name of the underlying database object table to retrieve the object....

Read More Show Less

Table of Contents

  • Dedication
  • Preface
  • Overview
    • Chapter 1: Introduction


  • Application Development Packages
    • Chapter 2: Executing Dynamic SQL and PL/SQL
    • Chapter 3: Intersession Communication
    • Chapter 4: User Lock and Transaction Management
    • Chapter 5: Oracle Advanced Queuing
    • Chapter 6: Generating Output from PL/SQL Programs
    • Chapter 7: Defining an Application Profile
    • Chapter 8: Managing Large Objects
    • Chapter 9: Datatype Packages
    • Chapter 10: Miscellaneous Packages


  • Server Management Packages
    • Chapter 11: Managing Session Information
    • Chapter 12: Managing Server Resources
    • Chapter 13: Job Scheduling in the Database


  • Distributed Database Packages
    • Chapter 14: Snapshots
    • Chapter 15: Advanced Replication
    • Chapter 16: Conflict Resolution
    • Chapter 17: Deferred Transactions and Remote Procedure Calls


  • What’s on the Companion Disk?
  • Colophon

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)