Oracle Database Application Developers Guide - Fundamentals, 10g Release 2 (10.2) b14251

Contents......Page 3
Documentation Accessibility......Page 19
Related Documents......Page 20
Conventions......Page 21
Oracle Database 10g Release 2 (10.2) New Features......Page 23
Oracle Database 10g Release 1 (10.1) New Features......Page 24
Overview of Oracle Application Development......Page 27
What Is PL/SQL?......Page 28
Advantages of PL/SQL......Page 29
Overview of Java Support Built Into the Database......Page 32
Overview of Oracle JVM......Page 33
Overview of Oracle Extensions to JDBC......Page 34
Overview of Oracle SQLJ......Page 37
Overview of Java Stored Procedures......Page 39
Overview of Oracle Database Web Services......Page 40
Overview of Pro*C/C++......Page 41
Implementing a Pro*C/C++ Application......Page 42
Highlights of Pro*C/C++ Features......Page 43
How You Implement a Pro*COBOL Application......Page 44
Overview of OCI and OCCI......Page 45
Parts of the OCI......Page 46
Building an OCI Application......Page 47
Using ODP.NET in a Simple Application......Page 48
Overview of Oracle Objects for OLE (OO4O)......Page 49
OO4O Automation Server......Page 50
OO4O Object Model......Page 51
Support for Oracle LOB and Object Datatypes......Page 54
Additional Sources of Information......Page 55
Using Built-In Packages and Libraries......Page 56
Java Compared to PL/SQL......Page 57
Part I SQL for Application Developers......Page 59
Deciding How to Group Operations in Transactions......Page 61
Committing Transactions......Page 62
Rolling Back Transactions......Page 64
Ensuring Repeatable Reads with Read-Only Transactions......Page 65
Declaring and Opening Cursors......Page 66
Closing Cursors......Page 67
Locking Data Explicitly......Page 68
Choosing a Locking Strategy......Page 69
Letting Oracle Database Control Table Locking......Page 71
Explicitly Acquiring Row Locks......Page 72
Example of a User Lock......Page 73
Using Serializable Transactions for Concurrency Control......Page 74
How Serializable Transactions Interact......Page 75
Setting the Isolation Level of a Transaction......Page 76
Referential Integrity and Serializable Transactions......Page 77
READ COMMITTED and SERIALIZABLE Isolation......Page 78
Autonomous Transactions......Page 80
Examples of Autonomous Transactions......Page 82
Defining Autonomous Transactions......Page 86
Limitations on Resuming Operations After an Error Condition......Page 87
Example of Resumable Storage Allocation......Page 88
Representing Data with SQL Datatypes: Overview......Page 91
Representing Character Data: Overview......Page 92
Choosing Between the CHAR and VARCHAR2 Datatypes......Page 93
Using Character Literals in SQL Statements......Page 94
What Are the Numeric Datatypes?......Page 95
Using Floating-Point Number Formats......Page 96
Using Comparison Operators for Native Floating-Point Datatypes......Page 99
Using Conversion Functions with Native Floating-Point Datatypes......Page 100
Client Interfaces for Native Floating-Point Datatypes......Page 101
Representing Datetime Data: Overview......Page 102
Manipulating the Date Format......Page 103
Performing Date Arithmetic......Page 104
Importing and Exporting Datetime Types......Page 105
Representing Large Amounts of Data......Page 106
Representing XML......Page 108
Representing Dynamically Typed Data......Page 109
Representing Data with ANSI/ISO, DB2, and SQL/DS Datatypes......Page 111
Representing Conditional Expressions as Data......Page 112
Identifying Rows by Address......Page 113
Accessing the ROWID Datatype......Page 114
Datatype Conversion During Assignments......Page 115
Datatype Conversion During Expression Evaluation......Page 116
What Are Regular Expressions?......Page 119
Oracle Database Implementation of Regular Expressions......Page 120
Oracle Database Support for the POSIX Regular Expression Standard......Page 121
POSIX Metacharacters in Oracle Database Regular Expressions......Page 122
Regular Expression Operator Multilingual Enhancements......Page 124
Perl-Influenced Extensions in Oracle Regular Expressions......Page 125
Using an Integrity Constraint to Enforce a Phone Number Format......Page 127
Using Back References to Reposition Characters......Page 128
Guidelines for Application-Specific Indexes......Page 131
Index the Correct Tables and Columns......Page 132
Choose the Order of Columns in Composite Indexes......Page 133
Drop Indexes That Are No Longer Required......Page 134
Creating Indexes: Basic Examples......Page 135
Advantages of Function-Based Indexes......Page 136
Examples of Function-Based Indexes......Page 137
Restrictions for Function-Based Indexes......Page 138
When to Enforce Business Rules with Integrity Constraints......Page 141
When to Use NOT NULL Integrity Constraints......Page 142
When to Use Default Column Values......Page 143
Choosing a Table's Primary Key......Page 144
Constraints On Views: for Performance, Not Data Integrity......Page 145
Enforcing Referential Integrity with Constraints......Page 146
About Nulls and Foreign Keys......Page 147
Defining Relationships Between Parent and Child Tables......Page 148
Deferring Constraint Checks......Page 149
Minimizing Space and Time Overhead for Indexes Associated with Constraints......Page 150
When to Use CHECK Integrity Constraints......Page 151
Rules for Multiple CHECK Constraints......Page 152
Example: Defining Integrity Constraints with the CREATE TABLE Command......Page 153
Enabling and Disabling Integrity Constraints......Page 154
Enabling Constraints......Page 155
Enabling and Disabling Existing Integrity Constraints......Page 156
Altering Integrity Constraints......Page 157
Renaming Integrity Constraints......Page 158
Limit on Columns in Composite Foreign Keys......Page 159
Choosing How Foreign Keys Enforce Referential Integrity......Page 160
Examples of Defining Integrity Constraints......Page 161
Part II PL/SQL for Application Developers......Page 165
Overview of PL/SQL Program Units......Page 167
Anonymous Blocks......Page 168
Stored Program Units (Procedures, Functions, and Packages)......Page 169
Remote Dependencies......Page 181
Signatures......Page 182
Controlling Remote Dependencies......Page 186
Declaring and Opening Cursor Variables......Page 188
Examples of Cursor Variables......Page 189
Handling PL/SQL Compile-Time Errors......Page 190
Handling Run-Time PL/SQL Errors......Page 192
Declaring Exceptions and Exception Handling Routines......Page 193
Handling Errors in Remote Procedures......Page 194
Debugging Stored Procedures......Page 195
Calling Stored Procedures......Page 197
Interactively Calling Procedures From Oracle Database Tools......Page 198
Privileges Required to Execute a Procedure......Page 199
Calling Remote Procedures......Page 200
Referencing Remote Objects......Page 201
Calling Stored Functions from SQL Expressions......Page 202
Naming Conventions......Page 203
Requirements for Calling PL/SQL Functions from SQL Expressions......Page 205
Controlling Side Effects......Page 206
Serially Reusable PL/SQL Packages......Page 212
Returning Large Amounts of Data from a Function......Page 217
Coding Your Own Aggregate Functions......Page 218
Programming with Dynamic SQL......Page 219
Why Use Dynamic SQL?......Page 220
Executing DDL and SCL Statements in PL/SQL......Page 221
Referencing Database Objects that Do Not Exist at Compilation......Page 222
Executing Dynamic PL/SQL Blocks......Page 223
Performing Dynamic Operations Using Invoker's Rights......Page 224
Sample DML Operation Using Native Dynamic SQL......Page 225
Sample Single-Row Query Using Native Dynamic SQL......Page 226
Choosing Between Native Dynamic SQL and the DBMS_SQL Package......Page 227
Advantages of Native Dynamic SQL......Page 228
Advantages of the DBMS_SQL Package......Page 231
Examples of DBMS_SQL Package Code and Native Dynamic SQL Code......Page 232
Overview of SQL Injection Techniques......Page 235
Guarding Against SQL Injection......Page 239
Designing Triggers......Page 241
Creating Triggers......Page 242
When Is the Trigger Fired?......Page 243
Controlling When a Trigger Is Fired (BEFORE and AFTER Options)......Page 244
Ordering of Triggers......Page 245
Modifying Complex Views (INSTEAD OF Triggers)......Page 246
Firing Triggers One or Many Times (FOR EACH ROW Option)......Page 249
Coding the Trigger Body......Page 250
Accessing Column Values in Row Triggers......Page 252
Triggers on Object Tables......Page 254
Triggers and Handling Remote Exceptions......Page 255
Restrictions on Creating Triggers......Page 256
Privileges Needed to Work with Triggers......Page 259
Recompiling Triggers......Page 260
Disabling Triggers......Page 261
Viewing Information About Triggers......Page 262
Examples of Trigger Applications......Page 263
How Events Are Published Through Triggers......Page 277
Event Attribute Functions......Page 278
List of Database Events......Page 281
Overview of Flashback Features......Page 287
Database Administration Features......Page 288
Database Administration Tasks Before Using Flashback Features......Page 289
Using Flashback Query (SELECT ... AS OF)......Page 290
Tips for Using Flashback Query......Page 291
Using the DBMS_FLASHBACK Package......Page 292
Using ORA_ROWSCN......Page 293
Using Flashback Version Query......Page 294
Flashback Transaction Query and Flashback Version Query: Example......Page 296
Flashback Tips - Performance......Page 298
Flashback Tips - General......Page 299
Invoking a PL/SQL Web Application......Page 301
Implementing a PL/SQL Web Application......Page 302
Using the mod_plsql Gateway......Page 303
Generating HTML Output with PL/SQL......Page 304
Passing List and Dropdown List Parameters from an HTML Form......Page 305
Passing Entry Field Parameters from an HTML Form......Page 306
Submitting a Completed HTML Form......Page 308
Maintaining State Information Between Web Pages......Page 309
Getting a Host Name or Address from PL/SQL......Page 310
Retrieving the Contents of an HTTP URL from PL/SQL......Page 311
Working with Tables, Image Maps, Cookies, and CGI Variables from PL/SQL......Page 313
What Are PL/SQL Server Pages and Why Use Them?......Page 315
Prerequisites for Developing and Deploying PL/SQL Server Pages......Page 316
PSP and Other Scripting Solutions......Page 317
Writing a PL/SQL Server Page......Page 318
Specifying Basic Server Page Characteristics......Page 319
Accepting User Input......Page 321
Including the Contents of Other Files......Page 322
Specifying Executable Statements in a PSP Script......Page 323
Substituting an Expression Result in a PSP Script......Page 324
Including Comments in a PSP Script......Page 325
Loading a PL/SQL Server Page into the Database......Page 326
Querying PSP Source Code......Page 327
Executing a PL/SQL Server Page Through a URL......Page 328
Setup for PL/SQL Server Pages Examples......Page 329
Printing the Sample Table with a Loop......Page 330
Allowing a User Selection......Page 331
Using an HTML Form to Call a PL/SQL Server Page......Page 332
Debugging PL/SQL Server Page Problems......Page 334
Putting PL/SQL Server Pages into Production......Page 335
What Is Database Change Notification?......Page 337
Using Database Change Notification in the Middle Tier......Page 338
What Is a Database Change Registration?......Page 341
Registration Properties......Page 342
Drop Table......Page 343
Creating a PL/SQL Stored Procedure as the Change Notification Recipient......Page 344
Registering Queries for Change Notification Through PL/SQL......Page 345
Querying Change Notification Registrations......Page 348
Interpreting a CHNF$_DESC Object......Page 349
Configuring Database Change Notification: Scenario......Page 350
Creating a PL/SQL Callback Procedure......Page 351
Registering the Query......Page 352
Best Practices......Page 353
Troubleshooting......Page 354
Part III Advanced Topics for Application Developers......Page 357
Overview of Multi-Language Programs......Page 359
What Is an External Procedure?......Page 360
Loading Java Class Methods......Page 361
Loading External C Procedures......Page 362
Publishing External Procedures......Page 366
The AS LANGUAGE Clause for External C Procedures......Page 367
Publishing Java Class Methods......Page 368
Locations of Call Specifications......Page 369
Passing Parameters to External C Procedures with Call Specifications......Page 372
Specifying Datatypes......Page 373
External Datatype Mappings......Page 374
The PARAMETERS Clause......Page 376
Specifying Properties......Page 377
Executing External Procedures with the CALL Statement......Page 383
Preconditions for External Procedures......Page 384
CALL Statement Syntax......Page 385
How the Database Server Calls External C Procedures......Page 386
Using Service Procedures with External C Procedures......Page 387
Doing Callbacks with External C Procedures......Page 393
Restrictions on Callbacks......Page 395
Debugging External Procedures......Page 396
Guidelines for External C Procedures......Page 397
Restrictions on External C Procedures......Page 398
X/Open Distributed Transaction Processing (DTP)......Page 399
DTP Terminology......Page 400
Oracle XA Library Interface Subroutines......Page 402
Extensions to the XA Interface......Page 403
Responsibilities of the DBA or System Administrator......Page 404
Defining the xa_open() String......Page 405
Interfacing XA with Precompilers and OCI......Page 408
Managing Transaction Control with XA......Page 410
Migrating Precompiler or OCI Applications to TPM Applications......Page 412
Troubleshooting XA Applications......Page 413
Accessing XA Trace Files......Page 414
Using SYS Account Tables to Monitor XA Transactions......Page 415
Using Database Links in XA Applications......Page 416
Using XA with Oracle Real Application Clusters......Page 417
SQL-Based XA Restrictions......Page 419
Miscellaneous Restrictions......Page 420
Introduction to Publish-Subscribe......Page 421
Publish-Subscribe Architecture......Page 422
Publish-Subscribe Concepts......Page 423
Examples of a Publish-Subscribe Mechanism......Page 425
C......Page 429
D......Page 430
E......Page 431
I......Page 432
N......Page 433
O......Page 434
P......Page 435
S......Page 436
T......Page 437
U......Page 438
X......Page 439