Database Development Guide - Oracle
Oracle DatabaseDatabase Development Guide12c Release 2 (12.2)E85801-05July 2021
Oracle Database Database Development Guide, 12c Release 2 (12.2)E85801-05Copyright 1996, 2021, Oracle and/or its affiliates.Primary Author: Amith KumarContributing Authors: Louise Morin, Chuck Murray, Tom Kyte, D. Adams, L. Ashdown, S. Moore, E.Paapanen, R. Strohm, R. WardContributors: D. Alpern, G. Arora, T. Chang, B. Cheng, R. Day, R. Decker, G. Doherty, A. Ganesh, M.Hartstein, Y. Hu, J. Huang, C. Iyer, N. Jain, V. Krishnaswamy, R. Kumar, S. Kumar, C. Lei, B. Llewellyn, K.Mohan, V. Moore, J. Muller, R. Murthy, R. Pang, B. Sinha, S. Vemuri, W. Wang, D. WongThis software and related documentation are provided under a license agreement containing restrictions onuse and disclosure and are protected by intellectual property laws. Except as expressly permitted in yourlicense agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverseengineering, disassembly, or decompilation of this software, unless required by law for interoperability, isprohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. Ifyou find any errors, please report them to us in writing.If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it onbehalf of the U.S. Government, then the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software,any programs embedded, installed or activated on delivered hardware, and modifications of such programs)and Oracle computer documentation or other Oracle data delivered to or accessed by U.S. Government endusers are "commercial computer software" or "commercial computer software documentation" pursuant to theapplicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use,reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/oradaptation of i) Oracle programs (including any operating system, integrated software, any programsembedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oraclecomputer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in thelicense contained in the applicable contract. The terms governing the U.S. Government’s use of Oracle cloudservices are defined by the applicable contract for such services. No other rights are granted to the U.S.Government.This software or hardware is developed for general use in a variety of information management applications.It is not developed or intended for use in any inherently dangerous applications, including applications thatmay create a risk of personal injury. If you use this software or hardware in dangerous applications, then youshall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure itssafe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of thissoftware or hardware in dangerous applications.Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks oftheir respective owners.Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks areused under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Epyc,and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registeredtrademark of The Open Group.This software or hardware and documentation may provide access to or information about content, products,and services from third parties. Oracle Corporation and its affiliates are not responsible for and expresslydisclaim all warranties of any kind with respect to third-party content, products, and services unless otherwiseset forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not beresponsible for any loss, costs, or damages incurred due to your access to or use of third-party content,products, or services, except as set forth in an applicable agreement between you and Oracle.
ContentsPrefaceAudiencexxxiDocumentation AccessibilityxxxiRelated DocumentsxxxiConventionsxxxiiChanges in Oracle Database 12c Release 2 (12.2)Part I1Database Development FundamentalsDesign Basics1.1Design for Performance1-11.2Design for Scalability1-21.3Design for Extensibility1-21.3.1Data Cartridges1-31.3.2External Procedures1-31.3.3User-Defined Functions and Aggregate Functions1-31.3.4Object-Relational Features1-41.4Design for Security1-41.5Design for Availability1-41.6Design for Portability1-51.7Design for Diagnosability1-51.8Design for Special Environments1-61.91.8.1Data Warehousing1-61.8.2Online Transaction Processing (OLTP)1-7Features for Special Scenarios1-81.9.1SQL Analytic Functions1-81.9.2Materialized Views1-91.9.3Partitioning1-10iii
1.9.421-11Connection Strategies for Database Applications2.1Design Guidelines for Connection Pools2-12.1.1Connection Storms2-12.1.2Guideline for Preventing Connection Storms: Use Static Pools2-22.2Design Guideline for Login Strategy2-32.3Design Guideline for Preventing Programmatic Session Leaks2-42.42.3.1Drained Connection Pools2-42.3.2Checking for Session Leaks2-42.3.3Lock Leaks2-42.3.4Logical Corruption2-5Using Runtime Connection Load Balancing2-52.4.1About Runtime Connection Load Balancing2-52.4.2Enabling and Disabling Runtime Connection Load 2-72.4.2.4ODP.NET2-72.4.33Temporal Validity SupportReceiving Load Balancing Advisory FAN Events2-8Performance and Scalability3.1Performance Strategies3.1.13.23.33-1Designing Your Data Model to Perform Well3-13.1.1.1Analyze the Data Requirements of the Application3-23.1.1.2Create the Database Design for the Application3-23.1.1.3Implement the Database Application3-33.1.1.4Maintain the Database and Database Application3-43.1.2Setting Performance Goals (Metrics)3-43.1.3Benchmarking Your Application3-4Tools for Performance3-53.2.1DBMS APPLICATION INFO Package3-53.2.2SQL Trace Facility (SQL TRACE)3-63.2.3EXPLAIN PLAN Statement3-7Monitoring Database Performance3-83.3.1Automatic Database Diagnostic Monitor (ADDM)3-83.3.2Monitoring Real-Time Database Performance3-93.3.3Responding to Performance-Related Alerts3-93.3.4SQL Advisors and Memory Advisors3-9iv
3.4Testing for Performance3-103.5Using Client Result Cache3-113.5.1About Client Result Cache3-123.5.2Benefits of Client Result Cache3-133.5.3Guidelines for Using Client Result Cache3-133.5.3.1SQL Hints3-153.5.3.2Table Annotation3-153.5.3.3Session Parameter3-163.5.3.4Effective Table Result Cache Mode3-163.5.3.5Displaying Effective Table Result Cache Mode3-173.5.3.6Result Cache Mode Use Cases3-173.5.3.7Queries Never Result Cached in Client Result Cache3-183.5.4Client Result Cache Consistency3-183.5.5Deployment-Time Settings for Client Result Cache3-193.5.5.1Server Initialization Parameters3-193.5.5.2Client Configuration Parameters3-213.5.6Client Result Cache Statistics3-213.5.7Validation of Client Result Cache3-223.5.7.1Measure Execution Times3-223.5.7.2Query V MYSTAT3-223.5.7.3Query V SQLAREA3-233.5.8Client Result Cache and Server Result Cache3-233.5.9Client Result Cache Demo Files3-243.5.10Client Result Cache Compatibility with Previous Releases3-243.6Statement Caching3-253.7OCI Client Statement Cache Auto-Tuning3-253.8Client-Side Deployment Parameters3-263.9Using Query Change Notification3-263.10Using Database Resident Connection Pool3-273.10.1About Database Resident Connection Pool3-273.10.2Configuring DRCP3-293.10.3Sharing Proxy Sessions3-293.10.4Using JDBC with DRCP3-303.10.5Using OCI Session Pool APIs with DRCP3-303.10.6Session Purity and Connection Class3-313.10.6.1Session Purity3-313.10.6.2Connection Class3-323.10.6.3Example: Setting the Connection Class as HRMS3-333.10.6.4Example: Setting the Connection Class as RECMS3-333.10.6.5Session Purity and Connection Class Defaults3-333.10.7Starting Database Resident Connection Pool3-34v
43.10.8Enabling DRCP3-343.10.9Benefiting from the Scalability of DRCP in an OCI Application3-343.10.10Benefiting from the Scalability of DRCP in a Java Application3-353.10.11Best Practices for Using DRCP3-353.10.12Compatibility and Migration3-373.10.13Using DRCP with Oracle Database Native Network Encryption3-373.10.14DRCP Restrictions3-373.10.15Using DRCP with Custom Pools3-383.10.16Explicitly Marking Sessions Stateful or Stateless3-393.10.17Using DRCP with Oracle Real Application Clusters3-403.10.18Using DRCP with Pluggable Databases3-403.10.19DRCP with Data Guard3-40Designing Applications for Oracle Real-World Performance4.1Using Bind Variables4-14.2Using Instrumentation4-24.3Using Set-Based Processing4-24.3.164-34.3.1.1About Iterative Data Processing4-34.3.1.2Iterative Data Processing: Row-By-Row4-34.3.1.3Iterative Data Processing: Arrays4-54.3.1.4Iterative Data Processing: Manual Parallelism4-64.3.25Iterative Data ProcessingSet-Based Processing4-9Security5.1Enabling User Access with Grants, Roles, and Least Privilege5-15.2Automating Database Logins5-25.3Controlling User Access with Fine-Grained Access Control5-35.4Using Invoker's and Definer's Rights for Procedures and Functions5-45.4.1What Are Invoker's Rights and Definer's Rights?5-55.4.2Protecting Users Who Run Invoker's Rights Procedures and Functions5-55.4.3How Default Rights Are Handled for Java Stored Procedures5-65.5Managing External Procedures for Your Applications5-65.6Auditing User Activity5-7High Availability6.1Transparent Application Failover (TAF)6-16.1.1About Transparent Application Failover6-16.1.2Configuring Transparent Application Failover6-2vi
6.1.36.26.36.4Fast Application Notification (FAN) and Fast Connection Failover (FCF)6-3About Fast Application Notification (FAN)6-36.2.2About Receiving FAN Event Notifications6-46.2.3About Fast Connection Failover (FCF)6-5Application Continuity and Transaction Guard6-66.3.1Overview of Application Continuity6-66.3.2Overview of Transaction Guard6-7Service and Load Management for Database CloudsAbout Service and Load Management for Database Clouds6-86-8Advanced PL/SQL Features7.1PL/SQL Data Types7-17.2Dynamic SQL7-17.3PL/SQL Optimize Level7-27.4Compiling PL/SQL Units for Native Execution7-27.5Exception Handling7-27.6Conditional Compilation7-27.7Bulk Binding7-3Part II86-26.2.16.4.17Using Transparent Application Failover CallbacksSQL for Application DevelopersSQL Processing for Application Developers8.18.2Description of SQL Statement Processing8-18.1.1Stages of SQL Statement Processing8-28.1.2Shared SQL Areas8-4Grouping Operations into Transactions8-48.2.1Deciding How to Group Operations in Transactions8-48.2.2Improving Transaction Performance8-58.2.3Managing Commit Redo Action8-68.2.4Determining Transaction Outcome After a Recoverable Outage8-88.2.4.1Understanding Transaction Guard8-88.2.4.2Understanding DBMS APP CONT.GET LTXID OUTCOME8-108.2.4.3Using Transaction Guard8-138.3Ensuring Repeatable Reads with Read-Only Transactions8-138.4Locking Tables Explicitly8-148.4.1Privileges Required to Acquire Table Locks8-158.4.2Choosing a Locking Strategy8-158.4.2.1When to Lock with ROW SHARE MODE and ROW EXCLUSIVE MODE8-16vii
8.58.68.4.2.2When to Lock with SHARE MODE8-178.4.2.3When to Lock with SHARE ROW EXCLUSIVE MODE8-188.4.2.4When to Lock with EXCLUSIVE MODE8-188.4.3Letting Oracle Database Control Table Locking8-188.4.4Explicitly Acquiring Row Locks8-198.4.5Examples of Concurrency Under Explicit Locking8-20Using Oracle Lock Management Services (User Locks)8-288.5.1When to Use User Locks8-288.5.2Viewing and Monitoring Locks8-29Using Serializable Transactions for Concurrency Control8.6.1Transaction Interaction and Isolation Level8-308.6.2Setting Isolation Levels8-328.6.3Serializable Transactions and Referential Integrity8-338.6.4READ COMMITTED and SERIALIZABLE Isolation Levels8-358.6.4.1Transaction Set Consistency Differences8-358.6.4.2Choosing Transaction Isolation Levels8-368.7Nonblocking and Blocking DDL Statements8-378.8Autonomous Transactions8-388.8.18.9Examples of Autonomous Transactions8-418.8.1.1Ordering a Product8-418.8.1.2Withdrawing Money from a Bank Account8-418.8.298-29Declaring Autonomous Routines8-44Resuming Execution After Storage Allocation Errors8-458.9.1What Operations Have Resumable Storage Allocation?8-458.9.2Handling Suspended Storage Allocation8-468.9.2.1Using an AFTER SUSPEND Trigger in the Application8-468.9.2.2Checking for Suspended Statements8-48Using SQL Data Types in Database Applications9.1Using the Correct and Most Specific Data Type9-19.1.1How the Correct Data Type Increases Data Integrity9-29.1.2How the Most Specific Data Type Decreases Storage Requirements9-29.1.3How the Correct Data Type Improves Performance9-39.2Representing Character Data9-69.3Representing Numeric Data9-79.3.1Floating-Point Number Components9-89.3.2Floating-Point Number Formats9-89.3.2.1Binary Floating-Point Formats9-99.3.3Representing Special Values with Native Floating-Point Data Types9-109.3.4Comparing Native Floating-Point Values9-11viii
9.3.5Arithmetic Operations with Native Floating-Point Data Types9-129.3.6Conversion Functions for Native Floating-Point Data Types9-129.3.7Client Interfaces for Native Floating-Point Data Types9-139.4Representing Date and Time Data9.4.1Displaying Current Date and Time9-159.4.2Inserting and Displaying Dates9-169.4.3Inserting and Displaying Times9-179.4.4Arithmetic Operations with Datetime Data Types9-189.4.5Conversion Functions for Datetime Data Types9-199.4.6Importing, Exporting, and Comparing Datetime Types9-209.5109-13Representing Specialized Data9-209.5.1Representing Spatial Data9-209.5.2Representing Multimedia Data9-209.5.3Representing Large Amounts of Data9-219.5.3.1Large Objects (LOBs)9-219.5.3.2LONG and LONG RAW Data Types9-229.5.4Representing Searchable Text9-229.5.5Representing XML Data9-229.5.6Representing Dynamically Typed Data9-239.5.7Representing ANSI, DB2, and SQL/DS Data9-259.6Identifying Rows by Address9-259.7Displaying Metadata for SQL Operators and Functions9-279.7.1ARGn Data Type9-289.7.2DISP TYPE Data Type9-289.7.3SQL Data Type Families9-29Using Regular Expressions in Database Applications10.1Overview of Regular Expressions10-110.2Oracle SQL Support for Regular Expressions10-210.3Oracle SQL and POSIX Regular Expression Standard10-410.4Operators in Oracle SQL Regular Expressions10-510.4.1POSIX Operators in Oracle SQL Regular Expressions10-510.4.2Oracle SQL Multilingual Extensions to POSIX Standard10-910.4.3Oracle SQL PERL-Influenced Extensions to POSIX Standard10-910.5Using Regular Expressions in SQL Statements: Scenarios10-1110.5.1Using a Constraint to Enforce a Phone Number Format10-1110.5.2Example: Enforcing a Phone Number Format with Regular Expressions10-1210.5.3Example: Inserting Phone Numbers in Correct and Incorrect Formats10-1210.5.4Using Back References to Reposition Characters10-13ix
1112Using Indexes in Database Applications11.1Guidelines for Managing Indexes11-111.2Managing Indexes11-211.3When to Use Domain Indexes11-211.4When to Use Function-Based Indexes11-211.4.1Advantages of Function-Based Indexes11-411.4.2Disadvantages of Function-Based Indexes11-411.4.3Example: Function-Based Index for Precomputing Arithmetic Expression11-611.4.4Example: Function-Based Indexes on Object Column11-711.4.5Example: Function-Based Index for Faster Case-Insensitive Searches11-811.4.6Example: Function-Based Index for Language-Dependent Sorting11-8Maintaining Data Integrity in Database Applications12.1Enforcing Business Rules with Constraints12-212.2Enforcing Business Rules with Both Constraints and Application Code12-312.3Creating Indexes for Use with Constraints12-412.4When to Use NOT NULL Constraints12-512.5When to Use Default Column Values12-612.6Choosing a Primary Key for a Table (PRIMARY KEY Constraint)12-712.7When to Use UNIQUE Constraints12-812.8Enforcing Referential Integrity with FOREIGN KEY Constraints12-912.8.1FOREIGN KEY Constraints and NULL Values12-1112.8.2Defining Relationships Between Parent and Child Tables12-1112.8.3Rules for Multiple FOREIGN KEY Constraints12-1212.8.4Deferring Constraint Checks12-1212.9Minimizing Space and Time Overhead for Indexes Associated with Constraints12-1512.10Guidelines for Indexing Foreign Keys12-1512.11Referential Integrity in a Distributed Database12-1512.12When to Use CHECK Constraints12-1612.12.1Restrictions on CHECK Constraints12-1612.12.2Designing CHECK Constraints12-1712.12.3Rules for Multiple CHECK Constraints12-1712.12.4Choosing Between CHECK and NOT NULL Constraints12-1812.13Examples of Defining Constraints12-1812.13.1Privileges Needed to Define Constraints12-1912.13.2Naming Constraints12-2012.14Enabling and Disabling Constraints12-2012.14.1Why Disable Constraints?12-2112.14.2Creating Enabled Constraints (Default)12-2112.14.3Creating Disabled Constraints12-22x
12.14.4Enabling Existing Constraints12-2212.14.5Disabling Existing Constraints12-2312.14.6Guidelines for Enabling and Disabling Key Constraints12-2312.14.7Fixing Constraint Exceptions12-2412.15Modifying Constraints12-2412.16Renaming Constraints12-2512.17Dropping Constraints12-2612.18Managing FOREIGN KEY Constraints12-2712.18.1Data Types and Names for Foreign Key Columns12-2712.18.2Limit on Columns in Composite Foreign Keys12-2712.18.3Foreign Key References Primary Key by Default12-2712.18.4Privileges Required to Create FOREIGN KEY Constraints12-2712.18.5Choosing How Foreign Keys Enforce Referential Integrity12-2812.19Part III13Viewing Information About Constraints12-28PL/SQL for Application DevelopersCoding PL/SQL Subprograms and Packages13.1Overview of PL/SQL Subprograms13-113.2Overview of PL/SQL Packages13-213.3Overview of PL/SQL Units13-413.3.113.4PLSQL OPTIMIZE LEVEL Compilation ParameterCreating PL/SQL Subprograms and Packages13-413-613.4.1Privileges Needed to Create Subprograms and Packages13-713.4.2Creating Subprograms and Packages13-713.4.3PL/SQL Object Size Limits13-813.4.4PL/SQL Data Types13-913.4.4.1PL/SQL Scalar Data Types13.4.4.2PL/SQL Composite Data Types13-1213.4.4.3Abstract Data Types13-12Returning Result Sets to Clients13-1213.4.513-913.4.5.1Advantages of Cursor Variables13-1313.4.5.2Disadvantages of Cursor Variables13-1413.4.5.3Returning Query Results Implicitly13-1713.4.6Returning Large Amounts of Data from a Function13-1713.4.7PL/SQL Function Result Cache13-1813.4.8Overview of Bulk Binding13-1813.4.8.1DML Statements that Reference Collections13-1913.4.8.2SELECT Statements that Reference Collections13-2013.4.8.3FOR Loops that Reference Collections and Return DML13-21xi
13.4.913-2113.5Altering PL/SQL Subprograms and Packages13-2213.6Deprecating Packages, Subprograms, and Types13-2313.7Dropping PL/SQL Subprograms and Packages13-2313.8Compiling PL/SQL Units for Native Execution13-2313.9Invoking Stored PL/SQL Subprograms13-2413.9.1Privileges Required to Invoke a Stored Subprogram13-2513.9.2Invoking a Subprogram Interactively from Oracle Tools13-2513.9.3Invoking a Subprogram from Another Subprogram13-2713.9.4Invoking a Remote Subprogram13-2813.1013.9.4.1Synonyms for Remote Subprograms13-2913.9.4.2Transactions That Invoke Remote Subprograms13-30Invoking Stored PL/SQL Functions from SQL Statements13-3113.10.1Why Invoke PL/SQL Functions from SQL Statements?13-3213.10.2Where PL/SQL Functions Can Appear in SQL Statements13-3213.10.3When PL/SQL Functions Can Appear in SQL Expressions13-3313.10.4Controlling Side Effects of PL/SQL Functions Invoked from SQL Statements13.1114PL/SQL Dynamic SQL13-3313.10.4.1Restrictions on Functions Invoked from SQL Statements13-3413.10.4.2PL/SQL Functions Invoked from Parallelized SQL Statements13-3513.10.4.3PRAGMA RESTRICT REFERENCES13-36Analyzing and Debugging Stored Subprograms13-3913.11.1PL/Scope13-4013.11.2PL/SQL Hierarchical Profiler13-4013.11.3Debugging PL/SQL and Java13-4013.11.3.1Compiling Code for Debugging13-4113.11.3.2Privileges for Debugging PL/SQL and Java Stored Subprograms13-4113.12Package Invalidations and Session State13-4213.13Example: Raising an ORA-04068 Error13-4313.14Example: Trapping ORA-0406813-43Using PL/Scope14.1Overview of PL/Scope14-114.2Privileges Required for Using PL/Scope14-214.3Specifying Identifier and Statement Collection14-214.4How Much Space is PL/Scope Data Using?14-314.5Viewing PL/Scope Data14-414.5.1Static Data Dictionary Views for PL/SQL and SQL Identifiers14-414.5.1.1PL/SQL and SQL Identifier Types that PL/Scope Collects14-414.5.1.2About Identifiers Usages14-614.5.1.3Identifiers Usage Unique Keys14-8xii
14.5.1.4About Identifiers Usage Context14.5.1.5About Identifiers Signature14.5.214-1114-1314.5.2.1SQL Statement Types that PL/Scope Collects14-1314.5.2.2Statements Location Unique Keys14-1414.5.2.3About SQL Statement Usage Context14-1514.5.2.4About SQL Statements Signature14-1614.5.315Static Data Dictionary Views for SQL Statements14-9SQL Developer14-1714.6Overview of Data Dictionary Views Useful to Manage PL/SQL Code14-1714.7Sample PL/Scope Session14-18Using the PL/SQL Hierarchical Profiler15.1Overview of PL/SQL Hierarchical Profiler15-115.2Collecting Profile Data15-215.3Understanding Raw Profiler Output15-415.3.1Namespaces of Tracked Subprograms15-615.3.2Special Function Names15-715.4Analyzing Profile Data15-715.4.1Creating Hierarchical Profiler Tables15-815.4.2Understanding Hierarchical Profiler Tables15-915.515.4.2.1Hierarchical Profiler Database Table Columns15.4.2.2Distinguishing Between Overloaded Subprograms15-1115.4.2.3Hierarchical Profiler Tables for Sample PL/SQL Procedure15-1215.4.2.4Examples of Calls to DBMS HPROF.analyze with Options15-13plshprof Utility15-915-1415.5.1plshprof Options15-1515.5.2HTML Report from a Single Raw Profiler Output File15-1515.5.2.1First Page of Report15-1615.5.2.2Function-Level Reports15-1715.5.2.3Module-Level Reports15-1815.5.2.4Namespace-Level Reports15-1815.5.2.5Parents and Children Report for a Function15-1915.5.2.6Understanding PL/SQL Hierarchical Profiler SQL-Level Reports15-2015.5.3HTML Difference Report from Two Raw Profiler Output Files15-2115.5.3.1Difference Report Conventions15-2115.5.3.2First Page of Difference Report15-2115.5.3.3Function-Level Difference Reports15-2315.5.3.4Module-Level Difference Reports15-2415.5.3.5Namespace-Level Difference Reports15-25xiii
15.5.3.61617Parents and Children Difference Report for a Function15-25Using PL/SQL Basic Block Coverage to Maintain Quality16.1Overview of PL/SQL Basic Block Coverage16-116.2Collecting PL/SQL Code Coverage Data16-216.3PL/SQL Code Coverage Tables Description16-2Developing PL/SQL Web Applications17.1Overview of PL/SQL Web Applications17-117.2Implementing PL/SQL Web Applications17-217.2.1PL/SQL Gateway17-217.2.1.1mod plsql17-217.2.1.2Embedded PL/SQL Gateway17-317.2.2PL/SQL Web Toolkit17-317.3Using mod plsql Gateway to Map Client Requests to a PL/SQL Web Application17-417.4Using Embedded PL/SQL Gateway17-517.4.1How Embedded PL/SQL Gateway Processes Client Requests17-517.4.2Installing Embedded PL/SQL Gateway17-717.4.3Configuring Embedded PL/SQL Gateway17-717.4.3.1Configuring Embedded PL/SQL Gateway: Overview17.4.3.2Configuring User Authentication for Embedded PL/SQL Gateway17-717-1017.4.4Invoking PL/SQL Stored Subprograms Through Embedded PL/SQL Gateway17-1917.4.5Securing Application Access with Embedded PL/SQL Gateway17-2017.4.6Restrictions in Embedded PL/SQL Gateway17-2017.4.7Using Embedded PL/SQL Gateway: Scenario17-2017.5Generating HTML Output with PL/SQL17-2217.6Passing Parameters to PL/SQL Web Applications17-2317.6.1Passing List and Dropdown-List Parameters from an HTML Form17-2317.6.2Passing Option and Check Box Parameters from an HTML Form17-2417.6.3Passing Entry-Field Parameters from an HTML Form17-2517.6.4Passing Hidden Parameters from an HTML Form17-2617.6.5Uploading a File from an HTML Form17-2617.6.6Submitting a Completed HTML Form17-2717.6.7Handling Missing Input from an HTML Form17-2717.6.8Maintaining State Information Between Web Pages17-27Performing Network Operations in PL/SQL Subprograms17-2817.717.7.1Internet Protocol Version 6 (IPv6) Support17-2817.7.2Sending E-Mail from PL/SQL17-2917.7.3Getting a Host Name or Address from PL/SQL17-30xiv
1817.7.4Using TCP/IP Connections from PL/SQL17-3017.7.5Retrieving HTTP URL Contents from PL/SQL17-3017.7.6Using Tables, Image Maps, Cookies, and CGI Variables from PL/SQL17-32Using Continuous Query Notification (CQN)18.1About Object Change Notification (OCN)18-218.2About Query Result Change Notification (QRCN)18-218.2.1Guaranteed Mode18-318.2.2Best-Effort Mode18-318.318.2.2.1Example: Query Too Complex for QRCN in Guaranteed Mode18-418.2.2.2Example: Query Whose Simplified Version Invalidates Objects18-4Events that Generate Notifications18-518.3.1Committed DML Transactions18-518.3.2Committed DDL Statements18-618.3.3Deregistration18-718.3.4Global Events18-718.4Notification Contents18-818.5Good Candidates for CQN18-818.6Creating CQN Registrations18-1118.7Using PL/SQL to Create CQN Registrations18-1118.7.1PL/SQL CQN Registration Interface18-1218.7.2CQN Registration Options18-1218.7.2.1Notification Type Option18-1318.7.2.2QRCN Mode (QRCN Notification Type Only)18-1318.7.2.3ROWID Option18-1318.7.2.4Operations Filter Option (OCN Notification Type Only)18-1418.7.2.5Transaction Lag Option (OCN Notification Type Only)18-1518.7.2.6Notification Grouping Options18-1518.7.2.7Reliable Option18-1618.7.2.8Purge-on-Notify and Timeout Options18-1718.7.3Prerequisites for Creating CQN Registrations18-1718.7.4Queries that Can Be Registered for Object Change Notification (OCN)18-1718.7.5Queries that Can Be Registered for Query Result Change Notification(QRCN)18-1818.7.5.1Queries that Can Be Registered for QRCN in Guaranteed Mode18-1818.7.5.2Queries that Can Be Registered for QRCN Only in Best-Effort Mode18-1918.7.5.3Queries that Cannot Be Registered for QRCN in Either Mode18-2018.7.6Using PL/SQL to Register Queries for CQN18-2118.7.6.1Creating a PL/SQL Notification Handler18-2118.7.6.2Creating a CQ NOTIFICATION REG INFO Object18-2218.7.6.3Identifying Individual Queries in a Notification18-25xv
18.7.6.418-2618.7.7Best Practices for CQN Registrations18-2618.7.8Troubleshooting CQN Registrations18-2618.7.9Deleting Registrations18-2818.7.1018.8Configuring CQN: Scenario18-2818.7.10.1Creating a PL/SQL Notification Handler18-2818.7.10.2Registering the Queries18-30Using OCI to Create CQN Registrations18-3218.8.1Using OCI for Query Result Set Notifications18-3218.8.2Using OCI to Register a Continuous Query Notification18-3318.8.3Using OCI Subscription Handle Attributes for Continuous Query Notification18-3418.8.4OCI ATTR CQ QUERYID Attribute18-3618.8.5Using OCI Continuous Query Notification Descriptors18-3718.8.5.118.8.618.918.10OCI DTYPE CHDES18-37Demonstrating Continuous Query Notification in an OCI Sample Program18-38Querying CQN Registrations18-48Interpreting Notifications18-4918.10.1Interpreting a CQ NOTIFICATION DESCRIPTOR Object18-4918.10.2Interpreting a CQ NOTIFICATION TABLE Object18-5018.10.3Interpreting a CQ NOTIFICATION QUERY Object18-5118.10.4Interpreting a CQ NOTIFICATION ROW Object18-51Part IV19Adding Queries to an Existing RegistrationAdvanced Topics for Application DevelopersUsing Oracle Flashback Technology19.1Overview of Oracle Flashback Technology19-119.1.1Application Development Features19-219.1.2Database Administration Features19-419.2Configuring Your Database for Oracle Flashback Technology19-519.2.1Configuring Your Database for Automatic Undo Management19-519.2.2Configuring Your Database for Oracle Flashback Transaction Query19-619.2.3Configuring Your Database for Flashback Transaction19-619.2.4Enabling Oracle Flashback Operations on Specific LOB Columns19-619.2.5Granting Necessary Privileges19-719.3Using Oracle Flashback Query (SELECT AS OF)19-819.3.1Example: Examining and Restoring Past Data19-919.3.2Guidelines for Oracle Flashback Query19-919.4Using Oracle Flashback Version Query19-1019.5Using Oracle Flashback Transaction Query19-1219.6Using Oracle Flashback Transaction Query with Oracle Flashback Version Query19-13xvi
19.7Using DBMS FLASHBACK Package19-1519.8Using Flashback Transaction19-1619.8.1Dependent Transactions19-1719.8.2TRANSACTION BACKOUT Parameters19-1719.8.3TRANSACTION BACKOUT Reports19-1819.92019.8.3.1* FLASHBACK TXN STATE19-1819.8.3.2* FLASHBACK TXN REPORT19-19Using Flashback Data Archive19-1919.9.1Creating a Flashback Data Archive19-2119.9.2Altering a Flashback Data Archive19-2119.9.3Dropping a Flashback Data Archive19-2319.9.4Specifying the Default Flashback Data Archive19-2319.9.5Enabling and Disabling Flashback Data Archive19-2319.9.6DDL Statements on Tables Enabled for Flashback Data Archive19-2419.9.7Viewing Flashback Data Archive Data19-2519.9.8Flashback Data Archive Scenarios19-2619.9.8.1Scenario: Using Flashback Data Archive to Enforce Digital Shredding19-2619.9.8.2Scenario: Using Flashback Data Archive to Access Historical Data19-2619.9.8.3Scenario: Using Flashback Data Archive to Generate Reports19-2719.9.8.4Scenario: Using Flashback Data Archive for Auditing19-2719.9.8.5Scenario: Using Flashback Data Archive to Recover Data19-2819.10General Guidelines for Oracle Flashback Technology19-2819.11Performance Guidelines for Oracle Flashback Technology19-3019.12Multitenant Container Database Restrictions for Oracle Flashback Technology19-31Choosing a Programming Environment20.1Overview of Application Architecture20-220.1.1Client/Server Architecture20-220.1.2Server-Side Programming20-220.1.3Two-Tier and Three-Tier Architecture20-320.2Overview of the Program Interface20-320.2.1User Interface20-420.2.2Stateful and Stateless User Interfaces20-420.3Overview of PL/SQL20-420.4Overview of Oracle Database Java Support
3.1.1.1 Analyze the Data Requirements of the Application 3-2 3.1.1.2 Create the Database Design for the Application 3-2 3.1.1.3 Implement the Database Application 3-3 3.1.1.4 Maintain the Database and Database Application 3-4 3.1.2 Setting Performance Goals (Metrics) 3-4 3.1.3 Benchmarking Your Application 3-4 3.2 Tools for Performance 3-5
viii Related Documentation The platform-specific documentation for Oracle Database 10g products includes the following manuals: Oracle Database - Oracle Database Release Notes for Linux Itanium - Oracle Database Installation Guide for Linux Itanium - Oracle Database Quick Installation Guide for Linux Itanium - Oracle Database Oracle Clusterware and Oracle Real Application Clusters
Oracle e-Commerce Gateway, Oracle Business Intelligence System, Oracle Financial Analyzer, Oracle Reports, Oracle Strategic Enterprise Management, Oracle Financials, Oracle Internet Procurement, Oracle Supply Chain, Oracle Call Center, Oracle e-Commerce, Oracle Integration Products & Technologies, Oracle Marketing, Oracle Service,
Oracle is a registered trademark and Designer/2000, Developer/2000, Oracle7, Oracle8, Oracle Application Object Library, Oracle Applications, Oracle Alert, Oracle Financials, Oracle Workflow, SQL*Forms, SQL*Plus, SQL*Report, Oracle Data Browser, Oracle Forms, Oracle General Ledger, Oracle Human Resources, Oracle Manufacturing, Oracle Reports,
2 Installing Oracle Database and Creating a Database 2.1 Overview of Installing Oracle Database Software and Creating a Database 2-1 2.1.1 Checking Oracle Database Installation Prerequisites 2-2 2.1.2 Deciding on Oracle Database Installation Choices 2-3 2.1.2.1 Install Option for Oracle Database 2-3 2.1.2.2 Installation Class for Oracle .
Getting Started with Database Classic Cloud Service. About Oracle Database Classic Cloud Service1-1. About Database Classic Cloud Service Database Deployments1-2. Oracle Database Software Release1-3. Oracle Database Software Edition1-3. Oracle Database Type1-4. Computing Power1-5. Database Storage1-5. Automatic Backup Configuration1-6
Oracle Database using Oracle Real Application Clusters (Oracle RAC) and Oracle Resource Management provided the first consolidation platform optimized for Oracle Database and is the MAA best practice for Oracle Database 11g. Oracle RAC enables multiple Oracle databases to be easily consolidated onto a single Oracle RAC cluster.
Oracle Database Net Services Administrator's Guide SQL*Plus User's Guide and Reference Oracle Database Heterogeneous Connectivity User's Guide Oracle Database 2 Day DBA Oracle Database Security Guide Many of the examples in this book use the sample schemas of the seed database, which is installed by default when you install Oracle.
For more information, see these Oracle resources: Oracle Database Backup and Recovery User's Guide Oracle Database Reference Oracle Database Utilities Many of the examples in this book use the sample schemas of the seed database, which is installed by default when you install Oracle. Refer to Oracle Database Sample Schemas
