Pub. Date:
Prentice Hall
Oracle PL/SQL by Example / Edition 5

Oracle PL/SQL by Example / Edition 5

by Benjamin Rosenzweig, Elena Rakhimov
Current price is , Original price is $59.99. You

Temporarily Out of Stock Online

Please check back later for updated availability.


Using PL/SQL for Oracle Database 12c, you can build solutions that deliver unprecedented performance and efficiency in any environment, including the cloud. Oracle® PL/SQL by Example, Fifth Edition, teaches all the PL/SQL skills you’ll need, through real-world labs, extensive examples, exercises, and projects. Now fully updated for the newest version of PL/SQL, it covers everything from basic syntax and program control through the latest optimization and security enhancements.

Step by step, you’ll walk through every key task, mastering today’s most valuable Oracle 12cPL/SQL programming techniques on your own. Start by downloading projects and exercises from Once you’ve done an exercise, the authors don’t just present the answer: They offer an in-depth discussion introducing deeper insights and modern best practices.

This book’s approach fully reflects the authors’ award-winning experience teaching PL/SQL to professionals at Columbia University. New database developers and DBAs can use it to get productive fast; experienced PL/SQL programmers will find it to be a superb Oracle Database 12csolutions reference.

New in This Edition

  • Updated code examples throughout
  • Result-caching of invoker’s right functions for better performance
  • Extended support for PL/SQL-only data types in dynamic SQL, OCI, and JDBC
  • Security enhancements, including ACCESSIBLE BY whitelists, improved privilege control, and Invisible Columns

Other topics covered

  • Mastering basic PL/SQL concepts and language fundamentals, and understanding SQL’s role in PL/SQL
  • Using conditional and iterative program control, including CONTINUE and CONTINUE WHEN
  • Efficiently handling errors and exceptions
  • Working with cursors and triggers, including compound triggers
  • Using stored procedures, functions, and packages to write modular code that other programs can run
  • Working with collections, object-relational features, native dynamic SQL, bulk SQL, and other advanced features

Product Details

ISBN-13: 9780133796780
Publisher: Prentice Hall
Publication date: 03/10/2015
Series: Prentice Hall Professional Oracle Series
Pages: 528
Sales rank: 748,028
Product dimensions: 7.00(w) x 8.90(h) x 1.00(d)

About the Author

Benjamin Rosenzweig is a Senior Project Manager at Misys Financial Software, where he has worked since 2002. Prior to that he was a principal consultant for more than three years at Oracle Corporation in the Custom Development Department. His computer experience ranges from creating an electronic Tibetan—English Dictionary in Kathmandu, Nepal, to supporting presentation centers at Goldman Sachs and managing a trading system at TIAA-CREF. Benjamin has been an instructor at the Columbia University Computer Technology and Application program in New York City since 1998. In 2002 he was awarded the “Outstanding Teaching Award” from the Chair and Director of the CTA program. He holds a B.A. from Reed College and a certificate in database development and design from Columbia University. His previous books with Prentice Hall are Oracle Forms Developer: The Complete Video Course (2000), and Oracle Web Application Programming for PL/SQL Developers (2003).

Elena Rakhimov has more than twenty years of experience in database architecture and development in a wide spectrum of enterprise and business environments ranging from non-profit organizations to Wall Street to her current position with a prominent software company where she heads up the database team. Her determination to stay “hands-on” notwithstanding, Elena managed to excel in the academic arena having taught relational database programming at Columbia University’s highly esteemed Computer Technology and Applications program. She was educated in database analysis and design at Columbia University and in applied mathematics at Baku State University in Azerbaijan. She currently resides in Vancouver, Canada.

Table of Contents

Preface xvii

Acknowledgments xxi

About the Authors xxiii

Introduction to PL/SQL New Features in Oracle 12c xxv

Invoker’s Rights Functions Can Be Result-Cached xxvi

More PL/SQL-Only Data Types Can Cross the PL/SQL-to-SQL Interface Clause xxvii

ACCESSIBLE BY Clause xxvii

FETCH FIRST Clause xxviii

Roles Can Be Granted to PL/SQL Packages and Stand-Alone Subprograms xxix

More Data Types Have the Same Maximum Size in SQL and PL/SQL xxx

Database Triggers on Pluggable Databases xxx

LIBRARY Can Be Defined as a DIRECTORY Object and with a CREDENTIAL Clause xxx

Implicit Statement Results xxxi



Invisible Columns xxxiii

Objects, Not Types, Are Editioned or Noneditioned xxxiv

PL/SQL Functions That Run Faster in SQL xxxiv

Predefined Inquiry Directives $$PLSQL_UNIT_OWNER and $$PLSQL_UNIT_TYPE xxxvi

Compilation Parameter PLSQL_DEBUG Is Deprecated xxxvii

Chapter 1: PL/SQL Concepts 1

Lab 1.1: PL/SQL Architecture 2

Lab 1.2: PL/SQL Development Environment 9

Lab 1.3: PL/SQL: The Basics 18

Summary 25

Chapter 2: PL/SQL Language Fundamentals 27

Lab 2.1: PL/SQL Programming Fundamentals 28

Summary 41

Chapter 3: SQL in PL/SQL 43

Lab 3.1: DML Statements in PL/SQL 44

Lab 3.2: Transaction Control in PL/SQL 49

Summary 55

Chapter 4: Conditional Control: IF Statements 57

Lab 4.1: IF Statements 58

Lab 4.2: ELSIF Statements 63

Lab 4.3: Nested IF Statements 67

Summary 70

Chapter 5: Conditional Control: CASE Statements 71

Lab 5.1: CASE Statements 71

Lab 5.2: CASE Expressions 80

Lab 5.3: NULLIF and COALESCE Functions 84

Summary 89

Chapter 6: Iterative Control: Part I 91

Lab 6.1: Simple Loops 92

Lab 6.2: WHILE Loops 98

Lab 6.3: Numeric FOR Loops 104

Summary 109

Chapter 7: Iterative Control: Part II 111

Lab 7.1: CONTINUE Statement 111

Lab 7.2: Nested Loops 118

Summary 122

Chapter 8: Error Handling and Built-in Exceptions 123

Lab 8.1: Handling Errors 124

Lab 8.2: Built-in Exceptions 126

Summary 132

Chapter 9: Exceptions 133

Lab 9.1: Exception Scope 133

Lab 9.2: User-Defined Exceptions 137

Lab 9.3: Exception Propagation 141

Summary 147

Chapter 10: Exceptions: Advanced Concepts 149


Lab 10.2: EXCEPTION_INIT Pragma 153

Lab 10.3: SQLCODE and SQLERRM 155

Summary 158

Chapter 11: Introduction to Cursors 159

Lab 11.1: Types of Cursors 159

Lab 11.2: Cursor Loop 165

Lab 11.3: Cursor FOR LOOPs 175

Lab 11.4: Nested Cursors 177

Summary 181

Chapter 12: Advanced Cursors 183

Lab 12.1: Parameterized Cursors 183

Lab 12.2: Complex Nested Cursors 185

Lab 12.3: FOR UPDATE and WHERE CURRENT Cursors 187

Summary 190

Chapter 13: Triggers 191

Lab 13.1: What Triggers Are 191

Lab 13.2: Types of Triggers 205

Summary 211

Chapter 14: Mutating Tables and Compound Triggers 213

Lab 14.1: Mutating Tables 213

Lab 14.2: Compound Triggers 217

Summary 223

Chapter 15: Collections 225

Lab 15.1: PL/SQL Tables 226

Lab 15.2: Varrays 235

Lab 15.3: Multilevel Collections 240

Summary 242

Chapter 16: Records 243

Lab 16.1: Record Types 243

Lab 16.2: Nested Records 250

Lab 16.3: Collections of Records 253

Summary 257

Chapter 17: Native Dynamic SQL 259

Lab 17.1: EXECUTE IMMEDIATE Statements 260

Lab 17.2: OPEN-FOR, FETCH, and CLOSE Statements 271

Summary 280

Chapter 18: Bulk SQL 281

Lab 18.1: FORALL Statements 282

Lab 18.2: The BULK COLLECT Clause 291

Lab 18.3: Binding Collections in SQL Statements 299

Summary 309

Chapter 19: Procedures 311

Benefits of Modular Code 312

Lab 19.1: Creating Procedures 312

Lab 19.2: Passing Parameters IN and OUT of Procedures 315

Summary 319

Chapter 20: Functions 321

Lab 20.1: Creating Functions 321

Lab 20.2: Using Functions in SQL Statements 327

Lab 20.3: Optimizing Function Execution in SQL 329

Summary 331

Chapter 21: Packages 333

Lab 21.1: Creating Packages 334

Lab 21.2: Cursor Variables 344

Lab 21.3: Extending the Package 353

Lab 21.4: Package Instantiation and Initialization 366

Lab 21.5: SERIALLY_REUSABLE Packages 368

Summary 371

Chapter 22: Stored Code 373

Lab 22.1: Gathering Information about Stored Code 373

Summary 382

Chapter 23: Object Types in Oracle 385

Lab 23.1: Object Types 386

Lab 23.2: Object Type Methods 394

Summary 404

Chapter 24: Oracle-Supplied Packages 405

Lab 24.1: Extending Functionality with Oracle-Supplied Packages 406

Lab 24.2: Error Reporting with Oracle-Supplied Packages 419

Summary 429

Chapter 25: Optimizing PL/SQL 431

Lab 25.1: PL/SQL Tuning Tools 432

Lab 25.2: PL/SQL Optimization Levels 438

Lab 25.3: Subprogram Inlining 444

Summary 453

Appendix A: PL/SQL Formatting Guide 455

Case 455

White Space 455

Naming Conventions 456

Comments 457

Other Suggestions 457

Appendix B: Student Database Schema 461

Table and Column Descriptions 461

Index 469



Oracle PL/SQL by Example, 3rd edition, presents the Oracle PL/SQL programming language in a unique and highly effective format. It challenges you to learn Oracle PL/SQL by using it rather than by simply reading about it.Just as a grammar workbook would teach you about nouns and verbs by first showing you examples and then asking you to write sentences, Oracle PL/SQL by Example teaches you about cursors, loops, procedures, triggers, and so on by first showing you examples and then asking you to create these objects yourself.

Who This Book Is For

This book is intended for anyone who needs a quick but detailed introduction to programming with Oracle's PL/SQL language. The ideal readers are those with some relational database experience, with some Oracle experience, specifically with SQL and SQL*Plus, but with little or no experience with PL/SQL or with most other programming languages.

The content of this book is based on the material that is taught in an Introduction to PL/SQL class at Columbia University's Computer Technology and Applications (CTA) program in New York City. The student body is rather diverse, in that there are some students who have years of experience with information technology (IT) and programming, but no experience with Oracle PL/SQL, and then there are those with absolutely no experience in IT or programming. The content of the book, like the class, is balanced to meet the needs of both extremes. The exercises in this book can be used as lab and homework assignments to accompany the lectures in such a PL/SQL course.

How This Book Is Organized

The intent of this workbook is to teach you about Oracle PL/SQL by presenting you with a series of challenges followed by detailed solutions to those challenges. The basic structure of each chapter is as follows:

- Lab
- - Exercises
- - Exercise Answers (with detailed discussion)
- - Self-Review Questions
- Lab...

Test Your Thinking Questions

Each chapter contains interactive labs that introduce topics about Oracle PL/SQL. The topics are discussed briefly and then explored though exercises, which are the heart of each lab.

Each exercise consists of a series of steps that you will follow to perform a specific task, along with questions that are designed to help you discover the important things about PL/SQL programming on your own. The answers to these questions are given at the end of the Exercises, along with more in-depth discussion of the concepts explored.

The exercises are not meant to be closed-book quizzes to test your knowledge. On the contrary, they are intended to act as your guide and walk you through a task. You are encouraged to flip back and forth from the exercise question section to the exercise answer section so that, if need be, you can read the answers and discussions as you go along.

At the end of each lab is a series of multiple-choice self-review questions. These are meant to be closed-book quizzes to test how well you understood the lab material. The answers to these questions appear in Appendix A.Finally, at the end of each chapter you will find a Test Your Thinking section, which consists of a series of projects designed to solidify all of the skills you have learned in the chapter. If you have successfully completed all of the labs in the chapter, you should be able to tackle these projects with few problems. You will find guidance and/or solutions to these in Appendix D and at the companion Web site.

Customer Reviews

Most Helpful Customer Reviews

See All Customer Reviews