Expert PL/SQL Practices: for Oracle Developers and DBAs [NOOK Book]


Expert PL/SQL Practices is a book of collected wisdom on PL/SQL programming from some of the best and the brightest in the field. Each chapter is a deep-dive into a specific problem, technology, or feature set that you'll face as a PL/SQL programmer. Each author has chosen their topic out of the strong belief that what they share can make a positive difference in the quality and scalability of code that you write.

The path to mastery begins with syntax and the mechanics of ...

See more details below
Expert PL/SQL Practices: for Oracle Developers and DBAs

Available on NOOK devices and apps  
  • NOOK Devices
  • NOOK HD/HD+ Tablet
  • NOOK
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac
  • NOOK Study
  • NOOK for Web

Want a NOOK? Explore Now

NOOK Book (eBook)
$25.49 price
(Save 42%)$43.99 List Price


Expert PL/SQL Practices is a book of collected wisdom on PL/SQL programming from some of the best and the brightest in the field. Each chapter is a deep-dive into a specific problem, technology, or feature set that you'll face as a PL/SQL programmer. Each author has chosen their topic out of the strong belief that what they share can make a positive difference in the quality and scalability of code that you write.

The path to mastery begins with syntax and the mechanics of writing statements to make things happen. If you've reached that point with PL/SQL, then let the authors of Expert PL/SQL Practices show you how to combine syntax and mechanics with features and techniques to really make the language sing. You'll learn to do more with less effort, to write code that scales and performs well, and to eliminate and avoid defects.

These authors are passionate about PL/SQL and the power it places at your disposal. They want you to succeed, to know all that PL/SQL can offer. Let Expert PL/SQL Practices open your eyes to the full power of Oracle's world-class language for the database engine.

  • Goes beyond the manual to cover good techniques and best practices

  • Delivers knowledge usually gained only by hard experience

  • Covers the functionality that distinguishes PL/SQL as a powerful and scalable programming language for deploying logic inside the database engine

What you’ll learn

  • Learn when PL/SQL is best used, and when to avoid it

  • Move data efficiently using bulk SQL operations

  • Write code that scales through pipelining, parallelism, and profiling

  • Choose the right PL/SQL cursor type for any given application

  • Reduce coding errors through sound development practices such as unit-testing

  • Create and execute SQL and PL/SQL dynamically at runtime

Who this book is for

Expert PL/SQL Practices is for PL/SQL developers who want to develop deep expertise in key aspects of the language and its use in database programming. Any developer writing code against Oracle's class-leading database engine will find value from the collected wisdom in this book. Some knowledge of PL/SQL is assumed. Readers should at least be past the beginning stage with the language.

Table of Contents

  1. Do Not Use

  2. Dynamic SQL: Handling the Unknown

  3. PL/SQL and Parallel Processing

  4. Warnings and Conditional Compilation 

  5. PL/SQL Unit Testing

  6. Bulk SQL Operations

  7. Know Your Code 

  8. Contract-Oriented Programming

  9. PL/SQL from SQL

  10. Choosing the Right Cursor

  11. PL/SQL Programming in the Large

  12. Evolutionary Data Modeling

  13. Profiling for Performance

  14. Coding Conventions and Error Handling

  15. Dependencies and Invalidations

Read More Show Less

Product Details

  • ISBN-13: 9781430234869
  • Publisher: Apress
  • Publication date: 7/25/2011
  • Sold by: Barnes & Noble
  • Format: eBook
  • Edition number: 1
  • Pages: 512
  • Sales rank: 534,206
  • File size: 3 MB

Meet the Author

Michael Rosenblum is a Software Architect/Development DBA at Dulcian, Inc. where he is responsible for system tuning and application architecture. Michael supports Dulcian developers by writing complex PL/SQL routines and researching new features. He is the co-author of PL/SQL for Dummies (Wiley Press, 2006) and author of a number of database-related articles (IOUG Select Journal, ODTUG Tech Journal). Michael is an Oracle ACE, a frequent presenter at various regional and national Oracle user group conferences (Oracle OpenWorld, ODTUG, IOUG Collaborate, RMOUG, NYOUG, etc), and winner of the ODTUG Kaleidoscope 2009 Best Speaker Award. In his native Ukraine, he received the scholarship of the President of Ukraine, a Master of Science degree in Information Systems, and a diploma with honors from the Kiev National University of Economics.

Dominic Delmolino is the lead Oracle and database technologist for Agilex Technologies, a consulting firm specializing in assisting government and private enterprises to realize the value of their information. Dominic has more than 24 years of database experience, including more than 20 years as an Oracle Database engineering and development professional.�He is a member of the OakTable Network and regularly presents at conferences, seminars, and user group meetings in Europe and the U.S.�He also maintains, a site focused on database coding and design practices related to database application development.�Dominic holds a bachelor of science degree in computer science from Cornell University.

Lewis Cunningham has been working in IT for more than 20 years, and has worked with Oracle databases since 1993. His specialties are application design, database design, and coding�of high volume,�very large�databases. He is a senior database architect at a financial services company in St. Petersburg, Florida, working on very large, high transaction rate analytical databases and applications. He spends an inordinate amount of time keeping up with current technology and trends and speaking at user groups and doing webinars. Lewis is�an Oracle ACE Director and Oracle Certified Professional. He has written several articles for the Oracle Technology Network and maintains an Oracle technology blog at Lewis has written two books: EnterpriseDB: The Definitive Reference (Rampant Techpress, 2007) and SQL DML: The SQL Starter Series (CreateSpace, 2008). He lives in Florida with his wife and two sons. You can contact him at

Riyaj Shamsudeen is an industry-recognized RAC expert and performance tuning specialist with 19+ years of experience in implementing, using, and tuning RAC and Oracle products. He is an Oracle ACE Director and proud member of the OakTable network. Riyaj has co-authored many books about Oracle�Database. He is an active blogger (at and frequent international speaker in major conferences such as UKOUG, HOTSOS, Open World, and RMOUG.

Connor McDonald has worked with Oracle since the early 1990s, cutting his teeth on Oracle versions 6.0.36 and 7.0.12. Over the past 11 years, Connor has worked with systems in Australia, the U.K., southeast Asia, western Europe, and the United States. He has come to realize that although the systems and methodologies around the world are very diverse, there tend to be two common themes in the development of systems running on Oracle: either to steer away from the Oracle-specific functions or to use them in a haphazard or less-than-optimal fashion. It was this observation that led to the creation of a personal hints and tips website (�and more, presenting on the Oracle speaker circuit in an endeavor to improve the perception and usage of PL/SQL in the industry.

Melanie Caffrey is a senior development manager for Oracle Corporation, providing front-end and back-end Oracle solutions for the business needs of various clients. She is co-author of several technical publications, including Oracle Web Application Programming for PL/SQL Developers, Oracle DBA Interactive Workbook, and Oracle Database Administration: The Complete Video Course, all published by Prentice Hall. She has instructed students in Columbia University's Computer Technology and Applications Program in New York City, teaching advanced Oracle database administration and PL/SQL development. She is a frequent Oracle conference speaker.

Sue Harper is a product manager for Oracle SQL Developer and SQL Developer Data Modeler in the Database Development Tools group. She has been at Oracle since 1992 and is currently based in London. Sue is a regular contributor to magazines, maintains a technical blog, and speaks at many conferences around the world. She has authored the technical book Oracle SQL Developer 2.1 (Packt Publishing, 2009). When not at work, Sue is a keen walker and photographer. Sue takes time out to work with a charity in the slums of New Delhi, where she works with women and children.

Torben Holm has been in the computer business as a developer since 1987. He has been working with Oracle since 1992�his first�four years as system analyst and application developer (Oracle 7 and Forms 4.0/Reports 2.0 and database administrator), then�two years as developer (Oracle6/7, Forms 3.0 and RPT, and database administrator). He spent several years working for Oracle Denmark in the Premium Services group as a senior principal consultant, performing application development and database administration tasks. He also worked as an instructor in PL/SQL, SQL, and�database courses. Torben now works for Miracle A/S ( as a�consultant, with a focus in application development (PLSQL, mod_plsql, Forms, ADF) and database administration. He has been at Miracle A/S for more than 10 years. He is an Oracle Certified Developer and a member of the OakTable Network.

Robyn Sands is a software engineer for Cisco Systems, where she designs and develops embedded Oracle database products for Cisco customers. She has been working with Oracle since 1996, and has extensive experience in application development, large system implementations, and performance measurement. Robyn began her career in industrial and quality engineering, and has combined her prior education and experience with her love of data by searching for new ways to build database systems with consistent performance and minimal maintenance requirements. She is a member of the OakTable Network and co-author of two books on Oracle: Expert Oracle Practices and Pro Oracle SQL. Robyn occasionally posts random blog entries at

John Beresniewicz is a consulting member of the�technical staff at Oracle headquarters in Redwood Shores, California. He joined Oracle in 2002 to work on Enterprise Manager in the database performance area, and has played significant role in the design of diagnostic and tuning packs, real application testing, Support Workbench and Exadata. He has been a frequent speaker on database performance and PL/SQL programming over many years at Oracle OpenWorld and other conferences. He is co-author of Oracle Built-in Packages (O�Reilly & Associates) with Steven Feuerstein, and is a founding member of the OakTable Network.

Ron Crisco has been a software designer, developer, and project leader for�28 years, and�has worked with Oracle databases for 21 years. He works at Method R Corporation, designing and developing software, managing software products (like Method R Profiler, MR Tools, and MR Trace), consulting, and teaching courses. His specialty is simplifying complex work, which is especially valuable in helping the people around him accomplish extraordinary things.

Martin B�chi has worked since 2004 as lead software architect for Avaloq, a provider of a standardized banking software built on the Oracle RDBMS with 11 million lines of PL/SQL code. Together with two colleagues, he defines the system architecture and reviews the designs and code of 170 full-time PL/SQL developers, looking for simplicity, efficiency, and robustness. Martin regularly speaks at Oracle conferences. In 2009, he was named PL/SQL Developer of the Year by Oracle Magazine. Before getting into�Oracle database, Martin worked in object-oriented systems, formal methods, and approximate record matching. He holds�master of science degree from the Swiss Federal Institute of Technology and a Ph.D. from the Turku Center for Computer Science in Finland. In his spare time, Martin enjoys various outdoor sports with his family.

Adrian Billington is a consultant in application design, development, and performance tuning who has been working with Oracle databases since 1999. He is the man behind, a website full of SQL and PL/SQL features, utilities, and techniques for Oracle developers. Adrian is also an Oracle ACE and a member of the OakTable Network. He lives in the U.K. with his wife, Anji, and three children: Georgia, Oliver, and Isabella.

Stephan Petit began his career in 1995 at CERN, the European Laboratory for Particle Physics, located in Geneva, Switzerland. He is now in charge of a team of software engineers and students delivering applications and tools to the laboratory and beyond. One of these tools is the Engineering and Equipment Data Management System, also known as the CERN EDMS. Projects like CERN�s Large Hadron Collider (LHC) have a lifetime of 40 years or more. The EDMS is the digital engineering memory of the laboratory. More than a million documents relating to more than a million pieces of equipment are stored in the EDMS, which is also used as CERN�s Product Lifecycle Management (PLM) and Asset Tracking system. EDMS is based almost entirely on PL/SQL and is intended to have a lifetime at least as long as the LHC.

Stephan and his team of have been polishing coding conventions and best practices in PL/SQL in order to meet their very interesting mix of challenges: maintainability over decades, reliability, efficient error handling, scalability, and reusability of the modules. These challenges are compounded by the frequent rotation of team members, most of whom are students only temporarily at CERN. The oldest piece of code was written in 1995 and is still in use� with success! Apart from polishing PL/SQL, Stephan also enjoys being on stage from time to time, as rock band singer at the CERN's rock & roll summer festival, or as actor in various plays.

Arup Nanda has been an Oracle�Database administrator�since 1993, which has exposed him to all facets of database administration, from modeling to disaster recovery. He currently leads the global DBA team at Starwood Hotels, the parent of chains such as Sheraton and Westin, in White Plains, New York. He serves as a contributing editor of SELECT Journal, the publication of Independent Oracle Users Group (IOUG); speaks at many Oracle Technology events such as Oracle World and local user groups such as New York Oracle User Group; and has written many articles for both print publications such as Oracle Magazine and online publications such as Oracle Technology Network. Arup has co-authored two books: Oracle Privacy Security Auditing (Rampant, 2003) and Oracle PL/SQL for DBAs (O'Reilly, 2005). Recognizing his professional accomplishments and contributions to user community, Oracle chose him as the DBA of the Year in 2003. Arup lives in Danbury, Connecticut, with his wife, Anindita, and son, Anish. He can be reached at

Read More Show Less

Customer Reviews

Average Rating 5
( 10 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Terms of Use.
  • - Barnes & reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing all of 10 Customer Reviews
  • Posted September 5, 2011

    Practical advice for PL/SQL coding

    This is a book written by 15 of the top PL/SQL experts and each of them gets to take you deep into their chosen subject matter. Books like this are great in the fact that you get in-depth coverage of the material being presented by someone who is passionate about the subject. The authors show you what you can do to help make your code better for not only your benefit, but for the benefit of those that will come later and have to maintain your code. Here is a brief description of some of my favorite chapters: Chapter 1 shows why using PL/SQL might not be the best choice in a given situation. If you can get the job done strictly with SQL, then PL/SQL just adds unnecessary complexity and resource use. If you're coming from a programming background and haven't done much database work, this chapter would be a great reference for you. Chapter 2 covers dynamic SQL and the pros and cons of working with it. In my experience this technique is underused, and too many people try to make a single general purpose (static) query that is hard to tune and work with. When done right, dynamic SQL can offer significant performance benefits without introducing complexity or security issues. Chapter 7 talks about knowing your code. This is not only the code you write, but the code you must maintain. When your manager comes and asks you to make a change to some code, you need to know how that data is being used, what calls that code, and how the change will affect anything using it downstream. By putting the practices mentioned here into use, you will make it easier on yourself when making those changes and will have the confidence to know where and how to test your changes to verify nothing broke with the changes made. Chapter 14 talks about why using standards are important. Many might just gloss over this chapter, but I feel that it's one of the most important things to learn. Whether you're working in a really small group of people or working on a team of 50+ developers, having standards is critical. I've worked in shops where nobody would code to a standard, and that was made much worse when you get a call at 2AM and you had to troubleshoot someones code. I've also worked in shops where there was a documented standard and those late night calls with critical issues were handled quickly because of it. While the company's given standard might not be your preferred coding style, adhering to it will make your life much easier when you have to make changes to someone else's code that followed that same standard. Each of the chapters made me think about my coding style and how I write and test code. There are a lot of ideas to be gleaned from this book. While a couple of the authors will readily admit that their standards might not be right or make sense to be implemented in your shop, they all agree that using some type of standards is critical to saving time in both writing and maintaining code. Interestingly, there is some advice that I will take and use in other programming languages as it's that universal in scope. This is definitely a book that's worth reading if you work with PL/SQL as a developer or DBA, and is one that I will recommend to my coworkers.

    1 out of 1 people found this review helpful.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted March 20, 2014

    To anthony from a girl

    ASK HER OUT it doesnt matter what her friends say they r just jelous or however you spell that.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted March 19, 2014

    Anthony adice

    You don't really love this girl. Hre is a hint be relaxed and talk to her about things you like. It only gets akward if you think its akward. But if it dont work it will pass over. Everything does and nothing lasts forever.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted March 19, 2014


    Ok, some people have tried to help me with this, but... they couldnt really help. So here goes nothing.. Ok, I've liked this girl for a really lomg time(like at least 4 years) but I dont really think she likes me back... maybe one year, but not anymore. One day, this evil kid told everyone my secret. Ever since, it s gotten VERY AWKWARD to talk to her and I get all nervous in front of her. My parents took away my Ipod touch, so i cant really text her or anything. She is always surrounded by friends, so if i want to ask her out, it will be even MORE awkward. And to top it off, we are going to different schools next year also... so please help me out here.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted March 19, 2014

    Kristin to Trevon

    Sorry to Shyan, but personally i would never tell Emily you like the other girl. Not only could she be upset with you, but also Rhiannon(not sure about spelling) might sympathize with her fellow girl, and leave ya too. I do however agree that you should make a pro/con list about each of the girls. Remind yourself what you liked about Emily in the first place, and how that differs from why you liked rhiannon. ~ Kristin

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted March 16, 2014

    Teagan to Trevon

    I think you should tell Emily that you dont like it when she begs you to buy her things. Like this,"Emily, I'm sorry, but I don't like when you beg me to buy things. I like you a lot, but i have feelings for another girl." If she is a true friend, she will understand( hopefully), but i would cry anyway.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted March 16, 2014


    Okay so i have a gf emily but i really do like her but over two years she has gotten so annoying making me buy her stuff but i also have feelings for my best friend rhiannon she likes me and we hang out alot but i dont want to leave my emily heartbroken because she shows she really does love me and rhiannon does to i cant choose but i want to choose rhiannon because she is not like emily and does beg for me to buy her things and i have known emily more than rhiannon please help me

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted March 16, 2014


    Mercy boy. One sentence..anyway: with emily, if you truly love her then maybe your just attracted to Rhinnon. With emily: all girls have that stage where they try to take advantge. She might not mean it, but tell her you dont always have the money for her. Tell her that you have to by things for yourself sometimes, but still treat her every once in a while. Maybe once every two months, or more, depending on how much money you have. With i said you might just be attacted to her. Do you really 'love' her or is it just because she doesnt beg for gifts? Is she a cute popular girl? You can try to make a personal list about pros and cons of each girl.

    Was this review helpful? Yes  No   Report this review
  • Posted September 3, 2011

    How Everyone Should Be Practicing PL/SQL

    The title of this book is Expert PL/SQL Practices. It should be titled "How Everyone Should Be Practicing PL/SQL". This book contains a huge amount of material. All of it describes the ways that everyone should be using PL/SQL. Chapters of the book are written by different authors. Unfortunately, this is apparent in the style and content of one of my favorite Chapters, Chapter 1. Chapter 1 addresses what all PL/SQL programmers and developers spend most of their time on and where experts agree most performance improvements can be made. The material focuses on accessing database tables, through SQL and PL/SQL, and performing some processing against those tables i.e. row by row processing, loop processing, nested cursors, etc. The author bases his writing on one premise. The premise is that since relational databases are based on set theory, the code to access them should also be based on set theory. For each kind of processing, the author demonstrates how to take advantage of set theory, in PL/SQL programs, to have programs process more quickly. The results are everything the author claims. I did my own test. After making the changes the author recommends, my program ran 16 times faster. This would reduce a five hour run-time down to less than a half hour. Although Chapter 1 is one of my favorite chapters, I was a little disappointed. The author did not include full scripts of his demonstrations. He only included shell outlines of the scripts. He left the rest up to the reader, which wasn't trivial for me. Since he had the full scripts to do the analysis in the book, I cannot think of one good reason why he wouldn't include them. The scripts have not been made available on the publisher's web site either, as are scripts from other chapters in the book. Beyond Chapter 1, every chapter in the remainder of the book also contains material that can provide tremendous processing, performance and personal productivity benefits. Some of these require little or no code. For example: Chapter 6: Bulk SQL Operations discuses manipulating and processing many rows at once, rather than one row at a time. Significant performance benefits are obtained with only minor changes to the code (adding a couple of lines to initiate a bulk fetch). Chapter 7: Know Your Code is going to permanently change your thinking. Every programmer thinks they know their code but this chapter will show you what you don't know. It will even let you take code you are not familiar with and know the data, variables, runtime efficiency, etc. And finally, my second favorite chapter, Chapter 5: PL/SQL Unit Testing. This chapter is written by Sue Harper (the same Sue Harper of Sue Harper's Blog). She shows you how to consistently determine whether a program unit performs as expected and continues to perform as expected. She also discusses how to rerun these tests at any time to verify the results or quickly determine any problems and address them. As you read through this book, chapters will reference other chapters to build relationships, thoughts or principles. I don't know of any other book that takes such a holistic approach, to PL/SQL, as this one does. This book has helped me tremendously. I think it is a must for every PL/SQL library. I highly recommend Expert PL/SQL Practices.

    Was this review helpful? Yes  No   Report this review
  • Posted August 27, 2011

    MUST READ book for DBAs & Developers

    This is a MUST READ book for all DBAs & Developers. This is like having 30min 1-on-1 session with 15 subject matter experts. As a DBA I always face challenges when explaining to developers the DOs & DONTs in SQL & PL/SQL. The reasons being - 1. PL/SQL & SQL (oracle) has evolved a lot in last few years sometimes we don't keep track of new features. 2. Most of the developers practice either Java & or C++,C# religion, hence they look at PL/SQL with prejudiced view. Even DBAs also get away with their jobs using their ancient knowledge about PL/SQL from 7.3 era. Some of us do try to keep our knowledge up-to-date with white papers/articles, and try to make use of it but not effectively. This book explains developers how/when to use SQL vs PL/SQL effectively. It explains the coding styles/standards & their use. It not only explains DOs, but it explains DONTs as well. DBAs - One of the biggest challenge we normally have to deal with is code review.. making sure that it is not violating standards, compliance guideline and that we get it right when it is ready for implementation.. This book has tips to develop automated scripts/queries... just by running these we will be able to catch most of the violations. Developers - Even though most of them have knowledge about the PL/SQL objects & standard SQLs, but one tend to loose the touch as - They get pressured by deadlines - Busy gaining business knowledge - Keeping up with new features of their native programming languages. This book will provide you in-depth knowledge on using SQL and or PL/SQL "effectively"...Please note this book is not for beginners as it assumes you know the syntax & feature.

    Was this review helpful? Yes  No   Report this review
Sort by: Showing all of 10 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)