Product Description

This book is the definitive reference on PL/SQL, considered throughout the database community to be the best Oracle programming book available. Like its predecessors, this fifth edition of Oracle PL/SQL Programming covers language fundamentals, advanced coding techniques, and best practices for using Oracle's powerful procedural language. Thoroughly updated for Oracle Database 11g Release 2, this edition reveals new PL/SQL features and provides extensive code samples, ranging from simple examples to complex and complete applications, in the book and on the companion website.

This indispensable reference for both novices and experienced Oracle programmers will help you:

• Get PL/SQL programs up and running quickly, with clear instructions for executing, tracing, testing, debugging, and managing PL/SQL code
• Optimize PL/SQL performance with the aid of a brand-new chapter in the fifth edition
• Explore datatypes, conditional and sequential control statements, loops, exception handling, security features, globalization and localization issues, and the PL/SQL architecture
• Understand and use new Oracle Database 11g features, including the edition-based redefinition capability, the function result cache, the new CONTINUE statement, fine-grained dependency tracking, sequences in PL/SQL expressions, supertype invocation from subtypes, and enhancements to native compilation, triggers, and dynamic SQL
• Use new Oracle Database 11g tools and techniques such as PL/Scope, the PL/SQL hierarchical profiler, and the SecureFiles technology for large objects
• Build modular PL/SQL applications using procedures, functions, triggers, and packages

Product Details

• Amazon Sales Rank: #40729 in Books
• Published on: 2009-10-01
• Original language: English
• Number of items: 1
• Binding: Paperback
• 1226 pages

From the Publisher
The first edition of Oracle PL/SQL Programming quickly became an indispensable reference for PL/SQL developers. The second edition focuses on Oracle8, covering Oracle8 object types, object views, collections, and external procedures, as well as new datatypes and functions and tuning, tracing, and debugging PL/SQL programs. The diskette contains an online Windows-based tool with access to more than 100 files of source code.

About the Author
Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language. He is the author or coauthor of Oracle PL/SQL Programming, Oracle PL/SQL Best Practices, Oracle PL/SQL Developer's Workbook, Oracle Built-in Packages, Advanced Oracle PL/SQL Programming with Packages, and several pocket reference books (all from O'Reilly & Associates). Steven is a Senior Technology Advisor with Quest Software, has been developing software since 1980, and worked for Oracle Corporation from 1987 to 1992.

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.