Free PDF eBook | FileMount.com

More PDF Content

Page 1
Oracleо Database
Application Developer\’s Guide – Fundamentals
10g Release 1 (10.1)

Contents

• Send Us Your Comments… xxv
• Preface… xxvii
• Audience … xxvii
• Organization… xxix
• Related Documentation … xxxi
• Conventions… xxxiii
• Documentation Accessibility … xxxv
• What\’s New in Application Development?… xxxvii
• New Application Development Features in Oracle Database 10g Release 1 … xxxvii
• New Application Development Features in Oracle9i Release 2 … xxxix
• New Application Development Features in Oracle9i Release 1 … xlii
• Part I Introduction to Application Development Features of Oracle Database
• 1 Programmatic Environments
• Overview of Developing an Oracle Database Application… 1-2
• Overview of PL/SQL … 1-3
• A Simple PL/SQL Example … 1-4
• Advantages of PL/SQL … 1-5
• Full Support for SQL… 1-5
• Tight Integration with Oracle Database… 1-5
• Better Performance… 1-5
• Higher Productivity … 1-6
• Scalability… 1-6
• Maintainability… 1-6
• PL/SQL Support for Object-Oriented Programming … 1-6
• Object Types… 1-6
• Collections … 1-7
• Portability … 1-7
• Security… 1-7
• Built-In Packages for Application Development … 1-7
• Built-In Packages for Server Management… 1-8
• Built-In Packages for Distributed Database Access… 1-8
• Overview of Java Support Built Into the Database… 1-8
• Overview of Oracle JVM… 1-8
• Overview of Oracle Extensions to JDBC … 1-9
• JDBC Thin Driver… 1-10
• JDBC OCI Driver … 1-10
• JDBC Server-Side Internal Driver … 1-11
• Oracle Database Extensions to JDBC Standards … 1-11
• Sample JDBC 2.0 Program… 1-12
• Sample Pre-2.0 JDBC Program … 1-12
• JDBC in SQLJ Applications … 1-13
• Overview of Oracle SQLJ… 1-13
• Benefits of SQLJ … 1-15
• Comparing SQLJ with JDBC … 1-15
• SQLJ Stored Procedures in the Server … 1-16
• Overview of Oracle JPublisher … 1-17
• Overview of Java Stored Procedures … 1-17
• Overview of Database Web Services … 1-17
• Database as a Web Service Provider… 1-18
• Database as a Web Service Consumer… 1-18
• Overview of Writing Procedures and Functions in Java … 1-19
• Overview of Writing Database Triggers in Java … 1-19
• Why Use Java for Stored Procedures and Triggers? … 1-19
• Overview of Pro*C/C++… 1-20
• How You Implement a Pro*C/C++ Application … 1-20
• Highlights of Pro*C/C++ Features… 1-21
• Overview of Pro*COBOL… 1-23
• How You Implement a Pro*COBOL Application… 1-23
• Highlights of Pro*COBOL Features… 1-24
• Overview of OCI and OCCI … 1-25
• Advantages of OCI … 1-26
• Parts of the OCI… 1-27
• Procedural and Non-Procedural Elements… 1-27
• Building an OCI Application … 1-28
• Overview of Oracle Data Provider for .NET (ODP.NET)… 1-29
• Using ODP.NET in a Simple Application… 1-29
• Overview of Oracle Objects for OLE (OO4O) … 1-30
• OO4O Automation Server… 1-31
• OO4O Object Model… 1-32
• OraSession … 1-33
• OraServer… 1-33
• OraDatabase… 1-34
• OraDynaset… 1-34
• OraField … 1-35
• OraMetaData and OraMDAttribute … 1-35
• OraParameters and OraParameter … 1-35
• OraParamArray … 1-36
• OraSQLStmt … 1-36
• OraAQ… 1-36
• OraAQMsg … 1-37
• OraAQAgent … 1-37
• Support for Oracle LOB and Object Datatypes … 1-37
• OraBLOB and OraCLOB … 1-38
• OraBFILE … 1-38
• Oracle Data Control… 1-39
• Oracle Objects for OLE C++ Class Library … 1-39
• Additional Sources of Information … 1-39
• Choosing a Programming Environment… 1-40
• Choosing Whether to Use OCI or a Precompiler… 1-40
• Using Built-In Packages and Libraries … 1-41
• Java Compared to PL/SQL … 1-41
• PL/SQL Is Optimized for Database Access… 1-42
• PL/SQL Is Integrated with the Database… 1-42
• Both Java and PL/SQL Have Object-Oriented Features… 1-42
• Java Is Used for Open Distributed Applications … 1-42
• Part II Designing the Database
• 2 Selecting a Datatype
• Summary of Oracle Built-In Datatypes… 2-2
• Representing Character Data… 2-8
• Column Lengths for Single-Byte and Multibyte Character Sets… 2-9
• Implicit Conversion Between CHAR/VARCHAR2 and NCHAR/NVARCHAR2 .. 2-10
• Comparison Semantics … 2-10
• Representing Numeric Data with Number and Floating-Point Datatypes… 2-11
• Floating-Point Number System Concepts… 2-12
• About Floating-Point Formats … 2-12
• Representing Special Values with Native Floating-Point Formats … 2-14
• Behavior of Special Values for Native Floating-Point Datatypes… 2-15
• Rounding of Native Floating-Point Datatypes… 2-15
• Comparison Operators for Native Floating-Point Datatypes … 2-16
• Arithmetic Operators for Native Floating-Point Datatypes… 2-16
• Conversion Functions for Native Floating-Point Datatypes … 2-16
• Exceptions for Native Floating-Point Datatypes… 2-17
• Client Interfaces for Native Floating-Point Datatypes … 2-18
• SQL Native Floating-Point Datatypes … 2-18
• OCI Native Floating-Point Datatypes SQLT_BFLOAT and SQLT_BDOUBLE… 2-18
• Native Floating-Point Datatypes Supported in Oracle OBJECT Types… 2-18
• Pro*C/C++ Support for Native Floating-Point Datatypes… 2-19
• Storing Data Using the NUMBER Datatype… 2-19
• Representing Date and Time Data … 2-20
• Date Format … 2-21
• Checking If Two DATE Values Refer to the Same Day … 2-21
• Displaying the Current Date and Time … 2-21
• Setting SYSDATE to a Constant Value… 2-21
• Printing a Date with BC/AD Notation … 2-21
• Time Format … 2-22
• Performing Date Arithmetic … 2-22
• Converting Between Datetime Types… 2-23
• Handling Time Zones … 2-23
• Importing and Exporting Datetime Types … 2-24
• Establishing Year 2000 Compliance… 2-24
• Oracle Server Year 2000 Compliance … 2-25
• Centuries and the Year 2000 … 2-25
• Examples of The RR Date Format… 2-26
• Examples of The CC Date Format… 2-27
• Storing Dates in Character Datatypes … 2-27
• Viewing Date Settings… 2-28
• Altering Date Settings… 2-29
• Troubleshooting Y2K Problems in Applications … 2-29
• Representing Conditional Expressions as Data… 2-32
• Representing Geographic Coordinate Data … 2-33
• Representing Image, Audio, and Video Data… 2-33
• Representing Searchable Text Data… 2-34
• Representing Large Amounts of Data … 2-34
• Using RAW and LONG RAW Datatypes … 2-35
• Addressing Rows Directly with the ROWID Datatype… 2-36
• Extended ROWID Format … 2-36
• Different Forms of the ROWID … 2-37
• ROWID Pseudocolumn … 2-37
• Internal ROWID … 2-37
• External Character ROWID … 2-37
• External Binary ROWID… 2-38
• ROWID Migration and Compatibility Issues… 2-38
• Accessing Oracle Database Version 7 from an Oracle9i Client … 2-39
• Accessing an Oracle9i Database from a Client of Oracle Database Version 7 … 2-39
• Import and Export… 2-39
• ANSI/ISO, DB2, and SQL/DS Datatypes … 2-39
• How Oracle Database Converts Datatypes … 2-40
• Datatype Conversion During Assignments… 2-41
• Datatype Conversion During Expression Evaluation … 2-43
• Representing Dynamically Typed Data … 2-44
• Representing XML Data … 2-47
• 3 Maintaining Data Integrity Through Constraints
• Overview of Integrity Constraints… 3-2
• When to Enforce Business Rules with Integrity Constraints … 3-2
• Example of an Integrity Constraint for a Business Rule… 3-2
• When to Enforce Business Rules in Applications … 3-3
• Creating Indexes for Use with Constraints… 3-3
• When to Use NOT NULL Integrity Constraints … 3-3
• When to Use Default Column Values… 3-4
• Setting Default Column Values … 3-5
• Choosing a Table\’s Primary Key … 3-5
• When to Use UNIQUE Key Integrity Constraints … 3-6
• Constraints On Views: for Performance, Not Data Integrity … 3-7
• Enforcing Referential Integrity with Constraints… 3-8
• About Nulls and Foreign Keys … 3-10
• Defining Relationships Between Parent and Child Tables… 3-10
• No Constraints on the Foreign Key … 3-10
• NOT NULL Constraint on the Foreign Key … 3-10
• UNIQUE Constraint on the Foreign Key… 3-11
• UNIQUE and NOT NULL Constraints on the Foreign Key … 3-11
• Rules for Multiple FOREIGN KEY Constraints … 3-11
• Deferring Constraint Checks… 3-12
• Guidelines for Deferring Constraint Checks … 3-12
• Select Appropriate Data … 3-12
• Ensure Constraints Are Created Deferrable… 3-12
• Set All Constraints Deferred… 3-13
• Check the Commit (Optional) … 3-13
• Managing Constraints That Have Associated Indexes… 3-14
• Minimizing Space and Time Overhead for Indexes Associated with Constraints … 3-14
• Guidelines for Indexing Foreign Keys … 3-14
• About Referential Integrity in a Distributed Database… 3-15
• When to Use CHECK Integrity Constraints … 3-15
• Restrictions on CHECK Constraints … 3-16
• Designing CHECK Constraints … 3-16
• Rules for Multiple CHECK Constraints … 3-17
• Choosing Between CHECK and NOT NULL Integrity Constraints … 3-17
• Examples of Defining Integrity Constraints … 3-17
• Example: Defining Integrity Constraints with the CREATE TABLE Command… 3-18
• Example: Defining Constraints with the ALTER TABLE Command … 3-18
• Privileges Required to Create Constraints … 3-19
• Naming Integrity Constraints … 3-19
• Enabling and Disabling Integrity Constraints … 3-19
• Why Disable Constraints? … 3-20
• About Exceptions to Integrity Constraints … 3-20
• Enabling Constraints … 3-20
• Creating Disabled Constraints … 3-21
• Enabling and Disabling Existing Integrity Constraints … 3-21
• Enabling Existing Constraints … 3-21
• Disabling Existing Constraints … 3-22
• Tip: Using the Data Dictionary to Find Constraints … 3-22
• Guidelines for Enabling and Disabling Key Integrity Constraints … 3-23
• Fixing Constraint Exceptions … 3-23
• Altering Integrity Constraints… 3-23
• Renaming Integrity Constraints … 3-24
• Dropping Integrity Constraints … 3-25
• Managing FOREIGN KEY Integrity Constraints … 3-26
• Datatypes and Names for Foreign Key Columns … 3-26
• Limit on Columns in Composite Foreign Keys… 3-26
• Foreign Key References Primary Key by Default … 3-26
• Privileges Required to Create FOREIGN KEY Integrity Constraints … 3-27
• Choosing How Foreign Keys Enforce Referential Integrity … 3-27
• Viewing Definitions of Integrity Constraints … 3-28
• Examples of Defining Integrity Constraints … 3-28
• Example 1: Listing All of Your Accessible Constraints … 3-29
• Example 2: Distinguishing NOT NULL Constraints from CHECK Constraints 3-30
• Example 3: Listing Column Names that Constitute an Integrity Constraint … 3-30
• 4 Selecting an Index Strategy
• Guidelines for Application-Specific Indexes… 4-2
• Create Indexes After Inserting Table Data … 4-2
• Switch Your Temporary Tablespace to Avoid Space Problems Creating Indexes … 4-3
• Index the Correct Tables and Columns… 4-3
• Limit the Number of Indexes for Each Table … 4-4
• Choose the Order of Columns in Composite Indexes… 4-4
• Gather Statistics to Make Index Usage More Accurate… 4-5
• Drop Indexes That Are No Longer Required … 4-6
• Privileges Required to Create an Index … 4-6
• Creating Indexes: Basic Examples … 4-6
• When to Use Domain Indexes… 4-7
• When to Use Function-Based Indexes … 4-8
• Advantages of Function-Based Indexes … 4-9
• Examples of Function-Based Indexes … 4-10
• Example: Function-Based Index for Case-Insensitive Searches… 4-10
• Example: Precomputing Arithmetic Expressions with a Function-Based Index … 4-10
• Example: Function-Based Index for Language-Dependent Sorting … 4-11
• Restrictions for Function-Based Indexes … 4-11
• 5 How Oracle Database Processes SQL Statements
• Overview of SQL Statement Execution … 5-2
• Identifying Extensions to SQL92 (FIPS Flagging) … 5-2
• Grouping Operations into Transactions… 5-4
• Improving Transaction Performance… 5-4
• Committing Transactions … 5-5
• Rolling Back Transactions … 5-5
• Defining Transaction Savepoints … 5-6
• An Example of COMMIT, SAVEPOINT, and ROLLBACK … 5-6
• Privileges Required for Transaction Management … 5-7
• Ensuring Repeatable Reads with Read-Only Transactions … 5-7
• Using Cursors within Applications … 5-8
• Declaring and Opening Cursors … 5-9
• Using a Cursor to Execute Statements Again… 5-9
• Closing Cursors … 5-10
• Cancelling Cursors … 5-10
• Locking Data Explicitly … 5-10
• Choosing a Locking Strategy … 5-11
• When to Lock with ROW SHARE and ROW EXCLUSIVE Mode … 5-12
• When to Lock with SHARE Mode … 5-12
• When to Lock with SHARE ROW EXCLUSIVE Mode… 5-14
• When to Lock in EXCLUSIVE Mode … 5-15
• Privileges Required … 5-15
• Letting Oracle Database Control Table Locking… 5-15
• Explicitly Acquiring Row Locks … 5-16
• About User Locks… 5-17
• When to Use User Locks… 5-18
• Example of a User Lock … 5-18
• Viewing and Monitoring Locks… 5-19
• Using Serializable Transactions for Concurrency Control … 5-19
• How Serializable Transactions Interact… 5-21
• Setting the Isolation Level of a Transaction… 5-23
• The INITRANS Parameter … 5-23
• Referential Integrity and Serializable Transactions… 5-23
• Using SELECT FOR UPDATE… 5-24
• READ COMMITTED and SERIALIZABLE Isolation… 5-25
• Transaction Set Consistency … 5-25
• Comparison of READ COMMITTED and SERIALIZABLE Transactions… 5-26
• Choosing an Isolation Level for Transactions… 5-27
• Application Tips for Transactions… 5-28
• Autonomous Transactions… 5-28
• Examples of Autonomous Transactions … 5-32
• Entering a Buy Order… 5-32
• Example: Making a Bank Withdrawal … 5-33
• Defining Autonomous Transactions… 5-36
• Restrictions on Autonomous Transactions… 5-37
• Resuming Execution After a Storage Error Condition … 5-38
• What Operations Can Be Resumed After an Error Condition?… 5-38
• Limitations on Resuming Operations After an Error Condition… 5-38
• Writing an Application to Handle Suspended Storage Allocation … 5-39
• Example of Resumable Storage Allocation … 5-39
• 6 Coding Dynamic SQL Statements
• What Is Dynamic SQL?… 6-2
• Why Use Dynamic SQL? … 6-3
• Executing DDL and SCL Statements in PL/SQL … 6-3
• Executing Dynamic Queries… 6-4
• Referencing Database Objects that Do Not Exist at Compilation… 6-4
• Optimizing Execution Dynamically … 6-5
• Executing Dynamic PL/SQL Blocks … 6-6
• Performing Dynamic Operations Using Invoker\’s Rights … 6-7
• A Dynamic SQL Scenario Using Native Dynamic SQL … 6-7
• Sample DML Operation Using Native Dynamic SQL… 6-8
• Sample DDL Operation Using Native Dynamic SQL … 6-9
• Sample Single-Row Query Using Native Dynamic SQL … 6-9
• Sample Multiple-Row Query Using Native Dynamic SQL… 6-10
• Choosing Between Native Dynamic SQL and the DBMS_SQL Package… 6-11
• Advantages of Native Dynamic SQL… 6-11
• Native Dynamic SQL is Easy to Use… 6-12
• Native Dynamic SQL is Faster than DBMS_SQL… 6-14
• Performance Tip: Using Bind Variables… 6-14
• Native Dynamic SQL Supports User-Defined Types… 6-15
• Native Dynamic SQL Supports Fetching Into Records… 6-15
• Advantages of the DBMS_SQL Package … 6-16
• DBMS_SQL is Supported in Client-Side Programs… 6-16
• DBMS_SQL Supports DESCRIBE … 6-16
• DBMS_SQL Supports SQL Statements Larger than 32KB… 6-16
• DBMS_SQL Lets You Reuse SQL Statements… 6-16
• Examples of DBMS_SQL Package Code and Native Dynamic SQL Code… 6-17
• Querying Using Dynamic SQL: Example … 6-17
• Performing DML Using Dynamic SQL: Example… 6-19
• Performing DML with RETURNING Clause Using Dynamic SQL: Example … 6-19
• Using Dynamic SQL in Languages Other Than PL/SQL… 6-20
• 7 Using Procedures and Packages
• Overview of PL/SQL Program Units … 7-2
• Anonymous Blocks … 7-2
• Stored Program Units (Procedures, Functions, and Packages) … 7-4
• Naming Procedures and Functions … 7-5
• Parameters for Procedures and Functions… 7-5
• Parameter Modes … 7-6
• Parameter Datatypes … 7-7
• %TYPE and %ROWTYPE Attributes … 7-7
• Tables and Records … 7-8
• Default Parameter Values … 7-9
• Creating Stored Procedures and Functions … 7-9
• Privileges to Create Procedures and Functions … 7-10
• Altering Stored Procedures and Functions … 7-11
• Dropping Procedures and Functions … 7-11
• Privileges to Drop Procedures and Functions … 7-12
• External Procedures … 7-12
• PL/SQL Packages … 7-12
• Example of a PL/SQL Package Specification and Body … 7-13
• PL/SQL Object Size Limitation… 7-14
• Size Limitation by Version… 7-14
• Creating Packages … 7-15
• Creating Packaged Objects … 7-15
• Privileges to Create or Drop Packages … 7-16
• Naming Packages and Package Objects … 7-16
• Package Invalidations and Session State … 7-16
• Packages Supplied With Oracle Database … 7-17
• Overview of Bulk Binds … 7-17
• When to Use Bulk Binds… 7-18
• DML Statements that Reference Collections… 7-18
• SELECT Statements that Reference Collections… 7-19
• FOR Loops that Reference Collections and the Returning Into Clause … 7-19
• Triggers … 7-20
• Hiding PL/SQL Code with the PL/SQL Wrapper … 7-20
• Compiling PL/SQL Procedures for Native Execution … 7-21
• Remote Dependencies … 7-21
• Timestamps… 7-21
• Disadvantages of the Timestamp Model … 7-22
• Signatures … 7-23
• When Does a Signature Change? … 7-25
• Modes… 7-25
• Default Parameter Values … 7-26
• Examples of Changing Procedure Signatures… 7-26
• Controlling Remote Dependencies … 7-28
• Dependency Resolution… 7-29
• Suggestions for Managing Dependencies… 7-29
• Cursor Variables … 7-30
• Declaring and Opening Cursor Variables … 7-31
• Examples of Cursor Variables… 7-31
• Fetching Data … 7-31
• Implementing Variant Records … 7-32
• Handling PL/SQL Compile-Time Errors… 7-33
• Handling Run-Time PL/SQL Errors… 7-35
• Declaring Exceptions and Exception Handling Routines … 7-36
• Unhandled Exceptions … 7-38
• Handling Errors in Distributed Queries … 7-38
• Handling Errors in Remote Procedures … 7-38
• Debugging Stored Procedures… 7-40
• Calling Stored Procedures… 7-43
• A Procedure or Trigger Calling Another Procedure … 7-43
• Interactively Calling Procedures From Oracle Database Tools … 7-44
• Calling Procedures within 3GL Applications … 7-45
• Name Resolution When Calling Procedures… 7-45
• Privileges Required to Execute a Procedure … 7-45
• Specifying Values for Procedure Arguments … 7-46
• Calling Remote Procedures … 7-47
• Remote Procedure Calls and Parameter Values … 7-47
• Referencing Remote Objects … 7-48
• Synonyms for Procedures and Packages … 7-49
• Calling Stored Functions from SQL Expressions… 7-50
• Using PL/SQL Functions … 7-50
• Syntax for SQL Calling a PL/SQL Function… 7-51
• Naming Conventions … 7-51
• Name Precedence … 7-52
• Example of Calling a PL/SQL Function from SQL… 7-52
• Arguments … 7-53
• Using Default Values … 7-53
• Privileges … 7-54
• Requirements for Calling PL/SQL Functions from SQL Expressions… 7-54
• Controlling Side Effects … 7-55
• Restrictions … 7-55
• Declaring a Function… 7-56
• Parallel Query and Parallel DML… 7-57
• PRAGMA RESTRICT_REFERENCES – for Backward Compatibility… 7-59
• Using the Keyword TRUST … 7-61
• Differences between Static and Dynamic SQL Statements. … 7-62
• Overloading Packaged PL/SQL Functions… 7-63
• Serially Reusable PL/SQL Packages… 7-63
• Package States … 7-63
• Why Serially Reusable Packages? … 7-64
• Syntax of Serially Reusable Packages… 7-64
• Semantics of Serially Reusable Packages … 7-65
• Examples of Serially Reusable Packages… 7-65
• Example 1: How Package Variables Act Across Call Boundaries… 7-65
• Example 2: How Package Variables Act Across Call Boundaries… 7-66
• Example 3: Open Cursors in Serially Reusable Packages at Call Boundaries… 7-68
• Returning Large Amounts of Data from a Function… 7-69
• Coding Your Own Aggregate Functions … 7-71
• 8 Calling External Procedures
• Overview of Multi-Language Programs… 8-2
• What Is an External Procedure? … 8-3
• Overview of The Call Specification for External Procedures … 8-4
• Loading External Procedures… 8-4
• Loading Java Class Methods… 8-5
• Loading External C Procedures… 8-5
• Publishing External Procedures… 8-10
• The AS LANGUAGE Clause for Java Class Methods… 8-12
• The AS LANGUAGE Clause for External C Procedures … 8-12
• LIBRARY… 8-12
• NAME … 8-12
• LANGUAGE … 8-12
• CALLING STANDARD… 8-12
• WITH CONTEXT… 8-13
• PARAMETERS… 8-13
• AGENT IN … 8-13
• Publishing Java Class Methods … 8-13
• Publishing External C Procedures… 8-14
• Locations of Call Specifications… 8-14
• Example: Locating a Call Specification in a PL/SQL Package Body … 8-15
• Example: Locating a Call Specification in an Object Type Specification… 8-16
• Example: Locating a Call Specification in an Object Type Body… 8-16
• Passing Parameters to External C Procedures with Call Specifications… 8-18
• Specifying Datatypes… 8-19
• External Datatype Mappings … 8-21
• BY VALUE/REFERENCE for IN and IN OUT Parameter Modes … 8-23
• The PARAMETERS Clause … 8-24
• Overriding Default Datatype Mapping… 8-25
• Specifying Properties … 8-25
• INDICATOR… 8-27
• LENGTH and MAXLEN … 8-27
• CHARSETID and CHARSETFORM … 8-28
• Repositioning Parameters … 8-29
• Using SELF … 8-29
• Passing Parameters by Reference… 8-32
• WITH CONTEXT… 8-33
• Inter-Language Parameter Mode Mappings … 8-33
• Executing External Procedures with the CALL Statement… 8-33
• Preconditions for External Procedures … 8-34
• Privileges of External Procedures … 8-35
• Managing Permissions … 8-35
• Creating Synonyms for External Procedures … 8-35
• CALL Statement Syntax… 8-36
• Calling Java Class Methods… 8-36
• How the Database Server Calls External C Procedures… 8-37
• Handling Errors and Exceptions in Multi-Language Programs … 8-38
• Generic Compile Time Call specification Errors… 8-38
• C Exception Handling… 8-38
• Using Service Procedures with External C Procedures … 8-38
• OCIExtProcAllocCallMemory… 8-38
• OCIExtProcRaiseExcp… 8-44
• OCIExtProcRaiseExcpWithMsg … 8-46
• Doing Callbacks with External C Procedures… 8-47
• OCIExtProcGetEnv … 8-47
• Object Support for OCI Callbacks… 8-48
• Restrictions on Callbacks… 8-49
• Debugging External Procedures… 8-50
• Using Package DEBUG_EXTPROC … 8-51
• Demo Program… 8-51
• Guidelines for External C Procedures … 8-51
• Restrictions on External C Procedures … 8-53
• Part III The Active Database
• 9 Using Triggers
• Designing Triggers … 9-2
• Creating Triggers … 9-2
• Types of Triggers … 9-3
• Overview of System Events … 9-4
• Getting the Attributes of System Events… 9-4
• Naming Triggers … 9-4
• When Is the Trigger Fired? … 9-5
• Do Import and SQL*Loader Fire Triggers? … 9-5
• How Column Lists Affect UPDATE Triggers … 9-6
• Controlling When a Trigger Is Fired (BEFORE and AFTER Options) … 9-6
• Ordering of Triggers … 9-7
• Modifying Complex Views (INSTEAD OF Triggers)… 9-8
• Views that Require INSTEAD OF Triggers … 9-9
• INSTEAD OF Trigger Example … 9-10
• Object Views and INSTEAD OF Triggers… 9-11
• Triggers on Nested Table View Columns… 9-12
• Firing Triggers One or Many Times (FOR EACH ROW Option) … 9-13
• Firing Triggers Based on Conditions (WHEN Clause) … 9-14
• Coding the Trigger Body … 9-15
• Example: Monitoring Logons with a Trigger… 9-15
• Example: Calling a Java Procedure from a Trigger… 9-16
• Accessing Column Values in Row Triggers … 9-17
• Example: Modifying LOB Columns with a Trigger … 9-18
• INSTEAD OF Triggers on Nested Table View Columns… 9-18
• Avoiding Name Conflicts with Triggers (REFERENCING Option) … 9-19
• Detecting the DML Operation That Fired a Trigger… 9-19
• Error Conditions and Exceptions in the Trigger Body … 9-20
• Triggers and Handling Remote Exceptions … 9-20
• Restrictions on Creating Triggers … 9-21
• Who Is the Trigger User?… 9-25
• Privileges Needed to Work with Triggers … 9-26
• Compiling Triggers … 9-26
• Dependencies for Triggers … 9-27
• Recompiling Triggers … 9-27
• Modifying Triggers … 9-28
• Debugging Triggers … 9-28
• Enabling and Disabling Triggers… 9-28
• Enabling Triggers … 9-28
• Disabling Triggers … 9-29
• Viewing Information About Triggers … 9-29
• Examples of Trigger Applications … 9-31
• Auditing with Triggers: Example … 9-32
• Integrity Constraints and Triggers: Examples … 9-37
• Referential Integrity Using Triggers … 9-38
• Foreign Key Trigger for Child Table … 9-39
• UPDATE and DELETE RESTRICT Trigger for Parent Table … 9-40
• UPDATE and DELETE SET NULL Triggers for Parent Table: Example … 9-41
• DELETE Cascade Trigger for Parent Table: Example… 9-41
• UPDATE Cascade Trigger for Parent Table: Example … 9-42
• Trigger for Complex Check Constraints: Example … 9-43
• Complex Security Authorizations and Triggers: Example … 9-45
• Transparent Event Logging and Triggers… 9-46
• Derived Column Values and Triggers: Example … 9-46
• Building Complex Updatable Views Using Triggers: Example … 9-47
• Tracking System Events Using Triggers … 9-49
• Fine-Grained Access Control Using Triggers: Example… 9-49
• CALL Syntax… 9-50
• Responding to System Events through Triggers … 9-50
• 10 Working With System Events
• Event Attribute Functions… 10-2
• List of Database Events… 10-7
• System Events … 10-7
• Client Events … 10-8
• 11 Using the Publish-Subscribe Model for Applications
• Introduction to Publish-Subscribe… 11-2
• Publish-Subscribe Architecture… 11-3
• Publish-Subscribe Concepts… 11-3
• Examples of a Publish-Subscribe Mechanism… 11-6
• Part IV Developing Specialized Applications
• 12 Using Regular Expressions With Oracle Database
• What are Regular Expressions?… 12-2
• Oracle Database Regular Expression Support… 12-2
• Oracle Database SQL Functions for Regular Expressions… 12-2
• Metacharacters Supported in Regular Expressions … 12-4
• Constructing Regular Expressions … 12-5
• Basic String Matching with Regular Expressions … 12-5
• Regular Expression Operations on Subexpressions … 12-5
• Regular Expression Operator and Metacharacter Usage… 12-5
• 13 Developing Web Applications with PL/SQL
• PL/SQL Web Applications … 13-2
• PL/SQL Gateway… 13-3
• Configuring mod_plsql… 13-4
• Uploading and Downloading Files With PL/SQL Gateway … 13-4
• Uploading Files to the Database… 13-4
• Downloading Files From the Database … 13-5
• Custom Authentication With PL/SQL Gateway … 13-5
• PL/SQL Web Toolkit… 13-6
• Generating HTML Output from PL/SQL… 13-8
• Passing Parameters to a PL/SQL Web Application… 13-9
• Passing List and Dropdown List Parameters from an HTML Form… 13-9
• Passing Radio Button and Checkbox Parameters from an HTML Form… 13-10
• Passing Entry Field Parameters from an HTML Form… 13-10
• Passing Hidden Parameters from an HTML Form… 13-12
• Uploading a File from an HTML Form … 13-13
• Submitting a Completed HTML Form … 13-13
• Handling Missing Input from an HTML Form … 13-13
• Maintaining State Information Between Web Pages … 13-14
• Performing Network Operations within PL/SQL Stored Procedures … 13-15
• Sending E-Mail from PL/SQL … 13-15
• Getting a Host Name or Address from PL/SQL … 13-16
• Working with TCP/IP Connections from PL/SQL… 13-16
• Retrieving the Contents of an HTTP URL from PL/SQL… 13-16
• Working with Tables, Image Maps, Cookies, and CGI Variables from PL/SQL … 13-19
• Embedding PL/SQL Code in Web Pages (PL/SQL Server Pages)… 13-19
• Choosing a Software Configuration… 13-20
• Choosing Between PSP and the PL/SQL Web Toolkit… 13-20
• How PSP Relates to Other Scripting Solutions … 13-20
• Writing the Code and Content for the PL/SQL Server Page … 13-21
• The Format of the PSP File … 13-21
• Syntax of PL/SQL Server Page Elements … 13-27
• Page Directive … 13-27
• Procedure Directive … 13-27
• Parameter Directive … 13-28
• Include Directive … 13-28
• Declaration Block… 13-28
• Code Block (Scriptlet) … 13-28
• Expression Block… 13-29
• Loading the PL/SQL Server Page into the Database as a Stored Procedure… 13-29
• Running a PL/SQL Server Page Through a URL … 13-30
• Sample PSP URLs… 13-30
• Examples of PL/SQL Server Pages… 13-31
• Sample Table … 13-31
• Dumping the Sample Table … 13-32
• Printing the Sample Table using a Loop… 13-32
• Allowing a User Selection … 13-33
• Sample HTML Form to Call a PL/SQL Server Page… 13-35
• Debugging PL/SQL Server Page Problems… 13-38
• Putting an Application using PL/SQL Server Pages into Production … 13-39
• Enabling PL/SQL Web Applications for XML … 13-41
• 14 Porting Non-Oracle Applications to Oracle Database 10g
• Performing Natural Joins and Inner Joins… 14-2
• Migrating a Schema and Data from Another Database System… 14-2
• Performing Several Comparisons within a Query… 14-2
• 15 Using Flashback Features
• Overview of Flashback Features… 15-2
• Application Development Features … 15-2
• Database Administration Features… 15-3
• Database Administration Tasks Before Using Flashback Features … 15-4
• Using Flashback Query (SELECT … AS OF) … 15-5
• Examining Past Data: Example … 15-6
• Tips for Using Flashback Query… 15-6
• Using the DBMS_FLASHBACK Package… 15-7
• Using ORA_ROWSCN … 15-9
• Using Flashback Version Query… 15-10
• Using Flashback Transaction Query … 15-12
• Flashback Transaction Query and Flashback Version Query: Example… 15-13
• Flashback Tips… 15-15
• Flashback Tips – Performance … 15-15
• Flashback Tips – General… 15-16
• 16 Using Oracle XA with Transaction Monitors
• X/Open Distributed Transaction Processing (DTP) … 16-2
• Required Public Information… 16-4
• XA and the Two-Phase Commit Protocol … 16-5
• Transaction Processing Monitors (TPMs) … 16-5
• Support for Dynamic and Static Registration… 16-5
• Oracle XA Library Interface Subroutines … 16-6
• XA Library Subroutines … 16-6
• Extensions to the XA Interface… 16-7
• Developing and Installing Applications That Use the XA Libraries … 16-8
• Responsibilities of the DBA or System Administrator… 16-8
• Responsibilities of the Application Developer… 16-9
• Defining the xa_open String… 16-9
• Syntax of the xa_open String … 16-10
• Required Fields … 16-11
• Optional Fields… 16-12
• Interfacing XA with Precompilers and OCIs… 16-16
• Using Precompilers with the Oracle XA Library… 16-16
• Using Precompilers with the Default Database … 16-16
• Using Precompilers with a Named Database… 16-17
• Using OCI with the Oracle XA Library … 16-18
• Transaction Control using XA … 16-19
• Examples of Precompiler Applications … 16-20
• Migrating Precompiler or OCI Applications to TPM Applications … 16-21
• XA Library Thread Safety… 16-23
• Specifying Threading in the Open String … 16-23
• Restrictions on Threading in XA … 16-23
• Troubleshooting XA Applications… 16-24
• XA Trace Files … 16-24
• The xa_open string DbgFl … 16-24
• Trace File Locations… 16-25
• Trace File Examples… 16-25
• In-Doubt or Pending Transactions… 16-26
• Oracle Database SYS Account Tables … 16-26
• XA Issues and Restrictions… 16-27
• Changes to Oracle XA Support … 16-32
• XA Changes from Release 8.0 to Release 8.1 … 16-32
• XA Changes from Release 7.3 to Release 8.0 … 16-32
• Session Caching Is No Longer Needed … 16-33
• Dynamic Registration Is Supported … 16-33
• Loosely Coupled Transaction Branches Are Supported … 16-33
• SQLLIB Is Not Needed for OCI Applications … 16-34
• No Installation Script Is Needed to Run XA … 16-34
• XA Library Use with Oracle Real Application Clusters Option on All Platforms .. 16-34
• Transaction Recovery for Oracle Real Application Clusters Has Been Improved .. 16-34
• Both Global and Local Transactions Are Possible … 16-34
• The xa_open String Has Been Modified… 16-35