Oracle Ole Db Provider Download

  1. Microsoft Ole Db Provider For Oracle
  2. Oracle Provider For Ole Db Download 11g
  3. Oracle Ole Db Provider Download
  4. Oracle Provider For Ole Db 12.2.0.1.0 Download
  5. Oracle Ole Driver
  6. Oracle Ole Db Provider Download 32 Bit
Skip Headers

After installing the Oracle Provider for OLE DB from the 11.2.0.3. The Oracle Provider for OLE DB 11.2.0.3 is not installed or registered correctly. It does appear as installed in the Oracle Universal Installer. However, it does not show up in the list of available providers when attempting to test the connection using a UDL file. The standard Oracle OLEDB connectivity is provided out of the box in SSIS by the way, so you should not install any additional drivers. According to your description, I’m trying to reproduce this issue. But after installing ODTwithODAC121010, I could see the Oracle provider for OLEDB in OLEDB Connection provider list. Download the OleDB provider from Oracle and install that one. And again, all tools can only use those OleDB provider, which have been installed on the machine; no tool has it's own 'embbeded' provider. On the same machine that hosts SQL Server 2012 and SSIS 2012 I've installed Excel 2010 + PowerPivot for Excel. Oracle Provider for OLE DB 12.1.0.2 now supports the IRowsetFastLoad interface, which enables fast memory-based bulk-copy operations in the Oracle database. This interface also works with the SQL Server Integration Services fast-load functionality to permit rapid ingestion of large amounts of data to the Oracle database.


Oracle Provider for OLE DB Developer's Guide
Release 9.2

Part Number A95498-01

Home

Book List

Contents

Index

Master Index

Feedback

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

OraOLEDB Provider Specific Features

The following sections describe provider-specific features of OraOLEDB:

  • Data Source
  • OraOLEDB Sessions
  • Commands
  • Rowsets
  • LOB Support
  • Unicode Support
  • Using OraOLEDB with Visual Basic

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

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:

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 7.3.4 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.

Microsoft ole db provider for oracle

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 only be displayed if the logon information is incomplete.

Connecting to an Oracle Database

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

When connecting to a remote database, DataSource must be set to the appropriate net service name which is the alias in the tnsnames.ora file. For more information, refer to Oracle9i 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'.
  • OSAuthent - specifies whether OS Authentication will be used when connecting to an Oracle database. See 'OS 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'.
  • OLEDB.NET - enables or disables compatibility with OLE DB .NET Data Provider. See 'OLE DB .NET Data Provider Compatibility'.

Default Attribute Values

The default values for these attributes are located under the HKEY_LOCAL_MACHINESOFTWAREORACLEOLEDB registry key.

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, the default registry values are used. However, if the attributes are provided, 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:

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.

OS Authentication

The OSAuthent attribute specifies whether OS 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 OS authentication is not used.

OS 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 NT clients, refer to the information on authenticating database users on Windows NT in Oracle9i Security and Network Integration Guide.

After the Windows NT client has been set up properly for OS 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, whenever a logon fails due to an expired password. When enabled, the provider displays the dialog 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 Oracle9i Database Administrator's Guide.

Example: Connecting to an Oracle Database Using ADO

The following are examples illustrating how to connect to an Oracle database using OraOLEDB and ADO.

Note:

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

Connect using ConnectionString
Connect without using ConnectionString
Connect and set provider specific attributes
OS Authenticated connect setting user ID to '/'
OS Authenticated connect using OSAuthent

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 server 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.

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 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 9.0.1 or later 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, XACT_E_NOISORETAIN is returned.

Commands

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

Stored Procedures

When executing an Oracle PL/SQLstored 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/output parameters for PL/SQL stored procedures and stored functions. OraOLEDB supports input parameters for SQL statements.

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

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 executed. 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

NDatatype

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

The use of this property should be limited to SQLs containing parameters of N datatype 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 datatypes 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 datatype parameters.

Example: Setting the Custom Property NDatatype

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 datatype (CLOB, BLOB, or NCLOB). OraOLEDB requires this property to be set to TRUE, in order 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 datatype parameters. This property should be set to FALSE after the command has been executed. By default, the property is set to FALSE.

Link here: GDS VCI Kia/Hyundai Online Software. How about 2017 HDD For GDS VCI Kia/Hyundai Online Software Works For Original and Clone. This software support the models both for EURO and USA till 2017,BUT NOT ALL IN 2017.About 85% cars is update to 2017,some is 2015.This software works with original unit and also clone unit. Kia gds software free download. Released on Jun. Download hyundai gds for free. System Utilities downloads - Kia Hyundai GDS Driver by Microtronik L.L.C and many more programs are available for instant and free download. GDS VCI Diagnostic Tool for Hyundai & Kia BLUE with Trigger Module (Optional) Software Version: E-K-03-12 KIA. 4.4 on 12 votes. Kia Hyundai GDS Driver is a free-to-use app that enables AutoHex II WVCI to work with Kia / Hyundai dealer software (GDS). GDS provides complete diagnostics, analysis and reprogramming capabilities as well as a huge library of technical, service and repair information. And now buy it.you can get NEW kia GDS software free. Kia & Hyundai GDS VCI diagnostic tool Connection GDS VCI For Kia & Hyundai Description.

Note:

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

Example: Setting the Custom Property SPPrmsLOB

Stored Procedures and Functions Returning Rowsets

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

Rasterlink pro 5 activation key. Found 7 results for Verbace Pro 1.9. VerbAce-Pro Spanish-English Dictionary Direct Download by Verbace-pro Spanish-english Dictionary Serial Number Serial Numbers. Working 100% rasterlink pro 5 sg activation key.%! Click below to get Key, verified working at January 2018 SHOW SERIAL KEY. Log in OR If you are new in here. Rasterlink Pro 5 Activation Key. Club Dj Pro 5 Activation Key. Rasterlink Pro 5 Sg Activation Key. Rasterlink pro series are Mimaki's range of user-friendly, new generation RIP software. Features – Rasterlink Pro 5 White Layer Printing. Sample Results From Member Downloads. Rasterlink Pro 5 Serial Number, key, crack, keygen. However Rasterlink Pro 5 has been found on the sites linked above. Many downloads like Rasterlink Pro 5 may also include a serial number, cd key or keygen. If this is the case then it's usually included in the full crack download archive itself. If you are still having trouble finding Rasterlink Pro 5 after simplifying your search term then we.

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

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

If the PL/SQL stored function returns a REFCURSOR or has an argument of REFCURSOR type, OraOLEDB binds these appropriately and returns a rowset for each REFCURSOR 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 REFCURSOR 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 REFCURSORs being returned by a stored procedure.

Example: Stored Procedure Returning Multiple Rowsets

Microsoft Ole Db Provider For Oracle

PL/SQL Package
ADO Program

Rowsets

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 non-updatable 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.

Updatability

OraOLEDB supports both immediate and deferred update mode. However, insert and update operations cannot be deferred when the operation changes a non-scalar column, such as LONG, BLOB, or CLOB. When non-scalar 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(). But 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:

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 get 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, 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 executions 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 the 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 appropriately depending on the data size and the response time of the network. If the value is set too high, this could result in more wait time during the execution of the query. If the value is set too low, this could result in many more round trips to the database. Valid values are 1 to 429,496,296. The default is 100.

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

Tips for ADO Programmers

Oracle ole db provider download windows

Setting the ADO Rowset property LockType to adLockPessimistic is not supported by Oracle Provider for OLE DB. If LockType is set to adLockPessimistic, 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 the new data is submitted to the database, it 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 ALTER SESSION SET NLS_DATE_FORMAT command. In Visual Basic, the date formats are controlled by the Regional Settings properties in the Windows Control Panel. For more information on Visual Basic date formats, refer to your Visual Basic documentation.

For Oracle Provider for OLE DB, 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 as a string, the date must be in the 'YYYY-MM-DD HH24:MI:SS' format. For example:

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:

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

Case of Object Names

The names of all objects (tables, columns, views, and so forth) in Oracle 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:

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

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 datatype 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:

For more information on LOBs, see Oracle9i Application Developer's Guide - Large Objects (LOBs).

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) that accesses an Oracle9i database to dynamically generate contents in Japanese, Arabic, English, and Thai.

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 and NT 4.0 use 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/C++ need to explicitly specify DBTYPE_WSTR in their datatype bindings when Unicode data in involved.

How Oracle Unicode Support Works

OraOLEDB works in two modes, Unicode mode and non-Unicode mode. When the client character set is not a superset of the server 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 in order to prevent any data loss.

If the client character set is a superset of the server's, the provider operates in the non-Unicode 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 'Datatype Mappings in Rowsets and Parameters' for further information.

Unicode Support Setup

In order to prevent any data loss, the database character set should be UTF8. Other than this, there is no other setup required for the 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:

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 Oracle9i Database Administrator's Guide for more information.

See Also:

Oracle9i 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 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.

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

For ADO users, the following example applies:

OLE DB .NET Data Provider Compatibility

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

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:

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 only be set at the ADO command object level, as shown in the following example:

However, the OLE DB .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 when OraOLEDB is used by OLE DB .NET Data Provider:

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

Oracle Provider For Ole Db Download 11g

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 with changes made in the DataTable, the DataTable 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 the 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 the ROWID is selected along with a primary key, the 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, 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 will need to 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:

After the emp table has been created in the scott 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. Start SQL*Plus.
  2. Connect as username scott with the password tiger.
  3. 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 CREATEPACKAGE..END block.

Oracle Ole Db Provider Download

Setting Up the Visual Basic Project

Oracle Provider For Ole Db 12.2.0.1.0 Download

After the Oracle database setups are 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.
  3. Add two commands 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.
  4. Add the following code to Click subroutine of the button that will run the code to execute the PL/SQL procedure GetEmpRecords.
  5. Add the following code to Click subroutine of the button that will run the code to execute the PL/SQL function GetDept.
  6. Run the project and check the results. For example, if you choose the Get Employee Records by Dept button, you would get a dialog box requesting that you enter a department number.

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

Oracle Ole Driver


Copyright © 1999, 2002 Oracle Corporation.
All Rights Reserved.

Home

Book List

Contents

Index

Master Index

Feedback

Oracle Ole Db Provider Download 32 Bit

Comments are closed.