Home > Store

Oracle PL/SQL by Example, 5th Edition

Register your product to gain access to bonus material or receive a coupon.

Oracle PL/SQL by Example, 5th Edition

Best Value Purchase

Book + eBook Bundle

  • Your Price: $64.79
  • List Price: $107.98
  • Includes EPUB and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    Adobe Reader PDF The popular standard, used most often with the free Adobe® Reader® software.

    This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.

More Purchase Options


  • Your Price: $47.99
  • List Price: $59.99
  • Usually ships in 24 hours.

eBook (Watermarked)

  • Your Price: $38.39
  • List Price: $47.99
  • Includes EPUB and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    Adobe Reader PDF The popular standard, used most often with the free Adobe® Reader® software.

    This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.



  • Teaches all the Oracle PL/SQL skills needed to start developing PL/SQL fast
  • Includes hundreds of extensive exercises, examples, and projects
  • Covers the new features in Oracle 12c, which was recently released
  • Written by authors with extensive experience in training and teaching students and employees in the use of PL/SQL


  • Copyright 2015
  • Dimensions: 7" x 9-1/8"
  • Pages: 528
  • Edition: 5th
  • Book
  • ISBN-10: 0-13-379678-7
  • ISBN-13: 978-0-13-379678-0

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 informit.com/title/0133796787. 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



Please download the files associated with Oracle PL/SQL by Example here.

End of chapter exercises

Student Schema

Chapter Example Scripts

Sample Content

Online Sample Chapter


Sample Pages

Download the sample pages (includes Chapter 3 and Index)

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


Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020