Book description
Considered the best Oracle PL/SQL programming guide by the Oracle community, this definitive guide is precisely what you need to make the most of Oracle’s powerful procedural language. The sixth edition describes the features and capabilities of PL/SQL up through Oracle Database 12c Release 1.
Hundreds of thousands of PL/SQL developers have benefited from this book over the last twenty years; this edition continues that tradition. With extensive code examples and a lively sense of humor, this book explains language fundamentals, explores advanced coding techniques, and offers best practices to help you solve real-world problems.
- Get PL/SQL programs up and running quickly, with clear instructions for executing, tracing, testing, debugging, and managing code
- Understand new 12.1 features, including the ACCESSIBLE_BY clause, WITH FUNCTION and UDF pragma, BEQUEATH CURRENT_USER for views, and new conditional compilation directives
- Take advantage of extensive code samples, from easy-to-follow examples to reusable packaged utilities
- Optimize PL/SQL performance with features like the function result cache and Oracle utilities such as PL/Scope and the PL/SQL hierarchical profiler
- Build modular, easy-to-maintain PL/SQL applications using packages, procedures, functions, and triggers
Publisher resources
Table of contents
- Dedication
- A Note Regarding Supplemental Files
- Preface
-
I. Programming in PL/SQL
-
1. Introduction to PL/SQL
- What Is PL/SQL?
- The Origins of PL/SQL
- So This Is PL/SQL
-
About PL/SQL Versions
-
Oracle Database 12c New PL/SQL Features
- More PL/SQL-only datatypes cross PL/SQL-to-SQL interface
- ACCESSIBLE_BY clause
- Implicit statement results
- BEQUEATH CURRENT_USER views
- Grant roles to program units
- New conditional compilation directives
- Optimizing function execution in SQL
- Using %ROWTYPE with invisible columns
- FETCH FIRST clause and BULK COLLECT
- The UTL_CALL_STACK package
-
Oracle Database 12c New PL/SQL Features
- Resources for PL/SQL Developers
- Some Words of Advice
-
2. Creating and Running PL/SQL Code
- Navigating the Database
- Creating and Editing Source Code
- SQL*Plus
- Performing Essential PL/SQL Tasks
- Editing Environments for PL/SQL
- Calling PL/SQL from Other Languages
- 3. Language Fundamentals
-
1. Introduction to PL/SQL
-
II. PL/SQL Program Structure
- 4. Conditional and Sequential Control
- 5. Iterative Processing with Loops
-
6. Exception Handlers
- Exception-Handling Concepts and Terminology
- Defining Exceptions
- Raising Exceptions
- Handling Exceptions
- Building an Effective Error Management Architecture
- Making the Most of PL/SQL Error Management
-
III. PL/SQL Program Data
- 7. Working with Program Data
-
8. Strings
- String Datatypes
- Working with Strings
- String Function Quick Reference
- 9. Numbers
- 10. Dates and Timestamps
- 11. Records
-
12. Collections
- Collections Overview
- Collection Methods (Built-ins)
- Working with Collections
- Nested Table Multiset Operations
- Maintaining Schema-Level Collections
-
13. Miscellaneous Datatypes
- The BOOLEAN Datatype
- The RAW Datatype
- The UROWID and ROWID Datatypes
- The LOB Datatypes
- Working with LOBs
- Predefined Object Types
-
IV. SQL in PL/SQL
- 14. DML and Transaction Management
-
15. Data Retrieval
- Cursor Basics
- Working with Implicit Cursors
- Working with Explicit Cursors
- SELECT...FOR UPDATE
- Cursor Variables and REF CURSORs
- Cursor Expressions
-
16. Dynamic SQL and Dynamic PL/SQL
- NDS Statements
- Binding Variables
- Working with Objects and Collections
- Dynamic PL/SQL
- Recommendations for NDS
- When to Use DBMS_SQL
-
V. PL/SQL Application Construction
- 17. Procedures, Functions, and Parameters
- 18. Packages
-
19. Triggers
- DML Triggers
- DDL Triggers
- Database Event Triggers
- INSTEAD OF Triggers
- AFTER SUSPEND Triggers
- Maintaining Triggers
-
20. Managing PL/SQL Code
-
Managing Code in the Database
- Overview of Data Dictionary Views
- Display Information About Stored Objects
- Display and Search Source Code
- Use Program Size to Determine Pinning Requirements
- Obtain Properties of Stored Code
- Analyze and Modify Trigger State Through Views
- Analyze Argument Information
- Analyze Identifier Usage (Oracle Database 11g’s PL/Scope)
- Managing Dependencies and Recompiling Code
-
Compile-Time Warnings
- A Quick Example
- Enabling Compile-Time Warnings
-
Some Handy Warnings
- PLW-05000: Mismatch in NOCOPY qualification between specification and body
- PLW-05001: Previous use of ‘string’ (at line string) conflicts with this use
- PLW-05003: Same actual parameter (string and string) at IN and NOCOPY may have side effects
- PLW-05004: Identifier string is also declared in STANDARD or is a SQL built-in
- PLW-05005: Function string returns without value at line string
- PLW-06002: Unreachable code
- PLW-07203: Parameter string may benefit from use of the NOCOPY compiler hint
- PLW-07204: Conversion away from column type may result in suboptimal query plan
- PLW-06009: Procedure string OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
- Testing PL/SQL Programs
- Tracing PL/SQL Execution
- Debugging PL/SQL Programs
- Using Whitelisting to Control Access to Program Units
- Protecting Stored Code
- Introduction to Edition-Based Redefinition (Oracle Database 11g Release 2)
-
Managing Code in the Database
-
21. Optimizing PL/SQL Performance
- Tools to Assist in Optimization
- The Optimizing Compiler
-
Data Caching Techniques
- Package-Based Caching
- Deterministic Function Caching
-
THe Function Result Cache (Oracle Database 11g)
- Enabling the function result cache
- The RELIES_ON clause (deprecated in 11.2)
- Function result cache example: A deterministic function
- Function result cache example: Querying data from a table
- Function result cache example: Caching a collection
- When to use the function result cache
- When not to use the function result cache
- Useful details of function result cache behavior
- Managing the function result cache
- Fine-grained dependencies in 11.2 and higher
- The virtual private database and function result caching
- Caching Summary
- Bulk Processing for Repeated SQL Statement Execution
-
Improving Performance with Pipelined Table Functions
- Replacing Row-Based Inserts with Pipelined Function-Based Loads
- Tuning Merge Operations with Pipelined Functions
- Asynchronous Data Unloading with Parallel Pipelined Functions
- Performance Implications of Partitioning and Streaming Clauses in Parallel Pipelined Functions
- Pipelined Functions and the Cost-Based Optimizer
- Tuning Complex Data Loads with Pipelined Functions
- A Final Word on Pipelined Functions
- Specialized Optimization Techniques
- Stepping Back for the Big Picture on Performance
-
22. I/O and PL/SQL
- Displaying Information
- Reading and Writing Files
-
Sending Email
- Oracle Prerequisites
- Configuring Network Security
- Send a Short (32,767 Bytes or Less) Plain-Text Message
- Include “Friendly” Names in Email Addresses
- Send a Plain-Text Message of Arbitrary Length
- Send a Message with a Short (32,767 Bytes or Less) Attachment
- Send a Small File (32,767 Bytes or Less) as an Attachment
- Attach a File of Arbitrary Size
- Working with Web-Based Data (HTTP)
- Other Types of I/O Available in PL/SQL
-
VI. Advanced PL/SQL Topics
-
23. Application Security and PL/SQL
- Security Overview
-
Encryption
- Key Length
- Algorithms
- Padding and Chaining
- The DBMS_CRYPTO Package
- Encrypting Data
- Encrypting LOBs
- SecureFiles
- Decrypting Data
- Performing Key Generation
- Performing Key Management
- Cryptographic Hashing
- Using Message Authentication Codes
- Using Transparent Data Encryption
- Transparent Tablespace Encryption
- Row-Level Security
- Application Contexts
- Fine-Grained Auditing
-
24. PL/SQL Architecture
- DIANA
- How Oracle Executes PL/SQL Code
- The Default Packages of PL/SQL
- Execution Authority Models
- Conditional Compilation
- PL/SQL and Database Instance Memory
- Native Compilation
- What You Need to Know
- 25. Globalization and Localization in PL/SQL
-
26. Object-Oriented Aspects of PL/SQL
- Introduction to Oracle’s Object Features
- Object Types by Example
- Object Views
- Maintaining Object Types and Object Views
- Concluding Thoughts from a (Mostly) Relational Developer
-
23. Application Security and PL/SQL
- 27. Calling Java from PL/SQL
- 28. External Procedures
- A. Regular Expression Metacharacters and Function Parameters
- B. Number Format Models
- C. Date Format Models
- Index
- About the Authors
- Colophon
- Copyright
Product information
- Title: Oracle PL/SQL Programming, 6th Edition
- Author(s):
- Release date: February 2014
- Publisher(s): O'Reilly Media, Inc.
- ISBN: 9781449324452
You might also like
book
Oracle PL/SQL Programming, 5th Edition
This book is the definitive reference on PL/SQL, considered throughout the database community to be the …
book
Oracle PL/SQL Programming, Third Edition
Nearly a quarter-million PL/SQL programmers--novices and experienced developers alike--have found the first and second editions of …
video
Oracle PL/SQL Programming Fundamentals LiveLessons
4+ Hours of Video Instruction Overview Oracle PL/SQL Programming Fundamentals LiveLessons covers the basics of Oracle's …
video
Learning Oracle PL/SQL
In this Learning Oracle PL/SQL training course, expert author Darryl Hurley will teach you how to …