Oracle Database 11g PL/SQL Programming

Oracle Database 11g PL/SQL Programming

by Michael McLaughlin
Released April 2008
Publisher(s): McGraw Hill Computing
ISBN: 9780071643566

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Design Feature-Rich PL/SQL Applications

Deliver dynamic, client/server PL/SQL applications with expert guidance from an Oracle programming professional. With full coverage of the latest features and tools, Oracle Database 11g PL/SQL Programming lays out each topic alongside detailed explanations, cut-and-paste syntax examples, and real-world case studies. Access and modify database information, construct powerful PL/SQL statements, execute effective queries, and deploy bulletproof security. You'll also learn how to implement C, C++, and Java procedures, Web-enable your database, cut development time, and optimize performance.

  • Create, debug, and manage Oracle-driven PL/SQL programs
  • Use PL/SQL structures, delimiters, operators, variables, and statements
  • Identify and eliminate errors using PLSQL_WARNINGS and exception handlers
  • Work with functions, procedures, packages, collections, and triggers
  • Define and deploy varray, nested table, and associative array data types
  • Handle external routines, object types, large objects, and secure files
  • Communicate between parallel sessions using DBMS_ALERT and DBMS_PIPE
  • Call external procedures through Oracle Net Services and PL/SQL wrappers
  • Integrate internal and server-side Java class libraries using Oracle JVM
  • Develop robust Web applications using PL/SQL Gateway and Web Toolkit

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Dedication Page
  5. Contents at a Glance
  6. Contents
  7. Acknowledgments
  8. Introduction
  9. PART I PL/SQL Fundamentals
    1. 1 Oracle PL/SQL Overview
      1. History and Background
      2. Architecture
      3. Basic Block Structures
      4. Oracle 10g New Features
        1. Built-in Packages
        2. Compile-Time Warnings
        3. Conditional Compilation
        4. Number Datatype Behavior
        5. Optimized PL/SQL Compiler
        6. Regular Expressions
        7. Quoting Alternative
        8. Set Operators
        9. Stack Tracing Errors
        10. Wrapping PL/SQL Stored Programs
      5. Oracle 11g New Features
        1. Automatic Subprogram Inlining
        2. Continue Statement
        3. Cross-Session PL/SQL Function Result Cache
        4. Dynamic SQL Enhancements
        5. Mixed Name and Position Notation Calls
        6. Multiprocess Connection Pool
        7. PL/SQL Hierarchical Profiler
        8. PL/SQL Native Compiler Generates Native Code
        9. PL/Scope
        10. Regular Expression Enhancement
        11. SIMPLE_INTEGER Datatype
        12. Direct Sequence Calls in SQL Statements
      6. Summary
    2. 2 PL/SQL Basics
      1. Oracle PL/SQL Block Structure
      2. Variables, Assignments, and Operators
      3. Control Structures
        1. Conditional Structures
        2. Iterative Structures
      4. Stored Functions, Procedures, and Packages
        1. Stored Functions
        2. Procedures
        3. Packages
      5. Transaction Scope
        1. Single Transaction Scope
        2. Multiple Transaction Scopes
      6. Database Triggers
      7. Summary
    3. 3 Language Fundamentals
      1. Character and Lexical Units
        1. Delimiters
        2. Identifiers
        3. Literals
        4. Comments
      2. Block Structures
      3. Variable Types
        1. Scalar Datatypes
        2. Large Objects (LOBs)
        3. Composite Datatypes
        4. System Reference Cursors
      4. Variable Scope
      5. Summary
    4. 4 Control Structures
      1. Conditional Statements
        1. IF Statements
        2. CASE Statements
        3. Conditional Compilation Statements
      2. Iterative Statements
        1. Simple Loop Statements
        2. FOR Loop Statements
        3. WHILE Loop Statements
      3. Cursor Structures
        1. Implicit Cursors
        2. Explicit Cursors
      4. Bulk Statements
        1. BULK COLLECT INTO Statements
        2. FORALL Statements
      5. Summary
    5. 5 Error Management
      1. Exception Types and Scope
        1. Compilation Errors
        2. Run-Time Errors
      2. Exception Management Built-in Functions
      3. User-Defined Exceptions
        1. Declaring User-Defined Exceptions
        2. Dynamic User-Defined Exceptions
      4. Exception Stack Functions
        1. Exception Stack Management
        2. Error Stack Formatting
      5. Database Trigger Exception Management
        1. Critical Error Database Triggers
        2. Non-Critical Error Database Triggers
      6. Summary
  10. PART II PL/SQL Programming
    1. 6 Functions and Procedures
      1. Function and Procedure Architecture
      2. Transaction Scope
      3. Calling Subroutines
        1. Positional Notation
        2. Named Notation
        3. Mixed Notation
        4. Exclusionary Notation
        5. SQL Call Notation
      4. Functions
        1. Creation Options
        2. Pass-by-Value Functions
        3. Pass-by-Reference Functions
      5. Procedures
        1. Pass-by-Value Procedures
        2. Pass-by-Reference Procedures
      6. Summary
    2. 7 Collections
      1. Collection Types
        1. Varrays
        2. Nested Tables
        3. Associative Arrays
      2. Collection Set Operators
        1. CARDINALITY Operator
        2. EMPTY Operator
        3. MEMBER OF Operator
        4. MULTISET EXCEPT Operator
        5. MULTISET INTERSECT Operator
        6. MULTISET UNION Operator
        7. SET Operator
        8. SUBMULTISET Operator
      3. Collection API
        1. COUNT Method
        2. DELETE Method
        3. EXISTS Method
        4. EXTEND Method
        5. FIRST Method
        6. LAST Method
        7. LIMIT Method
        8. NEXT Method
        9. PRIOR Method
        10. TRIM Method
      4. Summary
    3. 8 Large Objects
      1. Character Large Objects: CLOB and NCLOB Datatypes
        1. PL/SQL Reading Files and Writing CLOB or NCLOB Columns
        2. Uploading CLOBs to the Database
      2. Binary Large Objects: BLOB Datatype
        1. PL/SQL Reading Files and Writing BLOB Columns
        2. Uploading BLOBs to the Database
      3. SecureFiles
      4. Binary Files: BFILE Datatype
        1. Creating and Using Virtual Directories
        2. Reading Canonical Path Names and Filenames
      5. DBMS_LOB Package
        1. Package Constants
        2. Package Exceptions
        3. Opening and Closing Methods
        4. Manipulation Methods
        5. Introspection Methods
        6. BFILE Methods
        7. Temporary LOB Methods
      6. Summary
    4. 9 Packages
      1. Package Architecture
        1. Forward Referencing
        2. Overloading
      2. Package Specification
        1. Variables
        2. Types
        3. Components: Functions and Procedures
      3. Package Body
        1. Variables
        2. Types
        3. Components: Functions and Procedures
      4. Definer vs. Invoker Rights
        1. Grants and Synonyms
        2. Remote Calls
      5. Managing Packages in the Database Catalog
        1. Finding, Validating, and Describing Packages
        2. Checking Dependencies
        3. Comparing Validation Methods: Timestamp vs. Signature
      6. Summary
    5. 10 Triggers
      1. Introduction to Triggers
      2. Database Trigger Architecture
      3. Data Definition Language Triggers
        1. Event Attribute Functions
        2. Building DDL Triggers
      4. Data Manipulation Language Triggers
        1. Statement-Level Triggers
        2. Row-Level Triggers
      5. Compound Triggers
      6. Instead-of Triggers
      7. System or Database Event Triggers
      8. Trigger Restrictions
        1. Maximum Trigger Size
        2. SQL Statements
        3. LONG and LONG RAW Datatypes
        4. Mutating Tables
        5. System Triggers
      9. Summary
  11. PART III PL/SQL Advanced Programming
    1. 11 Dynamic SQL
      1. Dynamic SQL Architecture
      2. Native Dynamic SQL (NDS)
        1. Dynamic Statements
        2. Dynamic Statements with Inputs
        3. Dynamic Statements with Inputs and Outputs
        4. Dynamic Statements with an Unknown Number of Inputs
      3. DBMS_SQL Package
        1. Dynamic Statements
        2. Dynamic Statements with Input Variables
        3. Dynamic Statements with Input and Output Variables
        4. DBMS_SQL Package Definition
      4. Summary
    2. 12 Intersession Communication
      1. Introducing Intersession Communication
        1. Requiring Permanent or Semipermanent Structures
        2. Not Requiring Permanent or Semipermanent Structures
        3. Comparing Intersession Communication Approaches
      2. The DBMS_PIPE Built-in Package
        1. Introducing the DBMS_PIPE Package
        2. Defining the DBMS_PIPE Package
        3. Working with the DBMS_PIPE Package
      3. DBMS_ALERT Built-in Package
        1. Introducing the DBMS_ALERT Package
        2. Defining the DBMS_ALERT Package
        3. Working with the DBMS_ALERT Package
      4. Summary
    3. 13 External Procedures
      1. Introducing External Procedures
      2. Working with External Procedures
        1. Defining the extproc Architecture
        2. Defining extproc Oracle Net Services Configuration
        3. Defining the Multithreaded External Procedure Agent
        4. Working with a C Shared Library
        5. Working with a Java Shared Library
      3. Troubleshooting the Shared Library
        1. Configuration of the Listener or Environment
        2. Configuration of the Shared Library or PL/SQL Library Wrapper
      4. Summary
    4. 14 Object Types
      1. Objects Basics
        1. Declaring Objects
        2. Implementing Object Bodies
        3. Getters and Setters
        4. Static Member Methods
        5. Comparing Objects
      2. Inheritance and Polymorphism
        1. Declaring Subclasses
        2. Implementing Subclasses
        3. Type Evolution
      3. Implementing Collection Object Bodies
        1. Declaring Object Type Collections
        2. Implementing Object Type Collections
      4. Summary
    5. 15 Java Libraries
      1. Oracle 11g JVM New Features
      2. Java Architecture
        1. Java Execution Control
        2. Java Resource Storage
        3. Java Class Names
        4. Java Resolvers
        5. Java Security and Permissions
        6. Java Threading
      3. Oracle Java Connection Types
        1. The Client-Side Driver, or JDBC Thin Driver
        2. The Oracle Call Interface Driver, or Middle-Tier Thick Driver
        3. The Oracle Server-Side Internal Driver, or Server-Tier Thick Driver
      4. Building Java Class Libraries in Oracle
        1. Building Internal Server Java Functions
        2. Building Internal Server Java Procedures
        3. Building Internal Server Java Objects
        4. Troubleshooting Java Class Libraries
      5. Mapping Oracle Types
      6. Summary
    6. 16 Web Application Development
      1. PL/SQL Web Server Architecture
        1. Oracle HTTP Server Architecture
        2. Oracle XML Database Server Architecture
      2. Configuring the Standalone Oracle HTTP Server
        1. Describing mod_plsql Cartridge
        2. Configuring the Oracle HTTP Server
      3. Configuring the XML DB Server
        1. Configuring Static Authentication
        2. Configuring Dynamic Authentication
        3. Configuring Anonymous Authentication
      4. Comparing Web-Enabled PL/SQL Procedures and PSPs
      5. Creating Web-Enabled PL/SQL Stored Procedures
        1. Developing Procedures Without Formal Parameters
        2. Developing Procedures with Formal Parameters
        3. Understanding Advantages and Limitations
      6. Building and Accessing PL/SQL Server Pages (PSPs)
        1. Developing and Running No Formal Parameter PSP Procedures
        2. Developing Formal Parameter PSP Procedures
        3. Understanding Advantages and Limitations
      7. Summary
  12. PART IV Appendixes
    1. A Oracle Database Administration Primer
      1. Oracle Database Architecture
      2. Starting and Stopping the Oracle Database
        1. Unix or Linux Operations
        2. Microsoft Windows Operations
      3. Starting and Stopping the Oracle Listener
      4. Oracle Roles and Privileges
      5. Accessing and Using the SQL*Plus Interface
        1. SQL Command-Line Interface
        2. Bind Variables
      6. Summary
    2. B Oracle Database SQL Primer
      1. Oracle SQL*Plus Datatypes
      2. Data Definition Language (DDL)
        1. Managing Tables and Constraints
        2. Managing Views
        3. Managing Stored Programs
        4. Managing Sequences
        5. Managing User-Defined Types
      3. Data Query Language (DQL)
        1. Queries
      4. Data Manipulation Language (DML)
        1. INSERT Statements
        2. UPDATE Statements
        3. DELETE Statements
      5. Data Control Language (DCL)
      6. Summary
    3. C PHP Primer
      1. History and Background
        1. What Is PHP?
        2. What Is Zend?
      2. Developing Web Programming Solutions
        1. What Goes Where and Why?
        2. What Does Oracle Contribute to PHP?
        3. Why Is PHP 5 Important?
        4. How to Use PHP
        5. How to Use PHP and OCI8 to Access the Oracle Database
      3. Summary
    4. D Oracle Database Java Primer
      1. Java and JDBC Architecture
      2. Configuring the Oracle Java Environment
      3. Java Programming Language Primer
        1. Java Basics
        2. Java Assignment Operators
        3. Java Conditional and Iterative Structures
        4. Java Method Definitions
        5. Java try-catch Blocks
      4. Testing a Client-Side or Thin-Driver JDBC Connection
      5. Accessing Scalar Variables
      6. Writing and Accessing Large Objects
        1. Writing and Accessing a CLOB Column
        2. Accessing a BFILE Column
      7. Summary
    5. E Regular Expression Primer
      1. Introduction to Regular Expressions
        1. Character Classes
        2. Collation Classes
        3. Metacharacters
        4. Metasequences
        5. Literals
      2. Oracle 11g Regular Expression Implementation
        1. REGEXP_COUNT Function
        2. REGEXP_INSTR Function
        3. REGEXP_LIKE Function
        4. REGEXP_REPLACE Function
        5. REGEXP_SUBSTR Function
      3. Using Regular Expressions
        1. REGEXP_COUNT Function
        2. REGEXP_INSTR Function
        3. REGEXP_LIKE Function
        4. REGEXP_REPLACE Function
        5. REGEXP_SUBSTR Function
      4. Summary
    6. F Wrapping PL/SQL Code Primer
      1. Limitations of Wrapping PL/SQL
        1. Limitations of the PL/SQL Wrap Utility
        2. Limitations of the DBMS_DDL.WRAP Function
      2. Using the Wrap Command-Line Utility
      3. Using the DBMS_DDL Command-Line Utility
        1. The WRAP Function
        2. The CREATE_WRAPPED Procedure
      4. Summary
    7. G PL/SQL Hierarchical Profiler Primer
      1. Configuring the Schema
      2. Collecting Profiler Data
      3. Understanding Profiler Data
        1. Reading the Raw Output
        2. Defining the PL/SQL Profiler Tables
        3. Querying the Analyzed Data
      4. Using the plshprof Command-Line Utility
      5. Summary
    8. H PL/Scope
      1. Configuring PL/Scope Data Collection
      2. Viewing PL/Scope Collected Data
      3. Summary
    9. I PL/SQL Reserved Words and Keywords
      1. Summary
    10. J PL/SQL Built-in Functions
      1. Character Functions
        1. ASCII Function
        2. ASCIISTR Function
        3. CHR Function
        4. CONCAT Function
        5. INITCAP Function
        6. INSTR Function
        7. LENGTH Function
        8. LOWER Function
        9. LPAD Function
        10. LTRIM Function
        11. REPLACE Function
        12. RPAD Function
        13. RTRIM Function
        14. UPPER Function
      2. Datatype Conversion
        1. CAST Function
        2. CONVERT Function
        3. TO_CHAR Function
        4. TO_CLOB Function
        5. TO_DATE Function
        6. TO_LOB Function
        7. TO_NCHAR Function
        8. TO_NCLOB Function
        9. TO_NUMBER Function
        10. TO_TIMESTAMP Function
      3. Error Reporting
        1. SQLCODE Function
        2. SQLERRM Function
      4. Miscellaneous
        1. BFILENAME Function
        2. COALESCE Function
        3. DECODE Function
        4. DUMP Function
        5. EMPTY_BLOB Function
        6. EMPTY_CLOB Function
        7. GREATEST Function
        8. LEAST Function
        9. NANVL Function
        10. NULLIF Function
        11. NVL Function
        12. SYS_CONTEXT Function
        13. USERENV Function
        14. VSIZE Function
      5. Number
        1. CEIL Function
        2. FLOOR Function
        3. MOD Function
        4. POWER Function
        5. REMAINDER Function
      6. Summary
  13. Index

Product information

  • Title: Oracle Database 11g PL/SQL Programming
  • Author(s): Michael McLaughlin
  • Release date: April 2008
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071643566