Table of Contents
Preface
Chapter 1: Overview of PL/SQL Programming Concepts
Chapter 2: Designing PL/SQL Code
Chapter 3: Using Collections
Chapter 4: Using Advanced Interface Methods
Chapter 5: Implementing VPD with Fine Grained Access Control
Chapter 6: Working with Large Objects
Chapter 7: Using SecureFile LOBs
Chapter 8: Compiling and Tuning to Improve Performance
Chapter 9: Caching to Improve Performance
Chapter 10: Analyzing PL/SQL Code
Chapter 11: Profiling and Tracing PL/SQL Code
Chapter 12: Safeguarding PL/SQL Code against SQL Injection Attacks
Appendix: Answers to Practice Questions
Index
- Chapter 1: Overview of PL/SQL Programming Concepts
- PL/SQL—the procedural aspect
- My first PL/SQL program
- PL/SQL development environments
- SQL Developer
- SQL Developer—the history
- Creating a connection
- SQL Worksheet
- Executing a SQL statement
- Calling a SQL script from SQL Developer
- Creating and executing an anonymous PL/SQL block
- Debugging the PL/SQL code
- Editing and saving the scripts
- SQL*Plus
- Executing a SQL statement in SQL*Plus
- Executing an anonymous PL/SQL block
- Procedures
- Executing a procedure
- Functions
- Function—execution methods
- Restrictions on calling functions from SQL expressions
- PL/SQL packages
- Cursors—an overview
- Cursor execution cycle
- Cursor attributes
- Cursor FOR loop
- Exception handling in PL/SQL
- System-defined exceptions
- User-defined exceptions
- The RAISE_APPLICATION_ERROR procedure
- Exception propagation
- Managing database dependencies
- Displaying the direct and indirect dependencies
- Dependency metadata
- Dependency issues and enhancements
- Reviewing Oracle-supplied packages
- Summary
- Practice exercise
- Chapter 2: Designing PL/SQL Code
- Understanding cursor structures
- Cursor execution cycle
- Cursor design considerations
- Cursor design—guidelines
- Cursor attributes
- Implicit cursors
- Explicit cursors
- Cursor variables
- Ref cursor types—strong and weak
- SYS_REFCURSOR
- Processing a cursor variable
- Cursor variables as arguments
- Cursor variables—restrictions
- Subtypes
- Subtype classification
- Oracle's predefined subtypes
- User-defined subtypes
- Type compatibility with subtypes
- Summary
- Practice exercise
- Chapter 3: Using Collections
- Collections—an overview
- Categorization
- Selecting an appropriate collection type
- Associative arrays
- Nested tables
- Nested table collection type as the database object
- DML operations on nested table columns
- A nested table collection type in PL/SQL
- Additional features of a nested table
- Varray
- Varray in PL/SQL
- Varray as a database collection type
- DML operations on varray type columns
- Collections—a comparative study
- Common characteristics of collection types
- Nested table versus associative arrays
- Nested table versus varrays
- PL/SQL collection methods
- EXISTS
- COUNT
- LIMIT
- FIRST and LAST
- PRIOR and NEXT
- EXTEND
- TRIM
- DELETE
- Manipulating collection elements
- Collection initialization
- Summary
- Practice exercise
- Chapter 4: Using Advanced Interface Methods
- Understanding external routines
- Architecture of external routines
- Oracle Net Configuration
- TNSNAMES.ora
- LISTENER.ora
- Oracle Net Configuration verification
- Benefits of external procedures
- Executing external C programs from PL/SQL
- Executing C program through external procedure—development steps
- Executing Java programs from PL/SQL
- Calling a Java class method from PL/SQL
- Uploading a Java class into the database—development steps
- The loadjava utility—an illustration
- Creating packages for Java class methods
- Summary
- Practice exercise
- Chapter 5: Implementing VPD with Fine Grained Access Control
- Fine Grained Access Control
- Overview
- Virtual Private Database—the alias
- How FGAC or VPD works?
- Salient features of VPD
- VPD implementation—outline and components
- Application context
- Policy function definition and implementation of row-level security
- Associating a policy using the DBMS_RLS package
- VPD implementation—demonstrations
- Assignment 1—implementing VPD using simple security policy
- Assignment 2—implementing VPD using an application context
- VPD policy metadata
- Policy utilities—refresh and drop
- Summary
- Practice exercise
- Chapter 6: Working with Large Objects
- Introduction to the LOB data types
- Internal LOB
- External LOB
- Understanding the LOB data types
- LOB value and LOB locators
- BLOB or CLOB!
- BFILE
- Temporary LOBs
- Creating LOB data types
- Directories
- Creating LOB data type columns in a table
- Managing LOB data types
- Managing internal LOBs
- Securing and managing BFILEs
- The DBMS_LOB package—overview
- Security model
- DBMS_LOB constants
- DBMS_LOB data types
- DBMS_LOB subprograms
- Rules and regulations
- Working with the CLOB, BLOB, and BFILE data types
- Initializing LOB data type columns
- Inserting data into a LOB column
- Populating a LOB data type using an external file
- Selecting LOB data
- Modifying the LOB data
- Delete LOB data
- Miscellaneous LOB notes
- LOB column states
- Locking a row containing LOB
- Opening and closing LOBs
- Accessing LOBs
- LOB restrictions
- Migrating from LONG to LOB
- Using temporary LOBs
- Temporary LOB operations
- Managing temporary LOBs
- Validating, creating, and freeing a temporary LOB
- Summary
- Practice exercise
- Chapter 7: Using SecureFile LOBs
- Introduction to SecureFiles
- SecureFile LOB—an overview
- Architectural enhancements in SecureFiles
- SecureFile LOB features
- Working with SecureFiles
- SecureFile metadata
- Enabling advanced features in SecureFiles
- Deduplication
- Compression
- Encryption
- Migration from BasicFiles to SecureFiles
- Online Redefinition method
- Summary
- Practice exercise
- Chapter 8: Compiling and Tuning to Improve Performance
- Native and interpreted compilation techniques
- Real native compilation
- Selecting the appropriate compilation mode
- When to choose interpreted compilation mode?
- When to choose native compilation mode?
- Setting the compilation mode
- Querying the compilation settings
- Compiling a program unit for a native or interpreted compilation
- Compiling the database for PL/SQL native compilation (NCOMP)
- Tuning PL/SQL code
- Comparing SQL and PL/SQL
- Avoiding implicit data type conversion
- Understanding the NOT NULL constraint
- Using the PLS_INTEGER data type for arithmetic operations
- Using a SIMPLE_INTEGER data type
- Modularizing the PL/SQL code
- Using bulk binding
- Using SAVE_EXCEPTIONS
- Rephrasing the conditional control statements
- Conditions with an OR logical operator
- Conditions with an AND logical operator
- Enabling intra unit inlining
- PLSQL_OPTIMIZE_LEVEL—the Oracle initialization parameter
- Case 1—PLSQL_OPTIMIZE_LEVEL = 0
- Case 2—PLSQL_OPTIMIZE_LEVEL = 1
- Case 3—PLSQL_OPTIMIZE_LEVEL = 2
- Case 4—PLSQL_OPTIMIZE_LEVEL = 3
- PRAGMA INLINE
- Summary
- Practice exercise
- Chapter 9: Caching to Improve Performance
- Introduction to result cache
- Server-side result cache
- SQL query result cache
- PL/SQL function result cache
- OCI client results cache
- Configuring the database for the server result cache
- The DBMS_RESULT_CACHE package
- Implementing the result cache in SQL
- Manual result cache
- Automatic result cache
- Result cache metadata
- Query result cache dependencies
- Cache memory statistics
- Invalidation of SQL result cache
- Displaying the result cache memory report
- Read consistency of the SQL result cache
- Limitation of SQL result cache
- Implementing result cache in PL/SQL
- The RESULT_CACHE clause
- Cross-session availability of cached results
- Invalidation of PL/SQL result cache
- Limitations of PL/SQL function result cache
- Argument and return type restrictions
- Function structural restrictions
- Summary
- Practice exercise
- Chapter 10: Analyzing PL/SQL Code
- Track coding information
- [DBA | ALL | USER]_ARGUMENTS
- [DBA | ALL | USER]_OBJECTS
- [DBA | ALL | USER]_SOURCE
- [DBA | ALL | USER]_PROCEDURES
- [DBA | ALL | USER]_DEPENDENCIES
- Using SQL Developer to find coding information
- The DBMS_DESCRIBE package
- DBMS_UTILITY.FORMAT_CALL_STACK
- Tracking propagating exceptions in PL/SQL code
- Determining identifier types and usages
- The PL/Scope tool
- The PL/Scope identifier collection
- The PL/Scope report
- Illustration
- Applications of the PL/Scope report
- The DBMS_METADATA package
- DBMS_METADATA data types and subprograms
- Parameter requirements
- The DBMS_METADATA transformation parameters and filters
- Working with DBMS_METADATA—illustrations
- Case 1—retrieve the metadata of a single object
- Case 2—retrieve the object dependencies on the F_GET_LOC function
- Case 3—retrieve system grants on the ORADEV schema
- Case 4—retrieve objects of function type in the ORADEV schema
- Summary
- Practice exercise
- Chapter 11: Profiling and Tracing PL/SQL Code
- Tracing the PL/SQL programs
- The DBMS_TRACE package
- Installing DBMS_TRACE
- DBMS_TRACE subprograms
- The PLSQL_DEBUG parameter and the DEBUG option
- Viewing the PL/SQL trace information
- Demonstrating the PL/SQL tracing
- Profiling the PL/SQL programs
- Oracle hierarchical profiler—the DBMS_HPROF package
- View profiler information
- Demonstrating the profiling of a PL/SQL program
- The plshprof utility
- Sample reports
- Summary
- Practice exercise
- Chapter 12: Safeguarding PL/SQL Code against SQL Injection Attacks
- SQL injection—an introduction
- SQL injection—an overview
- Types of SQL injection attacks
- Preventing SQL injection attacks
- Immunizing SQL injection attacks
- Reducing the attack's surface
- Controlling user privileges
- Invoker's and definer's rights
- Avoiding dynamic SQL
- Bind arguments
- Sanitizing inputs using DBMS_ASSERT
- The DBMS_ASSERT package
- Testing the code for SQL injection flaws
- Test strategy
- Reviewing the code
- Static code analysis
- Fuzz tools
- Generating test cases
- Summary
- Practice exercise
- Appendix: Answers to Practice Questions
- Chapter 1, Overview of PL/SQL Programming Concepts
- Chapter 2, Designing PL/SQL Code
- Chapter 3, Using Collections
- Chapter 4, Using Advanced Interface Methods
- Chapter 5, Implementing VPD with Fine Grained Access Control
- Chapter 6, Working with Large Objects
- Chapter 7, Using SecureFile LOBs
- Chapter 8, Compiling and Tuning to Improve Performance
- Chapter 9, Caching to Improve Performance
- Chapter 10, Analyzing PL/SQL Code
- Chapter 11, Profiling and Tracing PL/SQL Code
- Chapter 12, Safeguarding PL/SQL Code against SQL Injection Attacks
Book backreference:
