Contents

Title and Copyright Information

Preface

Audience

Documentation Accessibility

Related Documents

Conventions

What's New in Oracle Objects for OLE?

Oracle Database 10g Release 2 (10.2) New Features in Oracle Objects for OLE

Oracle Database 10g Release 1 (10.1) New Features in Oracle Objects for OLE

Oracle9i Release 2 (9.2) New Features in Oracle Objects for OLE

Oracle9i Release 1 (9.0.1) New Features in Oracle Objects for OLE

1 Introducing Oracle Objects for OLE

Overview of Oracle Objects for OLE

Oracle Objects for OLE In-Process Automation Server

Oracle Data Control

Oracle Objects for OLE C++ Class Library

Required Setups

Installation

System Requirements

Other Requirements

Oracle Objects for OLE File Locations

Component Certifications

Oracle Objects for OLE Redistributable Files

Redistributable File Locations

Updating Files and Registrations

2 Using Oracle Objects for OLE with Automation Clients

Using Automation Clients Overview

Demonstration Schema and Code Examples

Demonstration Schema Creation

Demonstration Schema

Other Schemas

Related Files

Using Oracle Objects for OLE Automation with Visual Basic

Using OO4O Automation with Active Server Pages (ASP)

Using Oracle Objects for OLE Automation with Excel

Using Microsoft C++

Using Oracle Data Control with Visual Basic

Setting Oracle Data Control Properties with the Properties Window

Setting Oracle Data Control Properties Programmatically

Using the Oracle Data Control with MS Visual C++

3 Basic Features

Overview of Client Applications

Accessing the Oracle Objects for OLE Automation Server

Obtaining an OraSession Object

Obtaining an OraServer Object

Connecting to Oracle Database

Using OraServer for Connection Multiplexing

Executing Commands

Queries

Data Manipulation Language Statements

Updating Database Records

Deleting Rows from a Table

Inserting New Rows into a Table

Thread Safety

Using the Connection Pool Management Facility

Creating the Connection Pool

Obtaining from and Returning Objects to the Pool

Destroying the Pool

Accessing the Pool attributes

Processing Transactions Using the Database from the Connection Pool

Detection of Lost Connections

PL/SQL Support

PL/SQL Integration with Oracle Objects for OLE

Executing PL/SQL Blocks Using ExecuteSQL and CreateSQL

Returning PL/SQL Cursor Variables

Returning PL/SQL Tables

Executing Data Definition Language Statements

Transaction Control

Microsoft Transaction Server Support

Asynchronous Processing

Nonblocking Mode

Checking the Status of a Nonblocking Operation

Canceling a Nonblocking Operation

Executing Multiple Queries in Asynchronous Mode

Limitations on Nonblocking

4 Advanced OO4O Features

Support for Oracle Object-Relational and LOB Data Types

Instantiating Oracle LOBs, Objects, and Collections

Oracle LOBs, Objects, and Collections

Using Large Objects (LOBs)

LOB Data Types

Using OraBLOB and OraCLOB

Retrieving LOBs From the Database

Using an OraDynaset Object

Using a Parameter object

Performance Considerations with LOB Read and Write

Single-Piece Operation

Multiple-Piece Operation

LOB Buffering Option

Writing LOB Data

Single-Piece Write Operation

Multiple-Piece Write Operation

Reading LOB Data

Single-Piece Read Operation

Multiple-Piece Read Operation

Oracle Object Data Types

About the OraObject Interface

Using the OraObject Interface

Retrieving an Embedded/Value Instance from the Database

Accessing Attributes of an Embedded/Value Instance

Modifying Attributes of an Embedded/Value Instance

Executing a Member Method of an Oracle Object Instance

About the OraRef Interface

Using the OraRef Interface

Retrieving a REF from the Database

Accessing Attributes of a Referenceable Instance

Modifying Attributes of a Referenceable Instance

Oracle Collections

About the OraCollection Interface

Retrieving a Collection Type Instance from the Database

Using a Dynaset Object

Using a Parameter Object

Accessing Collection Elements

Modifying Collection Elements

Creating a VARRAY Collection Type

Creating a Dynaset from an OraCollection Object

Advanced Queueing Interfaces

Monitoring Messages

Database Events

Application Failover Notifications

Failover Notification Registration

Enabling Failover

XML Generation

Datetime and Interval Data Types

Obtaining Datetime and Interval Data Types

Descriptions of Datetime and Interval Data Types

Database Schema Objects

5 Tuning and Troubleshooting

Tips and Techniques for Performance Tuning

Early Binding of OO4O Objects

Tuning and Customization

Avoiding Multiple Object Reference

Parameter Bindings

Array Processing

Using Read-Only, Forward-Only Dynaset

Using the PL/SQL Bulk Collection Feature

Migration from LONG RAW to LOB or BFILE

Using Connection Pooling

Oracle Objects for OLE Error Handling

OLE Automation Errors

Nonblocking Errors

Find Method Parser Errors

Find Method Run-Time Errors

OraObject Instance Errors

LOB Errors

Oracle Streams Advanced Queuing Errors

OraCollection Errors

OraNumber Errors

Oracle Errors

Oracle Data Control Errors

Troubleshooting

OLE Initialization or OLE Automation Errors

Oracle Network Errors

Access Violations

6 Quick Tour with Visual Basic

Introduction

About the Employee Database Application

Employee Form

Batch Insert Form

Getting Started: Steps to Accessing Oracle Data

Completed Sample Form_Load Procedure

Programming a Data Entry Form

About the Employee Form

Navigating Through Data

Moving to First or Last Rows

Moving to the Previous Row

Moving to the Next Row

Adding Records

Coding the Add Button

Coding the Commit Button (Add)

Updating Records

Coding the Update Button

Coding the Commit Button to Add and Update Records

Deleting Records

Querying the Database

Using Batch Insert

Programming a Batch Form

About the Batch Insert Form

Coding the Batch Insert Form_Load( ) Procedure

Coding the CmdAddtoGrid( ) Procedure

Coding the CommitGrid_Click( ) Procedure

7 Code Wizard for Stored Procedures

Oracle Objects for OLE Code Wizard Components

Data Types Supported by the OO4O Code Wizard

Using the OO4O Code Wizard

OO4O Code Wizard Command-Line Utility

OO4O Code Wizard Visual Basic Wizard Add-in

Code Wizard Examples

Accessing a PL/SQL Stored Function with Visual Basic and Active Server Pages

Accessing a PL/SQL Stored Procedure Using the LOB Type with Visual Basic

Accessing a PL/SQL Stored Procedure Using the VARRAY Type with Visual Basic

Accessing a PL/SQL Stored Procedure Using the Oracle OBJECT Type with Visual Basic

8 Introduction to Automation Objects

Overview of Automation Objects

OraSession Object Overview

OraServer Object Overview

OraDatabase Object Overview

OraDynaset Object Overview

OraField Object Overview

OraParameters Object Overview

OraParameter Object Overview

OraParamArray Object Overview

OraSQLStmt Object Overview

9 Server Objects

OraAQ Object

OraAQAgent Object

OraAQMsg Object

OraAttribute Object

OraBFILE Object

OraBLOB, OraCLOB Objects

OraClient Object

OraCollection Object

OraConnection Object

OraDatabase Object

OraDynaset Object

OraField Object

OraIntervalDS Object

OraIntervalYM Object

OraMDAttribute Object

OraMetaData Object

OraNumber Object

OraObject Object

OraParamArray Object

OraParameter Object

OraRef Object

OraServer Object

OraSession Object

OraSQLStmt Object

OraSubscription Object

OraTimeStamp Object

OraTimeStampTZ Object

OraConnections Collection

OraFields Collection

OraParameters Collection

OraSessions Collection

OraSubscriptions Collection

10 Server Methods

Abs Method

Add Method

Add (OraIntervalDS) Method

Add (OraIntervalYM) Method

Add (OraNumber) Method

Add (OraSubscriptions Collection) Method

AddIntervalDS Method

AddIntervalYM Method

AddNew Method

AddTable Method

Append (OraCollection) Method

Append (OraLOB) Method

AppendChunk Method

AppendChunkByte Method

AQAgent (OraAQMsg) Method

AQMsg (OraAQ) Method

ArcCos (OraNumber) Method

ArcSin (OraNumber) Method

ArcTan (OraNumber) Method

ArcTan2 (OraNumber) Method

Attribute (OraMetaData) Method

AutoBindDisable Method

AutoBindEnable Method

BeginTrans Method

Cancel Method

CancelEdit (OraRef) Method

Ceil (OraNumber) Method

ChangePassword (OraServer) Method

ChangePassword (OraSession) Method

Clone Method

Clone (OraLOB/BFILE) Method

Clone (OraCollection) Method

Clone (OraIntervalDS) Method

Clone (OraIntervalYM) Method

Clone (OraNumber) Method

Clone (OraObject/Ref) Method

Clone (OraTimeStamp) Method

Clone (OraTimeStampTZ) Method

Close Method

Close (OraBFILE) Method

CloseAll (OraBFILE) Method

CommitTrans Method

Compare (OraLOB) Method

ConnectSession Method

CopyToClipboard Method

Copy (OraLOB) Method

CopyFromFile (OraLOB) Method

CopyFromBFILE (OraLOB) Method

CopyToFile (OraLOB/BFILE) Method

Cos (OraNumber) Method

CreateAQ Method

CreateCustomDynaset Method

CreateDatabasePool Method

CreateDynaset Method

CreateIterator Method

CreateNamedSession Method

CreateOraIntervalDS Method

CreateOraIntervalYM Method

CreateOraNumber Method

CreateOraObject (OraDatabase) Method

CreateOraTimeStamp Method

CreateOraTimeStampTZ Method

CreatePLSQLCustomDynaset Method

CreatePLSQLDynaset Method

CreateSession Method

CreateSQL Method

CreateTempBLOB/CLOB Method

Delete Method

Delete (OraCollection) Method

Delete (OraRef) Method

DeleteIterator Method

Dequeue (OraAQ) Method

Describe Method

DestroyDatabasePool Method

DisableBuffering (OraLOB) Method

Div (OraIntervalDS) Method

Div (OraIntervalYM) Method

Div (OraNumber) Method

DynasetCacheParams Method

Edit Method

Edit (OraRef) Method

ElementValue Method

EnableBuffering (OraLOB) Method

Enqueue (OraAQ) Method

Erase (OraLOB) Method

ExecuteSQL Method

Exist (OraCollection) Method

Exp (OraNumber) Method

FetchOraRef Method

FieldSize Method

FindFirst, FindLast, FindNext, and FindPrevious Methods

Floor (OraNumber) Method

FlushBuffer (OraLOB) Method

GetDatabaseFromPool Method

GetChunk Method

GetChunkByte Method

GetChunkByteEx Method

GetXML Method

GetXMLToFile Method

GetRows Method

Get_Value Method

HypCos (OraNumber) Method

HypSin (OraNumber) Method

HypTan (OraNumber) Method

InitIterator Method

IsEqual (OraIntervalDS) Method

IsEqual (OraIntervalYM) Method

IsEqual (OraNumber) Method

IsEqual (OraTimeStamp) Method

IsEqual (OraTimeStampTZ) Method

IsGreater (OraIntervalDS) Method

IsGreater (OraIntervalYM) Method

IsGreater (OraNumber) Method

IsGreater (OraTimeStamp) Method

IsGreater (OraTimeStampTZ) Method

IsLess (OraIntervalDS) Method

IsLess (OraIntervalYM) Method

IsLess (OraNumber) Method

IsLess (OraTimeStamp) Method

IsLess (OraTimeStampTZ) Method

IterNext Method

IterPrev Method

LastServerErrReset Method

Ln (OraNumber) Method

Log (OraNumber) Method

MatchPos (OraLOB/BFILE) Method

Mod (OraNumber) Method

MonitorForFailover Method

MonitorStart (OraAQ) Method

MonitorStop (OraAQ) Method

MoveFirst, MoveLast, MoveNext, and MovePrevious Methods

MovePreviousn, MoveNextn, MoveRel, and MoveTo Methods

Mul (OraIntervalDS) Method

Mul (OraIntervalYM) Method

Mul (OraNumber) Method

Neg (OraIntervalDS) Method

Neg (OraIntervalYM) Method

Neg (OraNumber) Method

Open (OraServer) Method

Open (OraBFILE) Method

OpenDatabase Method

OriginalItem Method

OriginalName

Power (OraNumber) Method

Put_Value Method

Read (OraLOB/BFILE) Method

ReadChunk Method

Refresh Method

Refresh (OraRef) Method

Register Method

Remove Method

Remove (OraSubscriptions Collection) Method

RemoveFromPool Method

ResetTrans Method

Rollback Method

Round (OraNumber) Method

SetPi (OraNumber) Method

Sin (OraNumber) Method

Sqrt (OraNumber) Method

Sub (OraIntervalDS) Method

Sub (OraIntervalYM) Method

Sub (OraNumber) Method

Tan (OraNumber) Method

ToDate Method

ToOraNumber (OraIntervalDS) Method

ToOraTimeStamp Method

ToOraTimeStampLTZ Method

ToOraTimeStampTZ Method

ToUniversalTime Method

Trim (OraCollection) Method

Trim (OraLOB) Method

Trunc (OraNumber) Method

Unregister Method

Update Method

Update (OraRef) Method

Write (OraLOB) Method

11 Server Properties

Address (OraAQAgent) Property

ArraySize Property

AutoCommit Property

BOC Property

BOF Property

Bookmark Property

BookMarkable Property

CacheBlocks Property

CacheChanged Property

CacheMaximumSize Property

CacheOptimalSize Property

CacheSliceSize Property

CacheSlicesPerBlock Property

Client Property

Connect Property

Connection Property

ConnectionOK Property

Connections Property

Consumer (OraAQ) Property

Correlate (OraAQ) Property

Correlation (OraAQMsg) Property

Count Property

Count (OraMetaData) Property

Count (OraObject/Ref) Property

Database Property

DatabaseName Property

Databases Property

Day (OraTimeStamp) Property

Day (OraTimeStampTZ) Property

Days Property

DbPoolCurrentSize Property

DbPoolInitialSize Property

DbPoolMaxSize Property

Delay (OraAQMsg) Property

DequeueMode (OraAQ) Property

DequeueMsgId (OraAQ) Property

DirectoryName Property

DynasetOption Property

EditMode Property

EditOption (OraRef) Property

ElementType Property

EOC Property

EOF Property

ExceptionQueue Property

Exists Property

Expiration (OraAQMsg) Property

FetchLimit Property

FetchSize Property

FieldIndex Property

FieldName Property

FieldOriginalName Property

FieldOriginalNameIndex Property

Fields Property

FileName Property

Filter Property

Format (OraNumber) Property

Format (OraTimeStamp) Property

Format (OraTimeStampTZ) Property

HexValue (OraRef) Property

Hour (OraTimeStamp) Property

Hour (OraTimeStampTZ) Property

Hours Property

IsLocator (OraCollection) Property

IsMDObject Property

IsNull (OraCollection) Property

IsNull (OraLOB/BFILE) Property

IsNull (OraObject) Property

IsOpen (OraBFILE) Property

IsRefNull (OraRef) Property

LastErrorText Property

LastModified Property

LastServerErr Property

LastServerErrPos Property

LastServerErrText Property

MaxSize (OraCollection) Property

MinimumSize Property

Minute (OraTimeStamp) Property

Minute (OraTimeStampTZ) Property

Minutes Property

Month (OraTimeStamp) Property

Month (OraTimeStampTZ) Property

Months Property

Name Property

Name (AQAgent) Property

Name (OraAttribute) Property

Name (OraMDAttribute) Property

Nanosecond(OraTimeStamp) Property

Nanonsecond (OraTimeStampTZ) Property

Nanonseconds Property

Navigation (OraAQ) Property

NoMatch Property

NonBlockingState Property

Offset (OraLOB/BFILE) Property

OIPVersionNumber Property

Options Property

OraIDataType Property

OraMaxDSize Property

OraMaxSize Property

OraNullOK Property

OraPrecision Property

OraScale Property

Parameters Property

PinOption (OraRef) Property

PollingAmount Property

Priority (OraAQMsg) Property

RDMSVersion Property

RecordCount Property

RelMsgId (OraAQ) Property

RowPosition Property

SafeArray (OraCollection) Property

Second (OraTimeStamp) Property

Second (OraTimeStampTZ) Property

Seconds Property

Server Property

ServerType Property

Session Property

Sessions Property

Size Property

Size (OraCollection) Property

Size (OraLOB and OraBFILE) Property

SnapShot Property

Sort Property

SQL Property

Status Property

Status (OraLOB/BFILE) Property

Subscriptions Property

TableName (OraRef) Property

TableSize (OraCollection) Property

TimeZone (OraTimeStampTZ) Property

TotalDays Property

TotalYears Property

Transactions Property

Truncated Property

Type Property

Type (OraAttribute) Property

Type (OraCollection) Property

Type (OraMetaData) Property

TypeName (OraObject and OraRef) Property

Updatable Property

Value Property

Value (OraAttribute) Property

Value (OraAQMsg) Property

Value (OraIntervalDS) Property

Value (OraIntervalYM) Property

Value (OraMDAttribute) Property

Value (OraNumber) Property

Value (OraTimeStamp) Property

Value (OraTimeStampTZ) Property

Version (OraObject and Ref) Property

Visible (OraAQ) Property

Wait (OraAQ) Property

XMLAsAttribute Property

XMLCollID Property

XMLEncodingTag Property

XMLNullIndicator Property

XMLOmitEncodingTag Property

XMLRowID Property

XMLRowsetTag Property

XMLRowTag Property

XMLTagName Property

XMLUpperCase Property

Year (OraTimeStamp) Property

Year (OraTimeStampTZ) Property

Years Property

12 Data Control Events

DragDrop Event

DragOver Event

Error Event

MouseDown Event

MouseMove Event

MouseUp Event

Reposition Event

Validate Event

13 Data Control Methods

Drag Method

Move Method

Refresh Method

UpdateControls Method

UpdateRecord Method

ZOrder Method

14 Data Control Properties

AllowMoveLast Property

AutoBinding Property

BackColor Property

Caption Property

Connect Property

Database Property

DatabaseName Property

DirtyWrite Property

DragIcon Property

DragMode Property

EditMode Property

Enabled Property

Font Property

ForeColor Property

Height Property

Index Property

Left Property

MousePointer Property

Name Property

NoRefetch Property

Options Property

OracleMode Property

ReadOnly Property

Recordset Property

RecordSource Property

Session Property

Tag Property

Top Property

TrailingBlanks Property

Visible Property

Width Property

A Appendix A

Oracle Data Types

Additional Schemas

Schema Objects Used in OraMetaData Examples

Schema Objects Used in LOB Data Type Examples

Schema Objects Used in the OraObject and OraRef Examples

Schema Objects Used in OraCollection Examples

Glossary

Index