Pro .NET Oracle Programming / Edition 1

Pro .NET Oracle Programming / Edition 1

4.0 1
by Mark A. Williams
     
 

ISBN-10: 1590594258

ISBN-13: 9781590594254

Pub. Date: 11/04/2004

Publisher: Apress

This book never loses sight of its instructional mission: to effectively utilize the Oracle database from the .NET environment. Though Visual Studio and Oracle form a popular and powerful duo, there is a noticeable lack of written material in this area. The result: projects that often turn out with less-than-optimal solutions, due to lack of synergy between

Overview

This book never loses sight of its instructional mission: to effectively utilize the Oracle database from the .NET environment. Though Visual Studio and Oracle form a popular and powerful duo, there is a noticeable lack of written material in this area. The result: projects that often turn out with less-than-optimal solutions, due to lack of synergy between the application developer and the database.

The primary code in the book will be C#, but the code download will also be available in Visual Basic .NET (with an appendix describing terms of use). Hence, this book provides a one-stop reference for any VS .NET programmer using Oracle. The author maintains solid focus on databases, emphasizing the use of specific features from a Visual Studio application. The text also includes crucial aspects of developing Oracle-based applications. Assumably, you are already comfortable with the VS .NET environment, and with the aid of this book, you will come to work effectively with the Oracle database as well.

Product Details

ISBN-13:
9781590594254
Publisher:
Apress
Publication date:
11/04/2004
Series:
Pro Series: From Professional to Expert Ser.
Edition description:
2005
Pages:
472
Product dimensions:
7.00(w) x 9.25(h) x 0.96(d)

Table of Contents

About the Authorxiii
About the Technical Reviewersxv
Acknowledgmentsxvii
Introductionxix
Chapter 1Oracle Architecture and Connectivity1
Exploring the Oracle Server Architecture2
The Oracle Home2
The Difference Between a Database and an Instance4
The Schema6
The Tablespace7
The Oracle Network Architecture7
Oracle Network Components8
The Listener9
The tnsnames.ora File10
The sqInet.ora File11
Creating the Standard Configuration14
Creating a New tnsnames.ora Entry15
Troubleshooting Common Configuration Errors22
Creating Users23
Examining SQL *Plus33
SQL *Plus Variants34
Command-Line SQL *Plus34
The SQL *Plus Profile Files37
Selecting a .NET Data Provider39
Integrating Visual Studio and Oracle40
Server Explorer Database Connection40
Types of Oracle Objects Available44
Creating a Basic Connection45
The OracleConnection Class45
The "Hello, Oracle Here!" Sanity Check45
Chapter 1 Wrap-Up48
Chapter 2Retrieving Data49
Using the Application Templates50
The Console Application Template50
The Windows Forms Application Template51
Creating the Get Employees Sample53
Examining the Data Provider Classes55
The OracleConnection Class55
The OracleCommand Class59
The OracleParameter and OracleParameterCollection Classes66
The OracleDataReader Class73
Implementing Data Retrieval Techniques77
Using the TableDirect Method77
Controlling the Number of Rows Returned81
Bind Variables and the OracleParameter Class86
Using the DataReader Properties102
Using Visual Studio and the Microsoft Data Provider105
Chapter 2 Wrap-Up117
Chapter 3Manipulating Data119
Oracle Architecture Principles Still Apply119
Exploring Oracle's Locking Mechanism120
Multiversioning and Read Consistency121
Locking and MVRC in Action122
Using the Data Provider Classes129
The OracleParameter Class129
The OracleCommand Class133
The OracleDataAdapter Class133
The OracleCommandBuilder Class140
The Oracle Transaction Class142
Implementing DML Operations146
Using OracleDataAdapter and OracleCommandBuilder147
Performing Manual DML Operations152
Using Host Language Array Binding160
Using the Oracle Transaction Class166
Chapter 3 Wrap-Up177
Chapter 4Oracle Exception and Error Classes179
The OracleException Class179
The DataSource Property181
The Errors Property181
The Message Property181
The Number Property181
The Procedure Property181
The Source Property182
Using the OracleException Class182
The OracleError Class187
The OracleError Class Properties187
Using the OracleError Class188
The OracleErrorCollection Class190
An Exception Logging Approach194
Chapter 4 Wrap-Up201
Chapter 5Using PL/SQL from .NET203
Why Use PL/SQL?204
PL/SQL Packages205
Creating a Package Specification and Body205
Querying the user_source View208
Procedure and Function Overloading209
Anonymous PL/SQL Blocks210
Parameters and Return Values212
The load_table Method217
The insert_row Code217
The retrieve_row Code219
The calculate_points Code220
The get_team Code221
Running the Parameters Sample Application222
The Ref Cursor PL/SQL Data Type224
Declaring a Ref Cursor Variable in PL/SQL224
Returning Result Sets from PL/SQL225
The call_function Code228
The call_procedure Code229
Running the RefCursor Sample Application230
Performing Bulk Operations231
PL/SQL Associative Arrays231
Using Bulk Binding233
The Associative Array Bulk Sample Application234
The truncate_table Helper Method Code237
The associative_insert Method Code238
The associative_select Method Code240
Running the Associative Sample243
Chapter 5 Wrap-Up244
Chapter 6Working with Large Objects247
Overview of Oracle Large Objects248
The BFILE Large Object Type248
The CLOB Large Object Type249
The NCLOB Large Object Type249
The BLOB Large Object Type249
Working with BFILE Objects250
The OracleBFile Class250
The BFile Sample257
Working with CLOB and NCLOB Objects263
The OracleClob Class264
The CLOB Sample268
Working with BLOB Objects276
The BLOB Sample276
Chapter 6 Wrap-Up283
Chapter 7Advanced Connections and Authentication285
The Default Database Connection286
Using tnsnames-less Connections290
Privileged and Non-Privileged Connections296
The SYSDBA and SYSOPER Privileges296
Connecting as a Privileged User301
Connecting via Operating System Authentication304
Configuring Oracle for Operating System Authentication305
Testing Operating System Authentication307
Operating System Authentication in .NET308
Password Management310
Changing a Database Password311
Dealing with Expired Database Passwords314
Locking Out a Database Account319
Connection Pooling and Multiplexing322
Chapter 7 Wrap-Up330
Chapter 8Performing Common Tasks with Oracle Features331
Using Anonymous Database Access in a Web Application331
The ASPNET User332
Enabling Anonymous Access334
Creating Paged Results in a Web Application338
Using a Sequence Object to Generate a Primary Key Value347
The Increment by Property348
The Start with Property348
The Maxvalue/Nomaxvalue Property348
The Minvalue/Nominvalue Property348
The Cycle/Nocycle Property348
The Cache/Nocache Property349
The Order/Noorder Property349
Creating Sequences349
Using Sequences352
The Sequence Sample Code353
Creating a Top-N Query357
Chapter 8 Wrap-Up363
Chapter 9SQL Tracing365
Enabling Data Provider Tracing365
The TraceFileName Registry Key366
The TraceOption Registry Key366
The TraceLevel Registry Key366
Creating a Data Provider Trace File367
Enabling Basic SQL Tracing368
Tracing a SQL Statement371
Formatting the Trace File Using tkprof372
Enabling Extended SQL Tracing375
Creating a Trace File from an Application378
Interpreting the Trace File384
The Trace File Header386
The Session Section386
The First Parsing Section387
The Second Parsing Section389
Chapter 9 Wrap-Up391
AppendixObtaining and Installing the Oracle Software393
Obtaining the Oracle Software393
About the Oracle Universal Installer394
Installing the Oracle Database 10g Software394
Installing the Oracle Database 10g Client Software412
Configuring the Registry to Use the Server Network Configuration419
Index423

Customer Reviews

Average Review:

Write a Review

and post it to your social network

     

Most Helpful Customer Reviews

See all customer reviews >

Pro .NET Oracle Programming 4 out of 5 based on 0 ratings. 1 reviews.
Guest More than 1 year ago
Is it just me, that does not immediately see an overlap between Oracle and .NET? Historically, Oracle databases have tended to be installed on unix workstations (especially Solaris). And nowadays, on linux as well. But Williams points out that given the widespread presence of .NET, and that Oracle is the dominant database, then we should indeed have an important market intersection of the two. Other books have described using C#/.NET with Microsoft's SQL Server. Not surprising, because Microsoft supplies all these parts. But this book is perhaps overdue, in filling a gap in the documentation of .NET and Oracle. The exposition herre is logical, and not unlike some of those other books. (Cf. 'SQL Server Query Performance Tuning Distilled' by Dam.) Except of course that you are shown how to optimise table design and complex queries for Oracle. Plus, Williams describes this for Oracle 10g Enterprise Edition - Oracle's latest flagship version. The book uses C# as the programming language, since it seems to be the best supported 'native' language of .NET. In the development environment, he gives some simple GUIs to access the database. Done often in VB.NET or Microsoft Window Forms. Realistically, any GUIs for your work will be more intricate. But you get enough GUI framework code to get you started along these lines.