SUMMARY

SEARCH

Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.

BOOK PRO ODP NET FOR ORACLE DATABASE 11G

SUMMARY

336

Chapter 1: Introduction to Oracle NET Connectivity 1
Chapter 2: ODP.NET: A Functional Overview 19
Chapter 3: Connecting to Oracle with ODP.NET 49
Chapter 4: Retrieving and Manipulating Data with ODP.NET 69
Chapter 5: Using PL/SQL and NET CLR Stored Procedures with ODP.NET 117
Chapter 6: ODP.NET Globalization 161
Chapter 7: Transactions with ODP.NET 187
Chapter 8: Oracle Database Change Notifications with ODP.NET 205
Chapter 9: Using Oracle Database Streams Advanced Queuing with ODP.NET 223
Chapter 10: Oracle XML Support 253
Chapter 11: ODP.NET Security Features 287
Chapter 12: ODP.NET Performance 317
Chapter 13: Design Patterns and Considerations in Using ODP.NET 351
Chapter 14: ODT.NET Tool Basics 373
Chapter 15: Building Data-Driven Applications with ODT.NET 401
Index 429
iv
Chapter 1: Introduction to Oracle NET Connectivity 1
Making the Transition from SQL Server to Oracle 2
Introducing Oracle Connectivity 3
Accessing Oracle from Unmanaged Code 4
Using OLEDB in Unmanaged Code 5
Oracle Provider for OLEDB 5
MS OLEDB Provider for Oracle 6
Using ODBC in Unmanaged Code 6
Oracle ODBC Driver 6
Microsoft ODBC for Oracle 7
Using OO4O in Unmanaged Code 7
Accessing Oracle from Managed Code 8
Using OLEDB.NET in Managed Code 8
Using ODBC.NET in Managed Code 9
Using the Microsoft NET Managed Provider for Oracle 11
Introducing ODP.NET 11
Understanding the ODP.NET Architecture 12
v
Understanding the ODP.NET Classes 12
Accessing Data using ODP.NET 13
Using ODP.NET in ASP.NET Projects 13
Considering ODP.NET Performance 15
Introducing the Oracle Suite of Products 15
Summary 17
Chapter 2: ODP.NET: A Functional Overview 19
Exploring Oracle Features Accessible in ODP.NET Version 9 19
Manipulating XML 20
Manipulating LOBs 20
Using PL/SQL Associative Array Binding 21
Supporting Active Data Objects (ADO.NET) 2.0 22
Accessing Oracle Features from ODP.NET Version 10 22
Supporting Oracle Grids 23
Supporting Multiple Oracle Homes 23
Using Floating Point Data Types 24
Using Statement Caching 24
Supporting Command Cancellation and Timeout 25
Retrieving Parameters Programmatically 26
Supporting NET Stored Procedures 26
Using Client Identifiers 26
Using Database Change Notifications 27
Managing Connection Pools 28
Optimizing Connection Pools for RAC 28
Using a REF Cursor as an IN/OUT Parameter 29
Using 64-bit ODP.NET 29
Controlling the FetchSize Property 29
Configuring ODP.NET 30
vi
Accessing Oracle Features from ODP.NET Version 11 30
Enhancing Performance 31
Deploying ODP.NET Using xcopy 31
Supporting Oracle User Defined Types (UDTs) 31
Performing Bulk Copy Operations 32
Using Windows Authenticated User Connections Pooling 32
Publishing Connection Pool Performance Counters 33
Supporting Self-Tuning for Applications 34
Using Oracle Streaming AQ 34
Supporting Promotable Local Transactions 35
Using ODP.NET Security Enhancements 37
Running Callbacks for HA Event Notifications 38
Starting Up and Shutting Down Databases 38
Getting Started 39
Installing Oracle Database 11g 39
Installing ODAC.NET 43
Summary 48
Chapter 3: Connecting to Oracle with ODP.NET 49
Connecting via TNS 49
Understanding the TNSNames.ora file 51
Connecting in Other Ways 52
Connecting Without TNSNames.ora 52
Connecting via EZConnect 53
Learning the ODP.NET Connection Parameters 54
Connecting with Connection Pooling Activated 54
Connecting via Integrated Windows Authentication 57
Connecting with Special Privileges 58
Using Other Connection String Attributes 59
vii
Checking Whether ODP.NET Is Installed 60
Dynamically Building an ODP.NET Connection String 61
Using the OracleConnectionStringBuilder Class 61
Retrieving Available Oracle Data Sources 62
Understanding Transparent Application Failover 62
Enabling TAF in Your Application 63
Using TAF Callbacks 65
Summary 67
Chapter 4: Retrieving and Manipulating Data with ODP.NET 69
Understanding ODP.NET Data Types 69
Creating a Sample Table 73
Retrieving Multiple Rows of Data 76
Retrieving a Single Value 78
Handling NULL Values in ODP.NET 79
Retrieving Data into a Dataset 80
Using Parameterized Queries 81
Updating Data 83
Executing a Single INSERT, UPDATE, or DELETE Statement 84
Committing Dataset Changes to the Database 85
Generating Command Objects 88
Handling Master-Detail Relationships 90
Creating a Second Table 90
Retrieving from Multiple Tables 91
Binding a NET Form to Your Dataset 92
Committing Changes to Multiple Tables 94
Defining Table Relationships and Constraints in a DataSet 95
Manipulating LOBs and BFILEs 97
Uploading BLOB Data 97
Retrieving BLOB Data 99
Inserting CLOB/NCLOB Data 100
Retrieving CLOB/NCLOB Data 101
Creating BFILE Directory Mappings 102
Inserting BFILE Data 103
Retrieving BFILE Data 104
Manipulating RAW Data Types 105
Creating Automatically Incrementing Columns 108
Executing DDL from ODP.NET 109
Discovering Schema in ODP.NET 110
Handling ODP.NET Exceptions 113
Summary 115
Chapter 5: Using PL/SQL and NET CLR Stored Procedures with ODP.NET 117
Understanding the Basics of PL/SQL 117
Working with Anonymous PL/SQL Blocks 118
Executing an Anonymous PL/SQL Block 118
Passing Data into an Anonymous Block 119
Returning Data from an Anonymous Block 121
Working with PL/SQL Stored Procedures 122
Executing a PL/SQL Stored Procedure 123
Passing Data into a PL/SQL Stored Procedure 124
Retrieving Data from a PL/SQL Stored Procedure 126
Executing a PL/SQL Function 127
Handling Special IN and OUT Data Types 128
Using Associative Arrays 128
Passing Associative Arrays to PL/SQL Code 129
Retrieving Associative Arrays from PL/SQL Code 131
ix
Using VARRAYs 134
Using Nested Tables 138
Using REF Cursors 140
Reading a Result Set from a REF Cursor Using the OracleDataReader 141
Reading a Result Set from a REF Cursor Using the OracleDataAdapter 142
Retrieving Multiple Active Result Sets 144
User Defined Types (UDT) / OBJECT Types 146
Handling Custom-Defined PL/SQL Errors 151
Creating Your First NET CLR Stored Procedure 152
Deploying a NET CLR Stored Procedure 154
Executing the NET CLR Stored Procedure 157
Summary 159
Chapter 6: ODP.NET Globalization 161
Storing and Retrieving Double-Byte Data 161
Using the OracleGlobalization class 165
Setting Attributes at the Client Level 165
Setting Attributes at the Session Level 166
Setting Attributes at the Thread Level 167
Changing the Session Language 167
Formatting Calendar Dates 168
Displaying Various Date Formats and Languages 169
Designating Calendar Systems 171
Representing Currencies 172
Formatting Numbers 176
Dealing with Time Zones 176
Sorting and Comparing Strings 178
Applying Country-Based Formatting 181
Safely Mapping to NET Data Types 183
x
Summary 185
Chapter 7: Transactions with ODP.NET 187
Understanding Transactions and the OracleTransaction Class 187
Executing Your First Transaction 189
Executing Stored Procedures in a Transaction 191
Performing Partial Rollbacks 192
Working with Distributed Transactions 194
Creating a Second Database Instance 195
Executing Implicit Distributed Transactions 198
Executing Explicit Distributed Transactions 201
Executing Promotable Transactions 202
Summary 203
Chapter 8: Oracle Database Change Notifications with ODP.NET 205
Understanding Database Change Notification 205
Registering for Query-Based Change Notifications 206
Registering for Object-Based Change Notifications 211
Grouping Multiple Notification Requests 212
Removing a Registration 213
Retrieving Change Notification Information 214
Choosing to Poll 216
Considering Typical Usage Scenarios 217
Thinking About Performance 221
Summary 222
Chapter 9: Using Oracle Database Streams Advanced Queuing with ODP.NET 223
Understanding the Basics of AQ 224
Creating a Single-Consumer Queue 224
Setting Up a Single-Consumer Queue 224
xi
Enqueuing and Dequeuing a Single Message 226
Enqueuing and Dequeuing Multiple Messages 230
Creating a Multiple-Consumer Queue 232
Defining Recipients at the Queue Level 233
Defining Recipients at the Message Level 237
Enqueuing and Dequeuing Various Data Types in AQ 238
Using UDT Data Types 238
Using XML Data Types 244
Waiting for Incoming Messages 246
Dequeuing Messages Synchronously (Blocking) 247
Dequeuing Messages Asynchronously (Nonblocking) 248
Understanding the Useful OracleAQMessage Properties 250
Summary 251
Chapter 10: Oracle XML Support 253
Accessing Native XML Data (XMLTYPE) 254
Creating an XMLTYPE Column 254
Receiving XMLTYPE Data with XMLReader 255
Receiving XMLTYPE Data with OracleXMLType 257
Receiving XMLTYPE Data as a String 259
Passing XML Data to and from PL/SQL Stored Procedures 260
Validating Against XML Schema 262
Using XSLT to Transform XML Data 266
Retrieving Relational Data as XML 269
Using the XMLCommandType property 269
Using the Dataset.GetXML Method 272
Using the DBMS_XMLGEN.GETXML Stored Procedure 273
Manipulating Relational Data as XML 274
Inserting Relational Data Using XML 274
Updating Relational Data Using XML 278
Deleting Relational Data Using XML 280
Using XQuery to Query Data 282
Summary 284
Chapter 11: ODP.NET Security Features 287
Securing Your NET Applications 287
Authenticating Data 289
Implementing Username/Password Authentication 289
Implementing Proxy Authentication 289
Implementing ClientId-Based Username/Password Authentication 290
Implementing Windows Authentication 291
Understanding Code Access Security 291
Using Code Groups 292
Using Permission Sets 293
Resolving Permissions in NET 294
Seeing CAS in Action 295
Configuring CAS Policies 297
Configuring CAS Policies via the GUI 297
Configuring CAS Policies Programmatically 301
Requesting Permissions 301
Requesting Permissions Declaratively 301
Requesting Permissions Imperatively 304
Ensuring That an Assembly Can Never Access Oracle 305
Refusing Permissions Declaratively at the Assembly Level 306
Denying Permissions Declaratively at the Method Level 307
Denying Permissions Imperatively at the Method Level 308
Using CAS with ASP.NET Applications 308
Implementing Best Practices 313
Preventing SQL Injection Attacks 313
Preventing Nonpersistent Cross-Site Scripting Attacks 314
Summary 315
Chapter 12: ODP.NET Performance 317
Measuring Performance 317
Enabling the Performance Counters 318
Measuring Performance Programmatically 320
Speeding Up Connections with Connection Pooling 321
Performing Faster Floating Point Arithmetic 323
Executing Statements Faster 325
Batching Your SQL Statements Together For Execution 325
Using Statement Caching 328
REF Cursors and Multiple Active Resultsets (MARs) 331
Passing Parameters More Efficiently 331
Using Bind Arrays to Pass Parameters in Bulk 331
Using PL/SQL Associative Arrays 334
Managing LOBs More Efficiently 337
Enabling the LOB Cache 337
Setting the InitialLOBFetchSize Property 340
Retrieving Data More Efficiently 341
Changing the FetchSize Property 341
Using the Client Result Cache 343
Importing Data More Efficiently 346
Applying Optimization Best Practices 348
Using Stored Procedures Whenever Possible 349
Using the Right Data Access Object 349
Summary 349
xiv
Chapter 13: Design Patterns and Considerations in Using ODP.NET 351
Programming to an Interface Instead of an Implementation 352
Using the Data Access Object 353
Using Microsoft’s Enterprise Library 354
Creating Oracle.NET 355
Creating the ODP.NET DAO Class 355
Modifying the DBProviderMapping Class 361
Modifying the DatabaseConfigurationView Class 362
Compiling the New DAAB 364
Using the ODP.NET DAO 364
Editing the Application Configuration File 364
Accessing Data via the DAAB 366
Reading Multiple Rows into a Dataset 366
Updating Multiple Rows from a Dataset 367
Reading Data Using a DataReader 368
Considering Best Practices 369
Planning for Multiple Data Sources 369
Keeping Provider-Specific Code Within the Data Tier 369
Outputting Business Objects, Not Datasets 370
Deciding How to Map Data Source Structures to Business Objects 370
Deciding How to Manage Data Source Settings 370
Summary 370
Chapter 14: ODT.NET Tool Basics 373
Installing ODT.NET 373
Managing the Database Schema 374
Managing Users, Roles, and Object Privileges 377
Editing and Debugging PL/SQL Code 380
Creating a PL/SQL Procedure 380
Creating a PL/SQL Package 383
Debugging PL/SQL stored procedures 385
Managing Oracle SQL Scripts 391
Managing Advanced Queues 394
Importing Tables and Data from External Data Sources 396
Summary 399
Chapter 15: Building Data-Driven Applications with ODT.NET 401
Designing Queries Visually 401
Generating Strongly Typed DataSet Objects 404
Generating UDT Classes 413
Designing a UDT Object Visually 413
Creating the UDT Object Table Visually 414
Creating the OVERSEAS_JOBS Table 415
Generating the UDT Classes 417
Using the UDT in Your Project 420
Generating ASP.NET Code 423
Summary 427
Index 429