Introduction
Foreword
Acknowledgments
PART I—Introducing Oracle
CHAPTER 1—Oracle and Visual Basic
A Brief History of Oracle
The Architecture of a Relational Database
File-Based Systems
Client/Server Architecture
Multitier Architecture
Network Computing Architecture
Client
Application Server
Universal Data Server
The Oracle Server
Standard Features
Scalability
The Oracle Server Advanced Features
Distributed Databases
Parallel Server
Replication
Visual Basic in an Oracle World
The Past
The Present
The Future
Moving Up to Oracle
Moving from Microsoft Access
Moving from Microsoft SQL Server
Summary
CHAPTER 2—Oracle Database Architecture
Instances and Databases
Memory Structures
The Database Buffer Cache
Redo Log Buffer
Shared Pool
Library Cache
Data Dictionary Cache
Additional Memory Areas
Processes
Database Writer (DBWR)
Log Writer (LGWR)
System Monitor (SMON)
Process Monitor (PMON)
Archiver (ARCH)
Server Processes
Listener Process
Database Files
Control Files
Parameter File
Online Redo Log Files
Data Files
The Logical Structure
Data Concurrency and Data Consistency
Locking Strategies for Concurrency
How Is Consistency Achieved?
Schemas
Users
Tables
Indexes
Clusters
Sequence Generators
Procedures
Views
Synonyms
Tying It All Together: How Oracle Works
Summary
CHAPTER 3—Creating a Database
Administration Tools
SQL*Plus
Server Manager
NT Instance Manager
Oracle Enterprise Manager
Other Utility Programs
Oracle Installation
Services on NT
Oracle Database Assistant
Initialization Files
Starting Up and Shutting Down the Database
Starting Up a Database
Shutting Down a Database
Creating the Database
Rollback Segments
Temporary Tablespaces
Running the Script
Testing the Database
Your Default Users
Configuring the Network
Tnsnames.ora
Listener.ora
Operation of the Listener Process
Controlling the Listener Process
Summary
CHAPTER 4—Database Administration
Backup Procedures
Data Export and Impot
The Export Utility
The Export in Practice
The Impot Utility
Impot in Practice
Exports for Backups
Mirroring the Redo Logs
Adding New Log Files
Mirroring the Control Files
Ensuring the Archive Processes Are Active
Doing Hot Backups
Recovering a Database
Recovery Fundamentals
The Recovery
Setting Up Database Security
Creating Users
Using Operating-System Authentication
Assigning Privileges
System Privileges
Object Privileges
Granting Privileges
Using Roles
Predefined Roles
Stored Procedures to Enhance Security
Regular Maintenance Tasks
Daily
Weekly
Monthly
Using Enterprise Manager
Summary
CHAPTER 5—Database Design
The Conceptual Design
Entity-Relationship Diagrams
Adding the Attributes
Reviewing the Design
The Logical Design
Identifying the Record Types and Fields
Identifying Any Data Dependencies
Normalizing the Database
Ensure Attributes Are Atomic
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Higher Normalization Techniques
Simplifying the Design
De-normalizing the Database
Using Keys
Primary Keys
Surrogate Keys
Foreign Keys
Automating the Design Process
Summary
CHAPTER 6—Implementing a Database
Creating a Tablespace
Optional Parameters
Related Commands
Creating a Table
Oracle Data Types
Creating the Table at Last
Optional Parameters
Specifying the Storage Parameters
Related Commands
Expect Changes
Specifying Constraints
Column Constraints
Table Constraints
Implementing Referential Integrity
Specifying the Placement of Indexes
Creating Indexes
Creating and Using Sequences
Creating a Sequence
Optional Parameters
Maintaining a Sequence
Sequences in Practice
One Sequence Fits All
Many Sequences per Table
To Each Table Its Own Sequence
Audit Trails
Other Schema Objects
Clusters
Views
Synonyms
Setting Privileges
System Privileges
Object Privileges
Using Microsoft Database Designer
Database Diagrams
Creating Tables
Summary
PART II—Queries and Stored Procedures
CHAPTER 7—Queries
A SQL Overview
Types of SQL Commands
The Basic SELECT Command
SELECT Syntax
A Simple SELECT Statement
Narrowing the Selection
Sorting Your Results
Some Useful Operators
Calculated Columns
Oracle-Specific Functions
The DECODE Function
Group Functions
The GROUP BY Clause
The HAVING Clause
Queries from More Than One Table
Outer Joins
Subqueries
Subqueries That Return One Value
Subqueries That Return More Than One Row
Optimizing Your Queries
Efficient SELECT Statements
The Explain Statement
Specifying Schemas
Summary
CHAPTER 8—Updating Your Database
A Philosophy for Updating Tables
Inserting Rows
Single-Row Inserts
Multirow Inserts
Updating Rows
Deleting Rows
Transaction Control
Transaction Control in Action
Security
Database Triggers
Bulk Loading of Data
CREATE TABLE AS Subquery
SQL*LOADER
Roll Your Own
Dates and the Year 2000 Problem
Summary
CHAPTER 9—Introduction to PL/SQL
PL/SQL as an Extension of SQL
Stored Procedures
The Advantages of Stored Procedures
Procedure Structure
Function Structure
Trigger Structure
Procedure Parameters
Packages
Creating a Package
Built-In Packages
Tools for Creating a Stored Procedure
SQL*Plus
Executing the Script
Executing the Stored Procedure
Debugging Your Procedure
Visual Database Tools
Starting Up
Entering a Script
Executing a Procedure
Summary of Visual Database Tools
Oracle Procedure Builder
Using Procedure Builder
The Object Navigator
Creating a Local Program Unit
Running the Code with the PL/SQL
Interpreter
Handling Output Parameters
Debugging Your Procedure
The Stored Program Unit Editor
Error Handling in PL/SQL
Enhancing the Error Message
Summary
CHAPTER 10—Creating Stored Procedures
PL/SQL Data Types
Scalar Data Types
User-Defined (Composite) Variables
Declaring Variables
Using %TYPE and %ROWTYPE
Declaring Constants
Statements and Assignments
Flow Control Statements
Decision Flow Control
IF…THEN
IF…THEN…ELSE
IF…THEN…ELSIF
Loops
LOOP… END LOOP
WHILE…LOOP
FOR…LOOP
Using SQL Statements in PL/SQL
Update Statements
Using SELECT INTO
Exceptions in SELECT INTO
PL/SQL Cursors
Declaring a PL/SQL Cursor
Opening and Closing a PL/SQL Cursor
Fetching a PL/SQL Cursor
Error Conditions for PL/SQL Cursors
PL/SQL Cursors in Action
PL/SQL Cursor Loops
User-Defined Data Types
Records
PL/SQL Tables and Collections
Declaring a PL/SQL Table
Using a PL/SQL Table
Returning a PL/SQL Table to the Calling
Procedure
Implicit Loops for Tables
Cursor Variables
Declaring a Cursor Variable
Opening a Cursor Variable
Fetching Records
Closing the Cursor Variable
Passing Cursor Variables
Error Conditions
Using Triggers
Providing a Transaction Audit
Procedures and Visual Basic
Summary
PART III—Data-Access Technology
CHAPTER 11—Client-Side Technology
The Software Layers
The ODBC Driver Manager
The ODBC Driver
The OLE-DB Layer
The Oracle Call Interface
Net8 and SQL*Net
The Network Software
The Listener Process
Server Processes
Setting Up Net8 and SQL*Net
Making a Connection
Testing Your Network Setup
Potential Problem Areas
Failover Capabilities
ODBC
The Origins of ODBC
ODBC Drivers
ODBC Driver Categories
ODBC Conformance Levels
Selecting an ODBC Driver
Sources of ODBC Drivers
Microsoft ODBC Driver
Oracle ODBC Driver
Setting Up an ODBC Data Source
Creating an ODBC Connection String
Testing Your ODBC Connection
Potential Problems with the ODBC Setup
OLE-DB
OLE-DB Cursors
Cursors
Static Cursors
Dynamic Cursors
Keyset Cursors
Mixed Cursors
Firehose Cursors
How Cursors Work
Choosing a Cursor
Cursors in Action
Connections
Multiple Connections
DSN-less Connections
Closing an ODBC Connection
ODBC Connection Pooling
Oracle Multithreaded Server
Prestarted Connections
Alternative Access Methods
Oracle Precompilers
Oracle Objects for OLE
Summary
CHAPTER 12—Accessing Oracle from Visual
Basic
The JET Engine
Linked Tables
Linking an Oracle Table to Access
Typical Code
Data Access Objects
Features
Typical Code
Data Controls
Remote Data Objects
Features
Typical Code
ODBCDirect
Features
Typical Code
The ODBC API
Features
Disadvantages
Typical Code
ActiveX Data Objects
Features
Typical Code
Oracle Objects for OLE
Features
Typical Code
Comparison of Object Models
Exporting an Access Table to Oracle
Microsoft Access Export
Viewing an Exported Table in Oracle
Oracle Export Wizard
Summary
CHAPTER 13—Visual Basic Design Issues
Logging On to the Database
Views, Synonyms, and Schemas
Cursor Selection
Querying the Database
Updating the Database
Transaction Control
Using Visual Basic to Control Transactions
Using Oracle to Control Transactions
Using a Transaction Server
Locking Issues
Using BLOB-Type Data
Error Handling
Trapping Errors
Developing an Error Message
Logging Errors
Oracle Errors
Summary
CHAPTER 14—Data Access Objects
The DAO Object Hierarchy
DBEngine
Workspace
Groups and Users
Database
Accessing a Database
Database Object Properties
Creating Recordsets
Creating Updatable Recordsets
Creating Read-Only Recordsets
Recordset Properties
Exploring the Recordset Methods
Populating the Recordset
Changing the Current Record
Accessing the Fields of a Recordset
Changing the Records and Values in a
Recordset
Finding a Particular Record
Closing a Recordset
Using the GetRows Method
Executing SQL Commands
Calling Stored Procedures
Error Handling
Putting It All Together
Viewing the Structure of a Database
TableDefs
Fields
Indexes
A Practical Application of TableDefs
Drawbacks to DAO
Summary
PART IV—Advanced Oracle Techniques
CHAPTER 15—Remote Data Objects
The Object Hierarchy
rdoEngine
rdoEnvironments
rdoConnections
rdoResultsets
rdoQueries
rdoTables
Databases and Connections
Creating an rdoEnvironment Object
Creating a Connection
DSN-less Connections
rdoConnection Properties
rdoConnection Events
Working with rdoResultsets
Creating Updatable Result Sets
Creating Read-Only Result Sets
Scrollable Result Sets
Forward-Only Result Sets
Using rdoResultset Events
Exploring rdoResultset Methods and Properties
Populating the rdoResultset
Changing the Current Row
Accessing the Fields of a rdoResultset
Changing the Rows and Values in a
rdoResultset
Finding a Particular Row
Fetching Multiple Rows
Closing a rdoResultset
Using rdoQuery Objects
Executing SQL Commands
Calling Stored Procedures
Calling PL/SQL Functions
Asynchronous Operation
Checking for Completion
Using Events
Asynchronous Operation in Practice
Retrieving PL/SQL Tables
Error Handling
Putting It All Together
Viewing the Structure of a Database
rdoTables
rdoColumns
Indexes
Disadvantages of RDO
The Future of RDO
Summary
CHAPTER 16—ODBCDirect
The ODBCDirect Object Hierarchy
DBEngine
Workspace
Database
Connection
Accessing ODBCDirect
Making the Connection
Creating Recordsets
Using QueryDefs
QueryDef Properties
Calling Stored Procedures
Asynchronous Operations
Drawbacks to ODBCDirect
Summary
CHAPTER 17—ActiveX Data Objects
The ADO Object Model
Connection
Recordset
Command
Parameter
Field
Error
Property
Connecting to Oracle
OLE-DB Provider for ODBC
Native OLE-DB Provider for Oracle
Connection Events
Working with Recordsets
Other Ways to Create Recordsets
Exploring Recordset Methods and Properties
Populating the Recordset
Fetching Multiple Rows
Changing the Current Row
Accessing the Fields of a Recordset
Changing the Rows and Values in a
Recordset
Finding a Particular Row
Closing a Recordset
Recordset Events
The Command Object
Command Parameters
Executing SQL Commands
Calling Stored Procedures
Calling PL/SQL Functions
Retrieving PL/SQL Tables
Putting It All Together
Asynchronous Operation
Looking at the Database Structure
The OpenSchema Method
Error Handling
Using the Data Environment
Drawbacks to ADO
Summary
CHAPTER 18—Oracle Objects for OLE
The Oracle Objects for OLE Object Hierarchy
OraClient
OraSession
OraConnection
OraDatabase
The Early Binding Issue
Accessing a Database
Database Object Methods
Creating OraDynasets
Making Use of Triggers
Tuning the OraDynaset
OraDynaset Properties
Exploring the OraDynaset Methods
Populating the OraDynaset
Changing the Current Record
Accessing the Fields of an OraDynaset
Changing the Records and Values in an
OraDynaset
Finding a Particular Record
Closing a Dynaset
Using Parameters
Executing SQL Commands
Calling Stored Procedures
Retrieving Cursor Variables
Calling PL/SQL Functions
Retrieving PL/SQL Tables
Using OraSQLStmts
Batch Updates
Error Handling
Putting It All Together
Viewing the Structure of a Database
Drawbacks to OO4O
Summary
Epilogue
Index
Download
Another Visual Basic Books
Another Database Books
No comments:
Post a Comment