Beginning PL/SQL: From Novice to Professional (Beginning from Novice to Professional)

Beginning PL/SQL: From Novice to Professional (Beginning from Novice to Professional)
| 2007-08-24 00:00:00 | | 0 | PL/SQL

Get started with PL/SQL, the built-in language that every Oracle developer and database administrator must know, in Beginning PL/SQL, a fast-paced and example-filled tutorial. Learn from author Don Bales extensive experience to discover the most commonly used aspects of PL/SQL without wasting time with obscure and obsolete features.

Author Don Bales takes his 20+ years of experience and a wealth of statistics he's gathered on PL/SQL usage over those years and applies the 80/20 rule &emdash; cover whats most needed and used by PL/SQL professionals and avoid whats not necessary! The result is a book that covers all the key features of PL/SQL without wasting your time discussing esoteric and obsolete parts of the language. Learn what really matters, so that you can get to work feeling confident with what you know about PL/SQL.

Discover the 20% of PL/SQL that gives you 80% of the bangkey topics covered include variables and datatypes, executing statements, working with cursors, real-world objects, debugging, testing, and more. Learn how to write production-level, object-oriented PL/SQLyoull explore relational PL/SQL, but unlike most other books on the subject, Don Bales emphasizes the use of PL/SQLs object-oriented features as well. Work through real examples of using of PL/SQLyoull learn PL/SQL by applying it to real-world business problems, not by heavy theory. What youll learn How important SQL is in PL/SQL How to use PL/SQL in both a relational and object-relational setting How to create maintainable, modular, and reusable PL/SQL program units The importance of testing as you go, and of building a permanent test plan for each module The importance of building debugging capabilities into your code and building a permanent debug facility for each module The importance of documenting as you go, and in the process building a permanent documentation set for your reusable modules How to apply modular PL/SQL to solve real-world problems Who is this book for?

Anyone who wants to learn how to create stored procedures against an Oracle database using PL/SQL. Programmers developing applications to be deployed against an Oracle database will need PL/SQL to take full advantage of the power Oracle has to offer. Database administrators who wish to implement functionality exposed only via PL/SQL package interfaces will also find this book useful.

About the Apress Beginning Series

The Beginning series from Apress is the right choice to get the information you need to land that crucial entry-level job. These books will teach you a standard and important technology from the ground up because they are explicitly designed to take you from `novice to professional.` Youll start your journey by seeing what you need to knowbut without needless theory and filler. Youll build your skill set by learning how to put together real-world projects step by step. So whether your goal is your next career challenge or a new learning opportunity, the Beginning series from Apress will take you thereit is your trusted guide through unfamiliar territory!

User review
Inaccuracies mar and otherwise fine book.
Although the sequence of information presented in this text is good, as a beginner to the peculiarities of PL/SQL I found that due to several inaccuries within the example code in the text made it much more difficult than it needed to be. I found at least two substantial chunks of code that failed to compile due to errors. I was able to download the sample code from the Apress website and see where the author had corrected the issues, but it was frustrating when I encountered this twice inside the first 100 pages!


Good organization and relatively good presentation although the humor probably doesn't come through in writing as well as it would in a classroom. All this being said - I'm using it daily right now as a reference source. It was worth purchasing!

User review
Very Well Written, with a Lot of Advice - Some will Force the DBA to Cringe
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.

User review
Buy It, I am not kidding
This is really a good book to learn PL/SQL. I had intermediate skills on SQL and was able to grasp the Pl/Sql quickly reading the book. The author explains the sample Pl / Sql code in nice, relaxed and organized way. I have one small advice for the author as well, `If possible please give the chapter name related to the contents'. After reading the book I was able to write some good Pl / Sql code with Package, Body and Stored Procedures. Author was successful to communicate with the reader in `Easy Mode'. I highly recommend this book if you some knowledge of SQL and 0 knowledge on Pl / Sql. Currently I am reading a book `SOA Using Java Web Services` by Mark D Hansen, which is completely opposite style of writing, I feel like author Hansen is dropping me off of an airplane without a parachute when its flying 50,000 feet above the ground.


Tazim Khan (SCJP SCBCD SCWCD SCEA)

User review
A deep, thorough and quite enjoyable book on PL/SQL,,.
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.

User review
A friendly and well-written book recommended for anyone ready to learn PL/SQL.
A friendly and well-written book recommended for anyone ready to learn PL/SQL. This Beggining PL/SQL well-written and well-organized introduction to the PL/SQL programming language. It uses a careful, example-based, easy to understand approach.

Thank you