Product Description

This pocket reference provides information that will help the reader use Oracle's PL/SQL language. It condenses the most vital information from Oracle PL/SQL programming into an accessible quick reference that summarizes the basics of PL/SQL - block structure, fundamental language elements, data structures, control statements, and use of procedures, functions and packages.

Product Details

• Amazon Sales Rank: #644038 in Books
• Published on: 1999-04-26
• Original language: English
• Number of items: 1
• Binding: Paperback
• 99 pages

From the Publisher
This pocket reference boils down the most vital information from Oracle PL/SQL Programming into an accessible quick reference that summarizes the basics of PL/SQL: block structure, fundamental language elements (e.g., identifiers, declarations, defaults), data structures (including Oracle8 objects), control statements, and use of procedures, functions, and packages. It includes coverage of PL/SQL features in the newest version of Oracle, Oracle8i.

About the Author
Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language. He is also the author of Oracle PL/SQL Programming (O'Reilly & Associates, Second Edition, 1997), Advanced Oracle PL/SQL Programming with Packages (O'Reilly & Associates, 1996). and Oracle Built-in Packages (O'Reilly & Associates, 1998). Steven has been developing software since 1980 and worked for Oracle Corporation from 1987 to 1992. He is a partner with RevealNet, Inc. (www.revealnet.com) and is the chief architect of the RevealNet products, PL/SQL Knowledge Base and PL/Vision (a code library of prebuilt PL/SQL packages which accelerates PL/SQL-based application development). Steven also sysops RevealNet's "PL/SQL Pipeline," an online community for PL/SQL developers (www.revealnet.com/plsql-pipeline). He can be reached through email at feuerstein@revealnet.com. Steven is also President of PL/Solutions, which offers training and consulting on both PL/Vision and PL/SQL (www.plsolutions.com). Finally (on the Oracle side of Steven's life), he serves as codirector of the Oracle Practice at SSC, a systems management consulting firm based in Chicago (www.saraswati.com). Steven shares his Rogers Park, Chicago Georgian with his wife Veva, his youngest son Eli, two cats (Sister Itsacat and Moshe Jacobawitz), and Mercury (the Congo Red African Gray parrot). His older son, Chris, is busy making music and creating art nearby. Steven is a member of the Board of Directors of the Crossroads Fund, which provides grants to organizations in Chicago working for social change. Bill Pribyl, founder and principal of DataCraft, Inc. (www.datacraft.com), has been learning and teaching about Oracle databases and applications for more than ten years. His work with object-oriented technology dates to the mid-1980s, when he coauthored a paper on the application of entity-relationship modeling to object-oriented analysis. A self-avowed Oracle "generalist," Bill has served as an Oracle database administrator for several billion-dollar companies; led the development of web-based database applications for online commerce; configured an ultra-high availability database using Oracle replication; helped NASA apply database technology to space shuttle simulation software; and developed and taught classes in PL/SQL, Developer/2000, and the Oracle database. An ardent supporter of the Oracle user community, Bill is past chair of the South Central (USA) Oracle Users Group. He also served as Editor-in-Chief of Select, the quarterly publication of the International Oracle Users Group-Americas. His work has been published in Oracle user group publications worldwide. He also authored JavaScript programming examples for The Official Netscape LiveWire Pro Book. Bill lives near his alma mater, Rice University, in Houston, Texas with his wife Norma, son Johnny, stepson Geoffrey, four cats, and a deaf Dalmatian. He volunteer teaches a class on the Internet and beginning HTML to children in public middle school. 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. Chip is an Oracle Certified Professional and earned computer science and aerospace engineering degrees from St. Louis University. He can often be seen presenting at various Oracle User Group conferences. Chip lives happily in Chicagoland with his wife, Mary, his sons, Zachary and Charlie, and his dog, Rex. He can be reached at chip@ddtechnologies.com.

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!