1/38

Contents

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Changes in Oracle Database 12c Release 2 (12.2)

Part I Database Development Fundamentals

1 Design Basics

• Design for Performance
• Design for Scalability
• Design for Extensibility
  • Data Cartridges
  • External Procedures
  • User-Defined Functions and Aggregate Functions
  • Object-Relational Features
• Design for Security
• Design for Availability
• Design for Portability
• Design for Diagnosability
• Design for Special Environments
  • Data Warehousing
  • Online Transaction Processing (OLTP)
• Features for Special Scenarios
  • SQL Analytic Functions
  • Materialized Views
  • Partitioning
  • Temporal Validity Support

2 Connection Strategies for Database Applications

• Design Guidelines for Connection Pools
  • Connection Storms
  • Guideline for Preventing Connection Storms: Use Static Pools
• Design Guideline for Login Strategy
• Design Guideline for Preventing Programmatic Session Leaks
  • Drained Connection Pools
  • Checking for Session Leaks
  • Lock Leaks
  • Logical Corruption
• Using Runtime Connection Load Balancing
  • About Runtime Connection Load Balancing
  • Enabling and Disabling Runtime Connection Load Balancing
    • OCI
    • OCCI
    • JDBC
    • ODP.NET
  • Receiving Load Balancing Advisory FAN Events

3 Performance and Scalability

• Performance Strategies
  • Designing Your Data Model to Perform Well
    • Analyze the Data Requirements of the Application
    • Create the Database Design for the Application
      • Create the Logical Design
      • Create the Physical Design
    • Implement the Database Application
    • Maintain the Database and Database Application
  • Setting Performance Goals (Metrics)
  • Benchmarking Your Application
• Tools for Performance
  • DBMS_APPLICATION_INFO Package
  • SQL Trace Facility (SQL_TRACE)
  • EXPLAIN PLAN Statement
• Monitoring Database Performance
  • Automatic Database Diagnostic Monitor (ADDM)
  • Monitoring Real-Time Database Performance
  • Responding to Performance-Related Alerts
  • SQL Advisors and Memory Advisors
• Testing for Performance
• Using Client Result Cache
  • About Client Result Cache
  • Benefits of Client Result Cache
  • Guidelines for Using Client Result Cache
    • SQL Hints
    • Table Annotation
    • Session Parameter
    • Effective Table Result Cache Mode
    • Displaying Effective Table Result Cache Mode
    • Result Cache Mode Use Cases
    • Queries Never Result Cached in Client Result Cache
  • Client Result Cache Consistency
  • Deployment-Time Settings for Client Result Cache
    • Server Initialization Parameters
      • COMPATIBLE
      • CLIENT_RESULT_CACHE_SIZE
      • CLIENT_RESULT_CACHE_LAG
    • Client Configuration Parameters
  • Client Result Cache Statistics
  • Validation of Client Result Cache
    • Measure Execution Times
    • Query V$MYSTAT
    • Query V$SQLAREA
  • Client Result Cache and Server Result Cache
  • Client Result Cache Demo Files
  • Client Result Cache Compatibility with Previous Releases
  • Where to Find More Information About Client Result Cache
• Statement Caching
• OCI Client Statement Cache Auto-Tuning
• Client-Side Deployment Parameters
• Using Query Change Notification
• Using Database Resident Connection Pool
  • About Database Resident Connection Pool
  • Configuring DRCP
  • Sharing Proxy Sessions
  • Using JDBC with DRCP
  • Using OCI Session Pool APIs with DRCP
  • Session Purity and Connection Class
    • Session Purity
    • Connection Class
    • Example: Setting the Connection Class as HRMS
    • Example: Setting the Connection Class as RECMS
    • Session Purity and Connection Class Defaults
  • Starting Database Resident Connection Pool
  • Enabling DRCP
  • Benefiting from the Scalability of DRCP in an OCI Application
  • Benefiting from the Scalability of DRCP in a Java Application
  • Best Practices for Using DRCP
  • Compatibility and Migration
  • DRCP Restrictions
  • Using DRCP with Custom Pools
  • Explicitly Marking Sessions Stateful or Stateless
  • Using DRCP with Oracle Real Application Clusters
  • Using DRCP with Pluggable Databases
  • DRCP with Data Guard

4 Designing Applications for Oracle Real-World Performance

• Using Bind Variables
• Using Instrumentation
• Using Set-Based Processing
  • Iterative Data Processing
    • About Iterative Data Processing
    • Iterative Data Processing: Row-By-Row
    • Iterative Data Processing: Arrays
    • Iterative Data Processing: Manual Parallelism
  • Set-Based Processing

5 Security

• Enabling User Access with Grants, Roles, and Least Privilege
• Automating Database Logins
• Controlling User Access with Fine-Grained Access Control
• Using Invoker's and Definer's Rights for Procedures and Functions
  • What Are Invoker's Rights and Definer's Rights?
  • Protecting Users Who Run Invoker's Rights Procedures and Functions
  • How Default Rights Are Handled for Java Stored Procedures
• Managing External Procedures for Your Applications
• Auditing User Activity

6 High Availability

• Transparent Application Failover (TAF)
  • About Transparent Application Failover
  • Configuring Transparent Application Failover
  • Using Transparent Application Failover Callbacks
• Fast Application Notification (FAN) and Fast Connection Failover (FCF)
  • About Fast Application Notification (FAN)
  • About Receiving FAN Event Notifications
  • About Fast Connection Failover (FCF)
• Application Continuity and Transaction Guard
  • Overview of Application Continuity
  • Overview of Transaction Guard
• Service and Load Management for Database Clouds
  • About Service and Load Management for Database Clouds

7 Advanced PL/SQL Features

• PL/SQL Data Types
• Dynamic SQL
• PL/SQL Optimize Level
• Compiling PL/SQL Units for Native Execution
• Exception Handling
• Conditional Compilation
• Bulk Binding

Part II SQL for Application Developers

8 SQL Processing for Application Developers

• Description of SQL Statement Processing
  • Stages of SQL Statement Processing
  • Shared SQL Areas
• Grouping Operations into Transactions
  • Deciding How to Group Operations in Transactions
  • Improving Transaction Performance
  • Managing Commit Redo Action
  • Determining Transaction Outcome After a Recoverable Outage
    • Understanding Transaction Guard
      • How Transaction Guard Uses the LTXID
    • Understanding DBMS_APP_CONT.GET_LTXID_OUTCOME
      • CLIENT_LTXID Parameter
      • COMMITTED Parameter
      • USER_CALL_COMPLETED Parameter
      • Exceptions
    • Using Transaction Guard
• Ensuring Repeatable Reads with Read-Only Transactions
• 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
  • Transaction Interaction and Isolation Level
  • Setting Isolation Levels
  • Serializable Transactions and Referential Integrity
  • READ COMMITTED and SERIALIZABLE Isolation Levels
    • Transaction Set Consistency Differences
      • Oracle Database
      • Other Database Systems
    • Choosing Transaction Isolation Levels
• Nonblocking and Blocking DDL Statements
• Autonomous Transactions
  • Examples of Autonomous Transactions
    • Ordering a Product
    • Withdrawing Money from a Bank Account
      • Scenario 1: Sufficient Funds
      • Scenario 2: Insufficient Funds with Overdraft Protection
      • Scenario 3: Insufficient Funds Without Overdraft Protection
  • Declaring Autonomous Routines
• Resuming Execution After Storage Allocation Errors
  • What Operations Have Resumable Storage Allocation?
  • Handling Suspended Storage Allocation
    • Using an AFTER SUSPEND Trigger in the Application
    • Checking for Suspended Statements

9 Using SQL Data Types in Database Applications

• Using the Correct and Most Specific Data Type
  • How the Correct Data Type Increases Data Integrity
  • How the Most Specific Data Type Decreases Storage Requirements
  • How the Correct Data Type Improves Performance
• Representing Character Data
• Representing Numeric Data
  • Floating-Point Number Components
  • Floating-Point Number Formats
    • Binary Floating-Point Formats
  • Representing Special Values with Native Floating-Point Data Types
  • Comparing Native Floating-Point Values
  • Arithmetic Operations with Native Floating-Point Data Types
  • Conversion Functions for Native Floating-Point Data Types
  • Client Interfaces for Native Floating-Point Data Types
• Representing Date and Time Data
  • Displaying Current Date and Time
  • Inserting and Displaying Dates
  • Inserting and Displaying Times
  • Arithmetic Operations with Datetime Data Types
  • Conversion Functions for Datetime Data Types
  • Importing, Exporting, and Comparing Datetime Types
• Representing Specialized Data
  • Representing Spatial Data
  • Representing Multimedia Data
  • Representing Large Amounts of Data
    • Large Objects (LOBs)
    • LONG and LONG RAW Data Types
  • Representing Searchable Text
  • Representing XML Data
  • Representing Dynamically Typed Data
  • Representing ANSI, DB2, and SQL/DS Data
• Identifying Rows by Address
• Displaying Metadata for SQL Operators and Functions
  • ARGn Data Type
  • DISP_TYPE Data Type
  • SQL Data Type Families

10 Using Regular Expressions in Database Applications

• Overview of Regular Expressions
• Oracle SQL Support for Regular Expressions
• Oracle SQL and POSIX Regular Expression Standard
• Operators in Oracle SQL Regular Expressions
  • POSIX Operators in Oracle SQL Regular Expressions
  • Oracle SQL Multilingual Extensions to POSIX Standard
  • Oracle SQL PERL-Influenced Extensions to POSIX Standard
• Using Regular Expressions in SQL Statements: Scenarios
  • Using a Constraint to Enforce a Phone Number Format
  • Example: Enforcing a Phone Number Format with Regular Expressions
  • Example: Inserting Phone Numbers in Correct and Incorrect Formats
  • Using Back References to Reposition Characters

11 Using Indexes in Database Applications

• Guidelines for Managing Indexes
• Managing Indexes
• When to Use Domain Indexes
• When to Use Function-Based Indexes
  • Advantages of Function-Based Indexes
  • Disadvantages of Function-Based Indexes
  • Example: Function-Based Index for Precomputing Arithmetic Expression
  • Example: Function-Based Indexes on Object Column
  • Example: Function-Based Index for Faster Case-Insensitive Searches
  • Example: Function-Based Index for Language-Dependent Sorting

12 Maintaining Data Integrity in Database Applications

• Enforcing Business Rules with Constraints
• Enforcing Business Rules with Both Constraints and Application Code
• Creating Indexes for Use with Constraints
• When to Use NOT NULL Constraints
• When to Use Default Column Values
• Choosing a Primary Key for a Table (PRIMARY KEY Constraint)
• When to Use UNIQUE Constraints
• Enforcing Referential Integrity with FOREIGN KEY 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 III PL/SQL for Application Developers

13 Coding PL/SQL Subprograms and Packages

• Overview of PL/SQL Subprograms
• Overview of PL/SQL Packages
• Overview of PL/SQL Units
  • PLSQL_OPTIMIZE_LEVEL Compilation Parameter
• Creating PL/SQL Subprograms and Packages
  • Privileges Needed to Create Subprograms and Packages
  • Creating Subprograms and Packages
  • PL/SQL Object Size Limits
  • PL/SQL Data Types
    • PL/SQL Scalar Data Types
      • SQL Data Types
      • BOOLEAN Data Type
      • PLS_INTEGER and BINARY_INTEGER Data Types
      • REF CURSOR Data Type
      • User-Defined PL/SQL Subtypes
    • PL/SQL Composite Data Types
    • Abstract Data Types
  • Returning Result Sets to Clients
    • Advantages of Cursor Variables
    • Disadvantages of Cursor Variables
      • Parsing Penalty for Cursor Variable
      • Multiple-Row-Fetching Penalty for Cursor Variable
    • Returning Query Results Implicitly
  • Returning Large Amounts of Data from a Function
  • PL/SQL Function Result Cache
  • Overview of Bulk Binding
    • DML Statements that Reference Collections
    • SELECT Statements that Reference Collections
    • FOR Loops that Reference Collections and Return DML
  • PL/SQL Dynamic SQL
• Altering PL/SQL Subprograms and Packages
• Deprecating Packages, Subprograms, and Types
• Dropping PL/SQL Subprograms and Packages
• Compiling PL/SQL Units for Native Execution
• Invoking Stored PL/SQL Subprograms
  • Privileges Required to Invoke a Stored Subprogram
  • Invoking a Subprogram Interactively from Oracle Tools
  • Invoking a Subprogram from Another Subprogram
  • Invoking a Remote Subprogram
    • Synonyms for Remote Subprograms
    • Transactions That Invoke Remote Subprograms
• Invoking Stored PL/SQL Functions from SQL Statements
  • Why Invoke PL/SQL Functions from SQL Statements?
  • Where PL/SQL Functions Can Appear in SQL Statements
  • When PL/SQL Functions Can Appear in SQL Expressions
  • Controlling Side Effects of PL/SQL Functions Invoked from SQL Statements
    • Restrictions on Functions Invoked from SQL Statements
    • PL/SQL Functions Invoked from Parallelized SQL Statements
    • PRAGMA RESTRICT_REFERENCES
      • Example: PRAGMA RESTRICT_REFERENCES
      • Specifying the Assertion TRUST
      • Differences Between Static and Dynamic SQL Statements
• Analyzing and Debugging Stored Subprograms
  • PL/Scope
  • PL/SQL Hierarchical Profiler
  • Debugging PL/SQL and Java
    • Compiling Code for Debugging
    • Privileges for Debugging PL/SQL and Java Stored Subprograms
• Package Invalidations and Session State
• Example: Raising an ORA-04068 Error
• Example: Trapping ORA-04068

14 Using PL/Scope

• Overview of PL/Scope
• Privileges Required for Using PL/Scope
• Specifying Identifier and Statement Collection
• How Much Space is PL/Scope Data Using?
• Viewing PL/Scope Data
  • Static Data Dictionary Views for PL/SQL and SQL Identifiers
    • PL/SQL and SQL Identifier Types that PL/Scope Collects
    • About Identifiers Usages
    • Identifiers Usage Unique Keys
    • About Identifiers Usage Context
    • About Identifiers Signature
  • Static Data Dictionary Views for SQL Statements
    • SQL Statement Types that PL/Scope Collects
    • Statements Location Unique Keys
    • About SQL Statement Usage Context
    • About SQL Statements Signature
  • SQL Developer
• Overview of Data Dictionary Views Useful to Manage PL/SQL Code
• Sample PL/Scope Session

15 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
    • Understanding PL/SQL Hierarchical Profiler SQL-Level Reports
  • 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

16 Using PL/SQL Basic Block Coverage to Maintain Quality

• Overview of PL/SQL Basic Block Coverage
• Collecting PL/SQL Code Coverage Data
• PL/SQL Code Coverage Tables Description

17 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
      • Configuring Static Authentication with DBMS_EPG
      • Configuring Dynamic Authentication with DBMS_EPG
      • Configuring Anonymous Authentication with DBMS_EPG
      • Determining the Authentication Mode of a DAD
      • Examples: Creating and Configuring DADs
      • Example: Determining the Authentication Mode for a DAD
      • Example: Determining the Authentication Mode for All DADs
      • Example: Showing DAD Authorizations that Are Not in Effect
      • Examining Embedded PL/SQL Gateway Configuration
  • 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
  • Internet Protocol Version 6 (IPv6) Support
  • 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

18 Using Continuous Query Notification (CQN)

• About Object Change Notification (OCN)
• About Query Result Change Notification (QRCN)
  • Guaranteed Mode
  • Best-Effort Mode
    • Example: Query Too Complex for QRCN in Guaranteed Mode
    • Example: Query Whose Simplified Version Invalidates Objects
• Events that Generate Notifications
  • Committed DML Transactions
  • Committed DDL Statements
  • Deregistration
  • Global Events
• Notification Contents
• Good Candidates for CQN
• Creating CQN Registrations
• Using PL/SQL to Create 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
  • Deleting Registrations
  • Configuring CQN: Scenario
    • Creating a PL/SQL Notification Handler
    • Registering the Queries
• Using OCI to Create CQN Registrations
  • Using OCI for Query Result Set Notifications
  • Using OCI to Register a Continuous Query Notification
  • Using OCI Subscription Handle Attributes for Continuous Query Notification
  • OCI_ATTR_CQ_QUERYID Attribute
  • Using OCI Continuous Query Notification Descriptors
    • OCI_DTYPE_CHDES
      • OCI_DTYPE_CQDES
      • OCI_DTYPE_TABLE_CHDES
  • Demonstrating Continuous Query Notification in an OCI Sample Program
• 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

Part IV Advanced Topics for Application Developers

19 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: 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 DBMS_FLASHBACK Package
• Using Flashback Transaction
  • Dependent Transactions
  • TRANSACTION_BACKOUT Parameters
  • TRANSACTION_BACKOUT Reports
    • *_FLASHBACK_TXN_STATE
    • *_FLASHBACK_TXN_REPORT
• Using Flashback Data Archive
  • 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
• Multitenant Container Database Restrictions for Oracle Flashback Technology

20 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
      • JDBC Thin Driver
      • JDBC OCI Driver
      • JDBC Server-Side Internal Driver
    • Sample JDBC 2.0 Program
    • Sample Pre-2.0 JDBC Program
  • Overview of Oracle SQLJ
    • Benefits of SQLJ
  • Comparison of Oracle JDBC and Oracle SQLJ
  • 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
• Comparison of Precompilers and OCI
• Overview of Oracle Data Provider for .NET (ODP.NET)
• Overview of OraOLEDB

21 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
  • 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 C Procedures
  • Example: Calling an External C Procedure
  • Global Variables in External C Procedures
  • Static Variables in External C Procedures
  • Restrictions on External C Procedures

22 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

23 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

24 Using the Oracle ODBC Driver

• About Oracle ODBC Driver
• For All Users
  • Oracle ODBC Driver
    • What Is the Oracle ODBC Driver
    • New and Changed Features
    • Features Not Supported
    • Files Created by the Installation
    • Driver Conformance Levels
    • Known Limitations
  • Configuration Tasks
    • Configuring Oracle Net Services
    • Configuring the Data Source
    • Oracle ODBC Driver Configuration Dialog Box
  • Modifying the oraodbc.ini File
    • Reducing Lock Timeout
  • Connecting to a Data Source
    • Connecting to an Oracle Data Source
  • Troubleshooting
    • About Using the Oracle ODBC Driver for the First Time
    • Expired Password
• For Advanced Users
  • Creating Oracle ODBC Driver TNS Service Names
  • SQL Statements
  • Data Types
  • Implementation of Data Types (Advanced)
  • Limitations on Data Types
  • Error Messages
• For Programmers
  • Format of the Connection String
  • SQLDriverConnect Implementation
  • Reducing Lock Timeout in a Program
  • Linking with odbc32.lib (Windows) or libodbc.so (UNIX)
  • Information About rowids
  • Rowids in a WHERE Clause
  • Enabling Result Sets
  • Enabling EXEC Syntax
  • Enabling Event Notification for Connection Failures in an Oracle RAC Environment
  • Using Implicit Results Feature Through ODBC
  • About Supporting Oracle TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE Column Type in ODBC
  • About the Effect of Setting ORA_SDTZ in Oracle Clients (OCI, SQL*Plus, Oracle ODBC driver, and Others)
  • Supported Functionality
    • API Conformance
    • Implementation of ODBC API Functions
    • Implementation of the ODBC SQL Syntax
    • Implementation of Data Types (Programming)
  • Unicode Support
    • Unicode Support Within the ODBC Environment
    • Unicode Support in ODBC API
    • Unicode Functions in the Driver Manager
    • SQLGetData Performance
    • Unicode Samples
  • Performance and Tuning
    • General ODBC Programming Tips
    • Data Source Configuration Options
    • DATE and TIMESTAMP Data Types

25 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
      • Constructing an MGD_ID Object (SGTIN-64) Passing in the Category ID and a List of Components
      • Constructing an MGD_ID object (SGTIN-64) and Passing in the Category ID, the Tag Identifier, and the List of Additional Required Parameters
      • Constructing an MGD_ID object (SGTIN-64) and Passing in the Category Name, Category Version (if null, then the latest version is used), and a List of Components
      • Constructing an MGD_ID object (SGTIN-64) and Passing in the Category Name and Category Version, the Tag Identifier, and the List of Additional Required Parameters
    • 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)
      • EPC Pure Identity
      • EPC Encoding
      • EPC Tag Bit-Level Encoding
      • EPC Identity URI
      • EPC Tag URI Encoding
      • EPC Encoding Procedure
      • EPC Decoding Procedure
    • 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

26 Understanding Schema Object Dependency

• Overview of Schema Object Dependency
  • Example: Displaying Dependent and Referenced Object Types
  • Example: Schema Object Change that Invalidates Some Dependents
  • Example: View That Depends on Multiple Objects
• 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
  • Revalidation of Objects that Compiled with Errors
  • Revalidation of Unauthorized Objects
  • Revalidation of Invalid SQL Objects
  • Revalidation of Invalid PL/SQL Objects
• 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 Package Types
  • Controlling Dependency Mode
    • Dependency Resolution
    • Suggestions for Managing Dependencies
• Shared SQL Dependency Management

27 Using Edition-Based Redefinition

• Overview of Edition-Based Redefinition
• Editions
  • Editioned and Noneditioned Objects
    • Name Resolution for Editioned and Noneditioned Objects
    • Noneditioned Objects That Can Depend on Editioned Objects
      • Materialized Views
      • Virtual Columns
    • Editionable and Noneditionable Schema Object Types
    • Enabling Editions for a User
      • Potentially Editioned Objects with Noneditioned Dependents
      • Users Who Cannot Have Editions Enabled
    • EDITIONABLE and NONEDITIONABLE Properties
      • Creating New EDITIONABLE and NONEDITIONABLE Objects
      • Replacing or Altering EDITIONABLE and NONEDITIONABLE Objects
    • Rules for Editioned Objects
  • Creating an Edition
  • Editioned Objects and Copy-on-Change
    • Example: Editioned Objects and Copy-on-Change
    • Example: Dropping an Editioned Object
    • Example: Creating an Object with the Name of a Dropped Inherited Object
  • Making an Edition Available to Some Users
  • Making an Edition Available to All Users
  • Current Edition and Session Edition
    • Your Initial Session Edition
    • Changing Your Session 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 Writability of an Editioning View
  • Replacing an Editioning View
  • Dropped or Renamed Base Tables
  • 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
      • Forward Crossedition Trigger SQL
      • Reverse Crossedition Trigger SQL
      • Application SQL
    • Firing Order
      • FOLLOWS and PRECEDES Clauses
      • Trigger Type and Edition
    • Crossedition Trigger Execution
  • Creating a Crossedition Trigger
    • Coding the Forward Crossedition Trigger Body
      • Handling Data Transformation Collisions
      • Handling Changes to Other Tables
  • Transforming Data from Pre- to Post-Upgrade Representation
    • Preventing Lost Updates
  • Dropping the Crossedition Triggers
• Displaying Information About EBR Features
  • Displaying Information About Editions
  • Displaying Information About Editioning Views
  • Displaying Information About Crossedition Triggers
• Using EBR to Upgrade an Application
  • Preparing Your Application to Use Editioning Views
  • Procedure for EBR Using Only Editions
  • Procedure for EBR Using Editioning Views
  • Procedure for EBR Using Crossedition Triggers
  • Rolling Back the Application Upgrade
  • Reclaiming Space Occupied by Unused Table Columns
  • Example: Using EBR to Upgrade an Application
    • Existing Application
      • Example: How the Existing Application Was Created
      • Example: Viewing Data in the Existing Table
    • Preparing the Application to Use Editioning Views
    • Using EBR to Upgrade the Example Application
      • Example: Creating an Edition in Which to Upgrade the Example Application
      • Example: Changing the Table and Replacing the Editioning View
      • Example: Creating and Enabling the Crossedition Triggers
      • Example: Applying the Transforms
      • Example: Viewing Data in the Changed Table

28 Using Transaction Guard

• Problem That Transaction Guard Solves
• Solution That Transaction Guard Provides
• Transaction Guard Concepts and Scope
  • Logical Transaction Identifier (LTXID)
  • At-Most-Once Execution
  • Transaction Guard Coverage
  • Transaction Guard with XA Transactions
  • Transaction Guard Exclusions
• Database Configuration for Transaction Guard
  • Configuration Checklist
  • Transaction History Table
  • Service Parameters
    • Example: Adding and Modifying a Service for a Server Pool
    • Example: Adding an Administrator-Managed Service
    • Example: Modifying a Service (PL/SQL)
• Developing Applications That Use Transaction Guard
  • Typical Transaction Guard Usage
  • Details for Using the LTXID
  • Transaction Guard and Transparent Application Failover
  • Using Transaction Guard with ODP.NET
  • Connection-Pool LTXID Usage
  • Improved Commit Outcome for XA One Phase Optimizations
  • Additional Requirements for Transaction Guard Development
• Transaction Guard and Its Relationship to Application Continuity

Index