- Shopping Bag ( 0 items )
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...
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:
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.
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:
You will typically use UTL_REF programs when you have references to an object and one of the following is true:
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 (
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.
Table 9-5 lists the programs defined for the UTL_REF packages.
UTL_REF does not declare any nonprogram elements.
UTL_REF does not declare any exceptions. However, you may encounter any of the following Oracle exceptions when running the UTL_REF programs:
Insufficient privileges. You must have the appropriate privileges on the under- lying database table.
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.
Cannot serialize access for this transaction. You have tried to change data after the start of a serialized transaction.
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.
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....
|II||Application Development Packages||43|
|2||Executing Dynamic SQL and PL/SQL||45|
|4||User Lock and Transaction Management||232|
|5||Oracle Advanced Queuing||267|
|6||Generating Output from PL/SQL Programs||371|
|7||Defining and Application Profile||420|
|8||Managing Large Objects||446|
|III||Server Management Packages||587|
|11||Managing Session Information||589|
|12||Managing Server Resources||622|
|13||Job Scheduling in the Database||650|
|IV||Distributed Database Packages||683|
|17||Deferred Transactions and Remote Procedure Calls||850|
|A||What's on the Companion Disk?||897|