Product Description

For the past ten years, O'Reilly's "Oracle PL/SQL Programming" has been the bestselling book on PL/SQL, Oracle's powerful procedural language. Packed with examples and helpful recommendations, the book has helped everyone - from novices to experienced developers, and from Oracle Forms developers to database administrators - make the most of PL/SQL. The fourth edition is a comprehensive update, adding significant new content and extending coverage to include the very latest Oracle version, Oracle Database 10g Release 2. It describes such new features as the PL/SQL optimizing compiler, conditional compilation, compile-time warnings, regular expressions, set operators for nested tables, nonsequential collections in FORALL, the programmer-defined quoting mechanism, the ability to backtrace an exception to a line number, a variety of new built-in packages, and support for IEEE 754 compliant floating-point numbers. The new edition adds brand-new chapters on security (including encryption, row-level security, fine-grained auditing, and application contexts), file, email, and web I/O (including the built-in packages DBMS_OUTPUT, UTL_FILE, UTL_MAIL, UTL_SMTP, and UTL_HTTP) and globalization and localization. Co-authored by the world's foremost PL/SQL authority, Steven Feuerstein, this classic reference provides language syntax, best practices, and extensive code, ranging from simple examples to complete applications - making it a must-have on your road to PL/SQL mastery. A companion web site contains many more examples and additional technical content for enhanced learning.

Product Details

• Amazon Sales Rank: #223745 in Books
• Published on: 2005-08-22
• Original language: English
• Number of items: 1
• Binding: Paperback
• 1198 pages

From the Publisher
The fourth edition is a comprehensive update, adding significant new content and extending coverage to Oracle Database 10g Release 2. It adds brand-new chapters on security, I/O (file, email, and web), and internationalization. New features include the PL/SQL optimizing compiler, conditional compilation, compile-time warnings, regular expressions, and much more. Co-authored by the world's foremost PL/SQL authority, Steven Feuerstein, this classic reference provides language syntax, best practices, and extensive code.

About the Author
Steven Feuerstein is the author or coauthor of Oracle PL/SQL Programming, Oracle PL/SQL Best Practices, Oracle PL/SQL for DBAs, Oracle PL/SQL Developer's Workbook, Oracle Built-in Packages, and several pocket reference books (all from O'Reilly Media). Steven has been developing software since 1980, spent five years with Oracle (1987-1992), and serves as a Senior Technology Advisor to Quest Software. His products include utPLSQL (an open source unit testing framework for PL/SQL) and Qnxo (active mentoring software that helps to generate, reuse, and test code, http://www.qnxo.com). He has won numerous awards for his writing and trainings, offers a PL/SQL portal at http://www.oracleplsqlprogramming.com, and can be reached via email at steven@stevenfeuerstein.com. He lives in Chicago with his wife, Veva, and three cats. Two sons, Eli and Chris, orbit nearby.

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, Bill's current nonbillable titles include Webmaster for his neighborhood civic club, "IT Guy" for his son's Boy Scout troop, and Eucharistic Adoration Coordinator for his church. Visit Bill's firm at http://www.datacraft.com.

Excerpted from Oracle PL/SQL Programming by Steven Feuerstein, Bill Pribyl, Jonathan Gennick. Copyright © 2005. Reprinted by permission. All rights reserved.
Chapter 20 Managing PL/SQL Code

Writing the code for an application is just one step toward putting that application into production and then maintaining the code base. It is not possible within thescope of this book to fully address the entire life cycle of application design, development, and deployment. We do have room, however, to offer some ideas and advice about the following topics:

Managing and analyzing code in the database
When you compile PL/SQL programs, the source code is loaded into the data dictionary in a variety of forms (the text of the code, dependency relationships, parameter information, etc.). You can therefore use SQL to query these dictionary views to help you manage your code base.

Using native compilation
Beginning with Oracle9i Database, PL/SQL source code may optionally be compiled into native object code that is linked into Oracle. Native compilation can noticeably improve overall application performance (its impact is felt in compute-intensive programs, but does not affect SQL performance).

Using the optimizing compiler and compile-time warnings
Oracle Database 10g Release 1 added significant new and transparent capabilities to the PL/SQL compiler. The compiler will now automatically optimize your code, often resulting in substantial improvements in performance. In addition, the compiler will provide warnings about your code that will help you improve its readability, performance, and/or functionality.

Testing PL/SQL programs
This section offers suggestions for PL/SQL program testing based on the open source unit-testing framework, utPLSQL.

Debugging PL/SQL programs
Many development tools now offer graphical debuggers based on Oracle’s DBMS_DEBUG API. These provide the most powerful way to debug programs, but they are still just a small part of the overall debugging process. This section also discusses program tracing and explores some of the techniques and (dare I say) philosophical approaches you should utilize to debug effectively.

Tuning PL/SQL programs
This section offers a roundup of some of the more useful and generally applicable tuning tips, along with instructions for how you can analyze your program’s execution with built-in profiling and tracing utilities.

Protecting stored code
Oracle offers a way to "wrap" source code so that confidential and proprietary information can be hidden from prying eyes. This feature is most useful to vendors who sell applications based on PL/SQL stored code.

Managing Code in the Database

When you CREATE OR REPLACE a PL/SQL program, the source code for that program, along with other representations of that software, is stored in the database itself and exposed through a wide range of data dictionary views. This is a tremendous advantage for two key reasons:

Information about that code is available to you via the SQL language
I can write queries and even entire PL/SQL programs to read the contents of these data dictionary views and obtain lots of fascinating and useful information about my code base.

The database manages dependencies between your stored objects
For example, if a stored function relies on a certain table, and that table’s structure is changed, the status of that function is automatically set to INVALID. Recompilation then takes place automatically when someone tries to execute that function.

This SQL interface to your code base allows you to manage your code repository — running analyses on your code, documenting what has been written and changed, and so on. The following sections introduce you to some of the most commonly accessed sources of information in the data dictionary.

Data Dictionary Views for PL/SQL Programmers

The Oracle data dictionary is a jungle—lushly full of incredible information, but often with less than clear pathways to your destination. There are hundreds of views built on hundreds of tables, many complex interrelationships, special codes, and, all too often, nonoptimized view definitions. A subset of this multitude is particularly handy to PL/SQL developers; we will take a closer look at the key views in a moment. First, it is important to know that there are three types or levels of data dictionary
views:

USER_*
Views that show information about the database objects owned by the currently
connected schema.

ALL_*
Views that show information about all of the database objects to which the currently connected schema has access (either because it owns them or because it has been granted access to them). Generally they have the same columns as the corresponding USER view, with the addition of an OWNER column in the ALL views.

DBA_*
Views that show information about all the objects in the database. Generally the same columns as the corresponding ALL view.

We will work with the USER views in this chapter; you can easily modify any scriptsand techniques to work with the ALL views by adding an OWNER column to your logic. The following are some views a PL/SQL developer is most likely to find useful:

USER_DEPENDENCIES
The dependencies to and from objects you own. This view is mostly used byOracle to mark objects INVALID when necessary, and also by IDEs to display the dependency information in their object browsers.

USER_ERRORS
The current set of errors for all stored objects you own. This view is accessed by the SHOW ERRORS SQL*Plus command, described in Chapter 2. You can, however, write your own queries against it as well.

USER_OBJECTS
The objects you own. You can, for instance, use this view to see if an object is marked INVALID, find all the packages that have "EMP" in their names, etc.

USER_OBJECT_SIZE
The size of the objects you own. Actually, this view will show you the source,
parsed, and compile sizes for your code. Use it to identify the large programs in
your environment, good candidates for pinning into the SGA.

USER_PLSQL_OBJECT_SETTINGS
(Introduced in Oracle Database 10g Release 1) Information about the characteristics of a PL/SQL object that can be modified through the ALTER and SET DDL commands, such as the optimization level, debug settings, and more.

Customer Reviews

Oracle PL/SQL
Having been working on Sybase for a long time, we moved to Oracle. Initially I bought the Learning Oracle PL/SQL Programming which turned out to be seriusly lacking in contend and appeared to be more interested in promoting the web side of PLSQL. However, this book has answered every question and stumbling block I have had with re-introducing myself to PL/SQL. Good and numerous examples. Basically 900+ pages of Gems. If you know SQL, but want to learn PL/SQL, bypass the Learning Oracle PL/SQL book and dive into this on. Trust me, you won't regret it, nor will you pocket.

Very comprehensive PL/SQL book
This was the first book on PL/SQL that I ever read and is still the best. The authors cover the entire breadth of PL/SQL functionality with enough detail to answer all but the hardest PL/SQL questions.

Now in its 3rd edition. Highly recommended

Not recommended for beginners
1.this book doesn't even list triggers in the table of contents, which means that triggers were not included in that book at all!! 2.i dont know why, but the examples in the book were so different in every page and every chapter. it was really hard to know what the examples were all about. 3.table contents and actual contents do not match sometimes. the author mixes oracle8 features with oracle 7 features while explaining about oracle7 features, which only gave me confusions. 4.the only thing that attracted me was the tips that helped me pretty much.. In conclusion, if you are a beginner to pl/sql, please do not buy this book.