Jun 15, 2012 In the ‘User DSN’ tab of the ‘ODBC Data Source Administrator’ dialog, click Add. In the ‘Create New Data Source’ dialog, select the ‘Oracle in OraClient 11ghome1’ driver. Click ‘Finish’. In the ‘Oracle ODBC Driver Configuration’ dialog, enter the details of your data source. Improve the performance of the ODBC connection by increasing the FETCH BUFFER SIZE of the ORACLE-ODBC-driver to 20 000 000 Bytes or more (from 64 000 bytes). Kind regards, Herbert.
The problem occurs because of the Oracle ODBC driver for UNIX. Rather than increase the speed using READBUFF= and INSERTBUFF=, you can change the FetchBufferSize= parameter in the odbc.ini file entry for your Oracle connection. The default size is 64000 bytes. You can increase this number to 1024000. Set the Fetch Buffer Size in the Oracle Options tab of the Oracle ODBC Driver Configuration Dialog Box to a value specified in bytes. This value is the amount of memory needed that will determine how many rows of data the ODBC Driver will pre-fetch at a time from an Oracle database to the client's cache regardless of the number of rows the. Oracle ODBC driver use to return wrong columnssize and bufferlength values through SQLColumns APIs for CHAR columns. (Bug 9414079) Oracle ODBC driver use to return SQLNODATAFOUND when SQLROWSETSIZE was set to more than the remaining rows. (Bug 9264668) Oracle ODBC driver use to hang when transferring Microsoft Access table data to.
-->Instead of a dialog box, some applications might require a connection string that specifies, using particular attributes, how a driver connects to a data source. Each attribute identifies specific information that the driver needs to make a successful connection. The connection string format is always as follows:
Note
The Microsoft ODBC Driver for Oracle also supports the CONNECTSTRING argument from the first version (1.x) of the driver.
You must specify the data source name if you do not specify the UID, PWD, SERVER (or CONNECTSTRING), and DRIVER attributes. However, all other attributes are optional. When an attribute is unspecified, the attribute specified in the DSN tab of the ODBC Data Source Administrator dialog box is the default.
The connection string attributes are described in the following table:
Attribute | Description | Default value |
---|---|---|
DSN | The data source name, listed in the User DSN or System DSN tab of the ODBC Data Source Administrator dialog box. | ' |
PWD | The password for the Oracle Server you want to access. This driver supports the Oracle limit of 30 characters and has the same limitations as Oracle places on passwords. | ' |
SERVER | The string for the Oracle Server you want to access. This string can be an Oracle database alias, an entry in the Tnsnames.ora file, or an alias that will be mapped to the correct server using Oracle Names Server. A database alias can be set up using Oracle's SQL*Net Fast Configuration (or Net8 Easy Configuration for Oracle8 databases). For more information about connecting to an Oracle database, see your Oracle documentation. | ' |
UID | The Oracle Server user name. This attribute might not be optional—certain databases and tables might require this attribute for security purposes. To indicate Oracle's operating system authentication, use 'UID=/;'. | ' |
BUFFERSIZE | The optimal buffer size for fetching columns. The driver optimizes fetching so that one fetch from the Oracle Server returns enough rows to fill a buffer of this size. Larger values tend to increase performance if you fetch a lot of data. | 65535 |
SYNONYMCOLUMNS | When this value is true (1), a SQLColumns API call returns column information for Tables, Views, and Synonyms. Otherwise, SQLColumns returns only columns for Tables and Views. For faster performance, do not set this value. | |
REMARKS | When this value is true (1), the driver returns Remarks columns for the SQLColumns result set. For faster performance, do not set this value. | 0 |
StdDayOfWeek | Enforces the ODBC standard for the DAYOFWEEK scalar. This is the default value; users needing the localized version can change the behavior to use whatever Oracle returns. | 1 |
Examples
Odbc Configuration Tool
The following connection string connects to the Employees data source by using the Oracle Server mickey.world and the Oracle User cindy:
Install Oracle Odbc Driver
The following connection string connects to the Payroll data source by using Oracle's operating system authentication and the Oracle Server moola: