Oracle Database Application Developer’s Guide – Fundamentals

Developer’s Kit, Programmer’s Guide and Oracle XML DB Developer Guide. …… be loaded in a database scheme (using Oracle JDeveloper, third ….

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

 

Download Oracle Database Application Developer's Guide - Fundamentals pdf from www.stanford.edu, 582 pages, 4844.15KB.