Oracle 8i Dev Guide Fundamental

PDF Directory......Page 0
Contents......Page 3
Send Us Your Comments......Page 17
Preface......Page 19
1 Understanding the Oracle Programmatic Environments......Page 29
How Does PL/SQL Work?......Page 30
Tight Integration with Oracle......Page 31
Scalability......Page 32
Portability......Page 33
Security......Page 34
Advantages of OCI......Page 35
Procedural and Non-Procedural Elements......Page 36
Building an OCI Application......Page 37
Overview of Oracle Objects for OLE......Page 39
OO4O Object Model......Page 40
OraServer......Page 41
OraDynaset......Page 42
OraParameter......Page 43
OraAQ......Page 44
Support for Oracle LOB and Object Datatypes......Page 45
OraBFILE......Page 46
Additional Sources of Information......Page 47
How You Implement a Pro*C/C++ Application......Page 48
Highlights of Pro*C/C++ Features......Page 49
New Oracle8i Features Supported......Page 50
How You Implement a Pro*COBOL Application......Page 51
Highlights of Pro*COBOL Features......Page 52
New Oracle8i Features Supported......Page 53
JDBC OCI Driver......Page 54
Sample Program for the JDBC Thin Driver......Page 55
Why Use Stored Procedures?......Page 57
SQLJ Tool......Page 58
SQLJ Design Goals......Page 59
Comparison of SQLJ with JDBC......Page 60
SQLJ Example for Object Types......Page 61
SQLJ Stored Procedures in the Server......Page 64
Use OCI or a Precompiler?......Page 65
Core JDK Libraries......Page 66
Java vs. PL/SQL......Page 67
Java Is Used for Open Distributed Applications......Page 68
2 Managing Schema Objects......Page 71
Managing Tables......Page 72
Designing Tables......Page 73
Specifying PCTFREE......Page 74
Choosing Associated PCTUSED and PCTFREE Values......Page 76
Examples of Choosing PCTFREE and PCTUSED Values......Page 77
Privileges Required to Create a Table......Page 78
Altering Tables......Page 79
Dropping Tables......Page 80
Managing Temporary Tables......Page 81
Using Temporary Tables......Page 82
Example 2: Using Temporary Tables to Improve Performance......Page 83
Creating Views......Page 85
Creating Views with Errors......Page 86
Replacing Views......Page 87
Using Views......Page 88
Privileges Required to Drop a View......Page 90
Modifying a Join View......Page 91
Key-Preserved Tables......Page 92
Rule for DML Statements on Join Views......Page 93
DELETE Statements......Page 94
INSERT Statements......Page 95
Outer Joins......Page 96
Creating Sequences......Page 99
Using Sequences......Page 100
Referencing a Sequence......Page 101
Caching Sequence Numbers......Page 103
Dropping Sequences......Page 104
Using Synonyms......Page 105
Privileges Required to Drop a Synonym......Page 106
Privileges Required to Create Multiple Schema Objects......Page 107
Name Resolution in SQL Statements......Page 108
Renaming Schema Objects......Page 109
Renaming the Schema......Page 110
Listing Information about Schema Objects......Page 111
3 Selecting a Datatype......Page 115
Oracle Built-In Datatypes......Page 116
Future Compatibility......Page 119
Comparison Semantics......Page 120
Using the NUMBER Datatype......Page 121
Date Format......Page 122
Establishing Year 2000 Compliance......Page 123
Centuries and the Year 2000......Page 124
The ’RR’ Date Format......Page 125
The ’CC’ Date Format......Page 126
Storing Dates in Character Data Types......Page 127
Altering Date Settings......Page 128
Programming Hints and Tips......Page 129
Restrictions on LONG and LONG RAW Data......Page 132
Using RAW and LONG RAW Datatypes......Page 134
Different Forms of the ROWID......Page 135
ROWID Migration and Compatibility Issues......Page 137
ANSI/ISO, DB2, and SQL/DS Datatypes......Page 138
Rule 1: Assignments......Page 139
Rule 2: Expression Evaluation......Page 141
4 Maintaining Data Integrity......Page 143
When to Enforce Business Rules with Integrity Constraints......Page 144
Using NOT NULL Integrity Constraints......Page 145
Setting Default Column Values......Page 146
When to Use Default Values......Page 147
Choosing a Table’s Primary Key......Page 148
Using UNIQUE Key Integrity Constraints......Page 149
Nulls and Foreign Keys......Page 150
Relationships Between Parent and Child Tables......Page 152
How To Defer Constraint Checks......Page 153
Concurrency Control, Indexes, and Foreign Keys......Page 155
Referential Integrity in a Distributed Database......Page 156
Restrictions on CHECK Constraints......Page 157
CHECK and NOT NULL Integrity Constraints......Page 158
The CREATE TABLE Command......Page 159
Naming Integrity Constraints......Page 160
Why Disable Constraints?......Page 161
Creating Disabled Constraints......Page 162
Disabling Enabled Constraints......Page 163
Exception Reporting......Page 164
Examples of MODIFY CONSTRAINT......Page 165
Dropping Integrity Constraints......Page 166
Privileges Required for FOREIGN KEY Integrity Constraints......Page 167
Specifying Referential Actions for Foreign Keys......Page 168
Examples......Page 169
Managing Indexes......Page 173
Create Indexes After Inserting Table Data......Page 174
Dropping Indexes......Page 177
Function-Based Indexes......Page 178
Using Function-Based Indexes......Page 179
Example......Page 180
Example 3:......Page 183
Requirements and Restrictions for Function-Based Indexes......Page 184
Guidelines for Creating Clusters......Page 186
Creating Clusters, Clustered Tables, and Cluster Indexes......Page 187
Privileges Required to Create a Cluster, Clustered Table, and Cluster Index......Page 188
Dropping Clusters, Clustered Tables, and Cluster Indexes......Page 189
Creating Hash Clusters and Clustered Tables......Page 191
When to Use Hashing......Page 192
6 Speeding Up Index Access with Index-Organized Tables......Page 195
Advantages of Index-Organized Tables......Page 196
Features of Index-Organized Tables......Page 198
When to Use Index-Organized Tables......Page 201
Example......Page 203
Creating Index-Organized Tables......Page 204
Manipulating Index-Organized Tables......Page 205
Specifying an Overflow Data Segment......Page 206
Determining the Last Non-key Column Included in the Index Row Head Piece......Page 207
Storing Columns in the Overflow Segment......Page 208
Modifying Physical and Storage Attributes......Page 209
Partitioning an Index-Organized Table......Page 210
Key Compression......Page 213
Rebuilding an Index-Organized Table......Page 214
7 Processing SQL Statements......Page 217
Identifying Extensions to SQL92 (FIPS Flagging)......Page 218
Improving Performance......Page 220
Committing a Transaction......Page 221
An Example of COMMIT, SAVEPOINT, and ROLLBACK......Page 222
Privileges Required for Transaction Management......Page 223
Ensuring Repeatable Reads with Read-Only Transactions......Page 224
Using a Cursor to Re-Execute Statements......Page 225
Cancelling Cursors......Page 226
Explicit Data Locking......Page 227
Choosing a Locking Strategy......Page 228
SHARE......Page 229
SHARE ROW EXCLUSIVE......Page 231
Letting Oracle Control Table Locking......Page 232
Summary of Non-Default Locking Options......Page 233
Explicitly Acquiring Row Locks......Page 234
Sample User Locks......Page 236
Viewing and Monitoring Locks......Page 237
Concurrency Control Using Serializable Transactions......Page 238
The INITRANS Parameter......Page 241
Referential Integrity and Serializable Transactions......Page 242
Transaction Set Consistency......Page 244
Functionality Comparison Summary......Page 245
Choosing an Isolation Level......Page 246
Application Tips......Page 247
Autonomous Transactions......Page 248
Entering a Buy Order......Page 251
Example: Making a Bank Withdrawal......Page 252
Defining Autonomous Transactions......Page 256
8 Dynamic SQL......Page 257
What Is Dynamic SQL?......Page 258
To Execute Statements Not Supported by Static SQL in PL/SQL......Page 259
To Execute Dynamic Queries......Page 260
To Reference Database Objects that Do Not Exist at Compilation......Page 261
To Optimize Execution Dynamically......Page 262
To Invoke Dynamic PL/SQL Blocks......Page 263
To Perform Dynamic Operations Using Invoker-Rights......Page 264
Data Model......Page 265
Sample DDL Operation......Page 266
Sample Dynamic Single-Row Query......Page 267
Native Dynamic SQL vs. the DBMS_SQL Package......Page 268
Ease of Use......Page 269
Performance Improvements......Page 271
Support for Fetching Into Records......Page 272
Support for Bulk Dynamic SQL......Page 273
Reuse of SQL Statements......Page 274
Query Example......Page 275
DML Example......Page 277
DML Returning Example......Page 278
Application Development Languages Other Than PL/SQL......Page 280
9 Using Procedures and Packages......Page 281
Anonymous Blocks......Page 282
Parameters for Procedures and Functions......Page 285
Creating Stored Procedures and Functions......Page 289
Altering Stored Procedures and Functions......Page 291
PL/SQL Packages......Page 292
Creating Packages......Page 295
Naming Packages and Package Objects......Page 296
Oracle Supplied Packages......Page 297
Bulk Binds......Page 304
Timestamps......Page 308
Disadvantages of the Timestamp Model......Page 309
Signatures......Page 310
When Does a Signature Change?......Page 312
Examples of Signatures......Page 313
Controlling Remote Dependencies......Page 315
Dependency Resolution......Page 316
Suggestions for Managing Dependencies......Page 317
Examples of Cursor Variables......Page 318
Fetching Data......Page 319
Implementing Variant Records......Page 320
Compile-Time Errors......Page 321
Run-Time Error Handling......Page 323
Declaring Exceptions and Exception Handling Routines......Page 324
Unhandled Exceptions......Page 325
Handling Errors in Remote Procedures......Page 326
Debugging Stored Procedures......Page 327
A Procedure or Trigger Calling Another Procedure......Page 329
Interactively Calling Procedures From Oracle Tools......Page 330
Name Resolution When Calling Procedures......Page 331
Specifying Values for Procedure Arguments......Page 332
Remote Procedure Calls and Parameter Values......Page 333
Referencing Remote Objects......Page 334
Using PL/SQL Functions......Page 336
Naming Conventions......Page 337
Name Precedence......Page 338
Using Default Values......Page 339
Meeting Basic Requirements......Page 340
Controlling Side Effects......Page 341
Restrictions......Page 342
Declaring a Function......Page 343
Parallel Query/Parallel DML......Page 344
Using PRAGMA RESTRICT_REFERENCES......Page 346
Overloading......Page 349
Package States......Page 350
Syntax......Page 351
Semantics......Page 352
Examples......Page 353
The Need to Work with Multiple Languages......Page 357
What is an External Routine?......Page 358
The Call Specification......Page 359
Loading Java Class Methods......Page 360
1. Set Up the Environment......Page 361
Publishing an External Routine......Page 362
CALLING STANDARD......Page 364
Publishing Java Class Methods......Page 365
Locations of Call Specifications......Page 366
Example: Locating a Call Specification in a PL/SQL Package Body......Page 367
Example: Locating a Call Specification in an Object Type Body......Page 368
Passing Parameters to External C Routines with Call Specifications......Page 370
Specifying Datatypes......Page 372
External Datatype Mappings......Page 374
BY VALUE/REFERENCE for IN and IN OUT Parameter Modes......Page 375
The PARAMETERS Clause......Page 376
Specifying Properties......Page 377
LENGTH and MAXLEN......Page 379
Repositioning Parameters......Page 380
Using SELF......Page 381
WITH CONTEXT......Page 384
Executing External Routines: the CALL Statement......Page 385
Preliminaries......Page 386
Managing Permissions......Page 387
CALL Statement Syntax......Page 388
Calling External C Routines......Page 389
Generic Compile Time Call specification Errors......Page 390
OCIExtProcAllocCallMemory......Page 391
OCIExtProcRaiseExcp......Page 397
OCIExtProcRaiseExcpWithMsg......Page 398
OCIExtProcGetEnv......Page 399
Object Support for OCI Callbacks......Page 401
Restrictions on Callbacks......Page 402
Debugging External Routines......Page 403
Handling Global And Static Variables......Page 404
Call specification and CALLing Guidelines......Page 405
Restrictions on External C Routines......Page 406
11 Establishing Security Policies......Page 407
Security Threats and Countermeasures......Page 408
What Discretionary Security Policies Can Cover......Page 409
Features to Use in Establishing Security Policies......Page 410
Application Security......Page 411
Are Application Users Also Database Users?......Page 412
Does Security Enforcement Occur in the Application, or in the Database?......Page 413
Overview of Roles and Application Privilege Management......Page 414
SET ROLE Statement......Page 415
SET_ROLE Procedure......Page 416
Example......Page 417
Restricting Application Roles from Tool Users......Page 418
Limiting Roles Through PRODUCT_USER_PROFILE......Page 419
Virtual Private Database for Greatest Security......Page 420
Enable and Disable Roles Promptly......Page 422
Encapsulate Privileges in Stored Procedures......Page 423
Use Application Context and Fine-Grained Access Control......Page 424
Schema-independent Users......Page 425
Object Privileges......Page 427
SQL Statements Permitted by Object Privileges......Page 428
Protecting Role Use......Page 429
Default Roles......Page 430
Explicitly Enabling Roles......Page 431
Dropping Roles......Page 433
Granting System Privileges and Roles with the ADMIN OPTION......Page 434
Revoking System Privileges and Roles......Page 435
Granting and Revoking Schema Object Privileges with the GRANT OPTION......Page 436
Revoking Schema Object Privileges......Page 437
Granting to, and Revoking from, the User Group PUBLIC......Page 440
Fine-Grained Access Control......Page 441
Table- Or View-based Security Policies......Page 442
High Performance......Page 443
How to Add a Policy to a Table or View......Page 444
Example of a Dynamically Modified Statement......Page 445
Application Context......Page 446
Security Through Validation......Page 447
USERENV Application Context Namespace for Access to Predefined Attributes......Page 448
Feature Design Principles for Application Context......Page 451
Use Dynamic SQL......Page 452
Using Application Context as a Secure Data Cache......Page 453
Using Application Context to Provide Attributes as Bind Variables in a Predicate......Page 454
Task 1: Create a PL/SQL Package that Sets the Context for Your Application......Page 455
Task 4. Use the Context in a Policy Function......Page 458
Example 1: Order Entry Application......Page 459
Example 2: Human Resources Application #1......Page 463
Example 3: Human Resources Application #2......Page 465
Advantages of n-Tier Authentication......Page 470
Who Is the Real User?......Page 471
How to Audit? Whom to Audit?......Page 472
Can the User Be Reauthenticated to the Database?......Page 473
Passing Through the Identity of the Real User......Page 475
Limiting the Privilege of the Middle Tier......Page 476
Auditing Actions Taken on Behalf of the Real User......Page 477
The DBMS_OBFUSCATION_TOOLKIT Package......Page 478
Development Considerations......Page 479
12 Using Triggers......Page 483
Designing Triggers......Page 484
Creating Triggers......Page 485
Overview of System Events......Page 486
Triggering Statement......Page 487
Column List for UPDATE......Page 488
INSTEAD OF Triggers......Page 489
Views That Are Not Modifiable......Page 490
INSTEAD OF Trigger Example......Page 491
Object Views and INSTEAD OF Triggers......Page 492
Triggers on Nested Table View Columns......Page 493
FOR EACH ROW Option......Page 494
WHEN Clause......Page 495
The Trigger Body......Page 496
Accessing Column Values in Row Triggers......Page 497
INSTEAD OF Triggers on Nested Table View Columns......Page 498
Conditional Predicates......Page 499
Triggers and Handling Remote Exceptions......Page 500
Restrictions on Creating Triggers......Page 502
System Trigger Restrictions......Page 507
Privileges to Create Triggers......Page 508
Privileges for Referenced Schema Objects......Page 509
Dependencies......Page 510
Migration Issues......Page 511
Debugging Triggers......Page 512
Disabling Triggers......Page 513
Listing Information About Triggers......Page 514
Auditing with Triggers......Page 516
Integrity Constraints and Triggers......Page 522
Referential Integrity Using Triggers......Page 523
Complex Check Constraints......Page 529
Complex Security Authorizations and Triggers......Page 530
Derived Column Values and Triggers......Page 531
Building Complex Updatable Views......Page 532
Tracking System Events......Page 534
Publication Framework......Page 536
Event Publication......Page 537
13 Working With System Events......Page 539
Usage Notes......Page 540
Resource Manager Events......Page 544
Client Events......Page 545
14 Using Publish-Subscribe......Page 551
Introduction to Publish-Subscribe......Page 552
Client Notifications......Page 553
Rules......Page 554
Subscription Services......Page 555
Examples......Page 556
Working with TCP/IP Connections......Page 565
Embedding PL/SQL Code in Web Pages (PL/SQL Server Pages)......Page 566
How PSP Relates to Other Scripting Solutions......Page 567
The Format of the PSP File......Page 568
Returning XML, Text, or Other Document Types......Page 569
Naming the PL/SQL Stored Procedure......Page 570
Specifying Statements to be Executed in the Script......Page 571
Updating the Database Based Upon User Input......Page 572
Parameter Directive......Page 573
Code Block (Scriptlet)......Page 574
Loading the PL/SQL Server Page into the Database as a Stored Procedure......Page 575
Examples of PL/SQL Server Pages......Page 576
Dumping the Sample Table......Page 577
Printing the Sample Table using a Loop......Page 578
Allowing a User Selection......Page 579
Sample HTML Form to Call a PL/SQL Server Page......Page 581
Debugging PL/SQL Server Page Problems......Page 582
Putting an Application using PL/SQL Server Pages into Production......Page 583
16 Working with Transaction Monitors with Oracle XA......Page 587
X/Open Distributed Transaction Processing (DTP)......Page 588
XA and the Two-Phase Commit Protocol......Page 591
Support for Dynamic and Static Registration......Page 592
XA Library Subroutines......Page 593
Extensions to the XA Interface......Page 594
Responsibilities of the DBA or System Administrator......Page 595
Defining the xa_open String......Page 596
Required Fields......Page 597
Optional Fields......Page 599
Using Precompilers with the Oracle XA Library......Page 603
Using Precompilers with a Named Database......Page 604
Using OCI with the Oracle XA Library......Page 605
Transaction Control using XA......Page 606
Examples of Precompiler Applications......Page 608
Migrating Precompiler or OCI Applications to TPM Applications......Page 609
XA Library Thread Safety......Page 610
Restrictions on Threading in XA......Page 611
Trace File Locations......Page 612
In-doubt or Pending Transactions......Page 613
Oracle Server SYS Account Tables......Page 614
Database Links......Page 615
SET TRANSACTION......Page 616
Association Migration......Page 617
XA Changes from Release 8.0 to Release 8.1......Page 618
Dynamic Registration Is Supported......Page 619
Both Global and Local Transactions Are Possible......Page 620
The xa_open String Has Been Modified......Page 621
Index......Page 623