Oracle9i Application Developers Guide - Fundamentals

Contents......Page 3
1 Understanding the Oracle Programmatic Environments......Page 39
Overview of Developing an Oracle Application......Page 40
A Simple PL/SQL Example......Page 42
Better Performance......Page 43
PL/SQL Support for Object-Oriented Programming......Page 44
Security......Page 45
Built-In Packages for Distributed Database Access......Page 46
Overview of Writing Database Triggers in Java......Page 47
Overview of Oracle JDBC......Page 48
JDBC Server Driver......Page 49
Sample Program for the JDBC Thin Driver......Page 50
JDBC in SQLJ Applications......Page 51
SQLJ Tool......Page 52
Comparison of SQLJ with JDBC......Page 53
SQLJ Example for Object Types......Page 55
SQLJ Stored Procedures in the Server......Page 58
How You Implement a Pro*C/C++ Application......Page 59
Highlights of Pro*C/C++ Features......Page 60
How You Implement a Pro*COBOL Application......Page 62
Highlights of Pro*COBOL Features......Page 63
Overview of OCI and OCCI......Page 64
Parts of the OCI......Page 65
Procedural and Non-Procedural Elements......Page 66
Building an OCI Application......Page 67
Overview of Oracle Objects for OLE (OO4O)......Page 68
OO4O Object Model......Page 69
OraServer......Page 70
OraDynaset......Page 71
OraParameter......Page 72
OraAQ......Page 73
Support for Oracle LOB and Object Datatypes......Page 74
OraBFILE......Page 75
Additional Sources of Information......Page 76
Choosing Whether to Use OCI or a Precompiler......Page 77
Using Built-In Packages and Libraries......Page 78
Java Is Used for Open Distributed Applications......Page 79
2 Managing Schema Objects......Page 83
Designing Tables......Page 85
Creating Tables......Page 86
Managing Temporary Tables......Page 87
Using Temporary Tables......Page 88
Example 2: Using Temporary Tables to Improve Performance......Page 89
Tip: Referencing the Same Subquery Multiple Times......Page 90
Creating Views......Page 92
Expansion of Defining Queries at View Creation Time......Page 93
Replacing Views......Page 94
Using Views......Page 95
Privileges Required to Drop a View......Page 97
Modifying a Join View......Page 98
About Key-Preserved Tables......Page 99
Updating a Join View......Page 100
Deleting from a Join View......Page 101
Inserting into a Join View......Page 102
Outer Joins......Page 103
Creating Sequences......Page 106
Using Sequences......Page 107
Referencing a Sequence......Page 108
Caching Sequence Numbers......Page 110
Dropping Sequences......Page 111
Using Synonyms......Page 112
Privileges Required to Drop a Synonym......Page 113
Privileges Required to Create Multiple Schema Objects......Page 114
Rules for Name Resolution in SQL Statements......Page 115
Renaming Schema Objects......Page 116
Switching to a Different Schema......Page 117
Listing Information about Schema Objects......Page 118
3 Selecting a Datatype......Page 121
Summary of Oracle Built-In Datatypes......Page 122
Representing Character Data......Page 126
Column Lengths for Single-Byte and Multibyte Character Sets......Page 127
Comparison Semantics......Page 128
Representing Numeric Data......Page 129
Representing Date and Time Data......Page 130
Time Format......Page 131
Performing Date Arithmetic......Page 132
Establishing Year 2000 Compliance......Page 133
Centuries and the Year 2000......Page 134
Examples of The ’RR’ Date Format......Page 135
Examples of The ’CC’ Date Format......Page 136
Viewing Date Settings......Page 137
Altering Date Settings......Page 138
Troubleshooting Y2K Problems in Applications......Page 139
Representing Geographic Coordinate Data......Page 141
Migrating LONG Datatypes to LOB Datatypes......Page 142
Changing a LONG or LONG RAW Column to a LOB Datatype......Page 143
Restrictions on LONG and LONG RAW Datatypes......Page 144
Using RAW and LONG RAW Datatypes......Page 147
Extended ROWID Format......Page 148
Different Forms of the ROWID......Page 149
ROWID Migration and Compatibility Issues......Page 150
ANSI/ISO, DB2, and SQL/DS Datatypes......Page 152
Datatype Conversion During Assignments......Page 153
Datatype Conversion During Expression Evaluation......Page 155
Representing Dynamically Typed Data......Page 156
Representing XML Data......Page 158
4 Maintaining Data Integrity Through Constraints......Page 159
Example of an Integrity Constraint for a Business Rule......Page 160
When to Use NOT NULL Integrity Constraints......Page 161
When to Use Default Column Values......Page 162
Setting Default Column Values......Page 163
Choosing a Table’s Primary Key......Page 164
Constraints On Views for Performance, Not Data Integrity......Page 165
About Nulls and Foreign Keys......Page 166
Defining Relationships Between Parent and Child Tables......Page 169
Guidelines for Deferring Constraint Checks......Page 170
Minimizing Space and Time Overhead for Indexes Associated with Constraints......Page 172
About Referential Integrity in a Distributed Database......Page 173
Restrictions on CHECK Constraints......Page 174
Rules for Multiple CHECK Constraints......Page 175
Defining Integrity Constraints with the CREATE TABLE Command: Example......Page 176
Privileges Required to Create Constraints......Page 177
Enabling and Disabling Integrity Constraints......Page 178
Enabling Constraints......Page 179
Enabling Existing Constraints......Page 180
Guidelines for Enabling and Disabling Key Integrity Constraints......Page 181
Examples of MODIFY CONSTRAINT......Page 182
Dropping Integrity Constraints......Page 183
Foreign Key References Primary Key by Default......Page 184
Choosing How Foreign Keys Enforce Referential Integrity......Page 185
Examples of Defining Integrity Constraints......Page 186
5 Selecting an Index Strategy......Page 191
Switch Your Temporary Tablespace to Avoid Space Problems Creating Indexes......Page 192
Index the Correct Tables and Columns......Page 193
Choose the Order of Columns in Composite Indexes......Page 194
Gather Statistics to Make Index Usage More Accurate......Page 195
Privileges Required to Create an Index......Page 196
Creating Indexes: Basic Examples......Page 197
When to Use Function-Based Indexes......Page 198
Advantages of Function-Based Indexes......Page 199
Example: Function-Based Index for Case-Insensitive Searches......Page 200
Restrictions for Function-Based Indexes......Page 201
6 Speeding Up Index Access with Index-Organized Tables......Page 205
Advantages of Index-Organized Tables......Page 206
Features of Index-Organized Tables......Page 208
Why Use Index-Organized Tables?......Page 211
Example of an Index-Organized Table......Page 213
Creating Index-Organized Tables: Example......Page 214
Manipulating Index-Organized Tables: Example......Page 215
Specifying an Overflow Data Segment: Example......Page 216
Determining the Last Non-Key Column Included in the Index Row Head Piece: Example......Page 217
Storing Columns in the Overflow Segment: Example......Page 218
Modifying Physical and Storage Attributes: Example......Page 219
Partitioning an Index-Organized Table: Example......Page 220
Compressing the Keys of an Index-Organized Table: Example......Page 223
Rebuilding an Index-Organized Table: Example......Page 224
7 How Oracle Processes SQL Statements......Page 227
Identifying Extensions to SQL92 (FIPS Flagging)......Page 228
Improving Transaction Performance......Page 230
Committing Transactions......Page 231
An Example of COMMIT, SAVEPOINT, and ROLLBACK......Page 232
Privileges Required for Transaction Management......Page 233
Ensuring Repeatable Reads with Read-Only Transactions......Page 234
Using a Cursor to Execute Statements Again......Page 235
Cancelling Cursors......Page 236
Locking Data Explicitly......Page 237
Choosing a Locking Strategy......Page 238
When to Lock with SHARE Mode......Page 239
When to Lock with SHARE ROW EXCLUSIVE Mode......Page 241
Letting Oracle Control Table Locking......Page 242
Summary of Nondefault Locking Options......Page 243
Explicitly Acquiring Row Locks......Page 244
Example of a User Lock......Page 246
Viewing and Monitoring Locks......Page 247
Using Serializable Transactions for Concurrency Control......Page 248
The INITRANS Parameter......Page 251
Referential Integrity and Serializable Transactions......Page 252
Using SELECT FOR UPDATE......Page 253
Transaction Set Consistency......Page 254
Comparison of READ COMMITTED and SERIALIZABLE Transactions......Page 255
Choosing an Isolation Level for Transactions......Page 256
Application Tips for Transactions......Page 257
Autonomous Transactions......Page 258
Entering a Buy Order......Page 261
Example: Making a Bank Withdrawal......Page 262
Defining Autonomous Transactions......Page 266
Writing an Application to Handle Suspended Storage Allocation......Page 267
Example of Resumable Storage Allocation......Page 268
Querying Data at a Point in Time (Flashback Query)......Page 269
Setting Up the Database for Flashback Query......Page 270
Writing an Application that Uses Flashback Query......Page 271
Limitations of Flashback Query......Page 272
Recovering Incorrectly Updated or Deleted Data: Examples......Page 273
Storing a System Change Number: Example......Page 277
Using Explicit and Implicit Cursors with Flashback Queries: Example......Page 278
8 Coding Dynamic SQL Statements......Page 279
What Is Dynamic SQL?......Page 280
Executing DDL and SCL Statements in PL/SQL......Page 281
Referencing Database Objects that Do Not Exist at Compilation......Page 282
Optimizing Execution Dynamically......Page 283
Executing Dynamic PL/SQL Blocks......Page 284
Performing Dynamic Operations Using Invoker-Rights......Page 285
A Dynamic SQL Scenario Using Native Dynamic SQL......Page 286
Sample DDL Operation Using Native Dynamic SQL......Page 287
Sample Single-Row Query Using Native Dynamic SQL......Page 288
Choosing Between Native Dynamic SQL and the DBMS_SQL Package......Page 289
Native Dynamic SQL is Easy to Use......Page 290
Native Dynamic SQL is Faster than DBMS_SQL......Page 292
Native Dynamic SQL Supports Fetching Into Records......Page 293
DBMS_SQL Lets You Reuse SQL Statements......Page 294
Querying Using Dynamic SQL: Example......Page 295
Performing DML Using Dynamic SQL: Example......Page 297
Performing DML with RETURNING Clause Using Dynamic SQL: Example......Page 298
Using Dynamic SQL in Languages Other Than PL/SQL......Page 299
9 Using Procedures and Packages......Page 301
Anonymous Blocks......Page 302
Parameters for Procedures and Functions......Page 305
Creating Stored Procedures and Functions......Page 309
Altering Stored Procedures and Functions......Page 311
Dropping Procedures and Functions......Page 312
PL/SQL Packages......Page 313
Creating Packages......Page 315
Naming Packages and Package Objects......Page 316
Oracle Supplied Packages......Page 317
Overview of Bulk Binds......Page 318
When to Use Bulk Binds......Page 319
Hiding PL/SQL Code with the PL/SQL Wrapper......Page 321
Timestamps......Page 322
Disadvantages of the Timestamp Model......Page 323
Signatures......Page 324
When Does a Signature Change?......Page 326
Examples of Changing Procedure Signatures......Page 328
Controlling Remote Dependencies......Page 329
Suggestions for Managing Dependencies......Page 331
Declaring and Opening Cursor Variables......Page 332
Fetching Data......Page 333
Implementing Variant Records......Page 334
Handling PL/SQL Compile-Time Errors......Page 335
Handling Run-Time PL/SQL Errors......Page 337
Declaring Exceptions and Exception Handling Routines......Page 338
Unhandled Exceptions......Page 339
Handling Errors in Remote Procedures......Page 340
Debugging Stored Procedures......Page 341
Calling Stored Procedures......Page 342
Interactively Calling Procedures From Oracle Tools......Page 343
Name Resolution When Calling Procedures......Page 344
Specifying Values for Procedure Arguments......Page 345
Remote Procedure Calls and Parameter Values......Page 346
Referencing Remote Objects......Page 347
Using PL/SQL Functions......Page 349
Naming Conventions......Page 350
Name Precedence......Page 351
Using Default Values......Page 352
Meeting Basic Requirements......Page 353
Controlling Side Effects......Page 354
Restrictions......Page 355
Declaring a Function......Page 356
Parallel Query and Parallel DML......Page 357
Using PRAGMA RESTRICT_REFERENCES......Page 359
Overloading Packaged PL/SQL Functions......Page 362
Package States......Page 363
Syntax of Serially Reusable Packages......Page 364
Semantics of Serially Reusable Packages......Page 365
Examples of Serially Reusable Packages......Page 366
Returning Large Amounts of Data from a Function......Page 369
Coding Your Own Aggregate Functions......Page 371
10 Calling External Procedures......Page 373
Overview of Multi-Language Programs......Page 374
What Is an External Procedure?......Page 375
Overview of The Call Specification for External Procedures......Page 376
Loading Java Class Methods......Page 377
1. Set Up the Environment......Page 378
2. Identify the DLL......Page 379
Publishing External Procedures......Page 380
The AS LANGUAGE Clause for External C Procedures......Page 381
PARAMETERS......Page 382
Publishing Java Class Methods......Page 383
Locations of Call Specifications......Page 384
Example: Locating a Call Specification in a PL/SQL Package Body......Page 385
Example: Locating a Call Specification in an Object Type Body......Page 386
Passing Parameters to External C Procedures with Call Specifications......Page 389
Specifying Datatypes......Page 390
External Datatype Mappings......Page 392
BY VALUE/REFERENCE for IN and IN OUT Parameter Modes......Page 393
The PARAMETERS Clause......Page 394
Specifying Properties......Page 395
LENGTH and MAXLEN......Page 397
CHARSETID and CHARSETFORM......Page 398
Using SELF......Page 399
Passing Parameters by Reference......Page 402
Inter-Language Parameter Mode Mappings......Page 403
Executing External Procedures with the CALL Statement......Page 404
Privileges......Page 405
CALL Statement Syntax......Page 406
Calling Java Class Methods......Page 407
How the Database Server Calls External C Procedures......Page 408
OCIExtProcAllocCallMemory......Page 409
OCIExtProcRaiseExcp......Page 415
OCIExtProcRaiseExcpWithMsg......Page 417
OCIExtProcGetEnv......Page 418
Restrictions on Callbacks......Page 420
Demo Program......Page 422
Guidelines for Call Specifications and CALL Statements......Page 423
Restrictions on External C Procedures......Page 424
11 Database Security Overview for Application Developers......Page 427
Security Threats and Countermeasures......Page 428
What Information Security Policies Can Cover......Page 429
Features to Use in Establishing Security Policies......Page 430
Tip 1: Enable and Disable Roles Promptly......Page 432
Tip 2: Encapsulate Privileges in Stored Procedures......Page 433
Tip 4: Use Proxy Authentication and a Secure Application Role......Page 434
Tip 6: Use Application Context and Fine-Grained Access Control......Page 435
Introduction to Application Security Policies......Page 436
Are Application Users Also Database Users?......Page 437
Is Security Enforced in the Application or in the Database?......Page 438
Creating Roles to Simplify Application Privilege Management......Page 439
Creating Secure Application Roles......Page 440
Example of Creating a Secure Application Role......Page 441
Associating Privileges with the User’s Database Role......Page 442
Using the SET_ROLE Procedure......Page 443
Examples of Assigning Roles with Static and Dynamic SQL......Page 444
Shared Schemas......Page 446
Object Privileges......Page 448
SQL Statements Permitted by Object Privileges......Page 449
Managing Roles......Page 450
Enabling and Disabling Roles......Page 451
Default Roles......Page 452
Explicitly Enabling Roles......Page 453
Dropping Roles......Page 455
Granting System Privileges and Roles with the ADMIN OPTION......Page 456
Revoking System Privileges and Roles......Page 457
Granting and Revoking Schema Object Privileges and Roles......Page 458
Revoking Schema Object Privileges......Page 459
Revoking Security-Vulnerable Packages from PUBLIC......Page 462
When Grants and Revokes Take Effect......Page 463
12 Implementing Application Security Policies......Page 465
Features of Application Context......Page 466
Providing Access to Predefined Attributes Through the USERENV Namespace......Page 467
Externalized Application Contexts......Page 471
Using Application Context as a Secure Data Cache......Page 472
Using Application Context to Provide Attributes Similar to Bind Variables in a Predicate......Page 473
User Models and Virtual Private Database......Page 474
Creating a Virtual Private Database Policy with Oracle Policy Manager......Page 475
Task 1: Create a PL/SQL Package that Sets the Context for Your Application......Page 477
Task 3: Set the Context Before the User Retrieves Data......Page 480
Example 1: Implementing the Policy......Page 481
Example 2: Controlling User Access by Way of an Application......Page 485
Example 3: Event Triggers, Application Context, Fine-Grained Access Control, and Encapsulation of.........Page 487
Automatic Reparse......Page 491
Using the DBMS_SESSION Interface to Manage Application Context in Client Sessions......Page 492
Example 1: Global Access of Application Context......Page 493
Example 2: Global Access of Application Context for Proxy Authentication Applications......Page 494
Initializing Application Context Externally......Page 496
Obtaining Values from Other External Resources......Page 497
Obtaining Values for Users Not Known to the Database......Page 498
Application Context Utilizing LDAP......Page 499
Example: Initializing Application Context Globally......Page 502
Table- Or View-Based Security Policies......Page 505
Grouping of Security Policies......Page 507
How Fine-Grained Access Control Works......Page 508
How to Establish Policy Groups......Page 509
New Policy Groups......Page 510
How to Implement Policy Groups......Page 511
Validation of the Application Used to Connect......Page 513
How to Add a Policy to a Table or View......Page 514
EXEMPT ACCESS POLICY System Privilege......Page 516
Standard Oracle9i Auditing Techniques......Page 517
Fine-Grained Auditing Techniques......Page 519
Use of Ad Hoc Tools a Potential Security Problem......Page 521
Limiting Roles Through PRODUCT_USER_PROFILE......Page 522
Using Virtual Private Database for Highest Security......Page 523
Virtual Private Database and Oracle Label Security......Page 524
13 Proxy Authentication......Page 527
Advantages of Proxy Authentication......Page 528
Does the Middle Tier Have Too Much Privilege?......Page 529
Middle Tier to Database Authentication......Page 530
Client Re-Authentication Through Middle Tier to Database......Page 531
Passing Through the Identity of the Real User......Page 532
Limiting the Privilege of the Middle Tier......Page 533
Re-authenticating the Real User......Page 534
Using Proxy Authentication with Enterprise Users......Page 535
Support for Application User Models......Page 536
14 Data Encryption Using DBMS_ OBFUSCATION_TOOLKIT......Page 539
Securing Sensitive Information......Page 540
Principle 1: Encryption Does Not Solve Access Control Problems......Page 541
Principle 2: Encryption Does Not Protect Against a Malicious DBA......Page 542
Principle 3: Encrypting Everything Does Not Make Data Secure......Page 543
Oracle9i Data Encryption Capabilities......Page 544
Encrypting Indexed Data......Page 545
Key Management......Page 546
Storing the Keys in the Database......Page 547
User Managing the Keys......Page 549
Example of Data Encryption PL/SQL Program......Page 550
15 Using Triggers......Page 555
Designing Triggers......Page 556
Creating Triggers......Page 557
Getting the Attributes of System Events......Page 558
Triggering Statement......Page 559
How Column Lists Affect UPDATE Triggers......Page 560
Modifying Complex Views (INSTEAD OF Triggers)......Page 561
Views that Require INSTEAD OF Triggers......Page 562
INSTEAD OF Trigger Example......Page 563
Object Views and INSTEAD OF Triggers......Page 564
Triggers on Nested Table View Columns......Page 565
Firing Triggers One or Many Times (FOR EACH ROW Option)......Page 566
Firing Triggers Based on Conditions (WHEN Clause)......Page 567
Coding the Trigger Body......Page 568
INSTEAD OF Triggers on Nested Table View Columns......Page 570
Conditional Predicates......Page 571
Triggers and Handling Remote Exceptions......Page 572
Restrictions on Creating Triggers......Page 574
System Trigger Restrictions......Page 579
Privileges to Create Triggers......Page 580
Compiling Triggers......Page 581
Recompiling Triggers......Page 582
Debugging Triggers......Page 583
Disabling Triggers......Page 584
Viewing Information About Triggers......Page 585
Auditing with Triggers: Example......Page 587
Integrity Constraints and Triggers: Examples......Page 593
Referential Integrity Using Triggers......Page 594
Trigger for Complex Check Constraints: Example......Page 600
Complex Security Authorizations and Triggers: Example......Page 601
Derived Column Values and Triggers: Example......Page 602
Building Complex Updatable Views Using Triggers: Example......Page 603
Tracking System Events Using Triggers......Page 605
Publication Framework......Page 607
Event Publication......Page 608
16 Working With System Events......Page 611
Event Attribute Functions......Page 612
System Events......Page 618
Client Events......Page 619
17 Using the Publish-Subscribe Model for Applications......Page 625
Introduction to Publish-Subscribe......Page 626
Client Notifications......Page 627
Publish-Subscribe Concepts......Page 628
Examples of a Publish-Subscribe Mechanism......Page 630
What Is a PL/SQL Web Application?......Page 639
How Do I Generate HTML Output from PL/SQL?......Page 640
How Do I Pass Parameters to a PL/SQL Web Application?......Page 641
Passing Radio Button and Checkbox Parameters from an HTML Form......Page 642
Passing Entry Field Parameters from an HTML Form......Page 643
Handling Missing Input from an HTML Form......Page 645
Maintaining State Information Between Web Pages......Page 646
Sending Mail from PL/SQL......Page 647
Retrieving the Contents of an HTTP URL from PL/SQL......Page 648
Embedding PL/SQL Code in Web Pages (PL/SQL Server Pages)......Page 651
How PSP Relates to Other Scripting Solutions......Page 652
The Format of the PSP File......Page 653
Parameter Directive......Page 659
Code Block (Scriptlet)......Page 660
Loading the PL/SQL Server Page into the Database as a Stored Procedure......Page 661
Sample PSP URLs......Page 662
Sample Table......Page 663
Printing the Sample Table using a Loop......Page 664
Allowing a User Selection......Page 665
Sample HTML Form to Call a PL/SQL Server Page......Page 667
Debugging PL/SQL Server Page Problems......Page 670
Putting an Application using PL/SQL Server Pages into Production......Page 671
Enabling PL/SQL Web Applications for XML......Page 673
19 Porting Non-Oracle Applications to Oracle9i......Page 675
Is There an Automated Way to Migrate a Schema and Associated Data from Another Database System?......Page 676
How Do I Perform Large Numbers of Comparisons within a Query?......Page 677
Does Oracle Support Scalar Subqueries?......Page 678
20 Working with Transaction Monitors with Oracle XA......Page 679
X/Open Distributed Transaction Processing (DTP)......Page 680
XA and the Two-Phase Commit Protocol......Page 683
Support for Dynamic and Static Registration......Page 684
XA Library Subroutines......Page 685
Extensions to the XA Interface......Page 686
Responsibilities of the DBA or System Administrator......Page 687
Defining the xa_open String......Page 688
Required Fields......Page 689
Optional Fields......Page 691
Using Precompilers with a Named Database......Page 695
Using OCI with the Oracle XA Library......Page 697
Transaction Control using XA......Page 698
Examples of Precompiler Applications......Page 699
Migrating Precompiler or OCI Applications to TPM Applications......Page 700
Restrictions on Threading in XA......Page 702
The xa_open string DbgFl......Page 703
Trace File Examples......Page 704
Oracle Server SYS Account Tables......Page 705
Database Links......Page 706
Session State......Page 707
Transaction Branches......Page 708
Compatibility......Page 709
Session Caching Is No Longer Needed......Page 710
The XA Library Can Be Used with the Oracle Real Application Clusters Option on All Platforms......Page 711
Both Global and Local Transactions Are Possible......Page 712
The xa_open String Has Been Modified......Page 713
Index......Page 715