To access the contents, click the chapter and section titles.
Oracler Programming with Visual Basicr
(Publisher: Sybex, Inc.)
Author(s): Nick Snowdon
ISBN: 0782123228
Publication Date: 10/01/98
- 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 Import
- The Export Utility
- The Export in Practice
- The Import Utility
- Import 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
[an error occurred while processing this directive]