Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Application Development?

• Oracle Database 11g Release 2 (11.2) Features
• Oracle Database 11g Release 1 (11.1) Features

Part I SQL for Application Developers

1 SQL Processing for Application Developers

• Description of SQL Statement Processing
• Processing Other Types of SQL Statements
  • DDL Statement Processing
  • Transaction Control Statement Processing
  • Other Processing Types
• Grouping Operations into Transactions
  • Deciding How to Group Operations in Transactions
  • Improving Transaction Performance
  • Committing Transactions
  • Managing Commit Redo Action
  • Rolling Back Transactions
  • Defining Transaction Savepoints
• Ensuring Repeatable Reads with Read-Only Transactions
• Using Cursors
  • How Many Cursors Can a Session Have?
  • Using a Cursor to Reexecute a Statement
  • Scrollable Cursors
  • Closing a Cursor
  • Canceling a Cursor
• Locking Tables Explicitly
  • Privileges Required to Acquire Table Locks
  • Choosing a Locking Strategy
    • When to Lock with ROW SHARE MODE and ROW EXCLUSIVE MODE
    • When to Lock with SHARE MODE
    • When to Lock with SHARE ROW EXCLUSIVE MODE
    • When to Lock with EXCLUSIVE MODE
  • Letting Oracle Database Control Table Locking
  • Explicitly Acquiring Row Locks
  • Examples of Concurrency Under Explicit Locking
• Using Oracle Lock Management Services (User Locks)
  • When to Use User Locks
  • Viewing and Monitoring Locks
• Using Serializable Transactions for Concurrency Control
  • How Serializable Transactions Interact
  • Setting the Isolation Level of a Serializable Transaction
  • Referential Integrity and Serializable Transactions
  • READ COMMITTED and SERIALIZABLE Isolation
    • Transaction Set Consistency
    • Comparison of READ COMMITTED and SERIALIZABLE Transactions
    • Choosing an Isolation Level for Transactions
  • Application Tips for Transactions
• Autonomous Transactions
  • Examples of Autonomous Transactions
    • Ordering a Product
    • Withdrawing Money from a Bank Account
  • Defining Autonomous Transactions
• Resuming Execution After Storage Allocation Error
  • What Operations Can Be Resumed After an Error Condition?
  • Handling Suspended Storage Allocation

2 Using SQL Data Types in Database Applications

• Overview of SQL Data Types
• Representing Character Data
  • Overview of Character Data Types
  • Specifying Column Lengths as Bytes or Characters
  • Choosing Between CHAR and VARCHAR2 Data Types
  • Using Character Literals in SQL Statements
• Representing Numeric Data
  • Overview of Numeric Data Types
  • Floating-Point Number Formats
    • Using a Floating-Point Binary Format
    • Special Values for Native Floating-Point Formats
  • Comparison Operators for Native Floating-Point Data Types
  • Arithmetic Operations with Native Floating-Point Data Types
  • Conversion Functions for Native Floating-Point Data Types
  • Client Interfaces for Native Floating-Point Data Types
    • OCI Native Floating-Point Data Types SQLT_BFLOAT and SQLT_BDOUBLE
    • Native Floating-Point Data Types Supported in ADTs
    • Pro*C/C++ Support for Native Floating-Point Data Types
• Representing Date and Time Data
  • Overview of Date and Time Data Types
  • Displaying Current Date and Time
  • Changing the Default Date Format
  • Changing the Default Time Format
  • Arithmetic Operations with Date and Time Data Types
  • Converting Between Date and Time Types
  • Importing and Exporting Date and Time Types
• Representing Specialized Data
  • Representing Geographic Data
  • Representing Multimedia Data
  • Representing Large Amounts of Data
  • Representing Searchable Text
  • Representing XML
  • Representing Dynamically Typed Data
  • Representing Data with ANSI/ISO, DB2, and SQL/DS Data Types
• Representing Conditional Expressions as Data
• Identifying Rows by Address
  • Querying the ROWID Pseudocolumn
  • ROWID Data Type
    • Restricted ROWID
    • Extended ROWID
    • External Binary ROWID
  • UROWID Data Type
• How Oracle Database Converts Data Types
  • Data Type Conversion During Assignments
  • Data Type Conversion During Expression Evaluation
• Metadata for SQL Built-In Functions

3 Using Regular Expressions in Database Applications

• Overview of Regular Expressions
  • What Are Regular Expressions?
  • How Are Regular Expressions Useful?
  • Oracle Database Implementation of Regular Expressions
  • Oracle Database Support for the POSIX Regular Expression Standard
• Metacharacters in Regular Expressions
  • POSIX Metacharacters in Oracle Database Regular Expressions
  • Multilingual Extensions to POSIX Regular Expression Standard
  • PERL-Influenced Extensions to POSIX Regular Expression Standard
• Using Regular Expressions in SQL Statements: Scenarios
  • Using a Constraint to Enforce a Phone Number Format
  • Using Back References to Reposition Characters

4 Using Indexes in Database Applications

• Privileges Needed to Create Indexes
• Guidelines for Application-Specific Indexes
  • Which Come First, Data or Indexes?
  • Create a Temporary Table Space Before Creating Indexes
  • Index the Correct Tables and Columns
  • Limit the Number of Indexes for Each Table
  • Choose Column Order in Composite Indexes
  • Gather Index Statistics
  • Drop Unused Indexes
• Examples of Creating Basic Indexes
• When to Use Domain Indexes
• When to Use Function-Based Indexes
  • Advantages of Function-Based Indexes
  • Restrictions on Function-Based Indexes
  • Examples of Function-Based Indexes
    • Function-Based Index for Case-Insensitive Searches
    • Precomputing Arithmetic Expressions with a Function-Based Index
    • Function-Based Index for Language-Dependent Sorting

5 Maintaining Data Integrity in Database Applications

• Overview of Constraints
  • Enforcing Business Rules with Constraints
  • Enforcing Business Rules with Application Logic
  • Creating Indexes for Use with Constraints
  • When to Use NOT NULL Constraints
  • When to Use Default Column Values
  • Setting Default Column Values
  • Choosing a Primary Key for a Table
  • When to Use UNIQUE Constraints
  • When to Use Constraints On Views
• Enforcing Referential Integrity with Constraints
  • FOREIGN KEY Constraints and NULL Values
  • Defining Relationships Between Parent and Child Tables
  • Rules for Multiple FOREIGN KEY Constraints
  • Deferring Constraint Checks
• Minimizing Space and Time Overhead for Indexes Associated with Constraints
• Guidelines for Indexing Foreign Keys
• Referential Integrity in a Distributed Database
• When to Use CHECK Constraints
  • Restrictions on CHECK Constraints
  • Designing CHECK Constraints
  • Rules for Multiple CHECK Constraints
  • Choosing Between CHECK and NOT NULL Constraints
• Examples of Defining Constraints
  • Privileges Needed to Define Constraints
  • Naming Constraints
• Enabling and Disabling Constraints
  • Why Disable Constraints?
  • Creating Enabled Constraints (Default)
  • Creating Disabled Constraints
  • Enabling Existing Constraints
  • Disabling Existing Constraints
  • Guidelines for Enabling and Disabling Key Constraints
  • Fixing Constraint Exceptions
• Modifying Constraints
• Renaming Constraints
• Dropping Constraints
• Managing FOREIGN KEY Constraints
  • Data Types and Names for Foreign Key Columns
  • Limit on Columns in Composite Foreign Keys
  • Foreign Key References Primary Key by Default
  • Privileges Required to Create FOREIGN KEY Constraints
  • Choosing How Foreign Keys Enforce Referential Integrity
• Viewing Information About Constraints

Part II PL/SQL for Application Developers

6 Coding PL/SQL Subprograms and Packages

• Overview of PL/SQL Units
  • Anonymous Blocks
  • Stored PL/SQL Units
    • Naming Subprograms
    • Subprogram Parameters
    • Creating Subprograms
    • Altering Subprograms
    • Dropping Subprograms and Packages
    • External Subprograms
    • PL/SQL Function Result Cache
    • PL/SQL Packages
    • PL/SQL Object Size Limits
    • Creating Packages
    • Naming Packages and Package Objects
    • Package Invalidations and Session State
    • Packages Supplied with Oracle Database
    • Overview of Bulk Binding
    • When to Use Bulk Binds
    • Triggers
• Compiling PL/SQL Subprograms for Native Execution
• Cursor Variables
  • Declaring and Opening Cursor Variables
  • Examples of Cursor Variables
• Handling PL/SQL Compile-Time Errors
• Handling Run-Time PL/SQL Errors
  • Declaring Exceptions and Exception Handlers
  • Unhandled Exceptions
  • Handling Errors in Distributed Queries
  • Handling Errors in Remote Subprograms
• Debugging Stored Subprograms
  • PL/Scope
  • PL/SQL Hierarchical Profiler
  • Oracle JDeveloper
  • DBMS_OUTPUT Package
  • Privileges for Debugging PL/SQL and Java Stored Subprograms
  • Writing Low-Level Debugging Code
  • DBMS_DEBUG_JDWP Package
  • DBMS_DEBUG Package
• Invoking Stored Subprograms
  • Privileges Required to Invoke a Subprogram
  • Invoking a Subprogram Interactively from Oracle Tools
  • Invoking a Subprogram from Another Subprogram
  • Invoking a Subprogram from a 3GL Application
• Invoking Remote Subprograms
  • Synonyms for Remote Subprograms
  • Committing Transactions
• Invoking Stored PL/SQL Functions from SQL Statements
  • Why Invoke Stored PL/SQL Subprograms from SQL Statements?
  • Where PL/SQL Functions Can Appear in SQL Statements
  • When PL/SQL Functions Can Appear in SQL Expressions
  • Controlling Side Effects
    • Restrictions
    • Declaring a Function
    • Parallel Query and Parallel DML
    • PRAGMA RESTRICT_REFERENCES for Backward Compatibility
• Returning Large Amounts of Data from a Function
• Coding Your Own Aggregate Functions

7 Using PL/Scope

• Specifying Identifier Collection
• PL/Scope Identifier Data for STANDARD and DBMS_STANDARD
• How Much Space is PL/Scope Data Using?
• Viewing PL/Scope Data
  • Static Data Dictionary Views
    • Unique Keys
    • Context
    • Signature
  • Demo Tool
  • SQL Developer
• Identifier Types that PL/Scope Collects
• Usages that PL/Scope Reports
• Sample PL/Scope Session

8 Using the PL/SQL Hierarchical Profiler

• Overview of PL/SQL Hierarchical Profiler
• Collecting Profile Data
• Understanding Raw Profiler Output
  • Namespaces of Tracked Subprograms
  • Special Function Names
• Analyzing Profile Data
  • Creating Hierarchical Profiler Tables
  • Understanding Hierarchical Profiler Tables
    • Hierarchical Profiler Database Table Columns
    • Distinguishing Between Overloaded Subprograms
    • Hierarchical Profiler Tables for Sample PL/SQL Procedure
    • Examples of Calls to DBMS_HPROF.analyze with Options
• plshprof Utility
  • plshprof Options
  • HTML Report from a Single Raw Profiler Output File
    • First Page of Report
    • Function-Level Reports
    • Module-Level Reports
    • Namespace-Level Reports
    • Parents and Children Report for a Function
  • HTML Difference Report from Two Raw Profiler Output Files
    • Difference Report Conventions
    • First Page of Difference Report
    • Function-Level Difference Reports
    • Module-Level Difference Reports
    • Namespace-Level Difference Reports
    • Parents and Children Difference Report for a Function

9 Developing PL/SQL Web Applications

• Overview of PL/SQL Web Applications
• Implementing PL/SQL Web Applications
  • PL/SQL Gateway
    • mod_plsql
    • Embedded PL/SQL Gateway
  • PL/SQL Web Toolkit
• Using mod_plsql Gateway to Map Client Requests to a PL/SQL Web Application
• Using Embedded PL/SQL Gateway
  • How Embedded PL/SQL Gateway Processes Client Requests
  • Installing Embedded PL/SQL Gateway
  • Configuring Embedded PL/SQL Gateway
    • Configuring Embedded PL/SQL Gateway: Overview
    • Configuring User Authentication for Embedded PL/SQL Gateway
  • Invoking PL/SQL Stored Subprograms Through Embedded PL/SQL Gateway
  • Securing Application Access with Embedded PL/SQL Gateway
  • Restrictions in Embedded PL/SQL Gateway
  • Using Embedded PL/SQL Gateway: Scenario
• Generating HTML Output with PL/SQL
• Passing Parameters to PL/SQL Web Applications
  • Passing List and Dropdown-List Parameters from an HTML Form
  • Passing Option and Check Box Parameters from an HTML Form
  • Passing Entry-Field Parameters from an HTML Form
  • Passing Hidden Parameters from an HTML Form
  • Uploading a File from an HTML Form
  • Submitting a Completed HTML Form
  • Handling Missing Input from an HTML Form
  • Maintaining State Information Between Web Pages
• Performing Network Operations in PL/SQL Subprograms
  • Sending E-Mail from PL/SQL
  • Getting a Host Name or Address from PL/SQL
  • Using TCP/IP Connections from PL/SQL
  • Retrieving HTTP URL Contents from PL/SQL
  • Using Tables, Image Maps, Cookies, and CGI Variables from PL/SQL

10 Developing PL/SQL Server Pages (PSP)

• What Are PL/SQL Server Pages and Why Use Them?
• Prerequisites for Developing and Deploying PL/SQL Server Pages
• PL/SQL Server Pages and the HTP Package
• PL/SQL Server Pages and Other Scripting Solutions
• Developing PL/SQL Server Pages
  • Specifying Basic Server Page Characteristics
    • Specifying the Scripting Language
    • Returning Data to the Client Browser
    • Handling Script Errors
  • Accepting User Input
  • Naming the PL/SQL Stored Procedure
  • Including the Contents of Other Files
  • Declaring Global Variables in a PSP Script
  • Specifying Executable Statements in a PSP Script
  • Substituting Expression Values in a PSP Script
  • Using Quotation Marks and Escaping Strings in a PSP Script
  • Including Comments in a PSP Script
• Loading PL/SQL Server Pages into the Database
• Querying PL/SQL Server Page Source Code
• Running PL/SQL Server Pages Through URLs
• Examples of PL/SQL Server Pages
  • Setup for PL/SQL Server Pages Examples
  • Printing the Sample Table with a Loop
  • Allowing a User Selection
  • Using an HTML Form to Invoke a PL/SQL Server Page
  • Including JavaScript in a PSP File
• Debugging PL/SQL Server Pages
• Putting PL/SQL Server Pages into Production

11 Using Continuous Query Notification (CQN)

• Object Change Notification (OCN)
• Query Result Change Notification (QRCN)
  • Guaranteed Mode
  • Best-Effort Mode
• Events that Generate Notifications
  • Committed DML Transactions
  • Committed DDL Statements
  • Deregistration
  • Global Events
• Notification Contents
• Good Candidates for CQN
• Creating CQN Registrations
  • PL/SQL CQN Registration Interface
  • CQN Registration Options
    • Notification Type Option
    • QRCN Mode (QRCN Notification Type Only)
    • ROWID Option
    • Operations Filter Option (OCN Notification Type Only)
    • Transaction Lag Option (OCN Notification Type Only)
    • Notification Grouping Options
    • Reliable Option
    • Purge-on-Notify and Timeout Options
  • Prerequisites for Creating CQN Registrations
  • Queries that Can Be Registered for Object Change Notification (OCN)
  • Queries that Can Be Registered for Query Result Change Notification (QRCN)
    • Queries that Can Be Registered for QRCN in Guaranteed Mode
    • Queries that Can Be Registered for QRCN Only in Best-Effort Mode
    • Queries that Cannot Be Registered for QRCN in Either Mode
  • Using PL/SQL to Register Queries for CQN
    • Creating a PL/SQL Notification Handler
    • Creating a CQ_NOTIFICATION$_REG_INFO Object
    • Identifying Individual Queries in a Notification
    • Adding Queries to an Existing Registration
  • Best Practices for CQN Registrations
  • Troubleshooting CQN Registrations
• Querying CQN Registrations
• Interpreting Notifications
  • Interpreting a CQ_NOTIFICATION$_DESCRIPTOR Object
  • Interpreting a CQ_NOTIFICATION$_TABLE Object
  • Interpreting a CQ_NOTIFICATION$_QUERY Object
  • Interpreting a CQ_NOTIFICATION$_ROW Object
• Deleting Registrations
• Configuring CQN: Scenario
  • Creating a PL/SQL Notification Handler
  • Registering the Queries

Part III Advanced Topics for Application Developers

12 Using Oracle Flashback Technology

• Overview of Oracle Flashback Technology
  • Application Development Features
  • Database Administration Features
• Configuring Your Database for Oracle Flashback Technology
  • Configuring Your Database for Automatic Undo Management
  • Configuring Your Database for Oracle Flashback Transaction Query
  • Configuring Your Database for Flashback Transaction
  • Enabling Oracle Flashback Operations on Specific LOB Columns
  • Granting Necessary Privileges
• Using Oracle Flashback Query (SELECT AS OF)
  • Example of Examining and Restoring Past Data
  • Guidelines for Oracle Flashback Query
• Using Oracle Flashback Version Query
• Using Oracle Flashback Transaction Query
• Using Oracle Flashback Transaction Query with Oracle Flashback Version Query
• Using ORA_ROWSCN
  • Scenario: Packaged Subprogram Might Change Row
  • ORA_ROWSCN and Tables with Virtual Private Database (VPD)
• Using DBMS_FLASHBACK Package
• Using Flashback Transaction
  • Dependent Transactions
  • TRANSACTION_BACKOUT Parameters
  • TRANSACTION_BACKOUT Reports
    • *_FLASHBACK_TXN_STATE
    • *_FLASHBACK_TXN_REPORT
• Using Flashback Data Archive (Oracle Total Recall)
  • Creating a Flashback Data Archive
  • Altering a Flashback Data Archive
  • Dropping a Flashback Data Archive
  • Specifying the Default Flashback Data Archive
  • Enabling and Disabling Flashback Data Archive
  • DDL Statements on Tables Enabled for Flashback Data Archive
  • Viewing Flashback Data Archive Data
  • Flashback Data Archive Scenarios
    • Scenario: Using Flashback Data Archive to Enforce Digital Shredding
    • Scenario: Using Flashback Data Archive to Access Historical Data
    • Scenario: Using Flashback Data Archive to Generate Reports
    • Scenario: Using Flashback Data Archive for Auditing
    • Scenario: Using Flashback Data Archive to Recover Data
• General Guidelines for Oracle Flashback Technology
• Performance Guidelines for Oracle Flashback Technology

13 Choosing a Programming Environment

• Overview of Application Architecture
  • Client/Server Architecture
  • Server-Side Programming
  • Two-Tier and Three-Tier Architecture
• Overview of the Program Interface
  • User Interface
  • Stateful and Stateless User Interfaces
• Overview of PL/SQL
• Overview of Oracle Database Java Support
  • Overview of Oracle JVM
  • Overview of Oracle JDBC
    • Oracle JDBC Drivers
    • Sample JDBC 2.0 Program
    • Sample Pre-2.0 JDBC Program
  • Overview of Oracle SQLJ
    • Benefits of SQLJ
    • SQLJ Stored Subprograms in the Server
  • Comparing Oracle JDBC and Oracle SQLJ
  • Overview of Oracle JPublisher
  • Overview of Java Stored Subprograms
  • Overview of Oracle Database Web Services
• Choosing PL/SQL or Java
  • Similarities of PL/SQL and Java
  • PL/SQL Advantages Over Java
  • Java Advantages Over PL/SQL
• Overview of Precompilers
  • Overview of the Pro*C/C++ Precompiler
  • Overview of the Pro*COBOL Precompiler
• Overview of OCI and OCCI
  • Advantages of OCI and OCCI
  • OCI and OCCI Functions
  • Procedural and Nonprocedural Elements of OCI and OCCI Applications
  • Building an OCI or OCCI Application
• Choosing a Precompiler or OCI
• Overview of Oracle Data Provider for .NET (ODP.NET)
• Overview of OraOLEDB
• Overview of Oracle Objects for OLE (OO4O)
  • OO4O Automation Server
  • OO4O Object Model
    • OraSession
    • OraServer
    • OraDatabase
    • OraDynaset
    • OraField
    • OraMetaData and OraMDAttribute
    • OraParameter and OraParameters
    • OraParamArray
    • OraSQLStmt
    • OraAQ
    • OraAQMsg
    • OraAQAgent
  • Support for Oracle LOB and Object Data Types
    • OraBLOB and OraCLOB
    • OraBFILE
  • Oracle Data Control
  • Oracle Objects for OLE C++ Class Library

14 Developing Applications with Multiple Programming Languages

• Overview of Multilanguage Programs
• What Is an External Procedure?
• Overview of Call Specification for External Procedures
• Loading External Procedures
  • Loading Java Class Methods
  • Loading External C Procedures
    • Define the C Procedures
    • Set Up the Environment
    • Identify the DLL
    • Publish the External Procedures
• Publishing External Procedures
  • AS LANGUAGE Clause for Java Class Methods
  • AS LANGUAGE Clause for External C Procedures
    • LIBRARY
    • NAME
    • LANGUAGE
    • CALLING STANDARD
    • WITH CONTEXT
    • PARAMETERS
    • AGENT IN
• Publishing Java Class Methods
• Publishing External C Procedures
• Locations of Call Specifications
  • Example: Locating a Call Specification in a PL/SQL Package
  • Example: Locating a Call Specification in a PL/SQL Package Body
  • Example: Locating a Call Specification in an ADT Specification
  • Example: Locating a Call Specification in an ADT Body
  • Example: Java with AUTHID
  • Example: C with Optional AUTHID
  • Example: Mixing Call Specifications in a Package
• Passing Parameters to External C Procedures with Call Specifications
  • Specifying Data Types
  • External Data Type Mappings
  • Passing Parameters BY VALUE or BY REFERENCE
  • Declaring Formal Parameters
  • Overriding Default Data Type Mapping
  • Specifying Properties
    • INDICATOR
    • LENGTH and MAXLEN
    • CHARSETID and CHARSETFORM
    • Repositioning Parameters
    • SELF
    • BY REFERENCE
    • WITH CONTEXT
    • Interlanguage Parameter Mode Mappings
• Running External Procedures with CALL Statements
  • Preconditions for External Procedures
    • Privileges of External Procedures
    • Managing Permissions
    • Creating Synonyms for External Procedures
  • CALL Statement Syntax
  • Calling Java Class Methods
  • Calling External C Procedures
• Handling Errors and Exceptions in Multilanguage Programs
• Using Service Routines with External C Procedures
  • OCIExtProcAllocCallMemory
  • OCIExtProcRaiseExcp
  • OCIExtProcRaiseExcpWithMsg
• Doing Callbacks with External C Procedures
  • OCIExtProcGetEnv
  • Object Support for OCI Callbacks
  • Restrictions on Callbacks
  • Debugging External Procedures
  • Example: Calling an External Procedure
  • Global Variables in External C Procedures
  • Static Variables in External C Procedures
  • Restrictions on External C Procedures

15 Developing Applications with Oracle XA

• X/Open Distributed Transaction Processing (DTP)
  • DTP Terminology
  • Required Public Information
• Oracle XA Library Subprograms
  • Oracle XA Library Subprograms
  • Oracle XA Interface Extensions
• Developing and Installing XA Applications
  • DBA or System Administrator Responsibilities
  • Application Developer Responsibilities
  • Defining the xa_open String
    • Syntax of the xa_open String
    • Required Fields for the xa_open String
    • Optional Fields for the xa_open String
  • Using Oracle XA with Precompilers
    • Using Precompilers with the Default Database
    • Using Precompilers with a Named Database
  • Using Oracle XA with OCI
  • Managing Transaction Control with Oracle XA
  • Examples of Precompiler Applications
  • Migrating Precompiler or OCI Applications to TPM Applications
  • Managing Oracle XA Library Thread Safety
    • Specifying Threading in the Open String
    • Restrictions on Threading in Oracle XA
  • Using the DBMS_XA Package
• Troubleshooting XA Applications
  • Accessing Oracle XA Trace Files
    • xa_open String DbgFl
    • Trace File Locations
  • Managing In-Doubt or Pending Oracle XA Transactions
  • Using SYS Account Tables to Monitor Oracle XA Transactions
• Oracle XA Issues and Restrictions
  • Using Database Links in Oracle XA Applications
  • Managing Transaction Branches in Oracle XA Applications
  • Using Oracle XA with Oracle Real Application Clusters (Oracle RAC)
    • GLOBAL_TXN_PROCESSES Initialization Parameter
    • Managing Transaction Branches on Oracle RAC
    • Managing Instance Recovery in Oracle RAC with DTP Services (10.2)
    • Global Uniqueness of XIDs in Oracle RAC
    • Tight and Loose Coupling
  • SQL-Based Oracle XA Restrictions
    • Rollbacks and Commits
    • DDL Statements
    • Session State
    • EXEC SQL
  • Miscellaneous Restrictions

16 Developing Applications with the Publish-Subscribe Model

• Introduction to the Publish-Subscribe Model
• Publish-Subscribe Architecture
  • Database Events
  • Oracle Advanced Queuing
  • Client Notification
• Publish-Subscribe Concepts
• Examples of a Publish-Subscribe Mechanism

17 Using the Identity Code Package

• Identity Concepts
• What is the Identity Code Package?
• Using the Identity Code Package
  • Storing RFID Tags in Oracle Database Using MGD_ID ADT
    • Creating a Table with MGD_ID Column Type and Storing EPC Tag Encodings in the Column
    • Constructing MGD_ID Objects to Represent RFID Tags
    • Inserting an MGD_ID Object into a Database Table
    • Querying MGD_ID Column Type
  • Building a Function-Based Index Using the Member Functions of the MGD_ID Column Type
  • Using MGD_ID ADT Functions
    • Using the get_component Function with the MGD_ID Object
    • Parsing Tag Data from Standard Representations
    • Reconstructing Tag Representations from Fields
    • Translating Between Tag Representations
  • Defining a Category of Identity Codes and Adding Encoding Schemes to an Existing Category
    • Creating a Category of Identity Codes
    • Adding Two Metadata Schemes to a Newly Created Category
• Identity Code Package Types
• DBMS_MGD_ID_UTL Package
• Identity Code Metadata Tables and Views
• Electronic Product Code (EPC) Concepts
  • RFID Technology and EPC v1.1 Coding Schemes
  • Product Code Concepts and Their Current Use
    • Electronic Product Code (EPC)
    • Global Trade Identification Number (GTIN) and Serializable Global Trade Identification Number (SGTIN)
    • Serial Shipping Container Code (SSCC)
    • Global Location Number (GLN) and Serializable Global Location Number (SGLN)
    • Global Returnable Asset Identifier (GRAI)
    • Global Individual Asset Identifier (GIAI)
    • RFID EPC Network
• Oracle Database Tag Data Translation Schema

18 Schema Object Dependency

• Overview of Schema Object Dependencies
• Querying Object Dependencies
• Object Status
• Invalidation of Dependent Objects
  • Session State and Referenced Packages
  • Security Authorization
• Guidelines for Reducing Invalidation
  • Add Items to End of Package
  • Reference Each Table Through a View
• Object Revalidation
• Name Resolution in Schema Scope
• Local Dependency Management
• Remote Dependency Management
  • Dependencies Among Local and Remote Database Procedures
  • Dependencies Among Other Remote Objects
  • Dependencies of Applications
• Remote Procedure Call (RPC) Dependency Management
  • Time-Stamp Dependency Mode
  • RPC-Signature Dependency Mode
    • Changing Names and Default Values of Parameters
    • Changing Specification of Parameter Mode IN
    • Changing Subprogram Body
    • Changing Data Type Classes of Parameters
    • Changing Packaged Types
  • Controlling Dependency Mode
    • Dependency Resolution
    • Suggestions for Managing Dependencies
• Shared SQL Dependency Management

19 Edition-Based Redefinition

• Editions
  • Editioned and Noneditioned Objects
    • Editionable and Noneditionable Schema Object Types
    • Rules for Editioned Objects
    • Enabling Editions for a User
  • Creating an Edition
  • Inherited and Actual Objects
    • Dropping Inherited Objects
    • Actualizing Referenced Objects
  • Making an Edition Available to Some Users
  • Making an Edition Available to All Users
  • Current Edition and Session Edition
    • Your Initial Session Edition and Current Edition
    • Changing Your Session Edition and Current Edition
    • Displaying the Names of the Current and Session Editions
    • When the Current Edition Might Differ from the Session Edition
  • Retiring an Edition
  • Dropping an Edition
• Editioning Views
  • Creating an Editioning View
  • Partition-Extended Editioning View Names
  • Changing the 'Write-ability' of an Editioning View
  • Replacing an Editioning View
  • Dropping or Renaming the Base Table
  • Adding Indexes and Constraints to the Base Table
  • SQL Optimizer Index Hints
• Crossedition Triggers
  • Forward Crossedition Triggers
  • Reverse Crossedition Triggers
  • Crossedition Trigger Interaction with Editions
    • Which Triggers Are Visible
    • What Kind of Triggers Can Fire
    • Firing Order
    • Crossedition Trigger Execution
  • Creating a Crossedition Trigger
    • Coding the Forward Crossedition Trigger Body
    • Coding the Reverse Crossedition Trigger Body
  • Transforming Data from Pre- to Post-Upgrade Representation
  • Dropping the Crossedition Triggers
• Displaying Information About Editions, Editioning Views, and Crossedition Triggers
• Using Edition-Based Redefinition to Upgrade an Application
  • Preparing Your Application to Use Editioning Views
  • Procedure for Edition-Based Redefinition Using Only Editions
  • Procedure for Edition-Based Redefinition Using Editioning Views
  • Procedure for Edition-Based Redefinition Using Crossedition Triggers
  • Rolling Back the Application Upgrade
  • Reclaiming Space Occupied by Unused Table Columns
  • Example: Using Edition-Based Redefinition to Upgrade an Application
    • Existing Application
    • Preparing the Application to Use Editioning Views
    • Using Edition-Based Redefinition to Upgrade the Application

A Multithreaded extproc Agent

• Why Use the Multithreaded extproc Agent?
  • The Challenge of Dedicated Agent Architecture
  • The Advantage of Multithreading
• Multithreaded extproc Agent Architecture
  • Monitor Thread
  • Dispatcher Threads
  • Task Threads
• Administering the Multithreaded extproc Agent
  • Agent Control Utility (agtctl) Commands
  • Using agtctl in Single-Line Command Mode
    • Setting Configuration Parameters for a Multithreaded extproc Agent
    • Starting a Multithreaded extproc Agent
    • Shutting Down a Multithreaded extproc Agent
    • Examining the Value of Configuration Parameters
    • Resetting a Configuration Parameter to Its Default Value
    • Deleting an Entry for a Specific SID from the Control File
    • Requesting Help
  • Using Shell Mode Commands
    • Example: Setting a Configuration Parameter
    • Example: Starting a Multithreaded extproc Agent
  • Configuration Parameters for Multithreaded extproc Agent Control

Index