Beginning PL/SQL: From Novice to Professional (Expert's Voice in Oracle) [Paperback]

J. Donald Bales (Author)

Book Description

ISBN-10: 1590598822 | ISBN-13: 978-1590598825 | Publication Date: August 27, 2007 | Edition: 1

Beginning PL/SQL is a fast-paced and blissfully short introduction to Oracle's PL/SQL language. PL/SQL is the built-in language that every Oracle developer and database administrator simply must know. This book gets readers up-to-speed on the core of the language without wasting the readers time on esoteric and seldom used syntax.

Editorial Reviews

About the Author

Donald Bales is a Computer Applications Consultant specializing in the analysis, design, and programming of client-server and web-based distributed systems; systems integration; and data warehousing. Don has over twenty years experience with Oracle as both a developer and a database administrator, and ten years experience with Java. He is currently working on the migration of medical and industrial hygiene systems to a web environment for a major Oil company. When he is not developing applications, Donald can often be found working with horses, playing the piano, or playing the bagpipes. Donald has had several careers, and has at various times been a mechanic, a general contractor, Mr. Mom, a developer, and currently a consultant. He has a bachelor of science degree in Business from Elmhurst College in Elmhurst, Illinois. Don currently resides in Downers Grove, Illinois with his wife Diane and his daughter Kristyn. He can be contacted by email at [e-mail]

Product Details

• Paperback: 496 pages
• Publisher: Apress; 1 edition (August 27, 2007)
• Language: English
• ISBN-10: 1590598822
• ISBN-13: 978-1590598825
• Product Dimensions: 9.6 x 7.2 x 0.9 inches
• Shipping Weight: 1.7 pounds (View shipping rates and policies)
• Average Customer Review: 4.1 out of 5 stars  See all reviews (8 customer reviews)
• Amazon Best Sellers Rank: #170,528 in Books (See Top 100 in Books)
  • #91 in Books > Computers & Technology > Databases > Database Management Systems
  • #94 in Books > Computers & Technology > Databases > SQL

More About the Author

Discover books, learn about writers, read author blogs, and more.

Customer Reviews

Most Helpful Customer Reviews

17 of 18 people found the following review helpful:

4.0 out of 5 stars Very Well Written, with a Lot of Advice - Some will Force the DBA to Cringe, January 25, 2009

By 

Charles Hooper (Michigan, USA) - See all my reviews
(REAL NAME)   

Amazon Verified Purchase

This review is from: Beginning PL/SQL: From Novice to Professional (Expert's Voice in Oracle) (Paperback)

This book is very well written, with a couple minor problems which kept it from receiving a five star rating. The book takes the approach of here is how something works (with a detailed code example), here is a problem which needs to be solved using something similar to what you have just learned, and here is how I would write the solution for the problem. The book is easily read from cover to cover through the use of the author's humor, which seemed to dry up a bit half of the way through the book.

Comments about the book which I recorded as I read through, in no particular order:
* Page 282 suggests sticking to a standard set of VARCHAR2 column lengths, such as 2000 for comments (4000 for international). However, doing so may lead to excessive memory consumption problems as variable anchors are used in PL/SQL modules to declare variables.
* Testing and documentation are both demonstrated and stressed as necessary for the developer. The author states that roughly twice as much time should be spent testing a solution as the time required to code the solution.
* The author provides a brief description of basic SQL, just in case the author's advice of being comfortable with SQL was ignored.
* The book provides updated content for developers using Oracle 10g.
* Chapter titles appearing at the top of each page probably should have been labeled a little better to describe the contents of the chapter, rather than attempting to use a bit of witty humor for the chapter titles. This change would have made it easier to find a specific syntax example, although the index at the back of the book eliminates much of this being an issue.
* The author created a PL function in one of the early chapters of the book as a shorthand method of calling DBMS_OUTPUT.PUT_LINE, and this PL function was used in many of the later chapters of the book without indicating that PL is not a built-in PL/SQL function. There is a chance that this might cause some confusion for people who attempt to use the book for reference purposes, rather than as a book which should be read cover to cover.
* The author casually demonstrates a lot of good programming practices with Oracle databases, without drawing much attention to some of those good programming practices.
* The author stresses modularization of program processes, if the code will be used in more than one place, that code should be stored in a PL/SQL function or procedure. Unfortunately, without much caution, there is a chance that a developer will take that message to an extreme, coding all kinds of black box type procedures which then might be called in a for loop, repeatedly sending the same query to the database rather than sending the query once and storing the result (yes, this does happen with production code).
* Page 420 suggests creating a temporary table to store an intermediate result, using an example that if the table's average rowsize is 297 bytes, and only 13 bytes per row are needed, that the results should be placed into a temporary table to improve performance. The author stopped short of stating that the temporary tables should be created on the fly as needed, which would definitely not be a good suggestion.

Submitted to the publisher as errata:
The script on page 43, despite the description, the TO_NUMBER_OR_NULL function error handler does not catch errors when non-numeric values are passed into the function (such as the letter A). The author's code is only attempting to catch the exception INVALID_NUMBER, which is apparently insufficient on Oracle 10.2.0.4 - the author later indicated that he did not know why the code did not work when placed into a package, and modified the code to catch the PL/SQL error.

The script on page 94 references the column WORKER_TYPE_T.WORKER_TYPE_ID, but no such column exists when the tables are created using the scripts in the script library. The downloadable script library also contains the same error. The column listed in the script does not match the ERD diagram on page 27, which shows that the script should have referenced WORKER_TYPE_T.ID.

The script on page 119 references the column GENDER_T.GENDER_ID, but no such column exists when the tables are created using the scripts in the script library. The downloadable script library also contains the same error. The column listed in the script does not match the ERD diagram on page 27, which shows that the script should have referenced GENDER_T.ID.

The scripts which create the tables assume that the tablespace "USERS" exists in the database, but the book does not mention that such a tablespace needs to exist. Some of the columns listed in the tables created by the scripts are Oracle reserved words (SELECT * FROM V$RESERVED_WORDS ORDER BY KEYWORD;), which generally should be avoided as column names. The ERD diagram on page 27 shows a couple such reserved words (ID, NAME).

The scripts to create the tables/indexes in the database create the objects with odd extent sizes which will contribute to fragmentation in dictionary managed tablespaces, and poor full tablescan/fast full index scan performance. The scripts on pages 29-36 create objects with 10KB extent sizes, the scripts on pages 188-190 also create objects with 10KB extent sizes, another script created one or more objects with a 100KB extent size, while the script on pages 237-238 creates a table and an index with a 1MB extent size. All objects in a single tablespace should have the same extent size (unless they are in an Oracle controlled ASSM tablespace with auto controlled extent sizes), and the extent size should be a power of 2 - the extent size controls the maximum multi-block read size, so this needs to be considered as well.

The script on pages 159-160 is described as a plain old SQL solution, which was created in an effort to show that an all SQL solution (no PL/SQL) would out perform a PL/SQL solution. It did that, but just barely. The problem is that the plain old SQL solution calls 2 PL/SQL functions for each row inserted into the WORKER_T table. A pure SQL solution could have been constructed for this particular insert which would have better demonstrated the author's point.

On pages 266-268 the author attempts to force the Oracle optimizer to use an index access for a SQL statement containing "WHERE NAME LIKE '%DOE%'. The author stated that "the Optimizer is wrong" for selecting to use a full tablescan rather than an index type access, citing that the index type access would require the retrieval of about 1,080 4KB blocks to determine which table blocks to access, compared to the 5,500 4KB blocks during a full tablescan. On page 266 the author stated "Instead, it resorted to the worst of all options: a full table scan. Ouch!". On page 268 the author stated "On the other hand, you're an intelligent programmer who is much more knowledgeable and can therefore consider things like physics." The author fails to recognize that if the table's extent size were set at 1MB, with a 4KB block size, Oracle 10g R2 would have auto-set the db_file_multiblock_read_count to 256 (on earlier versions of Oracle the DBA could have set the same value). While the author's forced INDEX FULL SCAN was reading the 4KB blocks one at a time, the full tablescan would have been reading up to 256 blocks at a time, in roughly the same amount of time that it would have taken to fetch a couple blocks of the index from disk. The author's advice could put the developer reading this book at odds with a DBA who has read a couple performance tuning books and articles written by Oaktable Network members.

9 of 9 people found the following review helpful:

5.0 out of 5 stars Great book to solve the mystery of PL/SQL for a beginner, October 11, 2007

By 

Peggy Miller (Akron, NY USA) - See all my reviews
(REAL NAME)   

Amazon Verified Purchase

This review is from: Beginning PL/SQL: From Novice to Professional (Expert's Voice in Oracle) (Paperback)

As a Information Technology professional with little expertise in PL/SQL I bought this book as a replacement for a PL/SQL training course. It has taught me much and is a very good reference. I feel confident about attending the future Oracle training courses without having attended the pre-requisite PL/SQL training course after reading the contents in this book. I can see that the information in this book will still be useful for years to come as it covers fundamental information. I recommended ut as it lays a good foundation. It is not one of those books that states the obvious or is so simple that you to wonder why you purchased it (and we have all done that before, haven't we).

7 of 7 people found the following review helpful:

5.0 out of 5 stars A deep, thorough and quite enjoyable book on PL/SQL..., June 13, 2008

By 

J. Burke - See all my reviews
(REAL NAME)   

This review is from: Beginning PL/SQL: From Novice to Professional (Expert's Voice in Oracle) (Paperback)

As an Oracle developer for over 10 years, I have read many Oracle books on a variety of topics, and this book is definitely one of those that stands heads above the others. As the title suggests, Beginning PL/SQL: From Novice to Professional covers a range of PL/SQL topics, both foundational and advanced. From the fundamentals such as triggers, procedures, packages, the book moves into the more advanced operations of Bulk Collection, Objects and even one of the more neglected but no less important areas in PL/SQL, unit testing. The topics in this book are delivered to the reader from a professional point of view in that all examples of the PL/SQL in the book are approached and designed as quality, production ready code. Topics such as design patterns, best practices and even some of the more insidious Oracle "gotchas" are discussed in this book, making it a wealth of information available to both the new as well as seasoned developer looking to update their PL/SQL knowledge. As a final bonus, this book packs all of this pertinent information in an refreshingly compact size with a breezy, fresh writing style. As one who has many a weighty computer tome where maybe 50% of the book has pertinent information and the rest is fluff, finding books where the fluff is removed are books to be treasured. If you are a PL/SQL developer of any level, beginner or veteran, this book is definitely one that should be turned to for information.