|
| |
Oracle PL/SQL Best Practices
Second Edition: October 2007
ISBN 13: 9780596514105
Copyright © 2007 Steven Feuerstein. All rights reserved. Used with permission.
http://www.oreilly.com/catalog/9780596514105
As you will no doubt have gathered from reading this book, I believe that in order to write high-quality applications, you need to take advantage of the resources out there. I refer to many such resources throughout the book, and this appendix collects information on how to get hold of them.
This is not intended to be a complete guide to any and every resource that could be useful to a PL/SQL developer. Rather, it reflects my own personal experience: tools and content that I have used myself or that have been recommended by others I respect.
Note: download the zip file of all scripts and documents from the first edition of Oracle PL/SQL Best Practices, including a naming and coding conventions document, by clicking here.
Book Example Files
Throughout the text, I’ve made reference to files I’ve written specifically for this book. Some of these files contain reusable code you can put to use immediately in your applications. Others contain performance analysis scripts or simply demonstrations of techniques. You will find in Table B-1 a list of these files in alphabetical order, offering a brief description of the contents of each file, and where it is referenced in the book. You can download these files here.
This software is provided to you on a “user beware” basis. I give you permission to use and change this code as desired.
You can put this code in your production applications, but it is entirely your responsibility to fully test the code you use and make sure that it meets your requirements.
Table B-1. Book example files
| File name |
Description |
Referenced in |
11g_emplu.pkg
11g_emplu.tst
11g_emplu_compare.sp |
Demonstration of the performance of the Oracle Database
11g function result cache |
Chapter 9 |
| assert.pkg |
Simple assertion package you can use to validate assumptions
and pre- and post-conditions in Design By Contract |
Chapters 1 and 6 |
| atleast1cust.tst |
Comparison of different ways to answer the question “Is
there at least one row that satisfies the condition?” |
Chapter 9 |
| boolean_utils.pkg |
Boolean-VARCHAR2 conversion utility package |
Chapter 5 |
| bpl.sp |
“Display Boolean value” utility procedure |
Chapter 3 |
| callstack.sql |
Demonstration of the approach of “dumping” package
state through a standardized error package |
Chapter 3 |
| case.sql |
Showcase of CASE expression usage to simplify logic |
Chapter 5 |
| dbc_demo.sql |
Demonstration of Design by Contract in pre- and postconditions |
Chapter 1 |
| display_clob.sp |
Utility to display the contents of a CLOB value |
Chapter 8 |
| dropwhatever.sp |
Powerful, dangerous, and very flexible utility to “drop
whatever” using dynamic SQL |
Chapter 7 |
emplu.pkg
emplu.tst |
Comparison of different ways of querying rows of data,
leveraging various kinds of caching |
Chapter 9 |
| excquiz*.sql |
Quizzes to test and reinforce your understanding of
exception handling in PL/SQL |
Chapter 6 |
| excuse_tracker.pkg |
Demonstration of multilevel, string-indexed collections,
combined with assertion programs, to track strings that
have been used |
Chapter 6 |
| genlookup.sp |
Generation of functions to look up single rows of data,
showing the value of code generation of standardized code |
Chapter 1 |
mfe_customer_rules.pkg
mfe_customer_rules.qut |
Demonstration of a package containing rules and a Quest
Code Tester export of a test definition that exercises this code |
Chapter 8 |
| multiple_triggers.sql |
Demonstration of the problems of having multiple triggers
on the same trigger point, and the use of the compound
trigger (new in Oracle Database 11g) |
Chapter 8 |
my_commit.pks
my_commit.pkb |
Encapsulation package for COMMIT, allowing developers to
“hide” the commit from your code, and flexibly turn it on
and off |
Chapter 7 |
| nocopy.tst |
Demonstration of the NOCOPY hint to avoid copying the
contents of collections and other data structures passed as
IN OUT arguments |
Chapter 9 |
| overdue.pkg |
Demonstration of building tracing into your package to show
(when needed) what is happening inside that package |
Chapter 3 |
p.pks
p.pkb |
Encapsulation of DBMS_OUTPUT.PUT_LINE that is much
more flexible and easier to use |
Chapters 3, 7, 8 |
| pl.sp |
Single-procedure encapsulation of DBMS_OUTPUT.PUT_
LINE, handy for pasting into another program for a local
substitute for that built-in |
Chapter 3 |
| plvtmr.pkg |
Elapsed time computation package, a part of the PL/Vision
library (but installable all by its lonesome); use it to time
the elapsed time of PL/SQL blocks down to the hundredth
of a second |
Chapter 9 |
| ref_code_cache.pkg |
Demonstration of the use of a package-based collection to
cache data in the database and improve query performance |
Chapter 9 |
| select_into_template.sql |
Template for a function that returns one row of data, and
offers flexibility on the handling of the NO_DATA_FOUND
exception |
Chapter 6 |
| serial_package.sql |
Demonstration of the use of the serialization pragma for
packages |
Chapter 7 |
| standards.zip |
Collection of files that offer a set of standards and naming
conventions for writing PL/SQL code |
N/A |
| stdhdr.pkg |
Utility package to work with standard package headers
based on an XML format |
Chapter 2 |
| stdpkg_format.sql |
Template for a standard package format that you can use
as a starting point for development of new packages |
Chapter 2 |
string_to_list.sf
string_to_list.qut |
Generic utility to parse a delimited string into a collection
of individual items, as well as an export of a Quest Code
Tester test definition to exercise the utility |
Chapter 8 |
string_tracker.pks
string_tracker.pkb |
Utility to keep track of strings that have been used in a
given session |
Chapter 6 |
tabfunc_pipelined.sql
tabfunc_streaming.sql |
Demonstrations of table functions, both pipelined and
streaming |
Chapter 9 |
| topdown.zip |
The topdown package offers refactoring to create local modules
as you specify; includes Word documentation explaining
why this is so useful and how to use this package |
Chapter 8 |
| tmr.ot |
Elapsed time computation object type; use it to time the
elapsed time of PL/SQL blocks down to the hundredth of a
second |
Chapter 9 |
| trigger_conflict.sql |
Simple query against the USER_TRIGGERS data dictionary
view that helps you identify potentially conflicting triggers |
Chapter 8 |
watch.pkg
watch.tst
watch_noplv.pkg |
Replacement for DBMS_OUTPUT as a tracing mechanism
for “watching” what is happening inside a program;
the “noplv” version removes any dependencies on the
PL/Vision library
whats_not_optimal.sp Program that displays all program units whose optimization
level is less than the specified value |
Chapter 9 |
| workaround_comment.sql |
Template comment to document a workaround in your
code |
Chapter 2 |
Books and Other Content
In this section I list a number of books that you may find helpful. The first subsection references other books on the PL/SQL language. (Certainly, this relatively short book on best practices for PL/SQL cannot give you all the information you need to build applications with this language.) In the next subsection I provide descriptions of some of my favorite non-PL/SQL books. Finally, I list references to helpful online content.
Oracle PL/SQL Books
Let’s start with my own oeuvre, the Oracle PL/SQL Series from O’Reilly Media.
The major books are listed here. For full information on these and other books,
see the O’Reilly web site at www.oreilly.com.
Oracle PL/SQL Programming, by Steven Feuerstein with Bill Pribyl
A fairly complete language reference for Oracle PL/SQL. Now in its fourth edition, it is also known as “the ant book” since the cover showcases ants.
Oracle PL/SQL Developer’s Workbook, by Steven Feuerstein with Andrew Odewahn
A workbook containing problems (and accompanying solutions) that will test (and help you develop) your knowledge of Oracle PL/SQL language features.
Learning Oracle PL/SQL, by Bill Pribyl and Steven Feuerstein
A comparatively gentle introduction to the PL/SQL language, ideal for new programmers and those who know a language other than PL/SQL.
Oracle PL/SQL for DBAs, by Arup Nanda and Steven Feuerstein
PL/SQL isn’t just for developers. This book offers guidance on a variety of topics critical for database administrators (and very useful to developers as well), including security, performance, and scheduling.
Oracle PL/SQL Language Pocket Reference, by Steven Feuerstein, Bill Pribyl, and Chip Dawes
A quick reference to the PL/SQL language syntax.
Other Helpful Books
I think you will find these books on programming and other topics helpful and
interesting.
Code Complete, by Steven McConnell (Microsoft Press)
A classic text, this “practical handbook of software criticism” should be on the bookshelf of every developer (or at least in your team’s library). Chockfull of practical advice for constructing code, it shows examples in many languages, including Ada, which is enough like PL/SQL to make learning from McConnell a breeze. Don’t start coding without it! The web site for Steven McConnell’s consulting practice, http://www.construx.com, is also
packed with lots of good advice.
Refactoring: Improving the Design of Existing Code, by Martin Fowler (Addison-Wesley Professional)
According to this book, “refactoring is the process of changing a software system in such a way that it doesn’t alter the external of the code, yet improves its internal structure.” Sound great or what? This excellent book uses Java as its example language, but the writing is clear and the Java straightforward. There is much to apply here to PL/SQL programming.
Extreme Programming Explained, by Kent Beck (Addison-Wesley Professional)
This book is a highly readable and concise introduction to Extreme Programming (XP), a lightweight software development methodology. Visit http://www.xprogramming.com or http://www.extremeprogramming.org for a glimpse
into the world of this interesting approach to development.
Thinking in Java, by Bruce Eckels (Prentice Hall PTR)
I believe that every PL/SQL developer should learn enough Java to be able to read the code comfortably and write basic wrappers on top of underlying Java functionality. If you like the way I write, I think you will also enjoy Eckel’s book. Lots of examples, a sense of humor, and an accessible way of presenting Java concepts all helped me learn Java quickly.
Code and Other Laws Of Cyperspace, by Lawrence Lessig (Basic Books)
Laws constrain the behavior of human beings. And when you are in cyberspace, software constrains what you can do. In this sense, code is like a new form of law. Lessig’s somewhat dense but very insightful book analyzes the ramifications of code as a form of law. This book greatly enhanced my understanding of the critical role of software developers in the big world “out there.”
The Timeless Way of Building, by Christopher Alexander (Oxford University Press)
Christopher Alexander is an architect (real buildings in the real world) who is sharply critical of how most buildings are designed and built. This book takes a deep look at what makes for a high-quality environment for human beings.
From his analysis, Alexander concludes that it is possible to come up with an
objective pattern language to help architects create beautiful, life-enhancing
structures. This is a beautiful read, and I strongly encourage you to check it out.
Horton Hears a Who, by Dr. Seuss (MGM/UA)
OK, so this one’s not about technology. It’s “just” about life and compassion.
This book is one of my favorites in the whole wide world of books. If you have
children, I encourage you to get this book and read it to them a whole lot.
Online Content
You can’t beat books for learning a language intensively, but there is lots of online content to help PL/SQL developers ask and get answers to questions quickly.
Some of my favorites are listed here:
Complete Oracle Documentation Set
That’s right: all of Oracle’s documentation for every supported version of the
database is available online. Just visit the link above, type in the keyword for
the functionality in which you are interested, and search. Oracle will show
hits across all versions, and off you go. You can also download whole documents
as PDF files.
Quest Pipelines
The Quest Pipelines are noncommercial, online communities for developers
and DBAs who work with Oracle, DBA, SQL Server, or mySQL. This site is
hosted and supported by Quest Software, but the company does not push its
commercial products on this site. The Oracle Pipeline offers extensive discussion forums (containing content provided by developers and DBAs for the
last five-plus years), free downloads, tips, and much more.
Oracle Technology Network
OTN is one of the most important online resources for Oracle technologists generally, and PL/SQL developers in particular. I author a Best Practice PL/SQL column, and the PL/SQL product manager loads up the PL/SQL pages with lots of excellent white papers and related resources.
Obsession for PL/SQL
ToadWorld provides a portal for Toad users and PL/SQL developers. I have built a “second home” here called Obsession for PL/SQL to offer my latest
ideas on best practices for PL/SQL. You will also find and can download all my presentations, demonstration code, quizzes, puzzles, Qusefuls, ILovePLSQLand, so much more.
Free Software
There is an incredible amount of very useful software out there that comes
without a license fee. Here are some of my favorites, mostly things that I have
contributed.
In this appendix, I have decided to list only freeware products, and avoid listing commercial products to minimize perceived or actual conflicts of interest (being a Quest employee and all that jazz). You will find a number of references to commercial tools throughout the book, and they should be
more than enough to steer you to products that you think may be helpful.
Quest CodeGen Utility
Freeware from Quest Software, the CodeGen utility is, in essence, a very
generalized “Design Pattern Factory.” That is, you can use the Code Generation
Markup Language (CGML) to capture a pattern in your code (a piece of
code you find yourself writing over and over again). Then you generate the
code (in PL/SQL, Java, HTML, XML...whatever!) based on that pattern for
your specific database object. CodeGen comes with hundreds of predefined
patterns or templates, including the Quest Development Architecture (QDA)
templates that implement a table API for your underlying datasets.
Quest Error Manager (click on Downloads)
When I built CodeGen, I included in its table API templates for a generic error management framework. To make it easy for you to take advantage of
this framework, I extracted it from CodeGen and packaged it as its own
standalone utility. Use QEM to raise, handle, log, and communicate errors to
your users in a consistent and effective manner. The most important concept
inside QEM is that you trap and log information about instances of errors,
not just the Oracle error.
PL/Vision
From the web site: “The PL/Vision Code Library provides more than 1,000 PL/SQL functions and procedures that extend the capabilities of the PL/SQL
language. Easily mastered by developers with any level of PL/SQL experience,
PL/Vision integrates seamlessly into your development environment.
PL/Vision’s reusable and cleanly encapsulated packages dramatically accelerate
development, simplify maintenance, and increase program reliability.”
PL/Vision is old, but you will still find lots of handy utilities in it. You are
welcome and encouraged to change it to meet your needs.
utPLSQL
utPLSQL is PL/SQL’s analog of Java’s Junit. It is, in other words, an open
source unit testing framework, based on Extreme Programming principles. I
wrote utPLSQL back in 1999 and released it to the world. Since then
hundreds, and perhaps thousands, of PL/SQL developers have used it to
write extensive test packages and achieve a high level of regression testing in
their applications. The major downside of utPLSQL is that you still have to
writes lots of test code. Access this URL for the software as well as links to
forums and other resources.
log4plsql
Written by Guillaume Moulard, log4plsql is “a framework for logging information
from PL/SQL program units. It uses the underlying log4j utility (a
widely used standard logging facility for Java).”
|
|
|