SEARCH
Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.
BOOK EXPERT PLSQL PRACTICES FOR ORACLE DEVELOPERS AND DBAS
SUMMARY
-
Items Found: 550
- Chapter 1: Do Not Use
- Row-by-Row Processing
- Nested Row-by-Row Processing
- Lookup Queries
- Excessive Access to DUAL
- Arithmetics with Date
- Access to Sequences.
- Populating Master-Detail Rows
- Excessive Function Calls
- Unnecessary Function Execution
- Costly Function Calls
- Database Link Calls
- Excessive Use of Triggers
- Excessive Commits
- Excessive Parsing
- Chapter 2: Dynamic SQL: Handling the Unknown
- The Hero
- Native Dynamic SQL
- Dynamic Cursors
- DBMS_SQL
- Sample of Dynamic Thinking
- Security Issues
- Performance and Resource Utilization
- Anti-Patterns
- Comparing Implementations of Dynamic SQL
- Object Dependencies
- Negative Effects
- Positive Effects
- Chapter 3: PL/SQL and Parallel Processing
- Why Parallel Processing?
- Laws Affecting Parallel Processing
- The Rise of Big Data
- Parallel vs. Distributed Processing
- Parallel Hardware Architectures
- Identify Your Goals
- Speedup
- Scaling Up
- Degree of Parallelism
- Candidate Workloads for Parallel Processing
- Parallelism and OLTP
- Parallelism and Non-OLTP Workloads
- The MapReduce Programming Model
- Before Looking to PL/SQL
- Processes Available for Parallel Activities
- Using Parallel Execution Servers for MapReduce
- Pipelined Table Functions
- Guidance
- Chapter 4: Warnings and Conditional Compilation
- PL/SQL Warnings
- Basics
- Using Warnings
- Promoting Warnings to Errors
- Ignoring Warnings
- Compilation and Warnings
- Final Words on Warnings
- Conditional Compilation
- Basics
- What Part of the Code is Running?
- Benefits of Preprocessing Code
- Invalidations
- Controlling Compilation
- Inquiry Variables
- Final Words on Conditional Compilation
- Chapter 5: PL/SQL Unit Testing
- Why Test Your Code?
- What Is Unit Testing?
- Debugging or Testing?
- When Should You Build Tests?
- Tools for Building Unit Tests
- utPLSQL: Working with Command Line Code
- Quest Code Tester for Oracle
- Oracle SQL Developer
- Preparing and Maintaining the Unit Test Environment
- Creating the Unit Test Repository
- Maintaining the Unit Test Repository
- Importing Tests
- Building Unit Tests
- Using the Unit Test Wizard
- Creating the First Implementation
- Adding Startup and Teardown Processes.
- Collecting Code Coverage Statistics
- Specifying Parameters
- Adding Process Validation
- Saving Tests
- Debugging and Running Tests
- Broadening the Scope of the Tests
- Creating Lookup Values
- Seeding Test Implementations
- Creating a Dynamic Query
- Supporting Unit Test Features
- Running Reports
- Creating a Library of Components
- Exporting,Importing,and Synchronizing Tests
- Building Suites
- Running Tests from the Command Line
- Chapter 6: Bulk SQL Operations
- The Hardware Store
- Setting for the Examples in this Chapter
- Bulk Operations in PL/SQL
- Getting Started with BULK Fetch
- Three Collection-Style Datatypes
- Why should I bother?
- Monitoring Bulk Collect Overheads
- Refactoring Code to Use Bulk Collect
- Bulk Binding
- Getting Started with Bulk Bind
- Measuring Bulk Binding Performance
- Monitoring Memory Usage
- Improvements in 11g
- Error Handling with Bulk Bind
- SAVE EXCEPTIONS with Batches
- LOG ERRORS Clause
- Robust Bulk Bind
- A Justification for Massive Collections
- The Real Benefit: Client Bulk Processing
- Chapter 7: Know Your Code
- What This Chapter Will (and Will Not) Cover
- Automated Code Analysis
- Static Analysis
- Dynamic Analysis
- When to Analyze?
- Performing Static Analysis
- The Data Dictionary
- PL/SCOPE
- Performing Dynamic Analysis
- DBMS_PROFILER and DBMS_TRACE
- DBMS_HPROF
- Chapter 8: Contract-Oriented Programming
- Design by Contract
- Software Contracts
- Basic Contract Elements
- Assertions
- References
- Implementing PL/SQL Contracts
- Basic ASSERT Procedure
- Standard Package-Local ASSERT
- Enforcing Contracts Using ASSERT
- An Additional Improvement
- Contract-Oriented Function Prototype
- Example: Testing Odd and Even Integers
- Useful Contract Patterns
- Not-NULL IN / Not-NULL OUT
- FUNCTION RETURN Not-NULL
- FUNCTION RETURN BOOLEAN Not-NULL
- Check Functions: RETURN TRUE OR ASSERTFAIL
- Principles For Bug-Free Code
- Assert Preconditions Rigorously
- Modularize Ruthlessly
- Adopt Function-Based Interfaces
- Crash on ASSERTFAIL
- Regression Test Your Postconditions
- Avoid Correctness-Performance Tradeoffs
- Oracle 11g Optimized Compilation
- Chapter 9: PL/SQL from SQL
- The Cost of Using PL/SQL Functions in SQL
- Context-Switching
- Executions
- Suboptimal Data Access
- Optimizer Difficulties
- The Read-Consistency Trap
- Other Issues
- Reducing the Cost of PL/SQL Functions
- A Sense of Perspective
- Using SQL Alternatives
- Reducing Executions
- Assisting the CBO
- Tuning PL/SQL
- Chapter 10: Choosing the Right Cursor
- Explicit Cursors
- The Anatomy of an Explicit Cursor.
- Explicit Cursors and Bulk Processing
- REF Cursors in Brief
- Implicit Cursors
- The Anatomy of an Implicit Cursor
- The Implicit Cursor and the Extra Fetch Theory
- Static REF Cursors
- Cursor Variable Restrictions Laundry List.
- Your Client and REF Cursors
- A Few Words about Parsing
- Dynamic REF Cursors
- Example and Best Use
- The Threat of SQL Injection
- Describing REF Cursor Columns
- Chapter 11: PL/SQL Programming in the Large
- The Database as PL/SQL-Based Application Server
- Case Study: The Avaloq Banking System
- Strengths of Business Logic in the Database with PL/SQL.
- Limits of the Database as PL/SQL-Based Application Server
- Soft Factors
- Requirements of Programming in the Large
- Uniformity through Conventions
- Abbreviations
- Pre- and Suffixes for PL/SQL Identifiers
- Modularization of Code and Data
- Packages and Associated Tables as Modules
- Modules Containing Multiple Packages or Submodules
- Schemas as Modules
- Modularization within Schemas
- Modularization with Schemas vs. within Schemas
- Object-Oriented Programming with PL/SQL
- Object-Oriented Programming with User-Defined Types
- Object-Oriented Programming with PL/SQL Records
- Assessment
- Memory Management
- Measuring Memory Usage
- Collections
- Chapter 12: Evolutionary Data Modeling
- Lessons from Two Decades of System Development
- The Database and Agile Development
- Evolutionary Data Modeling
- Refactoring the Database
- Creating an Access Layer via PL/SQL
- The Agile Manifesto
- Using PL/SQL with Evolutionary Data Modeling
- Define the Interface
- Think Extensible
- Test Driven Development
- Use Schemas and Users Wisely
- Chapter 13: Profiling for Performance
- What Is Performance?
- Functional Requirements
- Response Time
- Throughput
- Resource Utilization
- Performance Is a Feature
- What Is Profiling?
- Sequence Diagrams
- The Magic of Profiles
- Benefits of Profiling
- Instrumentation
- Why is This Program Slow?
- Measurement Intrusion
- Identification
- Conditional Compilation
- Built-in Profilers
- Extended SQL Trace Data (Event 10046)
- Instrumentation Library for Oracle (ILO)
- Problem Diagnosis
- Method R
- ILO Example
- Profiling Example
- Chapter 14: Coding Conventions and Error Handling
- Why coding conventions?
- Formatting
- Case
- Comments
- Indentation.
- Dynamic Code
- Packages
- Stored Procedures
- Naming
- Parameters
- Calls
- Local Variables
- Constants
- Types
- Global Variables
- Local Procedures and Functions
- Procedure Metadata
- Functions
- Error Handling
- Error Trapping
- Error Reporting
- Error Recovery
- Test First. Display Second.
- Chapter 15: Dependencies and Invalidations
- Dependency Chains
- Shortening Dependency Chains
- Datatype Reference
- View for Table Alterations
- Adding Components into Packages
- Synonyms in Dependency Chains
- Resource Locking
- Forcing Dependency in Triggers
- Creating Triggers Disabled Initially
- Chapter 1: Do Not Use
- Row-by-Row Processing
- Nested Row-by-Row Processing
- Lookup Queries
- Excessive Access to DUAL
- Arithmetics with Date
- Access to Sequences.
- Populating Master-Detail Rows
- Excessive Function Calls
- Unnecessary Function Execution
- Costly Function Calls
- Database Link Calls
- Excessive Use of Triggers
- Excessive Commits
- Excessive Parsing
- Chapter 2: Dynamic SQL: Handling the Unknown
- The Hero
- Native Dynamic SQL
- Dynamic Cursors
- DBMS_SQL
- Sample of Dynamic Thinking
- Security Issues
- Performance and Resource Utilization
- Anti-Patterns
- Comparing Implementations of Dynamic SQL
- Object Dependencies
- Negative Effects
- Positive Effects
- Chapter 3: PL/SQL and Parallel Processing
- Why Parallel Processing?
- Laws Affecting Parallel Processing
- The Rise of Big Data
- Parallel vs. Distributed Processing
- Parallel Hardware Architectures
- Identify Your Goals
- Speedup
- Scaling Up
- Degree of Parallelism
- Candidate Workloads for Parallel Processing
- Parallelism and OLTP
- Parallelism and Non-OLTP Workloads
- The MapReduce Programming Model
- Before Looking to PL/SQL
- Processes Available for Parallel Activities
- Using Parallel Execution Servers for MapReduce
- Pipelined Table Functions
- Guidance
- Chapter 4: Warnings and Conditional Compilation
- PL/SQL Warnings
- Basics
- Using Warnings
- Promoting Warnings to Errors
- Ignoring Warnings
- Compilation and Warnings
- Final Words on Warnings
- Conditional Compilation
- Basics
- What Part of the Code is Running?
- Benefits of Preprocessing Code
- Invalidations
- Controlling Compilation
- Inquiry Variables
- Final Words on Conditional Compilation
- Chapter 5: PL/SQL Unit Testing
- Why Test Your Code?
- What Is Unit Testing?
- Debugging or Testing?
- When Should You Build Tests?
- Tools for Building Unit Tests
- utPLSQL: Working with Command Line Code
- Quest Code Tester for Oracle
- Oracle SQL Developer
- Preparing and Maintaining the Unit Test Environment
- Creating the Unit Test Repository
- Maintaining the Unit Test Repository
- Importing Tests
- Building Unit Tests
- Using the Unit Test Wizard
- Creating the First Implementation
- Adding Startup and Teardown Processes.
- Collecting Code Coverage Statistics
- Specifying Parameters
- Adding Process Validation
- Saving Tests
- Debugging and Running Tests
- Broadening the Scope of the Tests
- Creating Lookup Values
- Seeding Test Implementations
- Creating a Dynamic Query
- Supporting Unit Test Features
- Running Reports
- Creating a Library of Components
- Exporting,Importing,and Synchronizing Tests
- Building Suites
- Running Tests from the Command Line
- Chapter 6: Bulk SQL Operations
- The Hardware Store
- Setting for the Examples in this Chapter
- Bulk Operations in PL/SQL
- Getting Started with BULK Fetch
- Three Collection-Style Datatypes
- Why should I bother?
- Monitoring Bulk Collect Overheads
- Refactoring Code to Use Bulk Collect
- Bulk Binding
- Getting Started with Bulk Bind
- Measuring Bulk Binding Performance
- Monitoring Memory Usage
- Improvements in 11g
- Error Handling with Bulk Bind
- SAVE EXCEPTIONS with Batches
- LOG ERRORS Clause
- Robust Bulk Bind
- A Justification for Massive Collections
- The Real Benefit: Client Bulk Processing
- Chapter 7: Know Your Code
- What This Chapter Will (and Will Not) Cover
- Automated Code Analysis
- Static Analysis
- Dynamic Analysis
- When to Analyze?
- Performing Static Analysis
- The Data Dictionary
- PL/SCOPE
- Performing Dynamic Analysis
- DBMS_PROFILER and DBMS_TRACE
- DBMS_HPROF
- Chapter 8: Contract-Oriented Programming
- Design by Contract
- Software Contracts
- Basic Contract Elements
- Assertions
- References
- Implementing PL/SQL Contracts
- Basic ASSERT Procedure
- Standard Package-Local ASSERT
- Enforcing Contracts Using ASSERT
- An Additional Improvement
- Contract-Oriented Function Prototype
- Example: Testing Odd and Even Integers
- Useful Contract Patterns
- Not-NULL IN / Not-NULL OUT
- FUNCTION RETURN Not-NULL
- FUNCTION RETURN BOOLEAN Not-NULL
- Check Functions: RETURN TRUE OR ASSERTFAIL
- Principles For Bug-Free Code
- Assert Preconditions Rigorously
- Modularize Ruthlessly
- Adopt Function-Based Interfaces
- Crash on ASSERTFAIL
- Regression Test Your Postconditions
- Avoid Correctness-Performance Tradeoffs
- Oracle 11g Optimized Compilation
- Chapter 9: PL/SQL from SQL
- The Cost of Using PL/SQL Functions in SQL
- Context-Switching
- Executions
- Suboptimal Data Access
- Optimizer Difficulties
- The Read-Consistency Trap
- Other Issues
- Reducing the Cost of PL/SQL Functions
- A Sense of Perspective
- Using SQL Alternatives
- Reducing Executions
- Assisting the CBO
- Tuning PL/SQL
- Chapter 10: Choosing the Right Cursor
- Explicit Cursors
- The Anatomy of an Explicit Cursor.
- Explicit Cursors and Bulk Processing
- REF Cursors in Brief
- Implicit Cursors
- The Anatomy of an Implicit Cursor
- The Implicit Cursor and the Extra Fetch Theory
- Static REF Cursors
- Cursor Variable Restrictions Laundry List.
- Your Client and REF Cursors
- A Few Words about Parsing
- Dynamic REF Cursors
- Example and Best Use
- The Threat of SQL Injection
- Describing REF Cursor Columns
- Chapter 11: PL/SQL Programming in the Large
- The Database as PL/SQL-Based Application Server
- Case Study: The Avaloq Banking System
- Strengths of Business Logic in the Database with PL/SQL.
- Limits of the Database as PL/SQL-Based Application Server
- Soft Factors
- Requirements of Programming in the Large
- Uniformity through Conventions
- Abbreviations
- Pre- and Suffixes for PL/SQL Identifiers
- Modularization of Code and Data
- Packages and Associated Tables as Modules
- Modules Containing Multiple Packages or Submodules
- Schemas as Modules
- Modularization within Schemas
- Modularization with Schemas vs. within Schemas
- Object-Oriented Programming with PL/SQL
- Object-Oriented Programming with User-Defined Types
- Object-Oriented Programming with PL/SQL Records
- Assessment
- Memory Management
- Measuring Memory Usage
- Collections
- Chapter 12: Evolutionary Data Modeling
- Lessons from Two Decades of System Development
- The Database and Agile Development
- Evolutionary Data Modeling
- Refactoring the Database
- Creating an Access Layer via PL/SQL
- The Agile Manifesto
- Using PL/SQL with Evolutionary Data Modeling
- Define the Interface
- Think Extensible
- Test Driven Development
- Use Schemas and Users Wisely
- Chapter 13: Profiling for Performance
- What Is Performance?
- Functional Requirements
- Response Time
- Throughput
- Resource Utilization
- Performance Is a Feature
- What Is Profiling?
- Sequence Diagrams
- The Magic of Profiles
- Benefits of Profiling
- Instrumentation
- Why is This Program Slow?
- Measurement Intrusion
- Identification
- Conditional Compilation
- Built-in Profilers
- Extended SQL Trace Data (Event 10046)
- Instrumentation Library for Oracle (ILO)
- Problem Diagnosis
- Method R
- ILO Example
- Profiling Example
- Chapter 14: Coding Conventions and Error Handling
- Why coding conventions?
- Formatting
- Case
- Comments
- Indentation.
- Dynamic Code
- Packages
- Stored Procedures
- Naming
- Parameters
- Calls
- Local Variables
- Constants
- Types
- Global Variables
- Local Procedures and Functions
- Procedure Metadata
- Functions
- Error Handling
- Error Trapping
- Error Reporting
- Error Recovery
- Test First. Display Second.
- Chapter 15: Dependencies and Invalidations
- Dependency Chains
- Shortening Dependency Chains
- Datatype Reference
- View for Table Alterations
- Adding Components into Packages
- Synonyms in Dependency Chains
- Resource Locking
- Forcing Dependency in Triggers
- Creating Triggers Disabled Initially
