Product Description

The fourth edition of this popular pocket guide provides quick-reference information that will help you use Oracle's PL/SQL language, including the newest Oracle Database 11g features. It's a companion to Steven Feuerstein and Bill Pribyl's bestselling Oracle PL/SQL Programming. This concise guide boils down the most vital PL/SQL information into an accessible summary of: fundamental language elements (e.g., block structure, datatypes, declarations); statements for program control, cursor management, and exception handling; records, procedures, functions, triggers, and packages; calling PL/SQL functions in SQL; and, compilation options, object-oriented features, collections, and Java integration.The new edition describes such Oracle Database 11g elements as PL/SQL's function result cache, compound triggers, the Continue statement, the Simple_Integer datatype, and improvements to native compilation, regular expressions, and compiler optimization (including intra-unit inlining). In addition, this book now includes substantial new sections on Oracle's built-in functions and packages. When you need answers quickly, the "Oracle PL/SQL Language Pocket Reference" will save you hours of frustration.

Product Details

• Amazon Sales Rank: #56209 in Books
• Published on: 2007-10-23
• Original language: English
• Number of items: 1
• Binding: Paperback
• 170 pages

From the Publisher
The new edition of this must-have pocket guide boils down the most vital information from Oracle PL/SQL Programming, the bestseller that many consider "the Bible" for PL/SQL development. This concise booklet summarizes features available in Oracle's powerful new product-- Oracle9i-- and provides essential information on PL/SQL block structure, fundamental language elements, control statements, and use of procedures, functions, packages, triggers, Oracle objects, external procedures, and methods of calling Java classes from PL/SQL.

About the Author
Steven Feuerstein is considered 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 from O'Reilly Media). Steven has been developing software since 1980, spent five years with Oracle (1987-1992) and serves as PL/SQL Evangelist for Quest Software. His Oracle PL/SQL Best Practices column is one of the most popular pages on the Oracle Technology Network and he writes regularly for Oracle Magazine.

Bill Pribyl is the primary author of Learning Oracle PL/SQL and the coauthor of Oracle PL/SQL Programming and its companion pocket reference, all from O'Reilly Media. He is oddly proud of having used PL/SQL to write TCP/IP networking clients, tnsping callouts, near-realtime commodity price loaders, and transcendental functions. Bill, who holds a degree in physics from Rice University, is the former editor of the IOUG technical journal (Select) and former president of the South Central Oracle Users Group. At home with his family in Houston, Texas, one of Bill's current nonbillable titles include Webmaster for his neighborhood civic club.

Chip Dawes has been building and maintaining systems on relational databases since 1988 and with Oracle since 1990. He is currently a consultant with D&D Technologies, a Chicago consultancy. He enjoys working with, lecturing on, and writing about Oracle database administration, client server application development, and Unix system administration.

Excerpted from Oracle PL/SQL Language Pocket Reference by Steven Feuerstein, Bill Pribyl, Chip Dawes. Copyright © 2004. Reprinted by permission. All rights reserved.
Bulk Binds
You can use collections to improve the performance of SQL operations executed iteratively by using bulk binds. Bulk binds reduce the number of context switches between the PL/SQL engine and the SQL engine. Two PL/SQL language constructs implement bulk binds: FORALL and BULK COLLECT INTO.

The syntax for the FORALL statement is:

FORALL bulk_index IN [lower_bound..upper_bound
| INDICES OF collection_variable[BETWEEN lower_bound AND
upper_bound]
| VALUES OF collection_variable ]
[SAVE EXCEPTIONS]
sql_statement;

bulk_index can be used only in the sql_statement and only as a collection index (subscript). When PL/SQL processes this statement, the whole collection, instead of each individual collection element, is sent to the database server for processing. To delete all the accounts in the collection named inactives from the table ledger, do this:

FORALL i IN inactives.FIRST..inactives.LAST
DELETE FROM ledger WHERE acct_no = inactives(i);

With Oracle Database 10g, if nonconsecutive index values result from deletions, you will need to use the INDICES OF syntax to skip over the deleted elements:

FORALL i IN INDICES OF inactives
DELETE FROM ledger WHERE acct_no = inactives(i);

With Oracle Database 10g, if you are interested in the values of a sparse collection of integers instead of the indices, you will need to use the VALUES OF syntax:

FORALL i IN VALUES OF inactives_list
-- inactives_list is a collection of index values from
-- the inactives table which are earmarked for deletion
DELETE FROM ledger WHERE acct_no = inactives(i);

These new INDICES OF and VALUES OF keywords allow you to specify a subset of rows in a driving collection that will be used in the FORALL statement. To match the row numbers in the data collection with the row numbers in the driving collection, use the INDICES OF clause. To match the row numbers in the data collection with the values found in the defined rows of the driving collection, use the VALUES OF clause.

The default is for Oracle to stop after the first exception encountered. Use the keywords SAVE EXCEPTIONS to tell Oracle that processing should continue after encountering exceptions. The cursor attribute %BULK_EXCEPTIONS stores a collection of records containing the errors. These records have two fields, EXCEPTION_INDEX and EXCEPTION_CODE, which contain the FORALL iteration during which the exception was raised, as well as the SQLCODE for the exception. If no exceptions are raised, the SQL%BULK_EXCEPTIONS.COUNT method returns 0. For example:

DECLARE
TYPE NameList IS TABLE OF VARCHAR2(32);
name_tab NameList := NameList('Pribyl'
,'Dawes','Feuerstein','Gennick'
,'Pribyl','Beresniewicz','Dawes','Dye');
error_count NUMBER;
bulk_errors EXCEPTION;
PRAGMA exception_init(bulk_errors, -24381);
BEGIN
FORALL indx IN name_tab.FIRST..name_tab.LAST SAVE
EXCEPTIONS
INSERT INTO authors (name) VALUES (name_tab(indx));
-- authors has pk index on name
EXCEPTION

WHEN others THEN
error_count := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE('Number of errors is ' ||
error_count);
FOR indx IN 1..error_count LOOP
DBMS_OUTPUT.PUT_LINE('Error ' || indx || '
occurred during '||'iteration ' ||SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX);
DBMS_OUTPUT.PUT_LINE('Error is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));

Customer Reviews

Probably All You'll Need
If you're working with Oracle, you will hopefully already have some real code to look at and a database to experiment with.

In that case, I think this book will be the only printed backup to the language you will need.

By the way, it's also ideal if you're planning to take the Oracle Certification Exam.

An excellent little reference book
Why bother with hundreds of pages of text when all the information you'll need on the subject of PL/SQL can be found in a book that will fit in your back pocket? If you already know a bit about Oracle and programming this excellent little reference is all you will need to get you coding in PL/SQL.

I never keep it more than an arms length away from me at work.

Very good value for money
If you've got the author's "PL/SQL Programming" book, get this one too - a bargain, and much easier to carry around than the other one!