PK  artistName Oracle Corporation book-info cover-image-hash 510022806 cover-image-path OEBPS/dcommon/oracle-logo.jpg package-file-hash 683691535 publisher-unique-id E17726-01 unique-id 568411160 genre Oracle Documentation itemName Oracle® Provider for OLE DB Developer's Guide for Microsoft Windows, 11g Release 2 (11.2.0.2) releaseDate 2010-10-12T14:41:30Z year 2010 PKP~qlPK PKYuPK Cover

Oracle Corporation

PK[pTOPK What's New in OraOLEDB?

What's New in OraOLEDB?

The following sections describe the new features in Oracle Provider for OLE DB (OraOLEDB):

New Features in Oracle Provider for OLE DB for Release 11.1.0.7.20

Oracle Provider for OLE DB release 11.1.0.7.20 includes the following:

  • Command Timeout and Command Cancel

    The CommandTimeout property determines how long OraOLEDB waits before it attempts to terminate the executed command. The Cancel command cancels the OraOLEDB command currently being executed.

    A new registry value, EnableCmdTimeout, allows developers to enable or disable the CommandTimeout property value.

New Features in Oracle Provider for OLE DB for Release 11.1

Oracle Provider for OLE DB release 11.1 includes the following:

  • Improved Statement and Metadata Caching

    This release enhances the existing caching infrastructure to cache OraOLEDB data buffers and metadata information. This enhancement is independent of Database version and is available for all supported Database versions. This feature provides significant performance improvement for the applications that execute the same statement repeatedly.

  • Enhanced Failover Capability

    Oracle Provider for OLE DB introduced new connection string attributes and registry entries to enhance failover capability.

  • Support of ADO Disconnected Recordsets

    Oracle Provider for OLE DB introduced a new connection string attribute and registry entry to support ADO disconnected recordsets.

PK-2-PK Oracle Provider for OLE DB Developer's Guide, 11g Release 2 (11.2) for Microsoft Windows

Oracle® Provider for OLE DB

Developer's Guide

11g Release 2 (11.2) for Microsoft Windows

E17726-01

October 2010

Oracle Provider for OLE DB Developer's Guide, 11g Release 2 (11.2) for Microsoft Windows

E17726-01

Copyright © 1999, 2010, Oracle and/or its affiliates. All rights reserved.

Contributors:  Janis Greenberg, Eric Belden, Riaz Ahmed, Kiminari Akiyama, Neeraj Gupta, Sinclair Hsu, Gopal Kirsur, Sunil Mushran, Rajendra Pingte, Helen Slattery, Valarie Moore, Vikhram Shetty, Sujith Somanathan, Alex Keh, Christian Shay

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

PK=PK Introduction to Oracle Provider for OLE DB

1 Introduction to Oracle Provider for OLE DB

This chapter introduces Oracle Provider for OLE DB (OraOLEDB).

This chapter contains these topics:

Overview of OLE DB

OLE DB is an open standard data access methodology which utilizes a set of Component Object Model (COM) interfaces for accessing and manipulating different types of data. These interfaces are available from various database providers.

OLE DB Design

The design of OLE DB centers around the concept of a consumer and a provider. Figure 1-1 is an illustration of the OLE DB system. The consumer represents the traditional client. The provider places data into a tabular format and returns it to the consumer.

Figure 1-1 OLE DB Flow

Description of Figure 1-1 follows
Description of "Figure 1-1 OLE DB Flow"

OLE DB Data Providers

OLE DB data providers are a set of COM components that transfer data from a data source to a consumer. An OLE DB Provider places that data in a tabular format in response to calls from a consumer. Providers can be simple or complex. A provider may return a table, it may allow the consumer to determine the format of that table, and it may perform operations on the data.

Each provider implements a standard set of COM interfaces to handle requests from the consumer. A provider may implement optional COM interfaces to provide additional functionality.

With the standard interfaces, any OLE DB consumer can access data from any provider. Because of COM components, consumers can access them in any programming language that supports COM, such as C++, Visual Basic, and Java.

OLE DB Data Consumers

The OLE DB data consumer is any application or tool that utilizes OLE DB interfaces of a provider to access a broad range of data.

Overview of OraOLEDB

Oracle Provider for OLE DB (OraOLEDB) is an OLE DB data provider that offers high performance and efficient access to Oracle data by OLE DB consumers.

In general, this developer's guide assumes that you are using OraOLEDB through OLE DB or ADO.

For sample code, the latest patches, and other technical information on the Oracle Provider for OLE DB, go to

http://otn.oracle.com/tech/windows/ole_db

With the advent of the .NET framework, support has been provided for using the OLEDB.NET Data Provider with OraOLEDB. With the proper connection attribute setting, an OLEDB.NET Data Provider can utilize OraOLEDB to access Oracle Database.


See Also:

"OLEDB.NET Data Provider Compatibility" for further information on support for OLEDB.NET Data Provider

System Requirements

The following items are required on a system to use Oracle Provider for OLE DB:

  • Windows Operating System:

    • 32-bit: Windows 7 (Professional, Enterprise, and Ultimate Editions), Windows Vista (Business, Enterprise, and Ultimate Editions), Windows Server 2008 (Standard, Enterprise, Datacenter, Web, and Foundation Editions), Windows Server 2003 R2 (all editions), Windows Server 2003 (all editions), or Windows XP Professional Edition.

      Oracle supports 32-bit Oracle Provider for OLE DB on x86, AMD64, and Intel EM64T processors on these operating systems.

    • x64: Windows 7 x64 (Professional, Enterprise, and Ultimate Editions), Windows Vista x64 (Business, Enterprise, and Ultimate Editions), Windows Server 2008 R2 x64 (Standard, Enterprise, Datacenter, Web, and Foundation Editions), Windows Server 2008 x64 (Standard, Enterprise, Datacenter, Web, and Foundation Editions), Windows Server 2003 x64 (all editions), Windows Server 2003 R2 x64 (all editions), or Windows XP x64.

      Oracle supports 32-bit Oracle Provider for OLE DB and 64-bit Oracle Provider for OLE DB for Windows x64 on these operating systems.

  • Access to an Oracle Database (Oracle 9.2 or later)

  • Oracle Client and Oracle Net Services (included with Oracle Provider for OLE DB installation).

  • Redistributable files provided with Microsoft Data Access Components (MDAC) 2.1 or higher are required by the provider. These files are available at the Microsoft Web site:

    http://msdn.microsoft.com/en-us/data/aa937730.aspx

  • Oracle Services for Microsoft Transaction Server release 11.2 or higher. This is required for consumers using Microsoft Transaction Server (MTS) or COM+.

OraOLEDB Installation

Oracle Provider for OLE DB is included as part of your Oracle installation. It contains the features and demos that illustrate how to use this product to solve real-world problems.


See Also:

The Oracle Database Installation Guide for Windows for installation instructions

During the installation process, the files listed in Table 1-1 are installed on the system. Some files have ver in their name to indicate the release version.

Table 1-1 Oracle Provider for OLE DB Files

FileDescriptionLocation

OraOLEDBver.dll

Oracle Provider for OLE DB

ORACLE_BASE\ORACLE_HOME\bin

OraOLEDBrfcver.dll

Oracle rowset file cache manager

ORACLE_BASE\ORACLE_HOME\bin

OraOLEDBrmcver.dll

Oracle rowset memory cache manager

ORACLE_BASE\ORACLE_HOME\bin

OraOLEDBrstver.dll

Oracle rowset

ORACLE_BASE\ORACLE_HOME\bin

OraOLEDBgmrver.dll

Oracle ODBC SQL parser

ORACLE_BASE\ORACLE_HOME\bin

OraOLEDBlangver.dll

where lang is the required language

Language-specific resource DLL

ORACLE_BASE\ORACLE_HOME\bin

OraOLEDBpusver.dll

Property descriptions

ORACLE_BASE\ORACLE_HOME\bin

OraOLEDButlver.dll

OraOLEDB utility DLL

ORACLE_BASE\ORACLE_HOME\bin

OraOLEDBver.tlb

OraOLEDB type library

ORACLE_BASE\ORACLE_HOME\bin

OraOLEDB.h

OraOLEDB header file

ORACLE_BASE\ORACLE_HOME\oledb\include

OraOLEDBver.lib

OraOLEDB library file

ORACLE_BASE\ORACLE_HOME\oledb\lib

OraOLEDBlang.msb

where lang is the required language

Language-specific message file

ORACLE_BASE\ORACLE_HOME\oledb\mesg

readme and documentation files

Release notes and online documentation

ORACLE_BASE\ORACLE_HOME\oledb\directory

sample files

Sample code

ORACLE_BASE\ORACLE_HOME\oledb\samples


Component Certifications

Oracle provides support information for components on various platforms, lists compatible client and database versions, and identifies patches and workaround information.

Find the latest certification information at My Oracle Support (formerly OracleMetaLink):

http://metalink.oracle.com/

You must register online before using My Oracle Support. After logging into My Oracle Support, select Product Lifecycle from the left column. From the Products Lifecycle page, click Certifications. Other Product Lifecycle options include Product Availability, Desupport Notices, and Alerts.

PKb&aFFPK Glossary

Glossary

Component Object Model (COM)

A binary standard that enables objects to interact with other objects, regardless of the programming language that each object was written in.

consumer

A consumer is any application or tool that calls to a data source or the interfaces of provider to access data. See provider.

Oracle Net Services

The Oracle client/server communication software that offers transparent operation to Oracle tools or databases over any type of network protocol and operating system.

PL/SQL

Procedural language extension to SQL provided by Oracle .

provider

A provider is an interface or set of components that provides data to a consumer. As the term is used with Oracle Provider for OLE DB, a data provider is a set of COM components that transfer data from a data source to a consumer, by placing the data in a tabular format when called for. See consumer.

stored procedure

A stored procedure is a PL/SQL block that are stored in an Oracle Database and can be called by name from an application.

PK@@ PK Preface

Preface

Based on an open standard, Oracle Provider for OLE DB (OraOLEDB) allows access to Oracle Databases. This documentation describes OraOLEDB's provider-specific features and properties.

This document describes the features of Oracle Database for Windows that apply to the Windows 2000, Windows XP, and Windows Server 2003 operating systems.

This Preface contains these topics:

Audience

Oracle Provider for OLE DB Developer's Guide is intended for programmers developing applications to access an Oracle Database using Oracle Provider for OLE DB. This documentation is also valuable to systems analysts, project managers, and others interested in the development of database applications.

To use this document, you must be familiar with OLE DB and have a working knowledge of application programming using Microsoft C/C++, Visual Basic, or ActiveX Data Objects (ADO). Knowledge of Component Object Model (COM) concepts are also useful.

Readers should also be familiar with the use of Structured Query Language (SQL) to access information in relational database systems.

Documentation Accessibility

Our goal is to make Oracle products, services, and supporting documentation accessible to all users, including users that are disabled. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at http://www.oracle.com/accessibility/.

Accessibility of Code Examples in Documentation

Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.

Accessibility of Links to External Web Sites in Documentation

This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/support/contact.html or visit http://www.oracle.com/accessibility/support.html if you are hearing impaired.

Related Documents

For more information, see these Oracle resources:

For information about Oracle error messages, see Oracle Database Error Messages. Oracle error message documentation is available only in HTML. If you only have access to the Oracle Documentation CD, you can browse the error messages by range. Once you find the specific range, use your browser's "find in page" feature to locate the specific message. When connected to the Internet, you can search for a specific error message using the error message search feature of the Oracle online documentation.

Many of the examples in this book use the sample schemas of the seed database, which is installed by default when you install Oracle. Refer to Oracle Database Sample Schemas for information on how these schemas were created and how you can use them yourself.

To download free release notes, installation documentation, white papers, or other collateral, please visit the Oracle Technology Network (OTN). You must register online before using OTN; registration is free and can be done at

http://otn.oracle.com/membership/

If you already have a username and password for OTN, then you can go directly to the documentation section of the OTN Web site at

http://otn.oracle.com/documentation/

For additional information, see:

http://www.microsoft.com

Conventions

The following text conventions are used in this document:

ConventionMeaning
boldfaceBoldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.
italicItalic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.
monospaceMonospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.

PKUt ''PK Index

Index

A  C  D  E  F  G  H  I  K  L  M  N  O  P  R  S  T  U  V 

A

ADO, 2.1.2.1, 2.1.2.8, 2.1.4.6
ADO Applications with OLE DB Services, 2.1.2.1.1
ADO.NET, 2.1.9
attributes
connection string, 2.1.2.3

C

C#
connection string, 2.1.9.1
example, 2.1.9.1
C++/COM, 2.1.2.1
C++/COM Applications with OLE DB Services, 2.1.2.1.2
CacheType
connection string attribute for rowsets, 2.1.2.3, 2.1.5.5
caching, 2.1.4.8
Cancel method, 2.1.4.9
case of object names, 2.1.5.9
ChunkSize
connection string attribute for rowsets, 2.1.2.3, 2.1.5.5
class ID
CLSID_OraOLEDB, 2.1.2
CLSCTX_INPROC_SERVER macro, 2.1.2
CLSID_MSDAINITIALIZE class, 2.1.2.1.1
CoCreateInstance
creating an instance of the data source object, 2.1.2
columns
metadata, A.4
commands, 2.1.4
parameters, 2.1.4.3
preparing, 2.1.4.2
CommandTimeout property, 2.1.4.9
compatibility with OLE DB Services, 2.1.2.1
Component Certifications
My Oracle Support, 1.5
connecting
Oracle databases supported, 2.1.2
to a specific database, 2.1.2
to an Oracle database, 2.1.2.2
to an Oracle database using ADO, 2.1.2.8
connection string attributes, 2.1.2.3
defaults, 2.1.2.4
registry, 2.1.2.4
rowsets, 2.1.5.5
consumers
OLE DB, 1.1.1
creating
an instance of the data source object, 2.1.2
rowsets, 2.1.5.1
Cursor Stability, 2.1.3.1.1
CursorType
tips for ADO programmers, 2.1.5.6
custom error objects
interfaces supported, A.3.7

D

data source
creating an instance of, 2.1.2
distributed transactions, 2.1.2.5
objects, 2.1.2
properties, A.2.1
data source info
properties, A.2.2
DataTable, 2.1.9.3
datatypes
mappings between Oracle datatypes and OLE DB types, A.1
mappings in rowsets and parameters, A.1
OLE DB, A.1
Oracle, A.1, A.1
date formats
NLS_DATE_FORMAT, 2.1.5.8
settings, 2.1.5.8
DBNotificationPort, 2.1.2.3, 2.1.2.6
DBNotifications, 2.1.2.3, 2.1.2.6
DBPROP_AUTH_PASSWORD property
setting, 2.1.2
DBPROP_AUTH_USERNAME property
enabling operating system authentication, 2.1.2.7
setting, 2.1.2
DBPROP_INIT_DATASOURCE property
setting, 2.1.2
DBPROP_INIT_OLEDBSERVICES property, 2.1.2.1
DBPROP_INIT_PROMPT property
setting, 2.1.2
DBPROP_INIT_PROVIDERSTRING property
enabling operating system authentication, 2.1.2.7
setting, 2.1.2.4
DBPROP_IROWSETUPDATE property
setting of other properties, A.2.5.1
DBPROP_SERVERDATAONINSERT property, 2.1.5.3
DBPROPSET_DBINIT property set
setting properties, 2.1.2, 2.1.2.4
debugging, A.5
DeferUpdChk, 2.1.2.3, 2.1.5.5
connection string attribute to indicate whether to defer updateability, 2.1.5.5
demobld.sql, 2.2.1, 2.2.1
design
OLE DB, 1.1.1
DistribTX
connection string attribute for commands, 2.1.2.3
Distributed Transactions, 2.1.2.5
distributed transactions, 2.1.3.1.2

E

EnableCmdTimeout registry value, 2.1.4.9.1
Enhanced Failover Capability, 2.1.2.6
enlistment, 2.1.2.5
distributed transactions, 2.1.2.5
errors
HRESULT, 2.1.8
OLE and COM, 2.1.8
examples, 2.1.9.1, 2.1.9.1
connecting to an Oracle database using ADO, 2.1.2.8
stored procedure returning multiple rowsets, 2.1.4.6
using OraOLEDB with Visual Basic, 2.2

F

features
new, Preface
Oracle Provider for OLE DB, 2.1
FetchSize
connection string attribute for rowsets, 2.1.2.3, 2.1.5.5
files
installed on system for Oracle Provider for OLE DB, 1.4
Oracle Provider for OLE DB, 1.4

G

global transactions, 2.1.3.1.2

H

HRESULT
error return code, 2.1.8

I

initialization and authorization
properties, A.2.3
installation, 1.4
files for Oracle Provider for OLE DB, 1.4
interface call traces, A.5
interfaces
custom error objects, A.3.7
rowsets, A.3.4
sessions, A.3.2
supported by Oracle Provider for OLE DB, A.3
transaction options, A.3.6

K

KEY_HOMENAME, 2.1.2.4

L

LOB support, 2.1.6
ISequentialStream interface, 2.1.6
LockType
tips for ADO programmers, 2.1.5.6

M

MDAC, 1.3
metadata, 2.1.4.8
metadata caching, 2.1.4.8
metadata columns
supported by Oracle Provider for OLE DB, A.4
MetaDataCacheSize, 2.1.2.3
Microsoft Data Access Components, 1.3
Microsoft Distributed Transaction Coordinator, 2.1.3.1.2
Microsoft Transaction Server, 2.1.3.1.2
MTS, see Microsoft Transaction Server
My Oracle Support, 1.5

N

NDataType, 2.1.2.3
NDatatype, 2.1.4.4
.NET, 2.1.9
New Features in Oracle Provider for OLE DB for Release 11.1, Preface
New Features in Oracle Provider for OLE DB for Release 11.1.0.7.20, Preface

O

object names
case, 2.1.5.9
OLE DB
consumers, 1.1.1
datatypes, A.1
design, 1.1.1
Microsoft web site, 1.3
providers, 1.1.1
OLE DB .NET Data Provider, 2.1.2.3
compatibility, 2.1.9
OLE DB Services
ADO Applications with, 2.1.2.1.1
C++/COM Applications with, 2.1.2.1.2
compatibility with, 2.1.2.1
OleDbDataAdapter.Update(), 2.1.9.3
OLEDB.NET, 2.1.2.3, 2.1.9
operating system authentication, 2.1.2.7
DBPROP_INIT_PROVIDERSTRING, 2.1.2.7, 2.1.2.7
Oracle
datatypes, A.1, A.1
Oracle Provider for OLE DB
class ID, 2.1.2
features, 2.1
provider-specific information, A
system requirements, 1.3
Oracle Services for Microsoft Transaction Server, 1.3, 2.1.3.1.2
OracleMetaLink, 1.5
OraOLEDB sessions, 2.1.3
OSAuthent
connection string attribute for data source, 2.1.2.3
enabling operating system authentication, 2.1.2.7

P

password expiration
connection string attribute, 2.1.2.8
PwdChgDlg, 2.1.2.8
performance, 2.1.4.8
PLSQLRSet, 2.1.4.4
connection string attribute for commands, 2.1.2.3
properties
data source, A.2.1
data source info, A.2.2
initialization and authorization, A.2.3
rowset, A.2.5
rowset implications, A.2.5.1
sessions, A.2.4
supported by Oracle Provider for OLE DB, A.2
providers
OLE DB, 1.1.1
PwdChgDlg
connection string attribute for commands, 2.1.2.3
connection string attribute for data source, 2.1.2.8

R

registry
default attribute values, 2.1.2.4
returning rowsets
stored procedures and functions, 2.1.4.5
ROWID, 2.1.9.3
rowsets, 2.1.5
creating, 2.1.5.1
creating with ICommand, 2.1.5.1
creating with IOpenRowset, 2.1.5.1
date formats, 2.1.5.8
interfaces supported, A.3.4
properties, A.2.5
property implications, A.2.5.1
returning with procedures and functions, 2.1.4.5
schema, 2.1.5.7
searching with IRowsetFind, 2.1.5.4
updatability, 2.1.5.2

S

schema rowsets, 2.1.5.7
Server Data on Insert property, 2.1.5.3
sessions
interfaces supported, A.3.2
objects, 2.1.3
properties, A.2.4
SPPrmDefVal, 2.1.2.3
SPPrmsLOB, 2.1.2.3, 2.1.4.4
StmtCacheSize, 2.1.2.3
stored procedures and functions
executing, 2.1.4.1
returning rowsets, 2.1.4.5
system requirements
Oracle Provider for OLE DB, 1.3

T

tips
for ADO programmers, 2.1.5.6
TraceCategory, A.5.1
TraceFileName, A.5.1
TraceLevel, A.5.1
tracing, A.5
transaction options
interfaces supported, A.3.6
transactions
distributed, 2.1.3.1.2
global, 2.1.3.1.2
isolation levels, 2.1.3.1.1
local, 2.1.3.1.1
types supported, 2.1.3.1

U

UCS-2 character set, 2.1.7
Unicode, 2.1.7
UseSessionFormat
connection string attribute for commands, 2.1.2.3

V

VB.NET
connection string, 2.1.9.1
examples, 2.1.9.1
VCharNull, 2.1.2.3
PK-G^B^PKU? *VބVha\Na (h(BRH,b.(0hL_ 2 飑@H6$I2dThCԈ\je`Lbi&Gd&_m):B餔x9%y Fg|کޜghjhzfxi·iOwjZh֊Ĥ姾&!6Ke!Fjꪵղ*+j{Ά{lkKlžꮫk o֙m6oW n+=@k 00EO^Y2{ADZ#{l'ߊ0vkr2J D;ACP$J{[4NSpLiHs\4cG}viLa;88ށVϼe5H.=wZHK9>roy.>x>_nឹƻ</ {˲I#8$bW> #)ԩ*Xj#V؉5B-n;cI75mv)mwyק]Vw VyYN}]M\E\vķp ɺSR(aK[˹; ǵ56i\g^!Af}X18uTN]ՎYg> )cWqdH>je6ڙ]YcncSgGن̳>^Fd)BЈNQA'E}t),i<4ELtD1JzҸfW}κַ{=N ;PKP."p k PK@ $H A $H1? A $H A $H@Ԙϟ? A $H A | AjH A $H A Rc> 5$H A $H A1H$H A $H AԘ$H $H A $H FO@ DPB  <0… :|1ĉ+Z1ƍ;z0_E~ _>@ $H A $HQO>} A$H A $H A1E}| /_~O`O_|˗ϟ|IH A $H A Rc>o>ۗ_>o`>_>'0_|$H A $H AԘbo`o`>O`>71? A H H H iP>$X`>o`>o_>} '0| ̗/_> 4(0? H*\ȰÇ#JHŋ3jȱǏ QO>}O|'0|'0| 'P_($H A $H A1_E~/_>}| O߿| '0~b>@ $H A $H1? A $H A $H@Ԙϟ? A $H A | AjH A $H A Rc> 5$H A $H A1H$H H H FO@ DPB  <0… :|1ĉ+Z1ƍ;z0H$H A $H AԘ$H $H A $H Aj| $H A $H 5 Rc>@ $H A $H1? A $H A $H8`A&T!|'p "Lp!ÆB(q"Ŋ/b̨q#ǎ?*O@ Dx ? 4xaB'p "Lp!ÆB(q"Ŋ/b̨q#ǎ?П <0!B$XA .dC%NXE5nG!E$YI)UdK1eΤYM9uOA%JE-ZhQ+ZhѢEe/_>}E-ZhѢ,˗_ѢE-ZJ~  <0… :|1ĉ+Z1ƍ;z2dH <0… :|1ĉ+Z1ƍ;z2G~ ? 4xaB 6tbD)VxcF9vdȏ (,h „ 2l!Ĉ'Rh"ƌ7r#Ȑ'p@$XA .dC%NXE5nG!;ӧO|O@ DPB >QD-^ĘQF=~|)RH"E)RH"E/_>~"E)RH"E)RH"%˗H"E)RH"E)RH'RH"E)RH"E)R|)RH"E)RH"E/_>~"E)RH"E)RH"%˗H"E)RH"E)RH'RH"E)RH"E)R|)RH"E)RH"E/_>~"E)RH"E)RH"%/?$XA .dC%NXE5nG!E/#G9rȑ#G9rȐ/>9rȑ#G9rȑ#GzO@8`A&TaC!F8bE1fԸcGA`>O@ DPB >QD-^ĘQF=~#|  <0… :|1ĉ+Z1ƍ;z2~ ? 4xaB 6tbD)VxcF9vdH(? 4xaB 6tbD)VxcF9vdȐ˗oH"E)RH"E)RȈ'RH"E)RH"E)R|)RH"E)RH"E/H"E)RH"E)RȊD)RH"E)RH"E)RH"E)RH"E)RH"E)RH"EI`> 4xaB 6 <0… :|1+Z1ƍ;z0H $H A $H 9 Rc>@ $H A $H1? A $H A $H@Ԙϟ? A $H A | AjH A $H A Rc> 5$H A $H A1H$H A $H AԘ$H $H A $H Aj| $H A $H 5 Rc>@ $H A H @8`A&T!|'p "Lp!ÆB(q"Ŋ/b̨q#ǎ?*̗1_|I_| $H A $H A1_|O@H A $H A Rc_'p_>'_>}/?廘ϟ? A $H A | O`|o߾߿|/߿|/_ $H A $H Aj̗1_| O`/| /|Øϟ? AH H H iP>$X | 70}O`˗/|$ϟ?$XA .dC%NXE5nG23o~/_~ | _ $H A $H Aj̗1|˧|/_~˗o|$H A $H AԘ$H $H A $H Aj| $H A $H 5 Rc>@ $H A $H1? A $H A $H@Ԙϟ? A $H A | Aj,h „ 2l!Ĉ'Rh"ƌ7r| AjH A $H A Rc> 5$H A $H A1H$H A $H AԘ$H $H A $H Aj| $H A $H 5O@ DPB  <0… :|1ĉ+Z1ƍ;z`? 4xA$XA  <0… :|1ĉ+Z1ƍ;zr"@,h „8`A&TaC!F8bE1fԸcGA9dI'QTeK/aƔ9fM7qԉΝ;wܹsΝ۹sΝ;wܹa|vܹsΝ;w4/_|;wܹsΝ;w̗/_~;wܹsΝ;w0@ H*\ȰÇ#JHŋ3jȱǏ CO~ H*\ȰÇ#JHŋ3jȱǏ C~08`A&TaC!F8bE1fԸcGA0'p "Lp!ÆB(q"Ŋ/b̨q#ǎ? Q|O@ DPB >QD-^ĘQF=~#| <0… :|1ĉ+Z1ƍ;z2$G˗O?D)RH"E)RH"E>/>"E)RH"E)RH˧OH"E)RH"E)R~)RH"E)RH"E)_|D)RH"E)RH"EJ/>"E)RH"E)RH˧OH"E)RH"E)R~)RH"E)RH"E)_|D)RH"E)RH"EJ/>"E)RH"E)H"$@ <0… :|1ĉ+Z1ƍ;z27rȑ#G9rȑ#Gҟ?ϟ#G9rȑ#G9rH H,h „ 2l!Ĉ'Rh"ƌ7r#Ȑ'p@$XA .dC%NXE5nG!='p} H*\ȰÇ#JHŋ3jȱǏ Cz08`A&TaC!F8bE1fԸcGA 08`A&TaC!F8bE1fԸcGA`>8`A&TaC!F8bE1fԸcGA/_|D)RH"E)RH"EFܗ/_>"E)RH"E)RH'RH"E)RH"E)r"|"E)RH"E)RH"+)RH"E)RH"E)RH"E)RH"E > 4xaB 6 <0… :|1ĉ+Z1ƍ;z0@~,h „ 2d? 4xaB 6tbD)VxcF9va> 5$H A $H A1H$H A $H AԘ$H $H A $H Aj| $H A $H 5/_}&)0? A $H A $H<_|$)0? A $H A $H<7>/_~O`>1? A $H A $H<+O`|/|_? 4x0? H*\ȰÇ#JHŋ3jȱǏ yW0|/| '0|$H A $H AԘc>o`} W0|ϟ? A $H A |/_?/|/}ۘϟ? A $H A |˧|߿~O`|qH A $H A Rc> 5$H A $H A1H$H A $H FO@ DPB  <0… :|1ĉ+Z1ƍ;z0|;b>ϟ? A $H A |'0|| $H A $H 5擘O>˗_|o_/߾_|_|˗_> $H A $H Aj'1|_} >}߿}˗_> '0|$H A $H AԘOb߿|?O߿|˗/߿߿_>$X_>8`A&TaC!F8bE1fԸcG擘O`>~˗/߿|Ǐ_> '0|O`b>@ $H A $HI'_| /}ӗO~o` '0|/| $H A $H 5擘o`|˷|_}˧ϟ| ̷_>/_~ $H A $H A1H/| $H A $H 5(,h „ /> ? 4xaB 6tbD)VxcF9vQa> 5$H A $H A1H$H A $H AԘ$H $H A $H Aj| $H A $H 5O@ DPB O@ DPB >QD-^ĘQF=~T0 <0… ? 4xaB 6tbD)VxcF9v$E$XA 'p "Lp!ÆB(q"/b̨q#ǎ?П <0aB$XA .dC%NXE5nG!E$YI)UdK1eΤYM9usΝ;wܹs'}vܹsΝ;w~;wܹsΝ;wԗ/_;wܹsΝ; ˗/_};wܹsΝ;w0@ H*\ȰÇ#JHŋ3jȱǏ C08`A&TaC!F8bE1fԸcGA`>'p "Lp!ÆB(q"Ŋ/b̨q#ǎ? |  <0… :|1ĉ+Z1ƍ;z2dG$? 4xaB 6tbD)VxcF9vdH/>)RH"E)RH"E|/_>~"E)RH"E)RH"%˗H"E)RH"E)RH'RH"E)RH"E)R|)RH"E)RH"E/_>~"E)RH"E)RH"%˗H"E)RH"E)RH'RH"E)RH"E)R|)RH"E)RH"E/_>~"E)RH"E)R$DI"? 4xaB 6tbD)VxcF9vdHoȑ#G9rȑ#G9r?9rȑ#G9rȑ#G O>}ӧO_#G9rȑ#G9rȎ8p ,h „ 2l!Ĉ'Rh"ƌ7r#Ȑ8P ?$XA .dC%NXE5nG!=O| H*\ȰÇ#JHŋ3jȱǏ CO~ H*\ȰÇ#JHŋ3jȱǏ C0@ H*\ȰÇ#JHŋ3jȱǏ C̗/_~"E)RH"E)RH"#˗/H"E)RH"E)Rȉ)RH"E)RH"E9>"E)RH"E)RH)RH"E)RH"E)RH"E)RH"E)RH"E)RH"E)R$Eo@}Ǐ~'p "Lp!ÆB(q"Ŋ/b̨q#ǎ? ɰ}8P @}O@ <0… :|1ĉ+Z1ƍ;z2?˗/>}џ?~˗/_})RH"E)RH"E*/_|_}˷H"E)RH"E)RdA~O~OH"E)RH"E)߾|ٰ߾|)RH"E)RH"?˗ȉ'RH"E)RH"E/_}"/˗ϟH"E)RH"E)|/_~"E)RH"E)RHO@ DP!B}'p "Lp!ÆB(q"Ŋ/b̨q#ǎ?/_H 2dȐ!C 2dȐ!C/_ 2dȐ!C 2dȐ!C _!/dȐ!C 2dȐ!C 2!/dȐ!C 2dȐ!C 2_!_Ȑ!C 2dȐ!C d|BO?!C 2dȐ!C 2dH 2_} 2d!C 2dȐ!AۗO_H_Ȑ!C 2dȐ!C >~Rc|_Ȑ!CRH!RH!RH ߿O@ DP}o? H*\ȰÇ#JHŋ3jȱǏ/_}@N/_}ӷϟ? A $H A $ȇܗ/~ ӗ/> ԷO ,h „ 2l!Ĉ'Rh"ƌ7rG} O_|y}П <0… :|1ĉ+Z1ƍ;zQӗ/_>}}o_|П <0… :|1ĉ+Z1ƍ;zQ/_|ϟO_|? ԷO ,h „ 2l!Ĉ'Rh"ƌ7rG} ;o~O 0ǯ@,(P> H*\ȰÇ#JHŋ3jȱǏ+ ? 'p  <0… :|1ĉ+Z1ƍ;zQ;O#@,HP> H*\ȰÇ#JHŋ3jȱǏ+П ԷO ,h „ 2l!Ĉ'Rh"ƌ7rG} ;$'p  <0… :|1ĉ+Z1ƍ;zQ;? H A}'p "Lp!ÆB(q"Ŋ/b̨q#ǎ?~ǯ;O"@,HP> H*\ȰÇ#JHŋ3jȱǏ+П ԷO ,h „ 2l!Ĉ'Rh"ƌ7rG} ;$'p  <0… :|1ĉ+Z1ƍ;zQ;? H A}'p "Lp!ÆB(q"Ŋ/b̨q#ǎ?~ǯ;O"@,HP> H*\ȰÇ#JHŋ3jȱǏ+П ԷO ,h „ 2l!Ĉ'Rh"ƌ7rG} ;$'p  <0… :|1ĉ+Z1ƍ;zQ;? H A}'p "Lp!ÆB(q"Ŋ/b̨q#ǎ?~ǯ˗O?+П ԷO ,h „ 2l!Ĉ'Rh"ƌ7rG} ;o`|篠? 'p  <0… :|1ĉ+Z1ƍ;zQ@ /}/_>~ /_?$Xp}8`A&TaC!F8bE1fԸcG?WA /|_>_>П ԷO ,h „ 2l!Ĉ'Rh"ƌ7rG} ;o`ϟ~O?? o@$XA .dC%NXE5nǏw|O`'0_|˗? o@$XA .dC%NXE5nǏw@~O`'0?'p@}8`A O@ DPB >QD-^ĘQF=~_A/~ ԗ_>O`>П ԷO ,h „ 2l!Ĉ'Rh"ƌ7rG} ;? H A}'p "Lp!ÆB(q"Ŋ/b̨q#ǎ?~ǯП ԷO ,h „ 2l!Ĉ'Rh"ƌ7rG} #/_| a> H A}'p "Lp!ÆB(q"Ŋ/b̨q#ǎ?~ǯ?a>'p  <0… :|1ĉ+Z1ƍ;zQ?O|˗/}_|˗@~o_|8?}'p "Lp!ÆB(q"Ŋ/b̨q#ǎ?~Oa>_>_>۷_|_|O@o@$XA .dC%NXE5nǏ)G0~O`?~ 70~o?/@o@$XA .dC%NXE5nǏ)7_>'0|o`>'߾|˗/_>$O> H*\ȰÇ#JHŋ3jȱǏSo>~O`>'0_ _>ϟ>O@ O@ DPB >QD-^ĘQF=~|7P_>~ӗϟ|_|/|'p A O@ DPB >QD-^ĘQF=~H H A}'p "Lp!ÆB(q"Ŋ/b̨q#ǎ?~$D$X}8`A&TaC!F8bE1fԸcG?"@,HP> H*\ȰÇ#JHŋ3jȱǏ ? $o@$XA .dC%NXE5nǏП ԷO ,h „ 2l!Ĉ'Rh"ƌ7rG}@BO@ '? 4xaB 6tbD)VxcF9v>~ !'p  <0… :|1ĉ+Z1ƍ;zQ?8`A O@ DPB >QD-^ĘQF=~H H A}'p "Lp!ÆB(q"Ŋ/b̨q#ǎ?~$D$X}8`A&TaC!F8bE1fԸcG?"@,HP> H*\ȰÇ#JHŋ3jȱǏ ? $o@$XA .dC%NXE5nǏ? /_? H*\ȰÇ#JHŋ3jȱǏ !@,(_>}8`A&TaC!F8bE1fԸcGA˗_H H~O@ DPB >QD-^ĘQF=~_|B.O@G? 4xaB 6tbD)VxcF9v$} ? /> H*\ȰÇ#JHŋ3jȱǏ ˗/_8?~W? 4xaB 6tbD)VxcF9v$~/$Ao_| <0… :|1ĉ+Z1ƍ;z}/? /_|  <0… :|1ĉ+Z1ƍ;za?~˗o?ӗ/_|'p?$XA .dC%NXE5nGO|Ǐ_?'p@O|ӷ@O@ DPB >QD-^ĘQF=~_~ӧ`> 4O>~8p ,h „ 2l!Ĉ'Rh"ƌ7r#Ȑ ؏?~8p?~П'p "Lp!ÆB(q"Ŋ/b̨q#ǎ? `? П8`A&TaC!F8bE1fԸcGA9dI'QTeK/aƔ9fM7qԹgO?:hQG&U3 ;;PK 55PKΑ;\:p JGP ˻8;HMsldMA5l؏NK}~vZq{ύ?=y7gͩSFO5vNwɋ޺ǷW^tuhg`a&[d&X 9hw=MfpQ&`i(']ME9]ލ}7e^!ѧynKrX!@"Neu[Fޘ3ڗf̑Iݗ5 jҞJm^NvQz]C[WFzt˔ o\ڇ/ rpp Z*YJvZw`!0ace c\j2ʃL91πs g<[&De4QSSʴYNm]@d͘boluU5`vX徤|ݒZǽjj#+ ^GOnWӅypm9[࢟Mz鈅XV뮣ߴn/1.o'< ;PK_5{vPK$(qzA4d'W,M ~o[HIVφ& *ᱦEoJG>M4P_:Qӡd [GjyQO]/.saSVǐ7VqȘ3k ϟL4fSVٴװ{MbOrͻ"4N/OμA,NǧcνڿO|ӫo~~🴏OϟAX & 6.(V@dx!L՝`V"acN#h% CbCVC z@eUi%\^9١UHlD# /!t֛J>{`zbԙ(`G&rfa$ni'gyzyJ<駨 &vAǧP☪$+Iy鰘** *9=;B+F+-䪺),~njyKhAyhƫ0*ʶ˩VN|1reڊG&A 'r*˴iĂҚ($ L{NpɊ=sL_weQV{[ts[WvncSA(lbG}_í~g:ړu{ܽۆ7_)>8WIw.i8oSX`s. n\;gnƠyz>}ؿ}}{N?"[Oλ/_ӯ7yM}LN'Hɀm#8})%P#<0 a:HR섀[\$(pQh#.ҷB9 VD#n={b5d! 8-b1w^4'/#̨@4lԟ89jH>񏱫ɇBf 82[$$'yQ$&7CGr$(GIFR\<*#VЕb,gFZRd$.s9]Ҏ%)/N<&2}e !4iRSּMq&8'q:Ӊ؉̝ @JЂMB<{D'JъZrh*юz H=Q(MJWҒCB Қ8K;?ͩPJԢaZ T:P*RMԠ4XͪVW:UŤ@V VJֲJ^*Sֶ5hEZ}ֺ(+ޮԵ%^7S԰Mb:~,jcZve7ٔf h-YvjW[QԚv|-b+[ѶĻ-nuݲ-pr#=:ЍtK꾓ͮvz M;PK#TOPKqRHɚ<7.ǐl󫸈^ZR+h*T)\̜MG^͚^ZO2,v`6vNxq+_|PПݼ넒cν;qV‹OMJϾˇ~>(KG&.F8^Vh^Hv衇~($b(bz'0:b4Et8<#)(O`đ3xE_D6dF eKf PJ>{Q8%VRe(lϡi?1l>YB''cc}Mz)>@>ei7iB irU:)n*b褊Iij:+:Zf7٦7n,&鮛:\A@lR⪮:ꮰrJf,Wn /^”6BAp깓F’iāN-tqel3.s̀-DK#*nd[3.3PgZ yQb]S}j} x'H7|vin'NŢ -WngyӐw标.sDN# { WÍLm:'7G/'/|xחݻu/oνy׽=H.o3\;y>*@Hp)пma3~Oԁ6|VA#m\ ^Ȃ h ]B~<H嫄3T [C ;! }VQ0 e#Y㊇B S|C( Mi\qFֱ<(GQ}ԣ ?[L$ @( QMTN&*ft! rb G)І=#=)V~,CZmXj'1 {[ mNvl#V|fF;Pf3mM̈xxItW0DRRЙg<XZ|eߘMc& t#&uK^0!0r(= ʀ]fHgy8Tq,-TGR~ٳ$ /U= ˭ý/T C%acרmuȈ[Uvn/E[AfKh:9 |d Wxǜs7m6lSfsS42 J2S39 Vt'1r9>l^&l? n]߁]?:\qF;ӝf{oBܩ0w!RT&m>|g)C[ ?vOUDXڭݷ^ ^ ˾Ǩ{nȗ=S'Ʊhm}Oúc|Kv߯ú`Qd(|`_!Zr)'|4Q*4AE^!dvogs-207UuGvrEdTq5^ ^kg~f~+25S}sZvn.t W0'h}n.~(SUSI(Z ց9hg;s={?hPAo*8PnU҂8Fx^G9VxDw(uTb"f-0d/FT{'a4q)pnGׅwu9YyH^gHQ6zRy&H5`hXKHQ|(V~7XK(#v8zzz؋\n'\Ĉ8a7\VE˸;Xo&5UOr (MyᨆZIB4n؀@Wm6Ovg7V3wb揩mȎh5`?};6o^$yr]F#W6]pqHFsWzxnTVa6Tx(s~XGExHvcnnv!huwv`Ei`.qn7rBw,tJFWrxE*f7f/)6ߦu xf>Wdtee?Ɇ6`cGTfoXؑ|ȇJiZ6oavetib})fgb7ّQȖəDiyVGo  {$2HNi rgugf&qWvWIْWILՖ֖xК!xC׉mC7AY#'ٞ9Yyٟ:Zz*shI#ɋР1,D  jqA>ނ'9"FN4AmqA `20 g`j5 0ڣQǠ~~ Ljp `? XJD:APpF: Dq u*(QgabrѧvZTJ0_F*ZNC3L&D IѨW~qzR ڪaʪ]* Jz@j\a9iLsnzʧʪVʬ* њ*jʥ0z)*"qZO:*|Ϫz*:tB* º[Ү٤Fբ{* K2 [_*k  dfS*Cڧڳ{aҀ[J*rO: +k; }`6 ]5`nprIs{tA|% # +?ɚŵW e"+zw$[V0.R8iUbƕ@ ͣ:%8Y☺x>jz8'i!XqD>h W,iyJ9OWhgko]ׇL؆"Uxwv`^ٗ7޻[4ҋkv+xxZ3[ի竓G鿴|&0`w'h>|iyGHĹO鵼UWj m߉X'o)l56|K+a$ܸ73 5Ùu mg 0y^膐{ıi{X xEp1 #d^l?9U)[ǀvG(27Lj:v⸜h`@9l;ry+ 50{2X؈uᗀ_<W)| \|SɬH~HщeXwɘ||p vɖ=J ~'ȦIZ4P3lL\b׼ylv(P‚2/pL\Txww˒O=]`=ʹQ΅܂\+rȅ[15ʩ}=jL&ש+ M]6LB<+` +J &-}C \p##"13n8-F}[̿5wwJlElVkJ ջ#ՊGնE(3M֡[GʼnIDUӬ/!h-~̀vTMk-`ˆl8yɭ`ovP ڪϮH!*z/}ʒM<) cf:=dxB7ĐQ ?'HP E+ķ ϱEZP7J"LncUnI-޻՝̔љH'}فى\ō~ʥƩuIͨi۝̾^IBtƑLKT8N$6My+nZ˺;ԵTF%źe4W~ >ζd"NDx>KM}Xۚ  ]NtZ峋nvfgz`OԶ]޼%Oinphmm 푔Qxz ϝ8nMUIVe<ٺ ƀLS+֝ymQMGIM_@nPͬ|GIr_]Ow2

o{{;m."W)7ݾ܅1EpR@bOW3XjnK\VX-[݂S0_#,82eݩIz$: 4(8ͻ`(W0試hp,tmx,-ۏ!/bINPIFIkserMdL*贇jN|N]`m88nJcTLY]_aPK\]jlnw8ypBI~^SS^¸z=FZdOٌn,07|#־+J(("JDL*y3 CNIɓ*2Ȳe{IM*W4sɳ iIѣrɔ)LBJի$ ڴ+˧3YC*ٳV#؟DƊB:X_jw m Z)aK9k˄33 j֙ޛjfyTٶ7bFR" دŀb+p[KuDs$ä;h rSCT}Q#oB#<<okd3 >ޓw97=/;_8˔~5& r 㔡uuyc;xÞ}v|wX\`r%nGem+؀(ue!Hv#xO(*,؂.02(#\Q6x8HWhFcBuDXZwHXuJQׄNtP5T8]Vx͕Zx\\؅38dXfxhj8Ԇnpr8tXvxxzX ;PKD! !PK Provider-Specific Information

A Provider-Specific Information

This appendix describes OLE DB information that is specific to Oracle Provider for OLE DB. For generic OLE DB information that includes a detailed listing of all OLE DB properties and interfaces, see the Microsoft OLE DB Programmer's Reference Guide.

This appendix contains these topics:

Data Type Mappings in Rowsets and Parameters

This section lists the data type mapping between Oracle data types and OLE DB-defined types. Oracle Provider for OLE DB represents Oracle data types by using certain OLE DB-defined data types in the rowset as well as in parameters. OLE DB-defined types are also mapped to an Oracle data type when creating tables.

Each Oracle data type is mapped to a specific OLE DB data type, as shown in Table A-1. This correspondence is used when data type information is retrieved from an Oracle Database.

Table A-1 Data Type Mappings

Oracle Data TypeOLE DB Data Type - Regular (NonUnicode) ModeOLE DB Data Type - Unicode Mode

BFILE

DBTYPE_BYTES

DBTYPE_BYTES

BINARY_FLOAT

DBTYPE_R4

DBTYPE_R4

BINARY_DOUBLE

DBTYPE_R8

DBTYPE_R8

BLOB

DBTYPE_BYTES

DBTYPE_BYTES

CHAR

DBTYPE_STR

DBTYPE_WSTR

CLOB

DBTYPE_STR

DBTYPE_WSTR

DATE

DBTYPE_DBTIMESTAMP

DBTYPE_DBTIMESTAMP

FLOAT

DBTYPE_R8

DBTYPE_R8

INTERVAL DAY TO SECOND

DBTYPE_STR

DBTYPE_WSTR

INTERVAL YEAR TO MONTH

DBTYPE_STR

DBTYPE_WSTR

LONG

DBTYPE_STR

DBTYPE_WSTR

LONG RAW

BTYPE_BYTES

DBTYPE_BYTES

NCHAR

DBTYPE_STR

DBTYPE_WSTR

NCLOB

DBTYPE_STR

DBTYPE_WSTR

NUMBER

DBTYPE_VARNUMERIC

DBTYPE_VARNUMERIC

NUMBER(p,s)

DBTYPE_NUMERIC

DBTYPE_NUMERIC

NVARCHAR2

DBTYPE_STR

DBTYPE_WSTR

RAW

DBTYPE_BYTES

DBTYPE_BYTES

ROWID

DBTYPE_STR

DBTYPE_STR

TIMESTAMP

DBTYPE_DBTIMESTAMP

DBTYPE_DBTIMESTAMP

TIMESTAMP WITH TIME ZONE

DBTYPE_DBTIMESTAMP

DBTYPE_DBTIMESTAMP

TIMESTAMP WITH LOCAL TIME ZONE

DBTYPE_DBTIMESTAMP

DBTYPE_DBTIMESTAMP

VARCHAR

DBTYPE_STR

DBTYPE_WSTR


Properties Supported

This section lists the properties supported by Oracle Provider for OLE DB. The read/write status and initial values are noted.

Data Source Properties

Table A-2 lists data source properties.

Table A-2 DBPROPSET_DATASOURCE Properties

PropertyStatusInitial Value

DBPROP_CURRENTCATALOG

READ-ONLY

NULL


DataSourceInfo Properties

Table A-3 lists DataSourceInfo properties.

Table A-3 DBPROPSET_DATASOURCEINFO Properties

PropertyStatusInitial Value

DBPROP_ACTIVESESSIONS

READ-ONLY

0, Unlimited sessions

DBPROP_ASYNCTXNABORT

READ-ONLY

VARIANT_FALSE

DBPROP_ASYNCTXNCOMMIT

READ-ONLY

VARIANT_FALSE

DBPROP_BYREFACCESSORS

READ-ONLY

VARIANT_TRUE

DBPROP_CATALOGLOCATION

READ-ONLY

DBPROPVAL_CL_END

DBPROP_CATALOGTERM

READ-ONLY

"Database link"

DBPROP_CATALOGUSAGE

READ-ONLY

DBPROPVAL_CU_DML_STATEMENTS

DBPROP_COLUMNDEFINITION

READ-ONLY

DBPROPVAL_CD_NOTNULL

DBPROP_CONCATNULLBEHAVIOR

READ-ONLY

DBPROPVAL_CB_NON_NULL

DBPROP_CONNECTIONSTATUS

READ-ONLY

DBPROPVAL_CS_INITIALIZED

DBPROP_DATASOURCENAME

READ-ONLY

" ", set at run time

DBPROP_DATASOURCEREADONLY

READ-ONLY

VARIANT_FALSE

DBPROP_DBMSNAME

READ-ONLY

" ", set at run time

DBPROP_DBMSVER

READ-ONLY

set at run time

DBPROP_DSOTHREADMODEL

READ/WRITE

DBPROPVAL_RT_FREETHREAD

DBPROP_GROUPBY

READ-ONLY

DBPROPVAL_GB_CONTAINS_SELECT

DBPROP_HETEROGENEOUSTABLES

READ-ONLY

DBPROPVAL_HT_DIFFERENT_CATALOGS

DBPROP_IDENTIFIERCASE

READ-ONLY

DBPROPVAL_IC_UPPER

DBPROP_MAXINDEXSIZE

READ-ONLY

0, limit unknown - depends on block size

DBPROP_MAXOPENCHAPTERS

READ-ONLY

0, not supported

DBPROP_MAXORSINFILTER

READ-ONLY

0, not supported

DBPROP_MAXROWSIZE

READ-ONLY

0, no limit

DBPROP_MAXROWSIZEINCLUDESBLOB

READ-ONLY

VARIANT_FALSE

DBPROP_MAXSORTCOLUMNS

READ-ONLY

0, not supported

DBPROP_MAXTABLESINSELECT

READ-ONLY

0, no limit

DBPROP_MULTIPLEPARAMSETS

READ-ONLY

VARIANT_TRUE

DBPROP_MULTIPLERESULTS

READ-ONLY

DBPROP_MR_SUPPORTED | DBPROPVAL__MR_CONCURRENT

DBPROP_MULTIPLESTORAGEOBJECTS

READ-ONLY

VARIANT_FALSE

DBPROP_MULTITABLEUPDATE

READ-ONLY

VARIANT_FALSE

DBPROP_NULLCOLLATION

READ-ONLY

DBPROPVAL_NC_HIGH

DBPROP_OLEOBJECTS

READ-ONLY

DBPROPVAL_OO_BLOB

DBPROP_ORDERBYCOLUMNSINSELECT

READ-ONLY

VARIANT_FALSE

DBPROP_OUTPUTPARAMETERAVAILABILITY

READ-ONLY

DBPROPVAL_OA_ATEXECUTE

DBPROP_PERSISTENTIDTYPE

READ-ONLY

DBPROPVAL_PT_NAME

DBPROP_PREPAREABORTBEHAVIOR

READ-ONLY

DBPROPVAL_CB_PRESERVE

DBPROP_PREPARECOMMITBEHAVIOR

READ-ONLY

DBPROPVAL_CB_PRESERVE

DBPROP_PROCEDURETERM

READ-ONLY

"PL/SQL Stored Procedure"

DBPROP_PROVIDERFRIENDLYNAME

READ-ONLY

"Oracle Provider for OLE DB"

DBPROP_PROVIDERNAME

READ-ONLY

OraOLEDBver.dll

DBPROP_PROVIDEROLEDBVER

READ-ONLY

"02.01"

DBPROP_PROVIDERVER

READ-ONLY

set to current OraOLEDB version

DBPROP_QUOTEDIDENTIFIERCASE

READ-ONLY

DBPROPVAL_IC_SENSITIVE

DBPROP_ROWSETCONVERSIONSONCOMMAND

READ-ONLY

VARIANT_TRUE

DBPROP_SCHEMATERM

READ-ONLY

"Owner"

DBPROP_SCHEMAUSAGE

READ-ONLY

DBPROPVAL_SU_DML_STATEMENTS |DBPROPVAL_SU_TABLE_DEFINITION |DBPROPVAL_SU_INDEX_DEFINITION |DBPROPVAL_SU_PRIVILEGE_DEFINITION

DBPROP_SERVERNAME

READ-ONLY

" ", set at run time

DBPROP_SORTONINDEX

READ-ONLY

VARIANT_FALSE

DBPROP_SQLSUPPORT

READ-ONLY

DBPROPVAL_SQL_ODBC_MINIMUM |DBPROPVAL_SQL_ANSI92_ENTRY |DBPROPVAL_SQL_ESCAPECLAUSES

DBPROP_STRUCTUREDSTORAGE

READ-ONLY

DBPROPVAL_SS_ISEQUENTIAL_STREAM

DBPROP_SUBQUERIES

READ-ONLY

DBPROPVAL_SQ_CORRELATEDSUBQUERIES

DBPROP_SUPPORTEDTXNDDL

READ-ONLY

DBPROPVAL_TC_DDL_COMMIT

DBPROP_SUPPORTEDTXNISOLEVELS

READ-ONLY

DBPROPVAL_TI_CURSORSTABILITY |DBPROPVAL_TI_READCOMMITTED

DBPROP_SUPPORTEDTXNISORETAIN

READ-ONLY

DBPROPVAL_TR_DONTCARE

DBPROP_TABLETERM

READ-ONLY

"Table"

DBPROP_USERNAME

READ-ONLY

" ", set at run time


Initialization and Authorization Properties

Table A-4 lists initialization and authorization properties.

Table A-4 DBPROPSET_DBINIT Properties

PropertyStatusInitial Value

DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO

READ-ONLY

VARIANT_FALSE

DBPROP_AUTH_USERID

READ/WRITE

User ID

DBPROP_INIT_DATASOURCE

READ/WRITE

Connect string

DBPROP_INIT_HWND

READ/WRITE

Window handle for prompt

DBPROP_INIT_LCID

READ/WRITE

LCID of system

DBPROP_INIT_OLEDBSERVICES

READ/WRITE

DBPROPVAL_OS_ENABLEALL

DBPROP_INIT_PROMPT

READ/WRITE

DBPROMPT_NOPROMPT


Session Properties

Table A-5 lists session properties.

Table A-5 DBPROPSET_SESSION Properties

PropertyStatusInitial Value

DBPROP_SESS_AUTOCOMMITISOLEVELS

READ-ONLY

DBPROPVAL_TI_CURSORSTABILITY |DBPROPVAL_TI_READCOMMITTED


Rowset Properties

Table A-6 lists rowset properties.

Table A-6 DBPROPSET_ROWSET Properties

PropertyStatusInitial Value

DBPROP_ABORTPRESERVE

READ/WRITE

VARIANT_TRUE

DBPROP_ACCESSORORDER

READ-ONLY

DBPROP_AO_RANDOM

DBPROP_APPENDONLY

READ-ONLY

VARIANT_FALSE

DBPROP_BLOCKINGSTORAGEOBJECTS

READ-ONLY

VARIANT_FALSE

DBPROP_BOOKMARKINFO

READ-ONLY

0

DBPROP_BOOKMARKS

READ/WRITE

VARIANT_FALSE

DBPROP_BOOKMARKSKIPPED

READ/WRITE

VARIANT_TRUE

DBPROP_BOOKMARKTYPE

READ-ONLY

DBPROP_BMK_NUMERIC

DBPROP_CACHEDEFERRED

READ-ONLY

VARIANT_FALSE

DBPROP_CANFETCHBACKWARDS

READ/WRITE

VARIANT_FALSE

DBPROP_CANHOLDROWS

READ/WRITE

VARIANT_FALSE

DBPROP_CANSCROLLBACKWARDS

READ/WRITE

VARIANT_FALSE

DBPROP_CHANGEINSERTEDROWS

READ-ONLY

VARIANT_TRUE

DBPROP_CLIENTCURSOR

READ/WRITE

VARIANT_TRUE

DBPROP_COLUMNRESTRICT

READ-ONLY

VARIANT_TRUE

DBPROP_COMMANDTIMEOUT

READ/WRITE

0

DBPROP_COMMITPRESERVE

READ/WRITE

VARIANT_TRUE

DBPROP_DEFERRED

READ-ONLY

VARIANT_TRUE

DBPROP_DELAYSTORAGEOBJECTS

READ-ONLY

VARIANT_TRUE, no delayed update

DBPROP_FINDCOMPAREOPS

READ-ONLY

DBPROPVAL_CO_EQUALITY | DBPROPVAL_CO_STRING |DBPROPVAL_CO_CASESENSITIVE | DBPROPVAL_CO_CASEINSENSITIVE | DBPROPVAL_CO_CONTAINS |DBPROPVAL_CO_BEGINSWITH

DBPROP_HIDDENCOLUMNS

READ-ONLY

0

DBPROP_IACCESSOR

READ-ONLY

VARIANT_TRUE

DBPROP_ICOLUMNSINFO

READ-ONLY

VARIANT_TRUE

DBPROP_ICOLUMNSROWSET

READ/WRITE

VARIANT_TRUE

DBPROP_ICONNECTIONPOINTCONTAINER

READ-ONLY

VARIANT_TRUE

DBPROP_ICONVERTTYPE

READ-ONLY

VARIANT_TRUE

DBPROP_IMMOBILEROWS

READ-ONLY

VARIANT_TRUE

DBPROP_IMULTIPLERESULTS

READ/WRITE

VARIANT_TRUE

DBPROP_IROWSET

READ-ONLY

VARIANT_TRUE

DBPROP_IROWSETCHANGE

READ/WRITE

VARIANT_FALSE

DBPROP_IROWSETFIND

READ/WRITE

VARIANT_FALSE

DBPROP_IROWSETIDENTITY

READ-ONLY

VARIANT_TRUE

DBPROP_IROWSETINFO

READ-ONLY

VARIANT_TRUE

DBPROP_IROWSETLOCATE

READ/WRITE

VARIANT_FALSE

DBPROP_IROWSETREFRESH

READ/WRITE

VARIANT_FALSE

DBPROP_IROWSETSCROLL

READ/WRITE

VARIANT_FALSE

DBPROP_IROWSETUPDATE

READ/WRITE

VARIANT_FALSE

DBPROP_ISEQUENTIALSTREAM

READ/WRITE

VARIANT_TRUE

DBPROP_ISUPPORTERRORINFO

READ/WRITE

VARIANT_TRUE

DBPROP_LITERALBOOKMARKS

READ-ONLY

VARIANT_FALSE

DBPROP_LITERALIDENTITY

READ-ONLY

VARIANT_FALSE

DBPROP_LOCKMODE

READ-ONLY

DBPROPVAL_LM_NONE

DBPROP_MAXOPENROWS

READ/WRITE

0, No limit

DBPROP_MAXPENDINGROWS

READ-ONLY

0, No limit

DBPROP_MAXROWS

READ/WRITE

0

DBPROP_MAXROWSIZE

READ-ONLY

0

DBPROP_MAXROWSIZEINCLUDESBLOB

READ-ONLY

VARIANT_FALSE

DBPROP_NOTIFICATIONGRANULARITY

READ/WRITE

DBPROPVAL_NT_MULTIPLEROWS

DBPROP_NOTIFICATIONPHASES

READ/WRITE

DBPROPVAL_NP_OKTODO | DBPROPVAL_NP_ABOUTTODO |DBPROPVAL_NP_SYNCHAFTER | DBPROPVAL_NP_FAILEDTODO | DBPROPVAL_NP_DIDEVENT

DBPROP_NOTIFYCOLUMNSET

READ/WRITE

DBPROPVAL_NP_OKTODO | DBPROPVAL_NP_ABOUTTODO |DBPROPVAL_NP_SYNCHAFTER | DBPROPVAL_NP_FAILEDTODO | DBPROPVAL_NP_DIDEVENT

DBPROP_NOTIFYROWDELETE

READ/WRITE

DBPROPVAL_NP_OKTODO | DBPROPVAL_NP_ABOUTTODO |DBPROPVAL_NP_SYNCHAFTER | DBPROPVAL_NP_FAILEDTODO | DBPROPVAL_NP_DIDEVENT

DBPROP_NOTIFYROWFIRSTCHANGE

READ/WRITE

DBPROPVAL_NP_OKTODO | DBPROPVAL_NP_ABOUTTODO

DBPROP_NOTIFYROWINSERT

READ/WRITE

DBPROPVAL_NP_OKTODO | DBPROPVAL_NP_ABOUTTODO |DBPROPVAL_NP_SYNCHAFTER | DBPROPVAL_NP_FAILEDTODO | DBPROPVAL_NP_DIDEVENT

DBPROP_NOTIFYROWRESYNCH

READ/WRITE

DBPROPVAL_NP_OKTODO |DBPROPVAL_NP_ABOUTTODO |DBPROPVAL_NP_SYNCHAFTER

DBPROP_NOTIFYROWSETRELEASE

READ/WRITE

DBPROPVAL_NP_OKTODO |DBPROPVAL_NP_ABOUTTODO |DBPROPVAL_NP_SYNCHAFTER

DBPROP_NOTIFYROWSETFETCHPOSITIONCHANGE

READ/WRITE

DBPROPVAL_NP_OKTODO |DBPROPVAL_NP_ABOUTTODO |DBPROPVAL_NP_SYNCHAFTER

DBPROP_NOTIFYROWUNDOCHANGE

READ/WRITE

DBPROPVAL_NP_OKTODO | DBPROPVAL_NP_ABOUTTODO |DBPROPVAL_NP_SYNCHAFTER | DBPROPVAL_NP_FAILEDTODO | DBPROPVAL_NP_DIDEVENT

DBPROP_NOTIFYROWUNDODELETE

READ/WRITE

DBPROPVAL_NP_OKTODO | DBPROPVAL_NP_ABOUTTODO |DBPROPVAL_NP_SYNCHAFTER | DBPROPVAL_NP_FAILEDTODO | DBPROPVAL_NP_DIDEVENT

DBPROP_NOTIFYROWUNDOINSERT

READ/WRITE

DBPROPVAL_NP_OKTODO | DBPROPVAL_NP_ABOUTTODO |DBPROPVAL_NP_SYNCHAFTER | DBPROPVAL_NP_FAILEDTODO | DBPROPVAL_NP_DIDEVENT

DBPROP_NOTIFYROWUNDOUPDATE

READ/WRITE

DBPROPVAL_NP_OKTODO | DBPROPVAL_NP_ABOUTTODO |DBPROPVAL_NP_SYNCHAFTER | DBPROPVAL_NP_FAILEDTODO | DBPROPVAL_NP_DIDEVENT

DBPROP_ORDEREDBOOKMARKS

READ-ONLY

VARIANT_TRUE

DBPROP_OTHERINSERT

READ-ONLY

VARIANT_FALSE

DBPRBhOP_OTHERUPDATEDELETE

READ-ONLY

VARIANT_FALSE

DBPROP_OWNINSERT

READ-ONLY

VARIANT_TRUE

DBPROP_OWNUPDATEDELETE

READ-ONLY

VARIANT_TRUE

DBPROP_QUICKRESTART

READ/WRITE

VARIANT_FALSE

DBPROP_REENTRANTEVENTS

READ-ONLY

VARIANT_FALSE

DBPROP_REMOVEDELETED

READ-ONLY

VARIANT_TRUE

DBPROP_REPORTMULTIPLECHANGES

READ-ONLY

VARIANT_FALSE

DBPROP_RETURNPENDINGINSERTS

READ/WRITE

VARIANT_TRUE

DBPROP_ROWRESTRICT

READ/WRITE

VARIANT_FALSE

DBPROP_ROWTHREADMODEL

READ-ONLY

DBPROPVAL_RT_FREETHREAD

DBPROP_SERVERCURSOR

READ/WRITE

VARIANT_FALSE

DBPROP_SERVERDATAONINSERT

READ/WRITE

VARIANT_TRUE

DBPROP_STRONGIDENTITY

READ/WRITE

VARIANT_TRUE

DBPROP_TRANSACTEDOBJECT

READ-ONLY

VARIANT_TRUE

DBPROP_UNIQUEROWS

READ/WRITE

VARIANT_FALSE

DBPROP_UPDATABILITY

READ/WRITE

DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_DELETE | DBPROPVAL_UP_INSET


Rowset Property Implications

Oracle Provider for OLE DB sets other necessary properties if a particular property is set to VARIANT_TRUE.

  • If DBPROP_IROWSETLOCATE is set to VARIANT_TRUE, then the following properties are also set to VARIANT_TRUE:

    • DBPROP_IROWSETIDENTITY

    • DBPROP_CANHOLDROWS

    • DBPROP_BOOKMARKS

    • DBPROP_CANFETCHBACKWARDS

    • DBPROP_CANSCROLLBACKWARDS

  • If DBPROP_IROWSETSCROLL is set to VARIANT_TRUE, then the following properties are also set to VARIANT_TRUE:

    • DBPROP_IROWSETIDENTITY

    • DBPROP_IROWSETLOCATE

    • DBPROP_CANHOLDROWS

    • DBPROP_BOOKMARKS

    • DBPROP_CANFETCHBACKWARDS

    • DBPROP_CANSCROLLBACKWARDS

  • If DBPROP_IROWSETUPDATE is set to VARIANT_TRUE, then the DBPROP_IROWSETCHANGE property is also set to VARIANT_TRUE.

Interfaces Supported

This section identifies the OLE DB interfaces that are supported by Oracle Provider for OLE DB.

Data Source

   CoType TDataSource {
      interface IDBCreateSession;
      interface IDBInitialize;
      interface IDBProperties;
      interface IPersist;
      interface IDBInfo;
      interface ISupportErrorInfo;
   }

Session

   CoType TSession {
      interface IGetDataSource;
      interface IOpenRowset;
      interface ISessionProperties;
      interface IDBCreateCommand;
      interface IDBSchemaRowset;
      interface ISupportErrorInfo;
      interface ITransactionJoin;
      interface ITransactionLocal;
      interface ITransaction;
   }

Command

   CoType TCommand {
      interface IAccessor;
      interface IColumnsInfo;
      interface ICommand;
      interface ICommandProperties;
      interface ICommandText;
      interface IConvertType;
      interface IColumnsRowset;
      interface ICommandPrepare;
      interface ICommandWithParameters;
      interface ISupportErrorInfo;
   }

Rowset

   CoType TRowset {
      interface IAccessor;
      interface IColumnsInfo;
      interface IConvertType;
      interface IRowset;
      interface IRowsetInfo;
      interface IColumnsRowset;
      interface IConnectionPointContainer;
      interface IRowsetChange;
      interface IRowsetFind;
      interface IRowsetIdentity;
      interface IRowsetLocate;
      interface IRowsetRefresh;
      interface IRowsetScroll;
      interface IRowsetUpdate;
      interface ISupportErrorInfo;
   }

Multiple Results

   CoType TMultipleResults {
      interface IMultipleResults;
      interface ISupportErrorInfo;
   }

Transaction Options

   CoType TTransactionOptions {
      interface ITransactionOptions;
      interface ISupportErrorInfo;
   }

Custom Error Object

   CoType TCustomErrorObject {
      interface IErrorLookup;
   }

MetaData Columns Supported

DBTYPE_BASECOLUMNNAME, DBTYPE_BASETABLENAME, and DBTYPE_BASESCHEMANAME metadata columns are not populated for read-only recordsets. OraOLEDB creates a read-only recordset for server cursor for SQL queries with DISTINCT or UNIQUE keywords. OraOLEDB also creates a read-only recordset for server cursor for JOIN queries.

The following metadata columns are supported by the column rowset of OraOLEDB:

  • DBCOLUMN_IDNAME

  • DBCOLUMN_PROPID

  • DBCOLUMN_NAME

  • DBCOLUMN_NUMBER

  • DBCOLUMN_TYPE

  • DBCOLUMN_TYPEINFO

  • DBCOLUMN_COLUMNSIZE

  • DBCOLUMN_PRECISION

  • DBCOLUMN_SCALE

  • DBCOLUMN_FLAGS

  • DBCOLUMN_BASECATALOGNAME

  • DBCOLUMN_BASECOLUMNNAME

  • DBCOLUMN_BASESCHEMANAME

  • DBCOLUMN_BASETABLENAME

  • DBCOLUMN_COMPUTEMODE

  • DBCOLUMN_ISAUTOINCREMENT

  • DBCOLUMN_ISCASESENSITIVE

  • DBCOLUMN_ISSEARCHABLE

  • DBCOLUMN_OCTETLENGTH

  • DBCOLUMN_KEYCOLUMN

OraOLEDB Tracing

OraOLEDB provides the ability to trace the interface calls for debugging purposes. This feature has been provided to assist Oracle Support Services in debugging customer issues.

The provider can be configured to record the following information:

  • For OLE DB Interface method entry and exit:

    • Parameter values supplied (entry)

    • Return value; HRESULT (exit)

    • Thread ID (entry and exit)

  • For Distributed transaction enlistment and delistment:

    • Session object information

    • Transaction ID


      Note:

      To record global transaction enlistment and delistment information, the TraceLevel value must be set to session object. See "TraceLevel" .

Registry Setting for Tracing Calls

To trace the interface calls, you must configure the following registry values for HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME\OLEDB\:

  • TraceFileName

    Valid Value: Any valid path and file name

    TraceFileName specifies the file name that is to be used for logging trace information. If TraceOption is set to 0, the name is used as is. However, if TraceOption is 1, then the thread ID is appended to the file name provided. See "TraceOption" for more information.

  • TraceCategory

    Valid Values:

    • 0 = None

    • 1 = OLEDB Interface method entry

    • 2 = OLEDB Interface method exit

    • 4 = Distributed Transaction Enlistment and Delistment

    TraceCategory specifies the information that is to be traced. Combinations of different tracing categories can be made by adding the valid values. For example, set TraceCategory to 3 to trace all OLE DB interface method entries and exits.

  • TraceLevel

    Valid Values:

    • 0 = None

    • 1 = Data Source object

    • 2 = Session object

    • 4 = Command object

    • 8 = Rowset object

    • 16 = Error object

    • 64 = Multiple Results Object

    TraceLevel specifies the OLE DB objects to be traced. Because tracing all the entry and exit calls for all the OLE DB objects can be excessive, TraceLevel is provided to limit tracing to a single or multiple OLE DB objects. To obtain tracing on multiple objects, add the valid values. For example, if TraceLevel is set to 12 and TraceCategory is set to 3, the trace file will only contain method entry and exit for Command and Rowset objects.

    The TraceLevel value must be set to session object (2) to trace global transaction enlistment and delistment information.

  • TraceOption

    Valid Values:

    • 0 = Single trace file

    • 1 = Multiple trace files

    TraceOption specifies whether to log trace information in single or multiple files for each Thread ID. If a single trace file is specified, the file name specified in TraceFileName is used. If multiple trace file is requested, a Thread ID is appended to the file name provided to create a trace file for each thread.

PK卷BBPK Description of the illustration oledbvb1.gif

This illustration shows a typical Visual Basic form, in designer mode, with the title OleDb Test beside the Visual Basic icon.

There are two command buttons, with captions Get Employee Records by Dept and Get Dept No. for an Employee No. There are no labels and no other controls on the form.

PKʫPK Description of the illustration oop81002.gif

This illustration shows the program flow when using Oracle Provider for OLE DB. The following four objects are connected by bidirectional arrows in order: Consumer, Provider, Oracle Networking, and Oracle Database.

PK0ojPK Description of the illustration oledbvb2.gif

The dialog box prompts the user to enter a department number (10, 20, or 30) and, in this example, 30 is entered.

PKJT. PK Description of the illustration oledbvb3.gif

This illustration shows a dialog box titled OleDb1.

This example displays the text Number 7499 Name ALLEN Dept 30.

There is an OK button on the dialog box.

PK4B=PK Description of the illustration vbprjref.gif

This illustration shows the References dialog box, which contains the Available References windows, titled References - OleDb1.vhp. It contains a scrollable listing of available references. In this example, six references are checked:

  • Visual Basic for Applications

  • Visual Basic run time objects and procedures

  • Visual Basic objects and procedures

  • OLE Automation

  • Microsoft ActiveX Data Objects Recordset 2.1. library

  • Microsoft ActiveX Data Objects 2.1 Library

The last reference is highlighted and displayed with the following information about the reference:

Location C:\Program Files\Common Files\System\ADO\msado15.dll.

Language: Standard.

The dialog box includes buttons labeled OK, Cancel, Browse, and Help, as well as up and down priority arrows to change the priority of the various references.

PKfD?PK Oracle® Provider for OLE DB Developer's Guide for Microsoft Windows, 11g Release 2 (11.2.0.2) Cover Table of Contents Oracle Provider for OLE DB Developer's Guide, 11g Release 2 (11.2) for Microsoft Windows Preface What's New in OraOLEDB? Introduction to Oracle Provider for OLE DB Features of OraOLEDB Provider-Specific Information Glossary Index Copyright PKugt"  PK Oracle® Provider for OLE DB Developer's Guide for Microsoft Windows, 11g Release 2 (11.2.0.2) en-US E17726-01 Oracle Corporation Oracle Corporation Oracle® Provider for OLE DB Developer's Guide for Microsoft Windows, 11g Release 2 (11.2.0.2) 2010-10-12T14:41:30Z Describes Oracle Provider for OLE DB, a high performance, feature rich mechanism for Microsoft ADO and OLE DB data access to Oracle databases, its usage, and its configuration. PKx T1,PKV%ȣOΏ9??:a"\fSrğjAsKJ:nOzO=}E1-I)3(QEQEQEQEQEQEQE֝Hza<["2"pO#f8M[RL(,?g93QSZ uy"lx4h`O!LŏʨXZvq& c՚]+: ǵ@+J]tQ]~[[eϸ (]6A&>ܫ~+כzmZ^(<57KsHf妬Ϧmnẁ&F!:-`b\/(tF*Bֳ ~V{WxxfCnMvF=;5_,6%S>}cQQjsOO5=)Ot [W9 /{^tyNg#ЄGsֿ1-4ooTZ?K Gc+oyڙoNuh^iSo5{\ܹ3Yos}$.nQ-~n,-zr~-|K4R"8a{]^;I<ȤL5"EԤP7_j>OoK;*U.at*K[fym3ii^#wcC'IIkIp$󿉵|CtĈpW¹l{9>⪦׺*ͯj.LfGߍԁw] |WW18>w.ӯ! VӃ :#1~ +މ=;5c__b@W@ +^]ևՃ7 n&g2I8Lw7uҭ$"&"b eZ":8)D'%{}5{; w]iu;_dLʳ4R-,2H6>½HLKܹR ~foZKZ࿷1[oZ7׫Z7R¢?«'y?A}C_iG5s_~^ J5?œ tp]X/c'r%eܺA|4ծ-Ե+ْe1M38Ǯ `|Kյ OVڅu;"d56, X5kYR<̭CiطXԮ];Oy)OcWj֩}=܅s۸QZ*<~%뺃ȶp f~Bðzb\ݳzW*y{=[ C/Ak oXCkt_s}{'y?AmCjޓ{ WRV7r. g~Q"7&͹+c<=,dJ1V߁=T)TR՜*N4 ^Bڥ%B+=@fE5ka}ędܤFH^i1k\Sgdk> ֤aOM\_\T)8靠㡮3ģR: jj,pk/K!t,=ϯZ6(((((((49 xn_kLk&f9sK`zx{{y8H 8b4>ÇНE|7v(z/]k7IxM}8!ycZRQ pKVr(RPEr?^}'ðh{x+ՀLW154cK@Ng C)rr9+c:׹b Жf*s^ fKS7^} *{zq_@8# pF~ [VPe(nw0MW=3#kȵz晨cy PpG#W:%drMh]3HH<\]ԁ|_W HHҡb}P>k {ZErxMX@8C&qskLۙOnO^sCk7ql2XCw5VG.S~H8=(s1~cV5z %v|U2QF=NoW]ո?<`~׮}=ӬfԵ,=;"~Iy7K#g{ñJ?5$y` zz@-~m7mG宝Gٱ>G&K#]؃y1$$t>wqjstX.b̐{Wej)Dxfc:8)=$y|L`xV8ߙ~E)HkwW$J0uʟk>6Sgp~;4֌W+חc"=|ř9bc5> *rg {~cj1rnI#G|8v4wĿhFb><^ pJLm[Dl1;Vx5IZ:1*p)إ1ZbAK(1ׅ|S&5{^ KG^5r>;X׻K^? s fk^8O/"J)3K]N)iL?5!ƾq:G_=X- i,vi2N3 |03Qas ! 7}kZU781M,->e;@Qz T(GK(ah(((((((Y[×j2F}o־oYYq $+]%$ v^rϭ`nax,ZEuWSܽ,g%~"MrsrY~Ҿ"Fت;8{ѰxYEfP^;WPwqbB:c?zp<7;SBfZ)dϛ; 7s^>}⍱x?Bix^#hf,*P9S{w[]GF?1Z_nG~]kk)9Sc5Ո<<6J-ϛ}xUi>ux#ţc'{ᛲq?Oo?x&mѱ'#^t)ϲbb0 F«kIVmVsv@}kҡ!ˍUTtxO̧]ORb|2yԵk܊{sPIc_?ħ:Ig)=Z~' "\M2VSSMyLsl⺿U~"C7\hz_ Rs$~? TAi<lO*>U}+'f>7_K N s8g1^CeКÿE ;{+Y\ O5|Y{/o+ LVcO;7Zx-Ek&dpzbӱ+TaB0gNy׭ 3^c T\$⫫?F33?t._Q~Nln:U/Ceb1-im WʸQM+VpafR3d׫é|Aү-q*I P7:y&]hX^Fbtpܩ?|Wu󭏤ʫxJ3ߴm"(uqA}j.+?S wV ~ [B&<^U?rϜ_OH\'.;|.%pw/ZZG'1j(#0UT` Wzw}>_*9m>󑓀F?EL3"zpubzΕ$+0܉&3zڶ+jyr1QE ( ( ( ( ( ( ( (UIdC0EZm+]Y6^![ ԯsmܶ捆?+me+ZE29)B[;я*wGxsK7;5w)}gH~.Ɣx?X\ߚ}A@tQ(:ͧ|Iq(CT?v[sKG+*רqҍck <#Ljα5݈`8cXP6T5i.K!xX*p&ќZǓϘ7 *oƽ:wlຈ:Q5yIEA/2*2jAҐe}k%K$N9R2?7ýKMV!{W9\PA+c4w` Wx=Ze\X{}yXI Ү!aOÎ{]Qx)#D@9E:*NJ}b|Z>_k7:d$z >&Vv󃏽WlR:RqJfGإd9Tm(ҝEtO}1O[xxEYt8,3v bFF )ǙrPNE8=O#V*Cc𹾾&l&cmCh<.P{ʦ&ۣY+Gxs~k5$> ӥPquŽўZt~Tl>Q.g> %k#ú:Kn'&{[yWQGqF}AЅ׮/}<;VYZa$wQg!$;_ $NKS}“_{MY|w7G!"\JtRy+贾d|o/;5jz_6fHwk<ѰJ#]kAȎ J =YNu%dxRwwbEQEQEQEQEQEQEQEQEQE'fLQZ(1F)hQ@X1KEQE-Q@ 1KE3h=iPb(((1GjZ(-ʹRPbR@ 1KE7`bڒyS0(-&)P+ ڎԴP11F)h&:LRmQ@Q@Š(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((_ğ<+F; sU%ԑ >,BH(uSVUxhzHxH;N2qy95 aK$m8l;70߁3@75Y#t9? /q'#  خ/Z=/a/o!Gjp=ƻ&|HNicz~!UI5Q- Ta9QFާ&,ki."W| i՝A'#޿ %FvD9^qP}+1iƣ9Zɭo4ʹ&B``l<VxwK-cyeNxR(>gdeQ;I =?OxĺL#գGb)k,NNu9$`j~6~&xÚVXjݽs#lPYs r(+>*м)f.N ($ 2͍8,!ӭ]q"Mq}4WNUH,ĥSۉcq͐~8׋]KC@2sZzw$?h>lo;nݍ{g>_tokRӫUh FCN Sn4<[Xii;QA,p98oJA'%Uj''> Ǒ[[lj7#nN2q@a}oryQ$о7#T2X'|_CjI7m︧ܽ}k?o_u |E = ?*8'to뗟dQ e$ 'ʱ&hz[Sei%` GS&5$wOǺ^y Y&]cM2bg)I s@/(Ld.42D\wp u~ >3g'޳R,Dr)u ,BH(uSU ^.w$y-9lpoc'sp*Gvt ZEz֋qo5¨ 0 1_*~J+ OM'Rnw:ơ$b;h7+<J4^J] ݌QlFDepX#((?ޣyy-Z-Ph;7P,Ww ]x#ľ:<-o]4xU#WWF܍೓~HB+Q%=C9W蠢Hr2'>/=7&IOw>Rʏ37Wԡ~",-E5^Fp}Mzeڞ0ωuh<9GF^;nTc*(r |g!?X8rǂP?"{eg:9{,W#|ύv̀p$Zz|/T9öHQ4l]ʠ2=hc@jhs2X".]q8xE^.4ۈ.Ptdt OL u5ox×8+Bge#d2Pe>%gt];KeRv0''AM蚍j6,u/]|.9'U=KVtku5 K,S,J[ g{^>%NJ/A(l5_R̶_:cuĮ ặXTeu# 8 sRPˣǺ$h>M>)u%C s=ISʲ$VN?~ڵmX0|V_2~WQ@s/x{N쯴iW1 l̛V< c;N=?B־!4Vwm"m~ 83 kg} "Vo&@\цFAPa5Ɲm5.k0I9n6FG\W?_K_4?쯶y};/=;f{G.X! Hu=XMX+ >!xn֞xRK]X$`@# GQEy-|U|Sĺ[D >̇p߯#~+Ť^'m4-.&]Oz񜪹QJNcGQ@w%NgI"FYNw9  K;Id[nw=(HJbXdR+(+]k#Tq_",PVM̧y,f=_hU) m(0 c<GQExcy(MFkX#80T<Hbjx豴X4{  #V!n8?xĺcI|u,Ft$`t/]<5׈4~Նyis]VdiVD g$38'MFѬtv6QBpzNɬ؁{([ݵU`Uc@R:1RxO蚍j6,u/]|.9'Q@xIVke?YR^_l udVWL1c|2u:6vn"-) 0\č%FMSsMԾH/Y$ Y@Pb9 WdpkQR ]:E}oQ q-ΝKÑCO{j[eM>4tviXNG/ʟw@ziZdБm$߅#j9n3WXMqq[MykK"GyN@, ~%kYpp`; y }2=Eze[_ZDBH܌S#p<-rc\sX/fa_^gڶc? /i+߳\7_t#GQN=[N~\Ei2Gɻm_杭ɺuxϘ #8sN2scO>-WXB5↸-gz6ዽPo]E %m.>}2`]lj||g y5y5W DwI!)9\9P?|xY] ޞ#]L (lAe}XN6/s?\"Kqi `"[ ~m|b<~)̺$ !I~07ЩlwݾG񍮙Ro*v֍QG ɼe.ƶVCä\Mko2"c!۾༟,n#춾QM[\yb8ׁ&XHHfgӼr%ȇK&m-+>{jVt徛/ٮ-N35>md^*[M/I4Iouc Rn$ASŎL߈3d7ֺIsc;̭ϛ9'au:$Ml=gv5b{j>Qm_YΗĆ"Q! D% FaOGq@CvcWg:Toe[df1H$cWAO'UZ(xú'Qú߇}NS]yw-' ;?a1zz]Ƨmgn(d5~"xVQl"w]i%>("Cl=]'.m>*+!u뻤j]^FS#3x~_xRA 11#7n&m{Lռ'֑\[lށ83^W_fW'ץ 0[ɺ`Hp3ׯ*\O'UZ+YhZ kW4As Dr|{ҳo h_4;/emZĶqF݅KvioxĈ؁Hʞm*njtiVhz%!0 nbHlj||g y5y5W DwI!)9\9P<1uFGմy]n$K9IVHؐ'S+6޵1Ezޥ='ڤPz*BDzU~hrv>/oiF^M+b4YigxrvGόvEKex<}y*W6F͵$h1)ʒA .X&||g y5y5W DwI!)9\9P`߆H\"X 5M2Nm_^#|=E \2/,{.UQ'-<1uFGմy]n$K9IVHؐ'S+6޵_z}#ۻ j ?J>6!J# o|9Ai:7n*xg HQ-om[FE|~2nڡG  *jlw Z4Nu"̥`dp8R1޲1;_ճn۝}1z7|;qL~VoblŕgvWS@sa 6Q6xJQ>i^gp[ky&}-sXpqg#Sxc ۈKˉ/uUC t /8":J( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( (?PKLJ]]PKSO[!\ *_t  Exr%*_}!#U #4 & ֩3|b]L ]t b+Da&R_2lEٱZ`aC)/яmvUkS r(-iPE Vv_{z GLt\2s!F A#葡JY r|AA,hB}q|B`du }00(䡆<pb,G+oB C0p/x$…– ]7 @2HFc ) @AD \0 LHG',(A` `@SC)_" PH`}Y+_|1.K8pAKMA @?3҄$[JPA)+NH I ,@8G0/@R T,`pF8Ѓ)$^$ DDTDlA@ s;PKPKz'TQuw7Ŀ KX߁M2=S'TQt?.5w'97;~pq=" ~k?`'9q6 E|yayM^Om'fkC&<5x' ?A?Zx'jß={=SßM gVC.5+Hd֪xc^)Җufz{Cީ|D Vkznq|+Xa+{50rx{|OG.OϞ~f/ xxX[2H )c+#jpUOZYX\=SG ߨC|K@;_߆'e?LT?]:?>w ڔ`D^So~xo[Ӡ3i7B:Q8 Vc-ďoi:FM292~y_*_闱YN\Fr=xZ3鳎OwW_QEzW~c]REeaSM}}Hӏ4&.E]u=gMѠ+mF`rNn$w9gMa꺢nTuhf2Xv>އ a(Û6߭?<=>z'TQuw7Ŀ KX߁M2=S'TQt?.5Kko\.8S$TOX߀Gw?Zx汴X)C7~.i6(Щ=+4{mGӭ¸-]&'t_kV*I<1)4thtIsqpQJ+> \m^[aJ5)ny:4o&QEnyAEPEEss 72,PDۢ׃K W{Wjr+wگ iM/;pd?~&?@;7E4gv8 $l'z'TQuw7Ŀ Gֱ=ɿ&G?. iR(5W*$|?w᫼gkmIbHe/_t>tg%y.l}N5[]+Mk0ĠeHdPrsst'UiC,y8`V%9ZIia|ܪvi מYG,o}+kk{YbyIeb*sAtի82zWoEK5z*o-eo;n(P u-I)4Š(HQEQEQEQEhz(X/Đ?}Bk˩ ݏrk0]4>8XzV? }6$}d^F>nU K ?Bտk_9׾x~w'ߞ  uDŽtL ؈5c-E/"|_Oo.IH쐍=i*Iw5(ںw?t5s.)+tQ2dUt5Vĺ.jZ"@IRrZƅY4ߡ_;}ų(KyQf1Aǵt?sZg+?F5_oQR&Dg߿]6FuRD u>ڿxl7?IT8'shj^=.=J1rj1Wl$얲cPx;E,p$֟ˏkw qg"45(ǛkV/=+ũ)bYl~K#˝J_כ5&\F'I#8/|wʾ_Xj Q:os^T1.M_|TO.;?_  jF?g N 8nA2F%i =qW,G=5OU u8]Rq?wr'˻S+۾.ܼ 87Q^elo/T*?L|ۚ<%<,/v_OKs B5f/29n0=zqQq(ª=VX@*J(э(f5qJN_EVǞQEOuoѕOuoa5}gO?:߂8Wא|cڽ~]N&O( (<]>͠@VQ=^~U ̴m&\խ5i:}|}r~9՝f}_>'vVֲ$~^f30^in{\_.O F8to}?${φ|#x^#^n~w=~k~?'KRtO.㌡h![3Zu*ٷճ(ԟ]z_/W1(ԟ]v~g|Yq<ז0 ; b8֮s,w9\?uEyStKaª@\,)) (!EPEPEPEPEPzѧts{v>C/"N6`d*J2gGӧWqBq_1ZuΓ\X]r?=Ey88Mp&pKtO-"wR2 K^-Z< \c>V0^@O7x2WFjs<׻kZ(<Т(OFw/6$1[:ޯԯ#q~4|,LVPem=@=YLUxӃV}AUbcUB.Ds5*kٸAeG>PJxt͝ b88?*$~@ׯD VkraiJs}Q.20x&mXξ,Z]“A-J#`+-E/"<]\a'tZGy.(|lދ~gMK OZdxDŽU9T6ϯ^<Ϡt5CZ]].t۫S=s`ڳ%8iVK:nqe+#<.T6U>zWoy3^I {F?J~=G}k)K$$;$de8*G Uӟ4Ocºw}|]4=ݣ\x$ʠms?q^ipw\"ȿPs^Z Q_0GڼU.t}ROM[G#]8wٞ ӫ87}Cgw vHȩBM55vof =A_٭`Ygx[6 P,5}>蚊(0(+?>+?> k|TuXq6_ +szk :u_ Z߶Ak_U}Jc2u/1[_»ݸG41-bሬ۴}}Eȹפ_c?5gi @cL\L<68hF_Ih>X4K7UТ sMj =J7CKo>Օ5s:߀t ~ηaٿ?|gdL8+gG%o?x`دOqȱwc¨&TW_V_aI=dpG!wu۞սZ1yL50$(l3(:~'ַo A}a3N*[0ǭ HKQV}G@֜$ 9of$ArNqUOgË05#m?D)^_h//5_/<?4}Jį+GkpG4"$ r| >S4Ђ"S 1%R:ȝ 8;PKPz PK >@,4`H.|`a (Q 9:&[|ځ,4p Y&BDb,!2@, $wPA'ܠǃ@CO~/d.`I @8ArHx9H75j L 3B/` P#qD*s 3A:3,H70P,R@ p!(F oԥ D;"0 ,6QBRɄHhI@@VDLCk8@NBBL2&pClA?DAk%$`I2 #Q+l7 "=&dL&PRSLIP)PɼirqМ'N8[_}w;PK-PK Oracle Legal Notices

Oracle Legal Notices

Copyright Notice

Copyright © 1994-2012, Oracle and/or its affiliates. All rights reserved.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

License Restrictions Warranty/Consequential Damages Disclaimer

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

Warranty Disclaimer

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

Restricted Rights Notice

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

Hazardous Applications Notice

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Third-Party Content, Products, and Services Disclaimer

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Alpha and Beta Draft Documentation Notice

If this document is in prerelease status:

This documentation is in prerelease status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.

Oracle Logo

PKN61PK p { display: none; } /* Class Selectors */ .ProductTitle { font-family: sans-serif; } .BookTitle { font-family: sans-serif; } .VersionNumber { font-family: sans-serif; } .PrintDate { font-family: sans-serif; font-size: small; } .PartNumber { font-family: sans-serif; font-size: small; } PKeӺ1,PKꑈ53=Z]'yuLG*)g^!8C?-6(29K"Ĩ0Яl;U+K9^u2,@@ (\Ȱ Ë $P`lj 8x I$4H *(@͉0dа8tA  DсSP v"TUH PhP"Y1bxDǕ̧_=$I /& .)+ 60D)bB~=0#'& *D+l1MG CL1&+D`.1qVG ( "D2QL,p.;u. |r$p+5qBNl<TzB"\9e0u )@D,¹ 2@C~KU 'L6a9 /;<`P!D#Tal6XTYhn[p]݅ 7}B a&AƮe{EɲƮiEp#G}D#xTIzGFǂEc^q}) Y# (tۮNeGL*@/%UB:&k0{ &SdDnBQ^("@q #` @1B4i@ aNȅ@[\B >e007V[N(vpyFe Gb/&|aHZj@""~ӎ)t ? $ EQ.սJ$C,l]A `8A o B C?8cyA @Nz|`:`~7-G|yQ AqA6OzPbZ`>~#8=./edGA2nrBYR@ W h'j4p'!k 00 MT RNF6̙ m` (7%ꑀ;PKl-OJPKxAܽ[G.\rQC wr}BŊQ A9ᾑ#5Y0VȒj0l-GqF>ZpM rb ;=.ސW-WѻWo ha!}~ْ ; t 53 :\ 4PcD,0 4*_l0K3-`l.j!c Aa|2L4/1C`@@md;(H*80L0L(h*҇҆o#N84pC (xO@ A)J6rVlF r  fry†$r_pl5xhA+@A=F rGU a 1х4s&H Bdzt x#H%Rr (Ѐ7P`#Rщ'x" #0`@~i `HA'Tk?3!$`-A@1l"P LhʖRG&8A`0DcBH sq@AXB4@&yQhPAppxCQ(rBW00@DP1E?@lP1%T` 0 WB~nQ@;PKGC PK!/;xP` (Jj"M6 ;PK枰pkPK 1) collapsible = false; for (var k = 0; k < p.length; k++) { if ( getTextContent(p[k]).split(" ").length > 12 ) collapsible = false; c.push(p[k]); } } if (collapsible) { for (var j = 0; j < c.length; j++) { c[j].style.margin = "0"; } } } function getTextContent(e) { if (e.textContent) return e.textContent; if (e.innerText) return e.innerText; } } addLoadEvent(compactLists); function processIndex() { try { if (!/\/index.htm(?:|#.*)$/.test(window.location.href)) return false; } catch(e) {} var shortcut = []; lastPrefix = ""; var dd = document.getElementsByTagName("dd"); for (var i = 0; i < dd.length; i++) { if (dd[i].className != 'l1ix') continue; var prefix = getTextContent(dd[i]).substring(0, 2).toUpperCase(); if (!prefix.match(/^([A-Z0-9]{2})/)) continue; if (prefix == lastPrefix) continue; dd[i].id = prefix; var s = document.createElement("a"); s.href = "#" + prefix; s.appendChild(document.createTextNode(prefix)); shortcut.push(s); lastPrefix = prefix; } var h2 = document.getElementsByTagName("h2"); for (var i = 0; i < h2.length; i++) { var nav = document.createElement("div"); nav.style.position = "relative"; nav.style.top = "-1.5ex"; nav.style.left = "1.5em"; nav.style.width = "90%"; while (shortcut[0] && shortcut[0].toString().charAt(shortcut[0].toString().length - 2) == getTextContent(h2[i])) { nav.appendChild(shortcut.shift()); nav.appendChild(document.createTextNode("\u00A0 ")); } h2[i].parentNode.insertBefore(nav, h2[i].nextSibling); } function getTextContent(e) { if (e.textContent) return e.textContent; if (e.innerText) return e.innerText; } } addLoadEvent(processIndex); PKo"nR M PK*1$#"%+ ( E' n7Ȇ(,҅(L@(Q$\x 8=6 'נ9tJ&"[Epljt p#ѣHb :f F`A =l|;&9lDP2ncH R `qtp!dȐYH›+?$4mBA9 i@@ ]@ꃤFxAD*^Ŵ#,(ε  $H}F.xf,BD Z;PK1FAPK"p`ƒFF "a"E|ժOC&xCRz OBtX>XE*O>tdqAJ +,WxP!CYpQ HQzDHP)T njJM2ꔀJ2T0d#+I:<жk 'ꤱF AB @@nh Wz' H|-7f\A#yNR5 /PM09u UjćT|q~Yq@&0YZAPa`EzI /$AD Al!AAal 2H@$ PVAB&c*ؠ p @% p-`@b`uBa l&`3Ap8槖X~ vX$Eh`.JhAepA\"Bl, :Hk;PKx[?:PK_*OY0J@pw'tVh;PKp*c^PK#Sb(clhUԂ̗4DztSԙ9ZQҀEPEPEPEPEPEPEPM=iԍP Gii c*yF 1׆@\&o!QY00_rlgV;)DGhCq7~..p&1c:u֫{fI>fJL$}BBP?JRWc<^j+χ5b[hֿ- 5_j?POkeQ^hֿ1L^ H ?Qi?z?+_xɔŪ\썽O]χ>)xxV/s)e6MI7*ߊޛv֗2J,;~E4yi3[nI`Ѱe9@zXF*W +]7QJ$$=&`a۾?]N T䏟'X)Ɣkf:j |>NBWzYx0t!* _KkoTZ?K Gc+UyڹgNuh^iSo5{\ܹ3Yos}.>if FqR5\/TӮ#]HS0DKu{($"2xִ{SBJ8=}Y=.|Tsц2UЫ%.InaegKo z ݎ3ֹxxwM&2S%';+I',kW&-"_¿_ Vq^ܫ6pfT2RV A^6RKetto^[{w\jPZ@ޢN4/XN#\42j\(z'j =~-I#:q[Eh|X:sp* bifp$TspZ-}NM*B-bb&*xUr#*$M|QWY ~p~- fTED6O.#$m+t$˙H"Gk=t9r娮Y? CzE[/*-{c*[w~o_?%ƔxZ:/5𨴟q}/]22p qD\H"K]ZMKR&\C3zĽ[PJm]AS)Ia^km M@dК)fT[ijW*hnu Ͳiw/bkExG£@f?Zu.s0(<`0ֹoxOaDx\zT-^ѧʧ_1+CP/p[w 9~U^[U<[tĽwPv[yzD1W='u$Oeak[^ |Gk2xv#2?¹TkSݕ| rݞ[Vi _Kz*{\c(Ck_܏|?u jVڔ6f t?3nmZ6f%QAjJf9Rq _j7Z-y.pG$Xb]0')[_k;$̭?&"0FOew7 z-cIX岛;$u=\an$ zmrILu uٞ% _1xcUW%dtÀx885Y^gn;}ӭ)場QEQ@Q@Q@Q@Q@Q@!4xPm3w*]b`F_931˜[ן+(> E ly;<;MF-qst+}DH @YKlLmؤciN<|]IU)Lw(8t9FS(=>og<\Z~u_+X1ylsj'eՃ*U3`C!N9Q_WܱhKc93^ua>H ƕGk=8~e#_?{ǀe-[2ٔ7;=&K挑5zsLdx(e8#{1wS+ΝVkXq9>&yஏh$zq^0~/j@:/«Vnce$$uoPp}MC{$-akH@ɫ1O !8R9s5ԦYmϧ'OUṡ5T,!Ԛ+s#1Veo=[)g>#< s)ƽُA^䠮ωFUj(ǩ|N3Jڷ睁ϱuږZYGOTsI<&drav?A^_f׻B$,O__ԿC`it{6>G׈C~&$y؎v1q9Sc1fH[ѽ>,gG'0'@Vw,BO [#>ﱺg5ΒFVD%Yr:O5 Tu+O멃]ی38Ze}R&ѝ_xzc1DXgس;<,_,{ƽY'AS#oF.M#~cBuEx7G+Y)(5q+GCV;qF+CLQ)qEC&6z𿊘z}?&w=+)??&\g{;V??׻xGœdٿ׼-Nc')3K]N)iLTӿCdb7Q^a N sd>Fz[0S^s'Zi 77D}kWus ab~~H(>.fif9,~|Jk;YN3H8Y(t6Q݉k͇_÷Z+2߄&[ +Tr^藺97~c܎=[f1RrBǓ^kEMhxYVm<[џ6| kqbѱ| YA{G8p?\UM7Z66 g1U1igU69 u5Pƪ:VVZC=[@ҹ¨$kSmɳО\vFz~i3^a Osŧυ9Q}_3 όO{/wgoet39 vO2ea;Ύ7$U#?k+Ek&dpzbӱ+TaB0gN{[N7Gי}U7&@?>Fz~E!a@s ?'67XxO*!?qi]֏TQN@tI+\^s8l0)2k!!iW8F$(yOּT.k,/#1:}8uT˾+5=O/`IW G֯b.-<= HOm;~so~hW5+kS8s.zwE| ?4ӿw/K N 9?j(#0UT` Wzw}:_*9m>󑓀F?ELzv=8q:=WgJ`nDr Zе<ֹ](Q@Q@Q@Q@Q@Q@Q@Q@ 'IdC0EYJVcMty_~u+Sw-aO n<[YJgL#6i g5ЖDZ14cʝ!!\/M}/_AYR__>oC? _?7_G#RERW쏞KB}JxGSkǕA pƱơP m]hwB7U$Zq M95"3q1ioATߚ{g.t uu2k=;h#YB= fgS :TdLԃ!44mFK{Hrd^7oz|BVr<{)6AXգV»|>*/hS܏z͆OM=Εq (s|s׊LKQI :9NJ)P+!ʣoAF>+=@I}"x/}۠1aנc¹4emC:>p_xWKX` >R3_S½èųp3޺u3N e یbmͺ<_ mnݮ1Op?Gm)Qb%N585'%Ahs\6yw!"&Ɨ._wk)}GP;Z!#\"< *oƾ\)}N>"լ/~]Lg}pBG X?<zZ#x69S=6) jzx=y9O&>+e!!? ?s~k5Gʏ)?*ce7Ox~k5􇔾Q/e7/Ԑ#3OgNC0] ;_FiRl>Q.g>!%k#ú:Kn'&}?U@\pџPtp)v<{_i}Oվֲ3XIYIx~b<D?(=_JXH=bbi=Oh?_ C_O)}oW쏜? %Ƶ;-RYFi`wۭ{ϖZMtQ$"c_+ԃx1*0b;ԕ݋ESQEQEQEQEQEQEQEQEQEQZ(1F)h1K@XLRE&9P (bf{RӨ&)PEPEPbԴPGKZ(iإbn(:A%S0(-&)P+ ڎԴP11F)h&:LRmQ@Q@Š(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((PKje88PK Table of Contents

Contents

Title and Copyright Information

Preface

What's New in OraOLEDB?

1 Introduction to Oracle Provider for OLE DB

2 Features of OraOLEDB

A Provider-Specific Information

Glossary

Index

PKyOtPK Features of OraOLEDB

2 Features of OraOLEDB

This chapter describes components of Oracle Provider for OLE DB (OraOLEDB) and how to use the components to develop OLE DB consumer applications.

This chapter contains these topics:

OraOLEDB Provider Specific Features

The following sections describe provider-specific features of OraOLEDB:

Additional provider-specific information is provided in Appendix A, "Provider-Specific Information".

Data Types

The data types that OraOLEDB supports are listed in Table A-1 with Unicode and NonUnicode mappings.

OraOLEDB supports the Oracle data types described in the following sections.

  • Data Types introduced in Oracle Database 10g:

    • BINARY_FLOAT

    • BINARY_DOUBLE

  • Data Types introduced in Oracle9i:

    • TIMESTAMP

    • TIMESTAMP WITH TIME ZONE

    • TIMESTAMP WITH LOCAL TIME ZONE

    • INTERVAL YEAR TO MONTH

    • INTERVAL DAY TO SECOND


See Also:

For details about these and other data types, and time zones, see Oracle Database SQL Language Reference

Binary Data Types

BINARY_FLOAT is a single-precision floating point data type (4 bytes), which is mapped to OLE DB DBTYPE_R4.

BINARY_DOUBLE is a double-precision floating point data type (8 bytes), which is mapped to OLE DB DBTYPE_R8.

TIMESTAMP Data Types

This section discusses the Timestamp data types and then provides the following:

  • Sample data illustrating insertion and retrieval operations using each of the Timestamp data types.

  • A Visual Basic code example using the Timestamp data types.

Timestamp data types are mapped to the OLE DB DBTYPE_DBTIMESTAMP. The OLE DB DBTYPE_DBTIMESTAMP data type does not have TIME ZONE information.

The Timestamp data types include:

  • TIMESTAMP

  • TIMESTAMP WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIME ZONE

Data Insertion

For data insertion into a TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE column, the time zone setting of the client is used.

OLE DB Timestamp data type cannot provide the time zone information. For insert operations, the default time zone from the client session is added to the TIMESTAMP WITH TIME ZONE column data.

Data Retrieval

For data retrieval, TIME ZONE is dropped for TIMESTAMP WITH TIME ZONE columns, but TIME ZONE is used for TIMESTAMP WITH LOCAL TIME ZONE columns.

The OLE DB Timestamp data type cannot store time zone information.

Fractional Second

Fractional second is not supported for TIMESTAMP data types binding with Command objects.

Note that using ALTER SESSION to change time zone information does not change the time zone information in the new and existing Recordsets, which use the client time zone setting from the Regional options of the operating system. The maximum fractional_seconds_precision of TIMESTAMP is 9 and the default precision is 6.

ADO Consumers

For the Timestamp data types, ADO consumers must specify the value of CursorLocation as adUseServer and use Recordset for DML operations.

Examples of Timestamp Insert and Retrieval

The following scenarios assume that the default precision of 6 is used.

TIMESTAMP Column

Insert Data: 4/16/2003 11:19:19 AM (No time zone)

Data in DB: 4/16/2003 11.19.19.000000 AM

Data Retrieval: 4/16/2003 11:19:19 AM

TIMESTAMP WITH TIME ZONE Column

Insert Data: 4/16/2003 11:19:19 AM (Time zone of the Client session is used)

Data in DB: 4/16/2003 11.19.19.000000 AM -07:00

Data Retrieval: 4/16/2003 11:19:19 AM (Time zone is dropped)

TIMESTAMP WITH LOCAL TIME ZONE Column

The following scenario assumes that the time zone of the client session is -04:00, currently on US EDT (Eastern daylight time). For an insert operation, the data in the TIMESTAMP WITH LOCAL TIME ZONE column does not include time zone displacement, but its TIMESTAMP data is normalized to the database time zone -07:00, which is the same as US PDT (Pacific daylight time).

For a query, data is returned in the time zone of the client session. The time zone displacement is the difference (in hours and minutes) between the local time and the Coordinated Universal Time (UTC).

Insert Data: 4/16/2003 4:30:23 PM (Client time zone is -04:00)

Data in DB: 4/16/2003 01.30.23.000000 PM (Database time zone -07:00)

Data Retrieval: 4/16/2003 4:30:23 PM (Client time zone is -04:00)

Data Retrieval: 4/16/2003 3:30:23 PM (Client time zone is -05:00)

Data Retrieval: 4/16/2003 2:30:23 PM (Client time zone is -06:00)

Data Retrieval: 4/16/2003 1:30:23 PM (Client time zone is -07:00)

Visual Basic Example

 

...
Dim DT As Date 
 DT = Now() 
con.ConnectionString = "Provider=OraOLEDB.Oracle.1;User ID=user_name;" & _
    "Password=pwd;Data Source=Oracle;" 
con.Open 
'Must use adUseServer 
rec.CursorLocation = adUseServer 
rec.ActiveConnection = con 
rec.Open "select timestamp_column from test_table", con, adOpenDynamic,_
    adLockOptimistic 
rec.AddNew Array("timestamp_column"), Array(DT) 
 
update data 
rec.Update Array("timestamp_column"), Array("07/07/07 07:17:17 AM") 
...

INTERVAL Data Types

The INTERVAL data types are mapped to OLE DB DBTYPE_STR data type. The INTERVAL data types include:

  • INTERVAL YEAR TO MONTH

  • INTERVAL DAY TO SECOND

For the INTERVAL YEAR TO MONTH column, the maximum year_precision is 9 and the default is 2. For INTERVAL DAY TO SECOND column, the maximum day_precision is 9 and the default is 2 and the maximum fractional_seconds_precision is 9, the default is 6.


Note:

If the sign is not specified, then the default is +.

INTERVAL YEAR TO MONTH

Usage: (sign) years-months

Examples:

  • 2-3

    2 years and 3 months

  • +2-3

    2 years and 3 months

  • -2-3

    negative 2 years and 3 months

INTERVAL DAY TO SECOND

Usage: (sign) days hours:minutes:seconds.second_fraction

Examples:

  • 7 10:20:30.123456

    7 days, 10 hours, 20 minutes, and 30.123456 seconds

  • +7 10:20:30.123456

    7 days, 10 hours, 20 minutes, and 30.123456 seconds

  • -7 10:20:30.123456

    negative 7 days, 10 hours, 20 minutes, and 30.123456 seconds

Visual Basic Example

...
con.ConnectionString = "Provider=OraOLEDB.Oracle.1;User ID=user_name;"& _
     "Password=pwd;Data Source=Oracle;" 
con.Open 
'no restriction on using adUseServer or adUseClient 
rec.CursorLocation = adUseServer 
rec.ActiveConnection = con 
rec.Open "select * from test_table2", con, adOpenDynamic, adLockOptimistic 
rec.AddNew Array("year_to_month_column", "day_to_second_column"), _
    Array("8-1", "3 20:30:10.12")   
 
'update data 
rec.Update Array("year_to_month_column", "day_to_second_column"), _
     Array("2-3", "7 10:20:30.123456")
...

Data Source

A data source object in OraOLEDB is responsible for establishing the first connection to the Oracle Database. To establish the initial connection, the consumer must use the CoCreateInstance function to create an instance of the data source object. This function requires important information about the provider: class ID of the provider and executable context. The class ID of OraOLEDB is CLSID_OraOLEDB.

OraOLEDB is an in-process server. When calling CoCreateInstance, use the CLSCTX_INPROC_SERVER macro. For example:

// create an instance of OraOLEDB data source object and 
// obtain the IDBInitialize interface
hr = CoCreateInstance(CLSID_OraOLEDB, NULL, 
                      CLSCTX_INPROC_SERVER, IID_IDBInitialize, 
                     (void**)&pIDBInitialize);

The code snippet above does not enable OLEDB Services when instantiating the Data Source object. To enable OLEDB services, see "Compatibility with OLE DB Services" below.


Note:

OraOLEDB does not support persistent data source objects.

After the successful creation of an instance of a data source object, the consumer application can initialize the data source and create sessions.

OraOLEDB supports connections to Oracle Databases release 8i and higher. To connect to a specific database, the consumer is required to set the following properties of the DBPROPSET_DBINIT property set:

  • DBPROP_AUTH_USERNAME with the user ID, such as scott

  • DBPROP_AUTH_PASSWORD with the password, such as tiger

  • DBPROP_INIT_DATASOURCE with the net service name, such as myOraDb

The consumer could also populate DBPROP_INIT_PROMPT with DBPROMPT_PROMPT which causes the provider to display a logon box for the user to enter the connect information.

Using DBPROMPT_NOPROMPT disables display of the logon box. In this case, incomplete logon information causes the provider to return a logon error. However, if this property is set to DBPROMPT_COMPLETE or DBPROMPT_COMPLETEREQUIRED, the logon box will be displayed only if the logon information is incomplete.

Compatibility with OLE DB Services

OraOLEDB is compatible with OLE DB Services that are available in OLE DB version 2.0 and later. OLE DB Services contains useful services such as automatic transaction enlistment, Client Cursor Engine (CCE), connection and session pooling, which can enhance application performance, and others.

OLE DB Services can be used with OraOLEDB through C++/COM or ADO.

By default, the OLEDB_SERVICES registry entry for OraOLEDB is set, under the CLSID of OraOLEDB, to 0xffffffff (that is, -1), which enables all services. Certain OLE DB Services can also be disabled or enabled programmatically through the DBPROP_INIT_OLEDBSERVICES property setting.


See Also:

http://msdn.microsoft.com/en-us/library/ms724518(VS.85).aspx for more information on OLE DB Services and how to enable or disable specific services

ADO Applications with OLE DB Services

ADO automatically enables OLE DB Services. Thus, ADO applications do not need any special code to use OLEDB Services.

C++/COM Applications with OLE DB Services

For C++/COM applications, some additional steps are needed to use OLE DB Services.

The following code snippet shows one way that C++/COM applications can enable OLE DB Services. The code shows the OLE DB consumer creating an instance of the CLSID_MSDAINITIALIZE class through CoCreateInstance(), obtaining the IDataInitialize interface from that object, and then creating an OLE DB data source object through that interface.

// Instantiate the CLSID_MSDAINITIALIZE class and request for the
// IID_IDataInitialize interface from it
hr = CoCreateInstance(CLSID_MSDAINITIALIZE, NULL, CLSCTX_INPROC_SERVER, 
  IID_IDataInitialize, (void**)&pIDataInitialize);

// Set properties, datasource name, userid, and password, etc.
...

// Create an OLEDB data source object using the interface obtained from the
// CLSID_MSDAINITIALIZE class.
hr = pIDataInitialize->CreateDBInstance(CLSID_OraOLEDB, NULL, 
    CLSCTX_INPROC_SERVER, NULL,IID_IDBInitialize,(IUnknown**)&pIDBInitialize);
...

// If connection/session pooling was enabled, pIDBInitialize->Release()
// releases the connection/session back to the pool.
// pIDataInitialize->Release() should not be called until the application no
// longer need to use connection/session pooling and the rest of
// the OLE DB Services that were enabled for the application.
//  
pIDBInitialize->Release();

Connecting to an Oracle Database

To connect to an Oracle Database using OraOLEDB, the OLE DB connection string must be as follows:

"Provider=OraOLEDB.Oracle;User ID=user;Password=pwd;Data Source=constr;"

When connecting to a remote database, Data Source must be set to the correct net service name which is the alias in the tnsnames.ora file. For more information, refer to Oracle Net Services Administrator's Guide.

OraOLEDB-Specific Connection String Attributes

OraOLEDB offers provider-specific connection string attributes, which are set in the same way as the Provider and User ID are set. The provider-specific connection string attributes are:

  • CacheType - specifies the type of cache used to store the rowset data on the client. See "OraOLEDB-Specific Connection String Attributes for Rowsets".

  • ChunkSize - specifies the size of LONG or LONG RAW column data stored in the provider's cache. See "OraOLEDB-Specific Connection String Attributes for Rowsets".

  • DistribTX - enables or disables distributed transaction enlistment capability. See "Distributed Transactions".

  • FetchSize - specifies the size of the fetch array in rows. See "OraOLEDB-Specific Connection String Attributes for Rowsets".

  • OLEDB.NET - enables or disables compatibility with OLEDB.NET Data Provider. See "OLEDB.NET Data Provider Compatibility".

  • OSAuthent - specifies whether operating system authentication will be used when connecting to an Oracle Database. See "Operating System Authentication".

  • PLSQLRSet - enables or disables the return of a rowset from PL/SQL stored procedures. See "OraOLEDB Custom Properties for Commands".

  • PwdChgDlg - enables or disables displaying the password change dialog box when the password expires. See "Password Expiration".

  • UseSessionFormat - specifies whether to use the default NLS session formats or let OraOLEDB override some of these formats for the duration of the session. Valid values are 0 (FALSE) and 1 (TRUE). The default is FALSE which lets OraOLEDB override some of the default NLS session formats. If the value is TRUE, OraOLEDB uses the default NLS session formats.

    Note that this connection attribute does not appear under the \\HKEY_LOCAL_ MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME\OLEDB registry key.

  • VCharNull - enables or disables the NULL termination of VARCHAR2 OUT parameters from stored procedures.

  • SPPrmDefVal - specifies whether to use the default value or a NULL value if the application has not specified a stored procedure parameter value.

  • NDataType - specifies whether any of the parameters bound to the command are of N data types, which include NCHAR, NVARCHAR, or NCLOB. See "NDatatype".

    Note that this connection attribute does not appear under the \\HKEY_LOCAL_ MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME\OLEDB registry key.

  • SPPrmsLOB - specifies whether one or more parameters bound to the stored procedures are of LOB data type, which include CLOB, BLOB, or NCLOB. See "SPPrmsLOB".

    Note that this connection attribute does not appear under the \\HKEY_LOCAL_ MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME\OLEDB registry key.

  • StmtCacheSize – specifies the maximum number of statements that can be cached. See "Statement Caching".

  • MetaDataCacheSize - specifies the maximum number of SELECT statements for which the metadata can be cached. See "Metadata Caching".

  • DeferUpdChk - specifies whether or not to defer the updateability check to support updating read-only disconnected rowsets. See DeferUpdChk under "OraOLEDB-Specific Connection String Attributes for Rowsets".

  • DBNotifications - specifies whether or not to subscribe to the high availability events. See "Enhanced Failover Capability".

  • DBNotificationPort - specifies the port number, which is opened to listen to the Database notifications. See "Enhanced Failover Capability".

Default Attribute Values

The default values for these attributes are located under the \\HKEY_LOCAL_ MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME\OLEDB registry key, where KEY_HOMENAME is the Oracle home.

The registry default values are read by OraOLEDB from the registry when the provider is loaded into memory. If Oracle-specific connection string attributes are not provided at connection time, then the default registry values are used. However, if the attributes are provided, then these new values override the default registry values.

These attributes can be set by setting the DBPROP_INIT_PROVIDERSTRING property, provided in the DBPROPSET_DBINIT property set. For example:

"FetchSize=100;CacheType=Memory;OSAuthent=0;PLSQLRSet=1;StmtCacheSize=10;"

Distributed Transactions

The DistribTX attribute specifies whether sessions are enabled to enlist in distributed transactions. Valid values are 0 (disabled) and 1 (enabled). The default is 1 which indicates that sessions are enabled for distributed transaction enlistments.

Applications using Microsoft Transaction Server must have DistribTX set to 1, the default.

Enhanced Failover Capability

This feature enhances failover capability.

These connection string attributes support enhanced failover capability.

  • DBNotifications

    The DBNotifications attribute specifies whether or not to subscribe to high availability events. Valid values are 0 (FALSE) and 1 (TRUE). The default is FALSE, which indicates that OraOLEDB does not subscribe to high availability events. If this attribute is not provided at the connection time, then the default registry value is used.

  • DBNotificationPort

    The DBNotificationPort attribute specifies the port number, which is used to listen to the database notifications. The valid value is an unsigned integer.

    DBNotificationPort is effective only if the DBNotifications attribute is set to TRUE, either through the connection string attribute or by registry entry. The default for the DBNotificationPort attribute is 0, which implies that OraOLEDB opens a valid port randomly. OraOLEDB does not validate the port number, so it is the responsibility of the application to specify a valid port number.

Enabling Failover Capability Through Registry Entry

  • DBNotifications

    The DBNotifications registry entry specifies whether or not to subscribe to high availability events. Valid values are 0 (FALSE) and 1 (TRUE). The default value is FALSE, OraOLEDB does not subscribe. This registry entry value is used when the DBNotifications connection string attribute is not set. It is located under the \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME\OLEDB registry key.

Operating System Authentication

The OSAuthent attribute specifies whether operating system authentication will be used when connecting to an Oracle Database. Valid values are 0 (disabled) and 1(enabled). The default is 0, which indicates that operating system authentication is not used.

Operating system authentication is the feature by which Oracle uses the security mechanisms of the operating system to authorize users. For more information on this subject and how to set it up on Windows clients, refer to the information on authenticating database users on Windows in Oracle Database Platform Guide for Windows

After the Windows client has been set up properly for operating system authentication, this feature may be enabled by OraOLEDB clients by setting any of the following:

  • DBPROP_AUTH_USERNAME to /

  • DBPROP_INIT_PROVIDERSTRING to OSAuthent=1;

  • OSAuthent in the registry to 1

Password Expiration

Oracle9i provides a Password Expiration feature which allows database administrators to force users to change their passwords regularly. The PwdChgDlg attribute enables or disables the displaying of the password change dialog box, whenever a logon fails due to an expired password. When enabled, the provider displays the dialog box to change the password. When disabled, the logon fails with an error message. The valid values are 0 (disabled) and 1 (enabled). The default is 1 (enabled). For more information on the Password Expiration feature, see Oracle Database Administrator's Guide.

Example: Connecting to an Oracle Database Using ADO

The following examples illustrate how to connect to an Oracle Database using OraOLEDB and ADO.


Note:

If Data Source, User ID, and Password are provided with the Open method, then ADO ignores those ConnectionString attributes.

Connect Using ConnectionString

Dim con As New ADODB.Connection
con.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyOraDb;" & _
                     "User ID=scott;Password=tiger;"
con.Open

Connect Without Using ConnectionString

Dim con As New ADODB.Connection
con.Provider = "OraOLEDB.Oracle"
con.Open "MyOraDb", "scott", "tiger"

Connect and Set Provider-specific Attributes

Dim con As New ADODB.Connection
con.Provider = "OraOLEDB.Oracle"
con.ConnectionString = "FetchSize=200;CacheType=Memory;" & _
                       "OSAuthent=0;PLSQLRSet=1;Data Source=MyOraDb;" & _
                       "User ID=scott;Password=tiger;"
con.Open

Operating System-Authenticated Connect Setting User ID to /

Dim con As New ADODB.Connection
con.Provider = "OraOLEDB.Oracle"
con.Open "MyOraDb", "/", ""

Operating System-Authenticated Connect Using OSAuthent

Dim con As New ADODB.Connection
con.Provider = "OraOLEDB.Oracle"
con.ConnectionString = "Data Source=MyOraDb;OSAuthent=1;"
con.Open

VCharNull

The VCharNull attribute enables or disables the NULL termination of VARCHAR2 OUT parameters from stored procedures. Valid values are 0 (disabled) and 1 (enabled). The default is 1, which indicates that VARCHAR2 OUT parameters are NULL terminated. A value of 0 indicates that VARCHAR2 OUT parameters are padded with spaces.

The default value for this attribute is located under the \\HKEY_LOCAL_ MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME\OLEDB registry key, where HOMENAME is the Oracle home. If this attribute is not provided at the connection time, then the default registry value is used.

Note that with this connection attribute enabled, applications need to pad the stored procedure IN and IN OUT CHAR parameters with spaces explicitly, if the parameter is to be used in a WHERE clause.

SPPrmDefVal

The SPPrmDefVal attribute specifies whether to use the default value or a NULL value if the application has not specified a stored procedure parameter value. Valid values are 0 (FALSE) and 1 (TRUE). The default is FALSE, which enables OraOLEDB to pass a NULL value. If the value is TRUE, then OraOLEDB uses the default value.

The default value for this attribute is located under the \\HKEY_LOCAL_ MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME\OLEDB registry key. If this attribute is not provided at connection time, then the default registry value is used.

OraOLEDB Sessions

An OraOLEDB session object represents a single connection to an Oracle Database. The session object exposes the interfaces that allow data access and manipulation.

The first session created on the initialized data source inherits the initial connection established by IDBInitialize::Initialize(). Subsequent sessions that are created establish their own independent connections to the particular Oracle Database specified by the data source properties.

Each session object also defines a transaction space for a data source. All command and rowset objects created from a particular session object are part of the transaction of that session.

After all references to the session object are released, the session object is removed from memory and the connection is dropped.

Transactions

OraOLEDB supports local and distributed transactions, which provide explicit commit and abort transactional operations.

OraOLEDB does not support nested transactions. In addition, a local transaction cannot be started if the session is currently enlisted in a distributed transaction. This also applies to distributed transactions if the session is currently enlisted in a local transaction.

Local Transactions

OraOLEDB supports the ITransactionLocal interface for explicit transactions. By default, OraOLEDB is in an autocommit mode, meaning that each unit of work done on the database is automatically or implicitly committed. With the use of the ITransactionLocal interface, consumers may explicitly start a transaction for a particular session, allowing a unit of work to be explicitly committed or aborted by the consumer.

OraOLEDB supports the Read Committed (Cursor Stability) isolation level. In this level, the changes made by other transactions are not visible until those transactions are committed.

Distributed Transactions

OraOLEDB consumers must install Oracle Services for Microsoft Transaction Server (MTS) release 10.2 or higher to be able to participate in Microsoft Transaction Server (or COM+) transactions or to enlist in a distributed transaction coordinated by Microsoft Distributed Transaction Coordinator (MS DTC). For setup and configuration information on Oracle Services for MTS, see Oracle Services for Microsoft Transaction Server Developer's Guide.

OraOLEDB ignores IsoLevel, IsoFlags, and pOtherOptions parameters when ITransactionJoin::JoinTransaction() is called. These options must be provided when the consumer acquires a transaction object from MS DTC with the ITransactionDispenser::BeginTransaction() method call.

However, if IsoFlags is nonzero, then XACT_E_NOISORETAIN is returned.

Commands

OraOLEDB supports ANSI SQL as supported by Oracle Database and the ODBC SQL syntax.

Stored Procedures

When executing an Oracle PL/SQL stored procedure using a command, use Oracle native syntax or the ODBC procedure call escape sequence in the command text:

  • Oracle native syntax: BEGIN credit_account(123, 40); END;

  • ODBC syntax: {CALL credit_account(123, 40)}

Preparing Commands

OraOLEDB validates and fetches the metadata only for SELECT SQL statements.

Command Parameters

When using Oracle ANSI SQL, parameters in the command text are preceded by a colon. In ODBC SQL, parameters are indicated by a question mark (?).

OraOLEDB supports input, output, and input and output parameters for PL/SQL stored procedures and stored functions. OraOLEDB supports input parameters for SQL statements.


Note:

OraOLEDB supports only positional binding.

OraOLEDB Custom Properties for Commands

OraOLEDB custom properties for commands are grouped under the custom property set ORAPROPSET_COMMANDS. It provides these properties:

Table 2-1 Custom Properties for Commands

For Visual Basic UsersFor C++ Users

PLSQLRSet

ORAPROP_PLSQLRSet

NDatatype

ORAPROP_NDatatype

SPPrmsLOB

ORAPROP_SPPrmsLOB

AddToStmtCache

ORAPROP_AddToStmtCache


PLSQLRSet

This property is similar to the PLSQLRSet connection string attribute.

The property specifies whether OraOLEDB must return a rowset from the PL/SQL stored procedure. If the stored procedure, provided by the consumer, returns a rowset, PLSQLRSet must be set to TRUE (enabled). This property should be set to FALSE after the command has been run. By default, the property is set to FALSE (disabled).

Consumers should use the property over the attribute, as the property can be set at the command object rather than at the session. By setting it at the command object, the consumer is able to set the property only for the command object executing stored procedures which are returning rowsets. With the attribute, the consumer needed to set it even if only one of many stored procedures being executed by the ADO application returned a rowset. The use of this property should provide a performance boost to applications making use of the attribute previously.

Example: Setting the Custom Property PLSQLRSet

Dim objRes As NEW ADODB.Recordset
Dim objCon As NEW ADODB.Connection
Dim objCmd As NEW ADODB.Command
....
objCmd.ActiveConnection = objCon
objCmd.CommandType = adCmdText

' Enabling the PLSQLRSet property indicates to the provider
' that the command returns one or more rowsets
objCmd.Properties("PLSQLRSet") = TRUE

' Assume Employees.GetEmpRecords() has a REF CURSOR as
' one of the arguments
objCmd.CommandText = "{ CALL Employees.GetEmpRecords(?,?) }"

' Execute the SQL
set objRes = objCmd.Execute

' It is a good idea to disable the property after execute as the
' same command object may be used for a different SQL statement
objCmd.Properties("PLSQLRSet") = FALSE

NDatatype

This property allows the consumers to specify whether any of the parameters bound to the command are of Oracle's N data types (NCHAR, NVARCHAR or NCLOB). This information is required by OraOLEDB to detect and bind the parameters. This property should not be set for commands executing SELECT statements. However, this property must be set for all other SQL statements, such as INSERT, UPDATE, and DELETE.

The use of this property should be limited to SQL statements containing parameters of N data type as setting it incurs a processing overhead of at least one round-trip to the database. By default, this property is set to FALSE.


Note:

OraOLEDB does not support parameters of N data types in the WHERE clause of SQL statements.


Note:

Consumers are required to use the ODBC procedure call escape sequence to call stored procedures or functions having N data type parameters.

Example: Setting the Custom Property NDatatype

Dim objCon As NEW ADODB.Connection
Dim objCmd As NEW ADODB.Command
Dim prEmpno As NEW ADODB.Parameter
Dim prEname As NEW ADODB.Parameter
...
objCmd.ActiveConnection = objCon
objCmd.CommandType = adCmdText

' Create and append the parameters to the command object
Set prEmpno = objCmd.CreateParameter("prEmpno", adSmallInt, adParamInput, ,8521)
' prEname is bound to a NVARCHAR column in the EMP table
Set prEname = objCmd.CreateParameter("prEname", adBSTR, adParamInput, , "Joe")
objCmd.Parameters.Append prEmpno
objCmd.Parameters.Append prEname

' Enabling the NDatatype property indicates to the provider
' that one or more of the bound parameters is of N datatype
objCmd.Properties("NDatatype") = TRUE

' Assume column ENAME in table EMP is of NVARCHAR type
objCmd.CommandText = "INSERT INTO EMP (EMPNO, ENAME) VALUES (?, ?)"

' Execute the SQL
objCmd.Execute

' It is a good idea to disable the property after execute as the same command ' object may be used for a different SQL statement 
objCmd.Properties("NDatatype") = FALSE

SPPrmsLOB

This property allows the consumer to specify whether one or more of the parameters bound to the stored procedures are of Oracle's LOB data type (CLOB, BLOB, or NCLOB). OraOLEDB requires this property to be set to TRUE, to fetch the parameter list of the stored procedure prior to execution. The use of this property limits the processing overhead to stored procedures having one or more LOB data type parameters. This property should be set to FALSE after the command has been executed. By default, the property is set to FALSE.


Note:

Consumers are required to use the ODBC procedure call escape sequence to call stored procedures or functions having LOB data type parameters.

Example: Setting the Custom Property SPPrmsLOB

Dim objCon As NEW ADODB.Connection
Dim objCmd As NEW ADODB.Command
Dim prCLOB As NEW ADODB.Parameter
...
objCmd.ActiveConnection = objCon
objCmd.CommandType = adCmdText

' Create and append the parameters to the command object
Set prCLOB = objCmd.CreateParameter("prCLOB", adLongVarchar, adParamOutput, _                                                 10000)
objCmd.Parameters.Append prCLOB

' Enabling the SPPrmsLOB property indicates to the provider
' that one or more of the bound parameters is of LOB data type
objCmd.Properties("SPPrmsLOB") = TRUE

' Assume the Stored Procedure requires a CLOB parameter
objCmd.CommandText = "{ call storedproc(?) }"

'Execute the SQL
objCmd.Execute

' It is a good idea to disable the property after execute as the
' same command object may be used for a different SQL statement 
objCmd.Properties("SPPrmsLOB") = FALSE

AddToStmtCache

This property allows the consumer to cache the executed statements when the property is set to TRUE and statement caching is enabled. If the statement caching is disabled or if this property is set to FALSE, then the executed statement is not cached.

This property is ignored if statement caching is disabled. Statement caching can be enabled by setting the StmtCacheSize connection string attribute to a value greater than zero. This property provides a way to selectively add statements to the cache when statement caching is enabled. By default, the property is set to TRUE.

Example: Setting the Custom Property AddToStmtCache

Dim objCon As NEW ADODB.Connection
Dim objCmd As NEW ADODB.Command
... 

' Statement caching is enabled by setting the 'StmtCacheSize'
' connection string attribute to a value greater than zero
objCon.ConnectionString = "StmtCacheSize=10;Data Source=MyOraDb;" & _                        "User ID=scott;Password=tiger;"
objCon.Open
objCmd.ActiveConnection = objCon
objCmd.CommandType = adCmdText
objCmd.CommandText = "SELECT * FROM EMP"

' "SELECT * FROM EMP" statement would be added to the statement cache because
' StmtCacheSize connection string attribute value is greater than 0 and 
' AddToStmtCache property value is TRUE by default.
objCmd.Execute

' Do not add "SELECT * FROM DEPT" to the statement cache
objCmd.CommandText = "SELECT * FROM DEPT"
objCmd.Properties("AddToStmtCache") = FALSE

' "SELECT * FROM DEPT" statement would not be added to the statement cache
objCmd.Execute

Stored Procedures and Functions Returning Rowsets

Oracle Provider for OLE DB allows consumers to execute a PL/SQL stored procedure with an argument of REF CURSOR type or a stored function returning a REF CURSOR value.

OraOLEDB returns a rowset for the REF CURSOR bind variable. Because there is no predefined data type for REF CURSOR in the OLE DB specification, the consumer must not bind this parameter.

If the PL/SQL stored procedure has one or more arguments of REF CURSOR type, OraOLEDB binds these arguments and returns a rowset for each argument of REF CURSOR type.

If the PL/SQL stored function returns a REF CURSOR or has an argument of REF CURSOR type, OraOLEDB binds these and returns a rowset for each REF CURSOR type.

To use this feature, stored procedures or functions must be called in the ODBC procedure call escape sequence.

The stored procedure or function being called could be either standalone or packaged. However, the REF CURSOR being returned must be explicitly defined in a package in the database.

Multiple Rowsets

OraOLEDB supports returning more than one rowset from a stored procedure. Consumers can use this feature to access all the REF CURSORs being returned by a stored procedure.

Example: Stored Procedure Returning Multiple Rowsets

PL/SQL Package

CREATE OR REPLACE PACKAGE Employees AS  
  TYPE empcur IS REF CURSOR; 
 
  PROCEDURE GetEmpRecords(p_cursor OUT empcur,
                          q_cursor OUT empcur,
                          indeptno IN NUMBER,
                          p_errorcode OUT NUMBER);
 
  FUNCTION GetDept(inempno IN NUMBER,  
                   p_errorcode OUT NUMBER)  
    RETURN empcur; 
END Employees; 

CREATE OR REPLACE PACKAGE BODY Employees AS 
 
  PROCEDURE GetEmpRecords(p_cursor OUT empcur, 
                          q_cursor OUT empcur, 
                          indeptno IN NUMBER,  
                          p_errorcode OUT NUMBER) IS  
  BEGIN   
    p_errorcode := 0;  
    OPEN p_cursor FOR  
      SELECT *  
      FROM emp  
      WHERE deptno = indeptno  
      ORDER BY empno;  

OPEN q_cursor FOR  
      SELECT empno 
      FROM emp  
      WHERE deptno = indeptno  
      ORDER BY empno; 
 
  EXCEPTION  
    WHEN OTHERS THEN  
      p_errorcode:= SQLCODE;  
 
  END GetEmpRecords;  
 
  FUNCTION GetDept(inempno IN NUMBER,  
                   p_errorcode OUT NUMBER)  
    RETURN empcur IS  
      p_cursor empcur;  
  BEGIN   
    p_errorcode := 0;  
    OPEN p_cursor FOR  
      SELECT deptno  
      FROM emp  
      WHERE empno = inempno;  
    RETURN (p_cursor);  
 
  EXCEPTION  
    WHEN OTHERS THEN  
      p_errorcode:= SQLCODE; 
 
  END GetDept;   
 
END Employees;

ADO Program

Dim Con   As New ADODB.Connection
Dim Rst1  As New ADODB.Recordset 
Dim Rst2  As New ADODB.Recordset 
Dim Rst3  As New ADODB.Recordset 
Dim Cmd   As New ADODB.Command 
Dim Prm1  As New ADODB.Parameter 
Dim Prm2  As New ADODB.Parameter 

Con.Provider = "OraOLEDB.Oracle"
Con.ConnectionString = "Data Source=MyOraDb;" & _
                       "User ID=scott;Password=tiger;"
Con.Open
Cmd.ActiveConnection = Con

' Although Employees.GetEmpRecords() takes four parameters, only 
' two need to be bound because Ref cursor parameters are automatically 
' bound by the provider. 

Set Prm1 = Cmd.CreateParameter("Prm1", adSmallInt, adParamInput, , 30)
Cmd.Parameters.Append Prm1 
Set Prm2 = Cmd.CreateParameter("Prm2", adSmallInt, adParamOutput) 
Cmd.Parameters.Append Prm2 

' Enable PLSQLRSet property
Cmd.Properties ("PLSQLRSet") = TRUE  

' Stored Procedures returning resultsets must be called using the 
' ODBC escape sequence for calling stored procedures. 
Cmd.CommandText = "{CALL Employees.GetEmpRecords(?, ?)}" 

' Get the first recordset
Set Rst1 = Cmd.Execute 

' Disable PLSQLRSet property
Cmd.Properties("PLSQLRSet") = FALSE 

' Get the second recordset
Set Rst2 = Rst1.NextRecordset

' Just as in a stored procedure, the REF CURSOR return value must  
' not be bound in a stored function. 
Prm1.Value = 7839
Prm2.Value = 0

' Enable PLSQLRSet property
Cmd.Properties("PLSQLRSet") = TRUE 

' Stored Functions returning resultsets must be called using the 
' ODBC escape sequence for calling stored functions. 
Cmd.CommandText = "{CALL Employees.GetDept(?, ?)}" 

' Get the rowset
Set Rst3 = Cmd.Execute   

' Disable PLSQLRSet
Cmd.Properties ("PLSQLRSet") = FALSE

' Clean up
Rst1.Close
Rst2.Close
Rst3.Close

Statement Caching

Statement caching eliminates the need to parse each SQL or PL/SQL statement before execution, by caching server cursors created during the initial statement execution. Subsequent executions of the same statement can reuse the parsed information from the cursor, and then execute the statement without reparsing, for better performance.

To see performance gains from statement caching, Oracle recommends caching only those statements that will be repeatedly executed. Furthermore, SQL or PL/SQL statements should use parameters rather than literal values. This will enable you to take full advantage of statement caching. This is because parsed information from parameterized statements can be reused, even if the parameter values change in subsequent executions. However, if the literal values in the statements are different, the parsed information cannot be reused unless the subsequent statements also have the same literal values.

StmtCacheSize Connection String Attribute

This attribute enables or disables OraOLEDB statement caching. By default, this attribute is set to 10 (enabled). If it is set to a value greater than 0, OraOLEDB statement caching is enabled and the value specifies the maximum number of statements that can be cached for a connection.

After a connection has been cached to the specified maximum cache size, the cursor least recently used is freed to make room to cache the newly-created cursor. This value should not be greater than the value of the OPEN_CURSORS parameter set in the init.ora database configuration file.

AddToStmtCache Command Property

This property is relevant only when statement caching is enabled. If statement caching is enabled and this property is set to true (default), then statements are added to the cache when they are executed. If statement caching is disabled or if this property is set to false, then the executed statement is not cached.

Enabling Statement Caching Through the Registry

To enable statement caching by default for all OraOLEDB applications running in a system without changing the application, set the registry key of \\HKEY_LOCAL_ MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME\OLEDB\StmtCacheSize to a value greater than 0. Here, HOMENAME refers to the appropriate Oracle home. This value specifies the number of cursors that are to be cached on the server. By default, it is set to 10.

Connections and Statement Caching

Statement caching is managed separately for each connection. Therefore, for running the same statement on different connections, you need to parse once for each connection and cache a separate cursor for each connection.

Metadata Caching

This feature minimizes the retrieval of metadata for SELECT statements by caching the metadata during the initial statement execution. Subsequent executions of the same statement can reuse the cached metadata information for better performance. To see performance gains from metadata caching, Oracle recommends caching only those statements that are executed repeatedly.


Note:

Metadata caching is managed separately for each connection. Therefore, to run the same statement on different connections, the metadata must be cached once for each connection.

Enabling Metadata Caching Through the Connection String Attribute

The MetaDataCacheSize attribute enables or disables OraOLEDB metadata caching. If it is set to a value greater than 0, OraOLEDB metadata caching is enabled and the value specifies the maximum number of statements for which the metadata can be cached for a connection. By default, this attribute is set to 10.

Enabling Metadata Caching Through the Registry

To enable metadata caching by default for all OraOLEDB applications running in a system, without changing the application, set the following registry key to a value greater than 0. By default, it is set to 10.

\\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME\ OLEDB\MetaDataCacheSize

Command Timeout and Cancel Method

The Cancel method cancels the OraOLEDB command currently being executed. This method can be useful when the application needs to cancel a long running command during times of heavy network traffic or heavy server use.

Alternatively, by using the CommandTimeout property, developers can set a limit to the time that a command executes before OraOLEDB attempts to cancel it. OraOLEDB requires setting the EnableCmdTimeout registry value to 1 to enable CommandTimeout.

When using OLE DB, the default DPBROP_COMMANDTIMEOUT is 0 seconds. When using ADO, the default CommandTimeout property is 30 seconds.

Enabling CommandTimeout Through the Registry

Starting with OraOLEDB release 11.1.0.7.20, the installation adds a registry value called EnableCmdTimeout with the default value set to 0. Setting it to 0 disables command timeout and setting it to 1 enables it. The CommandTimeout property value setting takes effect only when EnableCmdTimeout is set to 1.

The registry value is:

\\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME\OLEDB\EnableCmdTimeout

Rowsets

This section discusses using Rowsets with OraOLEDB.

To Create Rowsets

OraOLEDB supports IOpenRowset::OpenRowset and ICommand::Execute for creating rowsets.

To Create Rowsets with IOpenRowset::OpenRowset

When using IOpenRowset::OpenRowset, note the following guidelines:

  • The pTableID parameter must contain a DBID structure that specifies a base table or a view.

  • The DBID structure's eKind member must be set to DBKIND_GUID_NAME, DBKIND_NAME, or DBKIND_PGUID_NAME.

  • The DBID structure's uName member must specify the base table or view name as a Unicode character string. It cannot be NULL.

  • The pIndexID parameter of OpenRowset must be NULL.

To Create Rowsets with ICommand::Execute

OraOLEDB supports SQL SELECT statements that return rowsets. OraOLEDB also supports returning rowsets from PL/SQL stored procedures and functions.

By default, ADO creates a nonupdatable rowset from a command object. An updatable rowset can be created by setting the Updatability and IRowsetChange properties on the command object. The Updatability property can be set to the following values:

Table 2-2 Possible Values for Updatability Property

ValueDescription

1

update

2

delete

3

update and delete

4

insert

5

insert and update

6

insert and delete

7

insert, delete, and update


The following ADO code sample sets the Updatability property on a command object to allow insert, delete, and update operations on the rowset object.

Dim Cmd As New ADODB.Command
Dim Rst As New ADODB.Recordset
Dim Con As New ADODB.Connection
...
Cmd.ActiveConnection = Con
Cmd.CommandText = "SELECT * FROM emp"
Cmd.CommandType = adCmdText
cmd.Properties("IRowsetChange") = TRUE
Cmd.Properties("Updatability") = 7
' creates an updatable rowset
Set Rst = cmd.Execute

Updatability

OraOLEDB supports both immediate and deferred update mode. However, insert and update operations cannot be deferred when the operation changes a nonscalar column, such as LONG, BLOB, or CLOB. When nonscalar column values are changed in a deferred update mode, the entire row is transmitted to the database as though the operation was in an immediate update mode. In addition, these operations cannot be undone with the Undo method (ADO) or IRowsetUpdate::Undo(). However, if they are in a transaction, they can be rolled back with RollbackTrans method (ADO) or ITransactionLocal::Abort().

Rowsets created using queries with joins are updatable by OraOLEDB only with the Client Cursor Engine enabled. C/C++ OLE DB consumers must enable this service to make these rowsets updatable. ADO consumers must specify the CursorLocation as adUseClient to make these rowsets updatable.

For example:

Dim objCon As New ADODB.Connection
Dim objRst As New ADODB.Recordset

objCon.Provider = "OraOLEDB.Oracle"
objCon.Open "MyOraDb", "scott", "tiger"
objRst.CursorLocation = adUseClient       'ADO Client Cursor
objRst.Open "select ename, dname " & _
       "from emp, dept " & _
       "where emp.deptno = dept.deptno", _
       objCon, adOpenStatic, adLockOptimistic, adCmdText


'Recordset created is updatable. Please note that CursorLocation 
'needs to be explicitly set to adUseClient for this join recordset
'to be updatable.

Server Data on Insert Property

If DBPROP_SERVERDATAONINSERT (Server Data on Insert) is set to TRUE using OraOLEDB, the consumer can obtain defaults, sequences, and triggered column values from newly inserted and updated rows, if the insert and update operations are made through the rowset.

Having DBPROP_SERVERDATAONINSERT set to TRUE may degrade performance for both insert and update executions using a rowset because OraOLEDB fetches row data from the database for the newly inserted and updated row. However, if DBPROP_SERVERDATAONINSERT is set to its default value of FALSE, only the explicitly provided values for insert and update operations are returned when column values are requested for those rows.

If the base table from which the rowset was created does not contain any defaults, sequences, or triggers, then it is highly recommended that DBPROP_SERVERDATAONINSERT retain its default value of FALSE.

The DBPROP_SERVERDATAONINSERT property does not affect the performance of insert and update operations using the command object.

To Search for Rows with IRowsetFind::FindNext

OraOLEDB only supports searches performed on CHAR, DATE, FLOAT, NUMBER, RAW, and VARCHAR2 columns. Otherwise, DB_E_NOTSUPPORTED is returned.

When a search is done with a NULL value, only the DBCOMPAREOPS_EQ and DBCOMPAREOPS_NE compare operations are supported. Otherwise, DB_E_NOTSUPPORTED is returned.

OraOLEDB-Specific Connection String Attributes for Rowsets

OraOLEDB-specific connection string attributes which affect the performance of the rowset are:

  • CacheType - specifies the type of caching used by the provider to store rowset data. OraOLEDB provides two caching mechanisms:

    • Memory - The provider stores all the rowset data in-memory. This caching mechanism provides better performance at the expense of higher memory utilization. The default is Memory.

    • File - The provider stores all the rowset data on disk. This caching mechanism limits memory consumption at the expense of performance.

  • ChunkSize - This attribute specifies the size, in bytes, of the data in LONG and LONG RAW columns fetched and stored in the provider cache. Providing a high value for this attribute improves performance, but requires more memory to store the data in the rowset. Valid values are 1 to 65535. The default is 100.

  • FetchSize - specifies the number of rows the provider will fetch at a time (fetch array). It must be set on the basis of data size and the response time of the network. If the value is set too high, then this could result in more wait time during the execution of the query. If the value is set too low, then this could result in many more round trips to the database. Valid values are 1 to 429,496, and 296. The default is 100.

  • DeferUpdChk - The DeferUpdChk attribute specifies whether or not to defer the updateability check. This supports updating ADO read-only disconnected rowsets. Valid values are 0 (FALSE) and 1 (TRUE). The default is FALSE, which implies that OraOLEDB does not defer the check. If this attribute is not provided at the connection time, then the default registry value is used.

The default attribute values are set in the registry. For more information, see "Default Attribute Values". The following ADO code example overrides the default attribute values:

Dim con As ADODB.Connection
Set con = NEW ADODB.Connection
con.ConnectionString = "Provider=OraOLEDB.Oracle;User ID=scott;" & _
                          "Password=tiger;Data Source=MyOraDB;" & _
                          "FetchSize=200;CacheType=File;"
con.Open

Tips for ADO Programmers

Setting the ADO Rowset property LockType to adLockPessimistic is not supported by Oracle Provider for OLE DB. If LockType is set to adLockPessimistic, then OraOLEDB behaves similar to when set as adLockOptimistic. This behavior occurs because OraOLEDB does not perform explicit locks on the rows being modified. However, when new data is submitted to the database, the database only performs the update if the rowset data was not already updated by another user, which means that dirty writes are not allowed. LockType values adLockReadOnly, adLockBatchOptimistic, and adLockOptimistic are supported by OraOLEDB.

Setting ADO Rowset property CursorType to adOpenKeyset or adOpenDynamic is not supported by Oracle Provider for OLE DB. OraOLEDB does not support either of the two as Oracle supports Statement Level Read Consistency, which ensures that the data returned by a query contains only committed data as of the time the query was executed. CursorType values adOpenStatic and adOpenForwardOnly are supported by OraOLEDB.

Schema Rowsets

The schema rowsets available through Oracle Provider for OLE DB are:

  • DBSCHEMA_COLUMNS

  • DBSCHEMA_INDEXES

  • DBSCHEMA_SCHEMATA

  • DBSCHEMA_VIEWS

  • DBSCHEMA_TABLES

  • DBSCHEMA_PROVIDER_TYPES (forward scroll only)

  • DBSCHEMA_FOREIGN_KEYS

  • DBSCHEMA_PRIMARY_KEYS

  • DBSCHEMA_PROCEDURES

  • DBSCHEMA_PROCEDURE_PARAMETERS

Date Formats

The date format for the Oracle session cannot be set using the ALTER SESSION SET NLS_DATE_FORMAT command. In Visual Basic, date formats are controlled by the Regional Settings properties in Windows Control Panel. For more information on Visual Basic date formats, refer to your Visual Basic documentation.

For Oracle Provider for OLE DB, if the Connection property UseSessionFormat is FALSE, which is a default value, then NLS_DATE_FORMAT is fixed for the session to 'YYYY-MM-DD HH24:MI:SS' by the provider. If you pass the date to Oracle Database as a string, the date must be in the 'YYYY-MM-DD HH24:MI:SS' format. If UseSessionFormat is TRUE, then NLS_DATE_FORMAT is not fixed by Oracle Provider for OLE DB and the default session NLS_DATE_FORMAT is used. For example:

SELECT * FROM EMP WHERE HIREDATE > '1981-06-15 17:32:12'

To use a different format, you need to use the SQL function, TO_DATE(), to specify the format for dates passed as strings. For example:

SELECT * FROM EMP WHERE HIREDATE > TO_DATE('15-JUN-81', 'DD-MON-YY')

However, for dates passed as parameters, the date format is controlled by ADO, which is controlled by the Regional Settings in Windows Control Panel. In this case, TO_DATE() should not be used. For example:

Private Sub Command1_Click()
  Dim objCon As New ADODB.Connection
  Dim objCmd As New ADODB.Command
  Dim objRst  As New ADODB.Recordset
  Dim pDate As New ADODB.Parameter

  objCon.Provider = "OraOLEDB.Oracle"
  objCon.Open "MyOraDb", "scott", "tiger"
  Set pDate = objCmd.CreateParameter("pDate", adDate, adParamInput)
  objCmd.Parameters.Append pDate
  objCmd.CommandText = _
         "SELECT * FROM EMP WHERE HIREDATE > ?"
  objCmd.ActiveConnection = objCon
  objCmd.CommandType = adCmdText
  pDate.Value = "06/15/1981"
  Set objRst = objCmd.Execute

    ...
End Sub

Case of Object Names

The names of all objects (tables, columns, views, and so forth) in Oracle Database are case-sensitive. This allows the two objects EMP and emp to exist in the same namespace in the database.

The query, SELECT ename FROM emp, executes correctly even though the table name is EMP (all uppercase) in the database. However, if you want to specify object names in mixed case, you can do so by enclosing the name in double quotes. For example:

SELECT ename FROM "Emp"

will execute successfully if the table name in the database is Emp. Double quotes preserve the case of the object names in Oracle Database.

LOB Support

The ISequentialStream interface is supported for all LONG, LONG RAW, and LOB (BLOB, CLOB, NCLOB, and BFILE) columns. The consumer can use this interface to read and write to all the LOB columns, except BFILE which is read-only. To have read and write access to these columns, the SELECT SQL statement used to create the rowset should not contain a join.


Note:

Although most of the LOB columns in an Oracle Database support up to 4 GB of data storage, ADO limits the maximum column size to 2 GB.

Columns having the BFILE data type are not updatable in the Rowset interface. However, these columns can be updated using the command interface, if the update is limited to modifying the directory and name of the external file pointed to by the BFILE column. For example:

INSERT INTO topomaps (areanum, topomap) 
VALUES (158, BFILENAME('mapdir', 'topo158.tps'))

For more information on LOBs, see Oracle Database SecureFiles and Large Objects Developer's Guide.

Unicode Support

OraOLEDB supports the Unicode character set. Using this feature, consumers can use OraOLEDB to access data in multiple languages on the same client computer. It can be especially useful in creating global Internet applications supporting as many languages as the Unicode standard entails. For example, you can write a single Active Server Page (ASP) application that accesses an Oracle9i Database to dynamically generate contents in Japanese, Arabic, English, Thai, and so on.

Types of Unicode Encoding

The Oracle Databases store the Unicode data in the UTF8 encoding scheme, which is an ASCII compatible multibyte encoding of Unicode. Microsoft Windows 2000 uses the UCS2 encoding, which is a 2-byte fixed-width encoding scheme. OraOLEDB transparently converts the data between the two encoding schemes allowing the consumers to deal with only UCS2.


Note:

The Unicode support is transparent to ADO consumers. OLE DB consumers using C or C++ need to explicitly specify DBTYPE_WSTR in their data type bindings when Unicode data in involved.

How Oracle Unicode Support Works

OraOLEDB works in two modes, Unicode mode and nonUnicode mode. When the client character set is not a superset of the server character set or the database character set is a multibyte character set, OraOLEDB automatically enables the Unicode mode. In this mode, OraOLEDB stores the data in its cache in the UCS2 encoding scheme. The user should ensure that the database's character set is UTF8 to prevent any data loss.

If the client character set is a superset of the server's, then the provider operates in the nonUnicode mode. This mode provides slightly better performance as it does not have to deal with larger character buffers required by the UCS2 encoding.

The detection of the client's and the server's character set is performed during logon.


Note:

OraOLEDB no longer requires the client character set to be set to UTF8 to enable the Unicode mode. The provider still supports such setups but no longer requires it.

See "Data Type Mappings in Rowsets and Parameters" for further information.

Unicode Support Setup

To prevent any data loss, the database character set should be UTF8. Other than this, there is no other setup required for Unicode support.

Database Setup

You must ensure that the Oracle Database is configured to store the data in the UTF8 character set. The character set configuration is typically specified during database creation. To check the character set setting of your database, execute the following query in SQL*Plus:

SQL> SELECT parameter, value FROM nls_database_parameters
     WHERE parameter = 'NLS_CHARACTERSET';

If the character set of your database is not UTF8, you need to create a new database with the UTF8 character set and import your data into it. See Oracle Database Administrator's Guide for more information.


See Also:

Oracle Database Globalization Support Guide for general information

Errors

OLE and COM objects report errors through the HRESULT return code of the object member functions. An OLE/COM HRESULT return code is a bit-packed structure. OLE provides macros that dereference structure members. OraOLEDB exposes IErrorLookup to retrieve information about an error.

All objects support extended error information. For this, the consumer must instantiate the OLE DB Extended Error object followed by calling the method GetErrorDescription() to get the error text.

// Instantiate OraOLEDBErrorLookup and obtain a pointer to its
//   IErrorLookup interface
CoCreateInstance(CLSID_OraOLEDBErrorLookup, NULL, CLSCTX_INPROC_SERVER,
                 IID_IErrorLookup, (void **)&pIErrorLookup)
//Call the method GetErrorDescription() to get the full error text
pIErrorLookup->GetErrorDescription()

The OraOLEDB provider returns the entire error stack in one text block.

For ADO users, the following example applies:

Dim oerr As ADODB.Error
For Each oerr in con.Errors
    MsgBox "Error: " & oerr.Description & vbCrLf _
        & "Source: " & oerr.Source
Next

OLEDB.NET Data Provider Compatibility

The OLE DB .NET Data Provider can utilize OraOLEDB as the OLE DB Provider for accessing Oracle Database.

To make OraOLEDB compatible with OLE DB .NET Data Provider, set the connection string attribute OLEDB.NET to True.

Setting the OLEDB.NET attribute to False disables .NET compatibility.


Note:

The OLEDB.NET connection string attribute must not be used in ADO applications.

Using the OLEDB.NET Attribute in a Connection String

When using OraOLEDB with the OLE DB .NET Data Provider, the OLEDB.NET connection attribute must be set to True as shown in the following examples:

// in VB.NET
Dim con As New OleDbConnection()
con.ConnectionString = "Provider=OraOLEDB.Oracle;User Id=scott;" & _
     "Password=tiger;Data Source=Oracle;OLEDB.NET=True;"
con.Open

// in C#
...
OleDbConnection con = new OleDbConnection();
con.ConnectionString = "Provider=OraOLEDB.Oracle;User Id=scott;" +
     "Password=tiger;Data Source=Oracle;OLEDB.NET=true;"
con.Open();
...

Using OraOLEDB Custom Properties

ADO allows OraOLEDB provider-specific properties to be set at the object level. The OraOLEDB-specific properties SPPrmsLOB and NDatatype can be set as connection string attributes as well as at the command-object level. The StmtCacheSize property can be set as a connection string attribute and the AddToStmtCache property can be set at the command object level. The following example shows the setting of properties at the command level:

// in VB
Dim cmd as new ADODB.Command
...
cmd.Properties("SPPrmsLOB") = True
cmd.Properties("NDatatype") = True
cmd.Properties("AddToStmtCache") = True
...

However, the OLEDB.NET Data Provider cannot expose OLE DB provider-specific properties at the object level. Therefore, the SPPrmsLOB and NDatatype properties can only be set as connection string attributes and AddToStmtCache property is not supported when OraOLEDB is used by OLE DB .NET Data Provider:

// in VB.NET
Dim con As New OleDbConnection()
con.ConnectionString = "Provider=OraOLEDB.Oracle;User Id=scott;" & _
     "Password=tiger;Data Source=Oracle;OLEDB.NET=True;" & _
     "SPPrmsLOB=False;NDatatype=False;"
con.Open()

Both SPPrmsLOB and NDatatype connection string attributes are set to False by default if they are not specified.

Setting either of these connection string attributes to True incurs additional processing overhead when executing commands with parameters. For this reason, before setting either attribute to True, see "OraOLEDB Custom Properties for Commands".

Updating Oracle with DataTable Changes

In order for the OleDbDataAdapter.Update() method to properly update Oracle Database with changes made in DataTable, which must contain a primary key of a database table. If the database table does not contain a primary key, the ROWID must be selected explicitly when populating DataTable, so that the ROWID can be used to uniquely identify a row when updating a row in the database.

Do not select the ROWID from database tables that contains a primary key. If ROWID is selected along with a primary key, ROWID will be the only column marked as the primary key.


See Also:

For further information on using the OLE DB .NET Data Provider

  • Microsoft .NET Documentation

  • Microsoft .NET Framework Class Library


Using OraOLEDB with Visual Basic

The following simple example illustrates how to use Oracle Provider for OLE DB with ADO in Visual Basic 6.0 to connect to an Oracle Database and execute PL/SQL stored procedures and functions.

Setting Up the Oracle Database

This example assumes that the Oracle Database has the demonstration table EMP under the user account scott. The scott account is included in the Oracle starter database. If the account does not exist on your database, create the account before running the sample program. If your database does not contain the emp table, then you can use the demobld.sql script to create the demonstration tables.

This example also uses exampledb as the database network alias when connecting to the Oracle Database. You must change this network alias to match your system.

Step 1   Build the Sample Tables:

  1. Start SQL*Plus.

  2. Connect as username scott with the password tiger.

  3. Run the demobld.sql script:

    SQL> @ORACLE_BASE\ORACLE_HOME\sqlplus\demo\demobld.sql;

After the emp table has been crescott account, you need to create the PL/SQL package that contains the stored procedure and function that are run in the Visual Basic example.

Step 2   Create the PL/SQL package:

  1. Connect as username scott with the password tiger.

  2. Create the PL/SQL packages shown in "PL/SQL Package".


    Note:

    When creating PL/SQL packages the / character is used as a terminator and must be added on a separate line following each CREATE PACKAGE...END block.

Setting Up the Visual Basic Project

After the Oracle Database setup is completed, you can create the Visual Basic 6.0 project.

  1. Start Visual Basic 6.0 and create a new project.

  2. Make sure that the Microsoft ActiveX Data Objects 2.1 Library and Microsoft ActiveX Data Objects Recordset 2.1 Library are included as Project References.

    Description of vbprjref.gif follows
    Description of the illustration vbprjref.gif

  3. Add two command buttons to the form. One of the buttons will run the code to execute the PL/SQL procedure GetEmpRecords. The other will run the code to execute the PL/SQL function GetDept.

    Description of oledbvb1.gif follows
    Description of the illustration oledbvb1.gif

  4. Add the following code to the Click subroutine of the button that will run the code to execute the PL/SQL procedure GetEmpRecords.

    Dim Oracon As ADODB.Connection
Dim recset As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim objErr As ADODB.Error
Dim Message, Title, Default, EmpNoValue

Message = "Enter an employee number (5000 - 9000)"
Title = "Choose an Employee"
Default = "7654"

On Error GoTo err_test

EmpNoValue = InputBox(Message, Title, Default)
If EmpNoValue = "" Then Exit Sub
If EmpNoValue < 5000 Or EmpNoValue > 9000 Then EmpNoValue = 7654

Set Oracon = CreateObject("ADODB.Connection")
Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                          "Data Source=exampledb;" & _
                          "User ID=scott;" & _
                          "Password=tiger;"

Oracon.Open
Set cmd.ActiveConnection = Oracon
Set param1 = cmd.CreateParameter("param1", adSmallInt, adParamInput, ,
                                 EmpNoValue)
cmd.Parameters.Append param1
Set param2 = cmd.CreateParameter("param2", adSmallInt, adParamOutput)
cmd.Parameters.Append param2

' Enable PLSQLRSet property
Cmd.Properties ("PLSQLRSet") = TRUE  

cmd.CommandText = "{CALL Employees.GetDept(?, ?)}"
Set recset = cmd.Execute

' Disable PLSQLRSet property
Cmd.Properties ("PLSQLRSet") = FALSE 

MsgBox "Number: " & EmpNoValue & "  Dept: " & recset.Fields("deptno").Value

Exit Sub

err_test:
    MsgBox Error$
    For Each objErr In Oracon.Errors
        MsgBox objErr.Description
    Next
    Oracon.Errors.Clear
    Resume Next

  5. Add the following code to the Click subroutine of the button that will run the code to execute the PL/SQL function GetDept.

    Dim Oracon As ADODB.Connection
Dim recset As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim objErr As ADODB.Error
 
Dim Message, Title, Default, DeptValue
Message = "Enter a department number (10, 20, or 30)"
Title = "Choose a Department"
Default = "30"

On Error GoTo err_test
DeptValue = InputBox(Message, Title, Default)
If DeptValue = "" Then Exit Sub
If DeptValue < 10 Or DeptValue > 30 Then DeptValue = 30

Set Oracon = CreateObject("ADODB.Connection")
Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                          "Data Source=exampledb;" & _
                          "User ID=scott;" & _
                          "Password=tiger;"  
Oracon.Open
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = Oracon
Set param1 = cmd.CreateParameter("param1", adSmallInt, adParamInput, ,
                                DeptValue)
cmd.Parameters.Append param1
Set param2 = cmd.CreateParameter("param2", adSmallInt, adParamOutput)
cmd.Parameters.Append param2

' Enable PLSQLRSet property
Cmd.Properties ("PLSQLRSet") = TRUE  

cmd.CommandText = "{CALL Employees.GetEmpRecords(?, ?)}"
Set recset = cmd.Execute

' Disable PLSQLRSet property
Cmd.Properties ("PLSQLRSet") = FALSE 

Do While Not recset.EOF
   MsgBox "Number: " & recset.Fields("empno").Value & "  Name: " &
   recset.Fields("ename").Value & "  Dept: " & recset.Fields("deptno").Value
   recset.MoveNext
Loop

Exit Sub

err_test:
    MsgBox Error$
    For Each objErr In Oracon.Errors
        MsgBox objErr.Description
    Next
    Oracon.Errors.Clear
    Resume Next

  6. Run the project and check the results. For example, if you choose the Get Employee Records by Dept button, then you would see a dialog box requesting that you enter a department number.

    Description of oledbvb2.gif follows
    Description of the illustration oledbvb2.gif

    After you have entered a department number and clicked OK, another dialog box displays employee names and numbers from that department.

    Description of oledbvb3.gif follows
    Description of the illustration oledbvb3.gif

PKRיÙPK OEBPS/dcommon/masterix.gifPK