1/38
Contents
List of Examples
List of Figures
List of Tables
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
Changes in This Release for Oracle Database Development Guide
Changes in Oracle Database 12
c
Release 1 (12.1.0.2)
Changes in Oracle Database 12
c
Release 1 (12.1.0.1)
Part I Database Development Fundamentals
1
Design Basics
1.1
Design for Performance
1.2
Design for Scalability
1.3
Design for Extensibility
1.3.1
Data Cartridges
1.3.2
External Procedures
1.3.3
User-Defined Functions and Aggregate Functions
1.3.4
Object-Relational Features
1.4
Design for Security
1.5
Design for Availability
1.6
Design for Portability
1.7
Design for Diagnosability
1.8
Design for Special Environments
1.8.1
Data Warehousing
1.8.2
Online Transaction Processing (OLTP)
1.9
Features for Special Scenarios
1.9.1
SQL Analytic Functions
1.9.2
Materialized Views
1.9.3
Partitioning
1.9.4
Temporal Validity Support
2
Performance and Scalability
2.1
Performance Strategies
2.1.1
Designing Your Data Model to Perform Well
2.1.1.1
Analyze the Data Requirements of the Application
2.1.1.2
Create the Database Design for the Application
2.1.1.3
Implement the Database Application
2.1.1.4
Maintain the Database and Database Application
2.1.2
Setting Performance Goals (Metrics)
2.1.3
Benchmarking Your Application
2.2
Tools for Performance
2.2.1
DBMS_APPLICATION_INFO Package
2.2.2
SQL Trace Facility (SQL_TRACE)
2.2.3
EXPLAIN PLAN Statement
2.3
Monitoring Database Performance
2.3.1
Automatic Database Diagnostic Monitor (ADDM)
2.3.2
Monitoring Real-Time Database Performance
2.3.3
Responding to Performance-Related Alerts
2.3.4
SQL Advisors and Memory Advisors
2.4
Using Instrumentation
2.5
Testing for Performance
2.6
Using Bind Variables
2.7
Using Client Result Cache
2.7.1
About Client Result Cache
2.7.2
Benefits of Client Result Cache
2.7.3
Guidelines for Using Client Result Cache
2.7.3.1
SQL Hints
2.7.3.2
Table Annotation
2.7.3.3
Session Parameter
2.7.3.4
Effective Table Result Cache Mode
2.7.3.5
Displaying Effective Table Result Cache Mode
2.7.3.6
Result Cache Mode Use Cases
2.7.3.7
Queries Never Result Cached in Client Result Cache
2.7.4
Client Result Cache Consistency
2.7.5
Deployment-Time Settings for Client Result Cache
2.7.5.1
Server Initialization Parameters
2.7.5.2
Client Configuration Parameters
2.7.6
Client Result Cache Statistics
2.7.7
Validation of Client Result Cache
2.7.7.1
Measure Execution Times
2.7.7.2
Query V$MYSTAT
2.7.7.3
Query V$SQLAREA
2.7.8
Client Result Cache and Server Result Cache
2.7.9
Client Result Cache Demo Files
2.7.10
Client Result Cache Compatibility with Previous Releases
2.7.11
Where to Find More Information About Client Result Cache
2.8
Statement Caching
2.9
OCI Client Statement Cache Auto-Tuning
2.10
Client-Side Deployment Parameters
2.11
Using Query Change Notification
2.12
Using Database Resident Connection Pool
2.12.1
About Database Resident Connection Pool
2.12.2
Configuring DRCP
2.12.3
Using JDBC with DRCP
2.12.4
Using OCI Session Pool APIs with DRCP
2.12.5
Session Purity and Connection Class
2.12.5.1
Session Purity
2.12.5.2
Connection Class
2.12.5.3
Session Purity and Connection Class Defaults
2.12.6
Starting Database Resident Connection Pool
2.12.7
Enabling DRCP
2.12.8
Benefiting from the Scalability of DRCP in an OCI Application
2.12.9
Benefiting from the Scalability of DRCP in a Java Application
2.12.10
Best Practices for Using DRCP
2.12.11
Compatibility and Migration
2.12.12
DRCP Restrictions
2.12.13
Using DRCP with Custom Pools
2.12.14
Explicitly Marking Sessions Stateful or Stateless
2.12.15
Using DRCP with Oracle Real Application Clusters
2.12.16
Using DRCP with Pluggable Databases
2.12.17
DRCP with Data Guard
2.13
Using Runtime Connection Load Balancing
2.13.1
About Runtime Connection Load Balancing
2.13.2
Enabling and Disabling Runtime Connection Load Balancing
2.13.2.1
OCI
2.13.2.2
OCCI
2.13.2.3
JDBC
2.13.2.4
ODP.NET
2.13.3
Receiving Load Balancing Advisory FAN Events
3
Security
3.1
Enabling User Access with Grants, Roles, and Least Privilege
3.2
Automating Database Logins
3.3
Controlling User Access with Fine-Grained Access Control
3.4
Using Invoker's and Definer's Rights for Procedures and Functions
3.4.1
What Are Invoker's Rights and Definer's Rights?
3.4.2
Protecting Users Who Run Invoker's Rights Procedures and Functions
3.4.3
How Default Rights Are Handled for Java Stored Procedures
3.5
Managing External Procedures for Your Applications
3.6
Auditing User Activity
4
High Availability
4.1
Failover and Query Replay
4.1.1
Transparent Application Failover (TAF)
4.1.1.1
About Transparent Application Failover
4.1.1.2
Configuring Transparent Application Failover
4.1.1.3
Using Transparent Application Failover Callbacks
4.1.2
Application Continuity
4.2
Fast Application Notification (FAN) and Fast Connection Failover (FCF)
4.2.1
About Fast Application Notification (FAN)
4.2.2
About Fast Connection Failover (FCF)
4.3
Application Continuity and Transaction Guard
4.3.1
Transaction Guard
4.3.2
Application Continuity for Java
4.4
Service and Load Management for Database Clouds
4.4.1
About Service and Load Management for Database Clouds
5
Advanced PL/SQL Features
5.1
PL/SQL Data Types
5.2
Dynamic SQL
5.3
PL/SQL Optimize Level
5.4
Compiling PL/SQL Units for Native Execution
5.5
Exception Handling
5.6
Conditional Compilation
5.7
Bulk Binding
Part II SQL for Application Developers
6
SQL Processing for Application Developers
6.1
Description of SQL Statement Processing
6.1.1
Stages of SQL Statement Processing
6.1.2
Shared SQL Areas
6.2
Grouping Operations into Transactions
6.2.1
Deciding How to Group Operations in Transactions
6.2.2
Improving Transaction Performance
6.2.3
Managing Commit Redo Action
6.2.4
Determining Transaction Outcome After a Recoverable Outage
6.2.4.1
Understanding Transaction Guard
6.2.4.2
Understanding DBMS_APP_CONT.GET_LTXID_OUTCOME
6.2.4.3
Using Transaction Guard
6.3
Ensuring Repeatable Reads with Read-Only Transactions
6.4
Locking Tables Explicitly
6.4.1
Privileges Required to Acquire Table Locks
6.4.2
Choosing a Locking Strategy
6.4.2.1
When to Lock with ROW SHARE MODE and ROW EXCLUSIVE MODE
6.4.2.2
When to Lock with SHARE MODE
6.4.2.3
When to Lock with SHARE ROW EXCLUSIVE MODE
6.4.2.4
When to Lock with EXCLUSIVE MODE
6.4.3
Letting Oracle Database Control Table Locking
6.4.4
Explicitly Acquiring Row Locks
6.4.5
Examples of Concurrency Under Explicit Locking
6.5
Using Oracle Lock Management Services (User Locks)
6.5.1
When to Use User Locks
6.5.2
Viewing and Monitoring Locks
6.6
Using Serializable Transactions for Concurrency Control
6.6.1
Transaction Interaction and Isolation Level
6.6.2
Setting Isolation Levels
6.6.3
Serializable Transactions and Referential Integrity
6.6.4
READ COMMITTED and SERIALIZABLE Isolation Levels
6.6.4.1
Transaction Set Consistency Differences
6.6.4.2
Choosing Transaction Isolation Levels
6.7
Nonblocking and Blocking DDL Statements
6.8
Autonomous Transactions
6.8.1
Examples of Autonomous Transactions
6.8.1.1
Ordering a Product
6.8.1.2
Withdrawing Money from a Bank Account
6.8.2
Declaring Autonomous Routines
6.9
Resuming Execution After Storage Allocation Errors
6.9.1
What Operations Have Resumable Storage Allocation?
6.9.2
Handling Suspended Storage Allocation
6.9.2.1
Using an AFTER SUSPEND Trigger in the Application
6.9.2.2
Checking for Suspended Statements
7
Using SQL Data Types in Database Applications
7.1
Using the Correct and Most Specific Data Type
7.1.1
How the Correct Data Type Increases Data Integrity
7.1.2
How the Most Specific Data Type Decreases Storage Requirements
7.1.3
How the Correct Data Type Improves Performance
7.2
Representing Character Data
7.3
Representing Numeric Data
7.3.1
Floating-Point Number Components
7.3.2
Floating-Point Number Formats
7.3.2.1
Binary Floating-Point Formats
7.3.3
Representing Special Values with Native Floating-Point Data Types
7.3.4
Comparing Native Floating-Point Values
7.3.5
Arithmetic Operations with Native Floating-Point Data Types
7.3.6
Conversion Functions for Native Floating-Point Data Types
7.3.7
Client Interfaces for Native Floating-Point Data Types
7.4
Representing Date and Time Data
7.4.1
Displaying Current Date and Time
7.4.2
Inserting and Displaying Dates
7.4.3
Inserting and Displaying Times
7.4.4
Arithmetic Operations with Datetime Data Types
7.4.5
Conversion Functions for Datetime Data Types
7.4.6
Importing, Exporting, and Comparing Datetime Types
7.5
Representing Specialized Data
7.5.1
Representing Spatial Data
7.5.2
Representing Multimedia Data
7.5.3
Representing Large Amounts of Data
7.5.3.1
Large Objects (LOBs)
7.5.3.2
LONG and LONG RAW Data Types
7.5.4
Representing Searchable Text
7.5.5
Representing XML Data
7.5.6
Representing Dynamically Typed Data
7.5.7
Representing ANSI, DB2, and SQL/DS Data
7.6
Identifying Rows by Address
7.7
Displaying Metadata for SQL Operators and Functions
7.7.1
ARGn Data Type
7.7.2
DISP_TYPE Data Type
7.7.3
SQL Data Type Families
8
Using Regular Expressions in Database Applications
8.1
Overview of Regular Expressions
8.2
Oracle SQL Support for Regular Expressions
8.3
Oracle SQL and POSIX Regular Expression Standard
8.4
Operators in Oracle SQL Regular Expressions
8.4.1
POSIX Operators in Oracle SQL Regular Expressions
8.4.2
Oracle SQL Multilingual Extensions to POSIX Standard
8.4.3
Oracle SQL PERL-Influenced Extensions to POSIX Standard
8.5
Using Regular Expressions in SQL Statements: Scenarios
8.5.1
Using a Constraint to Enforce a Phone Number Format
8.5.2
Using Back References to Reposition Characters
9
Using Indexes in Database Applications
9.1
Guidelines for Managing Indexes
9.2
Managing Indexes
9.3
When to Use Domain Indexes
9.4
When to Use Function-Based Indexes
9.4.1
Advantages of Function-Based Indexes
9.4.2
Disadvantages of Function-Based Indexes
9.4.3
Examples of Function-Based Indexes
10
Maintaining Data Integrity in Database Applications
10.1
Enforcing Business Rules with Constraints
10.2
Enforcing Business Rules with Both Constraints and Application Code
10.3
Creating Indexes for Use with Constraints
10.4
When to Use NOT NULL Constraints
10.5
When to Use Default Column Values
10.6
Choosing a Primary Key for a Table (PRIMARY KEY Constraint)
10.7
When to Use UNIQUE Constraints
10.8
Enforcing Referential Integrity with FOREIGN KEY Constraints
10.8.1
FOREIGN KEY Constraints and NULL Values
10.8.2
Defining Relationships Between Parent and Child Tables
10.8.3
Rules for Multiple FOREIGN KEY Constraints
10.8.4
Deferring Constraint Checks
10.9
Minimizing Space and Time Overhead for Indexes Associated with Constraints
10.10
Guidelines for Indexing Foreign Keys
10.11
Referential Integrity in a Distributed Database
10.12
When to Use CHECK Constraints
10.12.1
Restrictions on CHECK Constraints
10.12.2
Designing CHECK Constraints
10.12.3
Rules for Multiple CHECK Constraints
10.12.4
Choosing Between CHECK and NOT NULL Constraints
10.13
Examples of Defining Constraints
10.13.1
Privileges Needed to Define Constraints
10.13.2
Naming Constraints
10.14
Enabling and Disabling Constraints
10.14.1
Why Disable Constraints?
10.14.2
Creating Enabled Constraints (Default)
10.14.3
Creating Disabled Constraints
10.14.4
Enabling Existing Constraints
10.14.5
Disabling Existing Constraints
10.14.6
Guidelines for Enabling and Disabling Key Constraints
10.14.7
Fixing Constraint Exceptions
10.15
Modifying Constraints
10.16
Renaming Constraints
10.17
Dropping Constraints
10.18
Managing FOREIGN KEY Constraints
10.18.1
Data Types and Names for Foreign Key Columns
10.18.2
Limit on Columns in Composite Foreign Keys
10.18.3
Foreign Key References Primary Key by Default
10.18.4
Privileges Required to Create FOREIGN KEY Constraints
10.18.5
Choosing How Foreign Keys Enforce Referential Integrity
10.19
Viewing Information About Constraints
Part III PL/SQL for Application Developers
11
Coding PL/SQL Subprograms and Packages
11.1
Overview of PL/SQL Subprograms
11.2
Overview of PL/SQL Packages
11.3
Overview of PL/SQL Units
11.3.1
PLSQL_OPTIMIZE_LEVEL Compilation Parameter
11.4
Creating PL/SQL Subprograms and Packages
11.4.1
Privileges Needed to Create Subprograms and Packages
11.4.2
Creating Subprograms and Packages
11.4.3
PL/SQL Object Size Limits
11.4.4
PL/SQL Data Types
11.4.4.1
PL/SQL Scalar Data Types
11.4.4.2
PL/SQL Composite Data Types
11.4.4.3
Abstract Data Types
11.4.5
Returning Result Sets to Clients
11.4.5.1
Advantages of Cursor Variables
11.4.5.2
Disadvantages of Cursor Variables
11.4.5.3
Returning Query Results Implicitly
11.4.6
Returning Large Amounts of Data from a Function
11.4.7
PL/SQL Function Result Cache
11.4.8
Overview of Bulk Binding
11.4.8.1
DML Statements that Reference Collections
11.4.8.2
SELECT Statements that Reference Collections
11.4.8.3
FOR Loops that Reference Collections and Return DML
11.4.9
PL/SQL Dynamic SQL
11.5
Altering PL/SQL Subprograms and Packages
11.6
Dropping PL/SQL Subprograms and Packages
11.7
Compiling PL/SQL Units for Native Execution
11.8
Invoking Stored PL/SQL Subprograms
11.8.1
Privileges Required to Invoke a Stored Subprogram
11.8.2
Invoking a Subprogram Interactively from Oracle Tools
11.8.3
Invoking a Subprogram from Another Subprogram
11.8.4
Invoking a Remote Subprogram
11.8.4.1
Synonyms for Remote Subprograms
11.8.4.2
Transactions That Invoke Remote Subprograms
11.9
Invoking Stored PL/SQL Functions from SQL Statements
11.9.1
Why Invoke PL/SQL Functions from SQL Statements?
11.9.2
Where PL/SQL Functions Can Appear in SQL Statements
11.9.3
When PL/SQL Functions Can Appear in SQL Expressions
11.9.4
Controlling Side Effects of PL/SQL Functions Invoked from SQL Statements
11.9.4.1
Restrictions on Functions Invoked from SQL Statements
11.9.4.2
PL/SQL Functions Invoked from Parallelized SQL Statements
11.9.4.3
PRAGMA RESTRICT_REFERENCES
11.10
Debugging Stored Subprograms
11.10.1
PL/Scope
11.10.2
PL/SQL Hierarchical Profiler
11.10.3
Compiling Code for Debugging
11.10.4
Privileges for Debugging PL/SQL and Java Stored Subprograms
11.10.5
DBMS_DEBUG Package
11.11
Package Invalidations and Session State
12
Using PL/Scope
12.1
Specifying Identifier Collection
12.2
PL/Scope Identifier Data for STANDARD and DBMS_STANDARD
12.2.1
Do You Need STANDARD and DBMS_STANDARD Identifier Data?
12.2.2
Does Your Database Have STANDARD and DBMS_STANDARD Identifier Data?
12.2.3
Recompiling STANDARD and DBMS_STANDARD
12.2.4
Running utlrp.sql to Recompile Invalid PL/SQL Objects
12.3
How Much Space is PL/Scope Data Using?
12.4
Viewing PL/Scope Data
12.4.1
Static Data Dictionary Views
12.4.1.1
Unique Keys
12.4.1.2
Context
12.4.1.3
Signature
12.4.2
Demo Tool
12.4.3
SQL Developer
12.5
Identifier Types that PL/Scope Collects
12.6
Usages that PL/Scope Reports
12.7
Sample PL/Scope Session
13
Using the PL/SQL Hierarchical Profiler
13.1
Overview of PL/SQL Hierarchical Profiler
13.2
Collecting Profile Data
13.3
Understanding Raw Profiler Output
13.3.1
Namespaces of Tracked Subprograms
13.3.2
Special Function Names
13.4
Analyzing Profile Data
13.4.1
Creating Hierarchical Profiler Tables
13.4.2
Understanding Hierarchical Profiler Tables
13.4.2.1
Hierarchical Profiler Database Table Columns
13.4.2.2
Distinguishing Between Overloaded Subprograms
13.4.2.3
Hierarchical Profiler Tables for Sample PL/SQL Procedure
13.4.2.4
Examples of Calls to DBMS_HPROF.analyze with Options
13.5
plshprof Utility
13.5.1
plshprof Options
13.5.2
HTML Report from a Single Raw Profiler Output File
13.5.2.1
First Page of Report
13.5.2.2
Function-Level Reports
13.5.2.3
Module-Level Reports
13.5.2.4
Namespace-Level Reports
13.5.2.5
Parents and Children Report for a Function
13.5.3
HTML Difference Report from Two Raw Profiler Output Files
13.5.3.1
Difference Report Conventions
13.5.3.2
First Page of Difference Report
13.5.3.3
Function-Level Difference Reports
13.5.3.4
Module-Level Difference Reports
13.5.3.5
Namespace-Level Difference Reports
13.5.3.6
Parents and Children Difference Report for a Function
14
Developing PL/SQL Web Applications
14.1
Overview of PL/SQL Web Applications
14.2
Implementing PL/SQL Web Applications
14.2.1
PL/SQL Gateway
14.2.1.1
mod_plsql
14.2.1.2
Embedded PL/SQL Gateway
14.2.2
PL/SQL Web Toolkit
14.3
Using mod_plsql Gateway to Map Client Requests to a PL/SQL Web Application
14.4
Using Embedded PL/SQL Gateway
14.4.1
How Embedded PL/SQL Gateway Processes Client Requests
14.4.2
Installing Embedded PL/SQL Gateway
14.4.3
Configuring Embedded PL/SQL Gateway
14.4.3.1
Configuring Embedded PL/SQL Gateway: Overview
14.4.3.2
Configuring User Authentication for Embedded PL/SQL Gateway
14.4.4
Invoking PL/SQL Stored Subprograms Through Embedded PL/SQL Gateway
14.4.5
Securing Application Access with Embedded PL/SQL Gateway
14.4.6
Restrictions in Embedded PL/SQL Gateway
14.4.7
Using Embedded PL/SQL Gateway: Scenario
14.5
Generating HTML Output with PL/SQL
14.6
Passing Parameters to PL/SQL Web Applications
14.6.1
Passing List and Dropdown-List Parameters from an HTML Form
14.6.2
Passing Option and Check Box Parameters from an HTML Form
14.6.3
Passing Entry-Field Parameters from an HTML Form
14.6.4
Passing Hidden Parameters from an HTML Form
14.6.5
Uploading a File from an HTML Form
14.6.6
Submitting a Completed HTML Form
14.6.7
Handling Missing Input from an HTML Form
14.6.8
Maintaining State Information Between Web Pages
14.7
Performing Network Operations in PL/SQL Subprograms
14.7.1
Sending E-Mail from PL/SQL
14.7.2
Getting a Host Name or Address from PL/SQL
14.7.3
Using TCP/IP Connections from PL/SQL
14.7.4
Retrieving HTTP URL Contents from PL/SQL
14.7.5
Using Tables, Image Maps, Cookies, and CGI Variables from PL/SQL
15
Using Continuous Query Notification (CQN)
15.1
About Object Change Notification (OCN)
15.2
About Query Result Change Notification (QRCN)
15.2.1
Guaranteed Mode
15.2.2
Best-Effort Mode
15.3
Events that Generate Notifications
15.3.1
Committed DML Transactions
15.3.2
Committed DDL Statements
15.3.3
Deregistration
15.3.4
Global Events
15.4
Notification Contents
15.5
Good Candidates for CQN
15.6
Creating CQN Registrations
15.7
Using PL/SQL to Create CQN Registrations
15.7.1
PL/SQL CQN Registration Interface
15.7.2
CQN Registration Options
15.7.2.1
Notification Type Option
15.7.2.2
QRCN Mode (QRCN Notification Type Only)
15.7.2.3
ROWID Option
15.7.2.4
Operations Filter Option (OCN Notification Type Only)
15.7.2.5
Transaction Lag Option (OCN Notification Type Only)
15.7.2.6
Notification Grouping Options
15.7.2.7
Reliable Option
15.7.2.8
Purge-on-Notify and Timeout Options
15.7.3
Prerequisites for Creating CQN Registrations
15.7.4
Queries that Can Be Registered for Object Change Notification (OCN)
15.7.5
Queries that Can Be Registered for Query Result Change Notification (QRCN)
15.7.5.1
Queries that Can Be Registered for QRCN in Guaranteed Mode
15.7.5.2
Queries that Can Be Registered for QRCN Only in Best-Effort Mode
15.7.5.3
Queries that Cannot Be Registered for QRCN in Either Mode
15.7.6
Using PL/SQL to Register Queries for CQN
15.7.6.1
Creating a PL/SQL Notification Handler
15.7.6.2
Creating a CQ_NOTIFICATION$_REG_INFO Object
15.7.6.3
Identifying Individual Queries in a Notification
15.7.6.4
Adding Queries to an Existing Registration
15.7.7
Best Practices for CQN Registrations
15.7.8
Troubleshooting CQN Registrations
15.7.9
Deleting Registrations
15.7.10
Configuring CQN: Scenario
15.7.10.1
Creating a PL/SQL Notification Handler
15.7.10.2
Registering the Queries
15.8
Using OCI to Create CQN Registrations
15.8.1
Using OCI for Query Result Set Notifications
15.8.2
Using OCI to Register a Continuous Query Notification
15.8.3
Using OCI Subscription Handle Attributes for Continuous Query Notification
15.8.4
Using OCI_ATTR_CQ_QUERYID Attribute
15.8.5
Using OCI Continuous Query Notification Descriptors
15.8.5.1
OCI_DTYPE_CHDES
15.8.6
Demonstrating Continuous Query Notification in an OCI Sample Program
15.9
Querying CQN Registrations
15.10
Interpreting Notifications
15.10.1
Interpreting a CQ_NOTIFICATION$_DESCRIPTOR Object
15.10.2
Interpreting a CQ_NOTIFICATION$_TABLE Object
15.10.3
Interpreting a CQ_NOTIFICATION$_QUERY Object
15.10.4
Interpreting a CQ_NOTIFICATION$_ROW Object
Part IV Advanced Topics for Application Developers
16
Using Oracle Flashback Technology
16.1
Overview of Oracle Flashback Technology
16.1.1
Application Development Features
16.1.2
Database Administration Features
16.2
Configuring Your Database for Oracle Flashback Technology
16.2.1
Configuring Your Database for Automatic Undo Management
16.2.2
Configuring Your Database for Oracle Flashback Transaction Query
16.2.3
Configuring Your Database for Flashback Transaction
16.2.4
Enabling Oracle Flashback Operations on Specific LOB Columns
16.2.5
Granting Necessary Privileges
16.3
Using Oracle Flashback Query (SELECT AS OF)
16.3.1
Example of Examining and Restoring Past Data
16.3.2
Guidelines for Oracle Flashback Query
16.4
Using Oracle Flashback Version Query
16.5
Using Oracle Flashback Transaction Query
16.6
Using Oracle Flashback Transaction Query with Oracle Flashback Version Query
16.7
Using DBMS_FLASHBACK Package
16.8
Using Flashback Transaction
16.8.1
Dependent Transactions
16.8.2
TRANSACTION_BACKOUT Parameters
16.8.3
TRANSACTION_BACKOUT Reports
16.8.3.1
*_FLASHBACK_TXN_STATE
16.8.3.2
*_FLASHBACK_TXN_REPORT
16.9
Using Flashback Data Archive
16.9.1
Creating a Flashback Data Archive
16.9.2
Altering a Flashback Data Archive
16.9.3
Dropping a Flashback Data Archive
16.9.4
Specifying the Default Flashback Data Archive
16.9.5
Enabling and Disabling Flashback Data Archive
16.9.6
DDL Statements on Tables Enabled for Flashback Data Archive
16.9.7
Viewing Flashback Data Archive Data
16.9.8
Flashback Data Archive Scenarios
16.9.8.1
Scenario: Using Flashback Data Archive to Enforce Digital Shredding
16.9.8.2
Scenario: Using Flashback Data Archive to Access Historical Data
16.9.8.3
Scenario: Using Flashback Data Archive to Generate Reports
16.9.8.4
Scenario: Using Flashback Data Archive for Auditing
16.9.8.5
Scenario: Using Flashback Data Archive to Recover Data
16.10
General Guidelines for Oracle Flashback Technology
16.11
Performance Guidelines for Oracle Flashback Technology
16.12
Multitenant Container Database Restrictions for Oracle Flashback Technology
17
Choosing a Programming Environment
17.1
Overview of Application Architecture
17.1.1
Client/Server Architecture
17.1.2
Server-Side Programming
17.1.3
Two-Tier and Three-Tier Architecture
17.2
Overview of the Program Interface
17.2.1
User Interface
17.2.2
Stateful and Stateless User Interfaces
17.3
Overview of PL/SQL
17.4
Overview of Oracle Database Java Support
17.4.1
Overview of Oracle JVM
17.4.2
Overview of Oracle JDBC
17.4.2.1
Oracle JDBC Drivers
17.4.2.2
Sample JDBC 2.0 Program
17.4.2.3
Sample Pre-2.0 JDBC Program
17.4.3
Overview of Oracle SQLJ
17.4.3.1
Benefits of SQLJ
17.4.3.2
SQLJ Stored Subprograms in the Server
17.4.4
Comparing Oracle JDBC and Oracle SQLJ
17.4.5
Overview of Oracle JPublisher
17.4.6
Overview of Java Stored Subprograms
17.4.7
Overview of Oracle Database Web Services
17.5
Choosing PL/SQL or Java
17.5.1
Similarities of PL/SQL and Java
17.5.2
PL/SQL Advantages Over Java
17.5.3
Java Advantages Over PL/SQL
17.6
Overview of Precompilers
17.6.1
Overview of the Pro*C/C++ Precompiler
17.6.2
Overview of the Pro*COBOL Precompiler
17.7
Overview of OCI and OCCI
17.7.1
Advantages of OCI and OCCI
17.7.2
OCI and OCCI Functions
17.7.3
Procedural and Nonprocedural Elements of OCI and OCCI Applications
17.7.4
Building an OCI or OCCI Application
17.8
Choosing a Precompiler or OCI
17.9
Overview of Oracle Data Provider for .NET (ODP.NET)
17.10
Overview of OraOLEDB
18
Developing Applications with Multiple Programming Languages
18.1
Overview of Multilanguage Programs
18.2
What Is an External Procedure?
18.3
Overview of Call Specification for External Procedures
18.4
Loading External Procedures
18.4.1
Define the C Procedures
18.4.2
Set Up the Environment
18.4.3
Identify the DLL
18.4.4
Publish the External Procedures
18.5
Publishing External Procedures
18.5.1
AS LANGUAGE Clause for Java Class Methods
18.5.2
AS LANGUAGE Clause for External C Procedures
18.5.2.1
LIBRARY
18.5.2.2
NAME
18.5.2.3
LANGUAGE
18.5.2.4
CALLING STANDARD
18.5.2.5
WITH CONTEXT
18.5.2.6
PARAMETERS
18.5.2.7
AGENT IN
18.6
Publishing Java Class Methods
18.7
Publishing External C Procedures
18.8
Locations of Call Specifications
18.8.1
Example: Locating a Call Specification in a PL/SQL Package
18.8.2
Example: Locating a Call Specification in a PL/SQL Package Body
18.8.3
Example: Locating a Call Specification in an ADT Specification
18.8.4
Example: Locating a Call Specification in an ADT Body
18.8.5
Example: Java with AUTHID
18.8.6
Example: C with Optional AUTHID
18.8.7
Example: Mixing Call Specifications in a Package
18.9
Passing Parameters to External C Procedures with Call Specifications
18.9.1
Specifying Data Types
18.9.2
External Data Type Mappings
18.9.3
Passing Parameters BY VALUE or BY REFERENCE
18.9.4
Declaring Formal Parameters
18.9.5
Overriding Default Data Type Mapping
18.9.6
Specifying Properties
18.9.6.1
INDICATOR
18.9.6.2
LENGTH and MAXLEN
18.9.6.3
CHARSETID and CHARSETFORM
18.9.6.4
Repositioning Parameters
18.9.6.5
SELF
18.9.6.6
BY REFERENCE
18.9.6.7
WITH CONTEXT
18.9.6.8
Interlanguage Parameter Mode Mappings
18.10
Running External Procedures with CALL Statements
18.10.1
Preconditions for External Procedures
18.10.1.1
Privileges of External Procedures
18.10.1.2
Managing Permissions
18.10.1.3
Creating Synonyms for External Procedures
18.10.2
CALL Statement Syntax
18.10.3
Calling Java Class Methods
18.10.4
Calling External C Procedures
18.11
Handling Errors and Exceptions in Multilanguage Programs
18.12
Using Service Routines with External C Procedures
18.12.1
OCIExtProcAllocCallMemory
18.12.2
OCIExtProcRaiseExcp
18.12.3
OCIExtProcRaiseExcpWithMsg
18.13
Doing Callbacks with External C Procedures
18.13.1
OCIExtProcGetEnv
18.13.2
Object Support for OCI Callbacks
18.13.3
Restrictions on Callbacks
18.13.4
Debugging External C Procedures
18.13.5
Example: Calling an External C Procedure
18.13.6
Global Variables in External C Procedures
18.13.7
Static Variables in External C Procedures
18.13.8
Restrictions on External C Procedures
19
Developing Applications with Oracle XA
19.1
X/Open Distributed Transaction Processing (DTP)
19.1.1
DTP Terminology
19.1.2
Required Public Information
19.2
Oracle XA Library Subprograms
19.2.1
Oracle XA Library Subprograms
19.2.2
Oracle XA Interface Extensions
19.3
Developing and Installing XA Applications
19.3.1
DBA or System Administrator Responsibilities
19.3.2
Application Developer Responsibilities
19.3.3
Defining the xa_open String
19.3.3.1
Syntax of the xa_open String
19.3.3.2
Required Fields for the xa_open String
19.3.3.3
Optional Fields for the xa_open String
19.3.4
Using Oracle XA with Precompilers
19.3.4.1
Using Precompilers with the Default Database
19.3.4.2
Using Precompilers with a Named Database
19.3.5
Using Oracle XA with OCI
19.3.6
Managing Transaction Control with Oracle XA
19.3.7
Examples of Precompiler Applications
19.3.8
Migrating Precompiler or OCI Applications to TPM Applications
19.3.9
Managing Oracle XA Library Thread Safety
19.3.9.1
Specifying Threading in the Open String
19.3.9.2
Restrictions on Threading in Oracle XA
19.3.10
Using the DBMS_XA Package
19.4
Troubleshooting XA Applications
19.4.1
Accessing Oracle XA Trace Files
19.4.1.1
xa_open String DbgFl
19.4.1.2
Trace File Locations
19.4.2
Managing In-Doubt or Pending Oracle XA Transactions
19.4.3
Using SYS Account Tables to Monitor Oracle XA Transactions
19.5
Oracle XA Issues and Restrictions
19.5.1
Using Database Links in Oracle XA Applications
19.5.2
Managing Transaction Branches in Oracle XA Applications
19.5.3
Using Oracle XA with Oracle Real Application Clusters (Oracle RAC)
19.5.3.1
GLOBAL_TXN_PROCESSES Initialization Parameter
19.5.3.2
Managing Transaction Branches on Oracle RAC
19.5.3.3
Managing Instance Recovery in Oracle RAC with DTP Services (10.2)
19.5.3.4
Global Uniqueness of XIDs in Oracle RAC
19.5.3.5
Tight and Loose Coupling
19.5.4
SQL-Based Oracle XA Restrictions
19.5.4.1
Rollbacks and Commits
19.5.4.2
DDL Statements
19.5.4.3
Session State
19.5.4.4
EXEC SQL
19.5.5
Miscellaneous Restrictions
20
Developing Applications with the Publish-Subscribe Model
20.1
Introduction to the Publish-Subscribe Model
20.2
Publish-Subscribe Architecture
20.2.1
Database Events
20.2.2
Oracle Advanced Queuing
20.2.3
Client Notification
20.3
Publish-Subscribe Concepts
20.4
Examples of a Publish-Subscribe Mechanism
21
Using the Oracle ODBC Driver
21.1
About Oracle ODBC Driver
21.2
For All Users
21.2.1
Oracle ODBC Driver
21.2.1.1
What is the Oracle ODBC Driver
21.2.1.2
New and Changed Features
21.2.1.3
Features Not Supported
21.2.1.4
Files Created by the Installation
21.2.1.5
Driver Conformance Levels
21.2.1.6
Known Limitations
21.2.2
Configuration Tasks
21.2.2.1
Configuring Oracle Net Services
21.2.2.2
Configuring the Data Source
21.2.2.3
Oracle ODBC Driver Configuration Dialog Box
21.2.3
Modifying the oraodbc.ini File
21.2.3.1
Reducing Lock Timeout
21.2.4
Connecting to a Data Source
21.2.4.1
Connecting to an Oracle Data Source
21.2.5
Troubleshooting
21.2.5.1
Using the Oracle ODBC Driver for the First Time
21.2.5.2
Expired Password
21.3
For Advanced Users
21.3.1
Creating Oracle ODBC Driver TNS Service Names
21.3.2
SQL Statements
21.3.3
Data Types
21.3.4
Implementation of Data Types (Advanced)
21.3.5
Limitations on Data Types
21.3.6
Error Messages
21.4
For Programmers
21.4.1
Format of the Connection String
21.4.2
SQLDriverConnect Implementation
21.4.3
Reducing Lock Timeout in a Program
21.4.4
Linking with odbc32.lib (Windows) or libodbc.so (UNIX)
21.4.5
Obtaining Information About rowids
21.4.6
Rowids in a WHERE Clause
21.4.7
Enabling Result Sets
21.4.8
Enabling EXEC Syntax
21.4.9
Enabling Event Notification for Connection Failures in an Oracle RAC Environment
21.4.10
Using Implicit Results Feature Through ODBC
21.4.11
Supported Functionality
21.4.11.1
API Conformance
21.4.11.2
Implementation of ODBC API Functions
21.4.11.3
Implementation of the ODBC SQL Syntax
21.4.11.4
Implementation of Data Types (Programming)
21.4.12
Unicode Support
21.4.12.1
Unicode Support Within the ODBC Environment
21.4.12.2
Unicode Support in ODBC API
21.4.12.3
Unicode Functions in the Driver Manager
21.4.12.4
SQLGetData Performance
21.4.12.5
Unicode Samples
21.4.13
Performance and Tuning
21.4.13.1
General ODBC Programming Tips
21.4.13.2
Data Source Configuration Options
21.4.13.3
DATE and TIMESTAMP Data Types
22
Using the Identity Code Package
22.1
Identity Concepts
22.2
What is the Identity Code Package?
22.3
Using the Identity Code Package
22.3.1
Storing RFID Tags in Oracle Database Using MGD_ID ADT
22.3.1.1
Creating a Table with MGD_ID Column Type and Storing EPC Tag Encodings in the Column
22.3.1.2
Constructing MGD_ID Objects to Represent RFID Tags
22.3.1.3
Inserting an MGD_ID Object into a Database Table
22.3.1.4
Querying MGD_ID Column Type
22.3.2
Building a Function-Based Index Using the Member Functions of the MGD_ID Column Type
22.3.3
Using MGD_ID ADT Functions
22.3.3.1
Using the get_component Function with the MGD_ID Object
22.3.3.2
Parsing Tag Data from Standard Representations
22.3.3.3
Reconstructing Tag Representations from Fields
22.3.3.4
Translating Between Tag Representations
22.3.4
Defining a Category of Identity Codes and Adding Encoding Schemes to an Existing Category
22.3.4.1
Creating a Category of Identity Codes
22.3.4.2
Adding Two Metadata Schemes to a Newly Created Category
22.4
Identity Code Package Types
22.5
DBMS_MGD_ID_UTL Package
22.6
Identity Code Metadata Tables and Views
22.7
Electronic Product Code (EPC) Concepts
22.7.1
RFID Technology and EPC v1.1 Coding Schemes
22.7.2
Product Code Concepts and Their Current Use
22.7.2.1
Electronic Product Code (EPC)
22.7.2.2
Global Trade Identification Number (GTIN) and Serializable Global Trade Identification Number (SGTIN)
22.7.2.3
Serial Shipping Container Code (SSCC)
22.7.2.4
Global Location Number (GLN) and Serializable Global Location Number (SGLN)
22.7.2.5
Global Returnable Asset Identifier (GRAI)
22.7.2.6
Global Individual Asset Identifier (GIAI)
22.7.2.7
RFID EPC Network
22.8
Oracle Database Tag Data Translation Schema
23
Understanding Schema Object Dependency
23.1
Overview of Schema Object Dependency
23.2
Querying Object Dependencies
23.3
Object Status
23.4
Invalidation of Dependent Objects
23.4.1
Session State and Referenced Packages
23.4.2
Security Authorization
23.5
Guidelines for Reducing Invalidation
23.5.1
Add Items to End of Package
23.5.2
Reference Each Table Through a View
23.6
Object Revalidation
23.6.1
Revalidation of Objects that Compiled with Errors
23.6.2
Revalidation of Unauthorized Objects
23.6.3
Revalidation of Invalid SQL Objects
23.6.4
Revalidation of Invalid PL/SQL Objects
23.7
Name Resolution in Schema Scope
23.8
Local Dependency Management
23.9
Remote Dependency Management
23.9.1
Dependencies Among Local and Remote Database Procedures
23.9.2
Dependencies Among Other Remote Objects
23.9.3
Dependencies of Applications
23.10
Remote Procedure Call (RPC) Dependency Management
23.10.1
Time-Stamp Dependency Mode
23.10.2
RPC-Signature Dependency Mode
23.10.2.1
Changing Names and Default Values of Parameters
23.10.2.2
Changing Specification of Parameter Mode IN
23.10.2.3
Changing Subprogram Body
23.10.2.4
Changing Data Type Classes of Parameters
23.10.2.5
Changing Package Types
23.10.3
Controlling Dependency Mode
23.10.3.1
Dependency Resolution
23.10.3.2
Suggestions for Managing Dependencies
23.11
Shared SQL Dependency Management
24
Using Edition-Based Redefinition
24.1
Editions
24.1.1
Editioned and Noneditioned Objects
24.1.1.1
Name Resolution for Editioned and Noneditioned Objects
24.1.1.2
Noneditioned Objects That Can Depend on Editioned Objects
24.1.1.3
Editionable and Noneditionable Schema Object Types
24.1.1.4
Enabling Editions for a User
24.1.1.5
EDITIONABLE and NONEDITIONABLE Properties
24.1.1.6
Rules for Editioned Objects
24.1.2
Creating an Edition
24.1.3
Editioned Objects and Copy-on-Change
24.1.4
Making an Edition Available to Some Users
24.1.5
Making an Edition Available to All Users
24.1.6
Current Edition and Session Edition
24.1.6.1
Your Initial Session Edition
24.1.6.2
Changing Your Session Edition
24.1.6.3
Displaying the Names of the Current and Session Editions
24.1.6.4
When the Current Edition Might Differ from the Session Edition
24.1.7
Retiring an Edition
24.1.8
Dropping an Edition
24.2
Editioning Views
24.2.1
Creating an Editioning View
24.2.2
Partition-Extended Editioning View Names
24.2.3
Changing the Writability of an Editioning View
24.2.4
Replacing an Editioning View
24.2.5
Dropping or Renaming the Base Table
24.2.6
Adding Indexes and Constraints to the Base Table
24.2.7
SQL Optimizer Index Hints
24.3
Crossedition Triggers
24.3.1
Forward Crossedition Triggers
24.3.2
Reverse Crossedition Triggers
24.3.3
Crossedition Trigger Interaction with Editions
24.3.3.1
Which Triggers Are Visible
24.3.3.2
What Kind of Triggers Can Fire
24.3.3.3
Firing Order
24.3.3.4
Crossedition Trigger Execution
24.3.4
Creating a Crossedition Trigger
24.3.4.1
Coding the Forward Crossedition Trigger Body
24.3.5
Transforming Data from Pre- to Post-Upgrade Representation
24.3.5.1
Preventing Lost Updates
24.3.6
Dropping the Crossedition Triggers
24.4
Displaying Information About EBR Features
24.4.1
Displaying Information About Editions
24.4.2
Displaying Information About Editioning Views
24.4.3
Displaying Information About Crossedition Triggers
24.5
Using EBR to Upgrade an Application
24.5.1
Preparing Your Application to Use Editioning Views
24.5.2
Procedure for EBR Using Only Editions
24.5.3
Procedure for EBR Using Editioning Views
24.5.4
Procedure for EBR Using Crossedition Triggers
24.5.5
Rolling Back the Application Upgrade
24.5.6
Reclaiming Space Occupied by Unused Table Columns
24.5.7
Example: Using EBR to Upgrade an Application
24.5.7.1
Existing Application
24.5.7.2
Preparing the Application to Use Editioning Views
24.5.7.3
Using EBR to Upgrade the Application
25
Using Transaction Guard
25.1
Problem that Transaction Guard Solves
25.2
Solution that Transaction Guard Provides
25.3
Transaction Guard Concepts and Scope
25.3.1
Logical Transaction Identifier (LTXID)
25.3.2
At-Most-Once Execution
25.3.3
Transaction Guard Coverage
25.3.4
Transaction Guard Exclusions
25.4
Database Configuration for Transaction Guard
25.4.1
Configuration Checklist
25.4.2
Transaction History Table
25.4.3
Service Parameters
25.5
Developing Applications that Use Transaction Guard
25.5.1
Typical Transaction Guard Usage
25.5.2
Details for Using the LTXID
25.5.3
Connection-Pool LTXID Usage
25.5.4
Additional Requirements for Transaction Guard Development
25.6
Transaction Guard and Its Relationship to Application Continuity
26
Ensuring Application Continuity
26.1
About Application Continuity
26.1.1
Key Concepts for Application Continuity
26.2
Application Continuity Operation and Usage
26.2.1
How Application Continuity Works for Database Sessions
26.2.2
Actions for Using Application Continuity for Java
26.2.2.1
When Is Application Continuity Transparent?
26.2.2.2
Configuring Oracle JDBC for Application Continuity for Java
26.2.2.3
Configuring Oracle Database for Application Continuity for Java
26.2.2.4
Registering a Connection Initialization Callback in Application Continuity for Java (optional)
26.2.2.5
Delaying the Reconnection in Application Continuity for Java
26.2.2.6
Using Application Continuity for Planned Outages
26.2.2.7
Running Without Application Continuity
26.2.2.8
Disabling Replay in Application Continuity for Java
26.2.2.9
Killing or Disconnecting a Session Without Replay
26.2.3
Mutable Objects and Application Continuity
26.2.4
Session State Consistency
26.2.4.1
Dynamic Session State Consistency
26.2.4.2
Static Session State Consistency
26.3
Potential Side Effects of Application Continuity
26.4
Restrictions and Other Considerations for Application Continuity
Index
Scripting on this page enhances content navigation, but does not change the content in any way.