Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle Objects for OLE?

• Oracle Database 11g Release 1 (11.1) and Release 2 (11.2) New Features
• Oracle Database 10g Release 2 (10.2) New Features
• Oracle Database 10g Release 1 (10.1) New Features

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