What is the Oracle ODBC Driver

The Oracle ODBC Driver enables ODBC applications on Microsoft Windows, as well as UNIX platforms like Linux, Solaris, and AIX read and write access to Oracle® databases through the ODBC interface using Oracle Net Services software.

The Oracle ODBC Driver uses the Oracle Call Interface (OCI) client and server software to submit requests to and receive responses from the data source. Oracle Net Services communications protocol is used for communications between the OCI client and the Oracle server.

The Oracle ODBC Driver translates ODBC SQL syntax into syntax that can be used to access the data source. When the results are returned from the data source, the Oracle ODBC Driver translates them back to ODBC SQL syntax.

Figure 1-2 shows the Oracle ODBC Driver architecture as described in the preceding paragraphs.

Figure 1-2 Oracle ODBC Driver Architecture

Description of "Figure 1-2 Oracle ODBC Driver Architecture"

* The Oracle ODBC Resource DLL file (sqresxx.dll), where xx represents the language abbreviation, contains all pertinent language information; the default resource file used is sqresus.dll.

For more information about the OCI client and server software, refer to the OCI documentation.

Related Topics

Configuring the Data Source

Connecting to a Data Source

Driver Conformance Levels

New and Changed Features

Files Created by the Installation

New and Changed Features

• New Features for Release 12.1.0.1.0

• New Features for Release 11.2.0.1.0

• New Features for Release 11.1.0.1.0

• New Features for Release 10.1.0.2.0

• Changes for Release 10.1.0.2.0

New Features for Release 12.1.0.1.0

Features of the Oracle ODBC Driver Release 12.1.0.1.0 software for the Microsoft Windows Server 2008, Windows Server 2008 R2, Windows 7, Windows 8, Windows Server 2012, Linux X86-64 (32/64 bit), Sun Solaris SPARC64 (32,64 bit), IBM AIX 5L (32,64 bit), Sun Solaris X64 (32,64 bit), HPUX IA64 (32,64 bit), ZLinux (32,64 bit) operating systems are described as follows:

• Oracle ODBC Driver now supports 32 KB data columns with VARCHAR2, NVARCHAR2 and RAW data. See Oracle Database PL/SQL Language Reference and Oracle Database SQL Language Reference for information about creating 32 KB columns.

• New parameters in the odbc.ini file or connection level attributes:

  • SQL_TRANSLATE_ERRORS= T/F [Default is F (false)]

    Any migrated third party ODBC application, which is using the Babelfish feature, expects that errors returned by the server to be in their native database format, then users can register their translation of errors with the SQL Translation Profile in Oracle Database running in Babelfish mode. Once error translation is registered, then ODBC application users can enable this option, SQLTranslateErrors = T, to receive native errors as per their registration.

    For more information on SQL Translation Framework, see Oracle Database Migration Guide, in particular information about SQL Translation Framework Architecture and Overview, Translation Framework installation and configuration, and migration examples.

  See Table 1-5 for more information.

• Oracle ODBC driver now supports executing a stored procedure, which can return implicit results without using RefCursor. This support eases any third party ODBC application, which migrated to Oracle and wants to use this same functionality that was provided by their previous vendors.

  See Oracle Database Migration Guide for more information about implicit results support by Oracle Database.

• Extended support of SQLColAttribute() field identifiers to support Oracle Database auto increment feature. You can use this feature by including Oracle ODBC driver specific header file sqora.h in the application. See Oracle Call Interface Programmer's Guide for more information about auto increment:

  • SQL_COLUMN_AUTO_INCREMENT

    Starting from Oracle Database Release 12c Release 1 (12.1), Oracle supports auto increment columns so the Oracle ODBC Driver has extended the same support through the existing SQLColAttribute() identifier SQL_COLUMN_AUTO_INCREMENT. This property is read only and returns SQL_TRUE if the column is auto increment; otherwise, it returns SQL_FALSE.

  • SQL_ORCLATTR_COLUMN_PROP

    Starting from Oracle Database Release 12c Release 1 (12.1), Oracle ODBC Driver supports a new driver specific field identifier SQL_ORCLATTR_COLUMN_PROP, which returns the attributes of the column. This identifier returns SQLULEN value, which has all the column properties, shown as follows:

    +-----------------------------------------+
    | 32 |...| 10 | 9 | 8 |......| 3 | 2 | 1  |
    +-----------------------------------------+
                                   |   |   |
                                   |   |   |-> Column is auto-increment?
                                   |   |-> Auto value is always generated?
                                   |-> If generated by default when null?
    

• ODBC APIs supported in Oracle Database Release 12c Release 1 (12.1)

  • SQLMoreResults()

    Implements ODBC support for implicit results. See Table 1-7 in Implementation of ODBC API Functions and http://msdn.microsoft.com/en-us/library/ms714673(v=VS.85).aspx for more information.

New Features for Release 11.2.0.1.0

Features of the Oracle ODBC Driver Release 11.2.0.1.0 software for the Microsoft Windows XP, Microsoft Windows 2003 Server, Microsoft Windows Vista, Linux X86-32 (RHEL AS 4,5), Linux X86-64 (RHEL AS 4,5) (32/64 bit), Sun Solaris SPARC64 (9,10) (32,64 bit), IBM AIX 5L 5.2 (32,64 bit), Linux IA64 (64 bit), Linux on Power (32,64 bit), Sun Solaris X64 (64 bit), Hewlett Packard Itanium (32,64 bit) operating systems are described as follows:

• Prefetching of LONG/ LONG RAW data

  Oracle ODBC driver is enhanced to prefetch LONG or LONG RAW data to improve performance of ODBC applications. To do this, the maximum size of LONG data (MaxLargeData) must be set in the registry on Windows (you also need to add the registry key MaxLargeData in the DSN), and set this manually in the odbc.ini file on UNIX platforms. This enhancement improves the performance of Oracle ODBC driver up to 10 times, depending on the MaxLargeData size set by the user. The default value of MaxLargeData is 0. The maximum value for MaxLargeData that you can set is 64KB (65536 bytes).

  If the value of MaxLargeData is set to some value greater than 65536, the data fetched will only be 65536 bytes. If you have LONG or LONG RAW data in the database that is greater that 65536 bytes, MaxLargeData should be set to 0 (the default value), which will result in single row fetch and complete LONG data can be fetched. If you pass a buffer size less than the MaxLargeData size in non-polling mode, a data truncation error will occur if the LONG data size in the database is greater than the buffer size.

• Option for using OCIDescribeAny() for fetching metadata

  When an application makes heavy calls to small packaged procedures that return REF CURSORS, a performance improvement can be made by forcing the driver to use OCIDescribeAny(). To enable this option, set the value of UseOCIDescribeAny in odbc.ini to T (True), default value is F (False), on UNIX platforms, and through the registry on Windows.

New Features for Release 11.1.0.1.0

Features of the Oracle ODBC Driver Release 11.1.0.1.0 software for the Windows XP, Linux, Solaris, and AIX operating systems are described as follows:

• Disable Rule Hint (DRH Connect String)

  Added the new connection option, Disable RULE Hint that allows user to specify the option to select whether to use RULE Hint in catalog APIs. The change has been done to increase the performance of ODBC driver for catalog APIs. The default value for the option is TRUE which means that RULE Hint will not be used in catalog APIs by default.

• Bind Number As Float (BNF Connect String)

  Added the new connection option, Bind Number As Float. By introducing Column Binding for NUMBER Column as FLOAT when column contains float data speeds up the query execution that uses bind variables as FLOAT.

• Statement Caching

  Added support for OCI statement caching feature that provides and manages a cache of statements for each session. By implementing the support for OCI Statement Caching option, Oracle ODBC Driver will see the increase in performance where user have to parse the same statement multiple times in the same connection. The default value for the statement cache flag is FALSE.

New Features for Release 10.1.0.2.0

Features of the Oracle ODBC Driver Release 10.1.0.2.0 software for the Windows 98, Windows 2000, Windows XP, and Windows NT X86 operating systems are described as follows:

• Bind TIMESTAMP as DATE (BTD Connect String)

  Added the new connection option, Bind TIMESTAMP as DATE, that allows you to bind the ODBC driver SQL_TIMESTAMP data type to the Oracle DATE data type instead of to the Oracle TIMESTAMP data type (which is the default).

• MONTHNAME (exp) Function

  Added support for the MONTHNAME (exp) function which returns the name of the month represented by the date expression. For example, 'April'.

• DAYNAME (exp) Function

  Added support for the DAYNAME (exp) function which returns the name of the day represented by the date expression. For example, 'Tuesday'.

• Instant Client Configuration

  Added support for the Instant Client mode configuration.

Changes for Release 10.1.0.2.0

Changed or deprecated features of the Oracle ODBC Driver Release 10.1.0.2.0 include:

• Disable Microsoft Transaction Server

  Changed the default setting for the Disable Microsoft Transaction Server (MTS) from FALSE to TRUE. By default, MTS support is disabled.

• Floating Point Data Types

  Changed the mapping of the Oracle data types, BINARY_FLOAT and BINARY_DOUBLE, to map to the ODBC data types, SQL_REAL and SQL_DOUBLE, respectively.

• SQLGetData Extensions (GDE Connect String)

  Deprecated the SQLGetData Extensions connection in this release. The functionality of this feature is always enabled.

• Force Retrieval of Longs (FRL Connect String)

  Deprecated the Force Retrieval of Longs connection option in this release. The functionality of this feature is always enabled.

• Translation Options Configuration Tab

  Deprecated the Translation Options tab previously found on the Oracle ODBC Driver Configuration dialog box in this release.

• Release Notes

  Renamed the Release Notes file from ODBCRelnotes.wri to ODBCRelnotesUS.htm.

Features Not Supported

The Oracle ODBC Driver does not support the following ODBC 3.0 features:

• Interval data types

• SQL_C_UBIGINT and SQL_C_SBIGINT C data type identifiers

• Shared connections

• Shared environments

• The SQL_LOGIN_TIMEOUT attribute of SQLSetConnectAttr

Files Created by the Installation

Microsoft Driver Manager and Administrator Files

See the Microsoft ODBC 3.52 Software Development Kit and Programmer's Reference for the list of files that are installed with Microsoft's ODBC 3.52 Components.

The Microsoft ODBC components are packages in the Microsoft Data Access Component (MDAC) kit. Oracle ODBC driver on Windows has been tested using MDAC version 2.8. This can be downloaded from http://www.microsoft.com/download/en/search.aspx?q=ODBC+MDAC

unixODBC Driver Manager and Administrator Files

See the unixODBC readme and INSTALL files for the list of files that are installed with unixODBC Driver Manager.

The unixODBC Driver Manager can be downloaded from http://www.unixodbc.org/download.html

Driver Conformance Levels

ODBC defines Conformance Levels for drivers in two areas:

• ODBC application programming interface (API)

• ODBC SQL-99 syntax

The Oracle ODBC Driver supports all core API functionality and a limited set of Level 1 and Level 2 functionality. See API Conformance for more information.

The Oracle ODBC Driver is broadly compatible with the SQL-99 Core specification which is a superset of the SQL-92 Entry Level specification. Applications should call SQLGetInfo with the appropriate information type to retrieve a list of SQL-99 supported features.

Known Limitations

The following are not supported by Oracle ODBC driver:

• ODBC ASYNC interface

• Control-C to cancel execution in an application

Configuring Oracle Net Services

Before Configuring the Data Source, you must configure network database services so there is an entry for each TNS Service Name. To do this, use Oracle Net Configuration Assistant (NETCA).

Using NETCA, you can create an entry in the tnsnames.ora file for each TNS Service Name. NETCA is installed when you install Oracle Net Services.

Configuring the Data Source

After installing the Oracle ODBC Driver and Configuring Oracle Net Services, and before using the Oracle ODBC Driver, you must configure the data source.

Before an application can communicate with the data source, you must provide configuration information. The configuration information informs the Oracle ODBC Driver as to which information you want to access.

The data source consists of the data that you want to access, its associated operating system, database management system, and network platform used to access the database management system. The data source for requests submitted by the Oracle ODBC Driver is an Oracle database and supports transports available under Oracle Net Services.

To configure or add an Oracle data source:

After you have installed the Oracle ODBC Driver, use the ODBC Data Source Administrator to configure or add an Oracle data source for each of your Oracle databases. The Oracle ODBC Driver uses the information you enter when you add the data source to access the data. Follow these steps:

1. From the start menu, select Programs, Administrative Tools, Data Sources (ODBC). A list of installed drivers is displayed.

2. Click Add in the Create New Data Source window and then select the Oracle ODBC Driver in the list of installed drivers.

3. Click Finish. The Oracle ODBC Driver Configuration Dialog Box is displayed. You must enter the Data Source Name and TNS Service Name. You can provide the other information requested in the dialog box, or you can leave the fields blank and provide the information when you run the application.

4. After you have entered the data, click OK or click Return.

You can change or delete a data source at any time. The following subtopics explain how to add, change, or delete a data source.

To modify an Oracle data source:

1. From the start menu, select Programs, Administrative Tools, Data Sources(ODBC).

2. In the ODBC Data Source Administrator dialog box, select the data source from the Data Sources list and click Configure. The Oracle ODBC Driver Configuration dialog box is displayed.

3. In the Oracle ODBC Driver Configuration Dialog Box, modify the option values as necessary and click OK.

To delete an Oracle data source:

1. From the start menu, select Programs, Administrative Tools, Data Sources(ODBC).

2. In the ODBC Data Source Administrator dialog box, select the data source you want to delete from the Data Sources list.

3. Click Remove, and then click Yes to confirm the deletion.

Related Topics

Connecting to an Oracle Data Source

Using the Oracle ODBC Driver for the First Time

Oracle ODBC Driver Configuration Dialog Box

Figure 1-3 Oracle ODBC Driver Configuration Dialog Box

Description of "Figure 1-3 Oracle ODBC Driver Configuration Dialog Box"

The following list is an explanation of the main setup options and fields found on the Oracle ODBC Driver Configuration dialog box shown in the preceding graphic. The tabs found on the lower half of this dialog box are described in subsequent topics.

• Data Source Name - The name used to identify the data source to ODBC. For example, "odbc-pc". You must enter a Data Source Name.

• Description - A description or comment about the data in the data source. For example, "Hire date, salary history, and current review of all employees." The Description field is optional.

• TNS Service Name - The location of the Oracle database from which the ODBC driver will retrieve data. This is the same name entered in Configuring Oracle Net Services using the Oracle Net Configuration Assistant (NETCA). For more information, see the NETCA documentation and Using the Oracle ODBC Driver for the First Time. The TNS Service Name can be selected from a pull-down list of available TNS names. For example, "ODBC-PC". You must enter a TNS Service Name.

• User ID - The user name of the account on the server used to access the data. For example, "scott". The User ID field is optional.

You must enter the Data Source Name and the TNS Service Name. You can provide the other information requested in the dialog box or you can leave the fields blank and provide the information when you run the application.

In addition to the main setup options previously described, there is a Test Connection button available. The Test Connection button verifies whether the ODBC environment is configured properly by connecting to the database specified by the Data Source Name definition. When you press the Test Connection button, you are prompted for the username and password.

For an explanation of the Options tabs found on the lower half of the Oracle ODBC Driver Configuration dialog box, click any of the following links:

Application Options

Oracle Options

Workarounds Options

SQL Server Migration Options

Application Options

Figure 1-4 The Application Options Tab of the Oracle ODBC Driver Configuration Dialog Box

Description of "Figure 1-4 The Application Options Tab of the Oracle ODBC Driver Configuration Dialog Box"

The following list is an explanation of the fields found on the Application Options tab shown in the preceding graphic:

• Enable Result Sets - Enables the processing of Oracle Result Sets. If Result Sets are not required for your application, Result Set support can be disabled. There is a small performance penalty for procedures called from packages not containing Result Sets. Result Sets are enabled by default.

• Enable Query Timeout - Enables query timeout for SQL queries. By default, the Oracle ODBC Driver supports the SQL_ATTR_QUERY_TIMEOUT attribute for the SQLSetStmtAttr function. If this box is not checked, the Oracle ODBC Driver responds with a "not capable" message. Query Timeout is enabled by default.

• Read-Only Connection - Check this box to force read-only access. The default is write access.

• Enable Closing Cursors - Enables closing cursors. By default, closing cursors is disabled (the field is empty), meaning a call to close a cursor does not force the closing of OCI cursors when this behavior is not desired because it can cause an unnecessary performance hit. You should enable closing cursors when it is desirable to force the closing of OCI cursors upon a call to close a cursor.

• Enable Thread Safety - Thread safety can be disabled for a data source. If thread safety is not required, disabling this option eliminates the overhead of using thread safety. By default, thread safety is enabled.

• Batch Autocommit Mode - By default, commit is executed only if all statements succeed.

• Numeric Settings - Allows you to choose which numeric settings will be used to determine the decimal and group separator characters when receiving and returning numeric data that is bound as strings. This option allows you to choose Oracle NLS settings (the default setting), Microsoft default regional settings (to provide a way to mirror the Oracle OLE DB driver's behavior for greater interoperability), or US numeric settings (which are necessary when using MS Access or DAO (Database Access Objects) in non-US environments).

The main configuration setup options are described in the Oracle ODBC Driver Configuration Dialog Box topic.

Oracle Options

Figure 1-5 The Oracle Options Tab of the Oracle ODBC Driver Configuration Dialog Box

Description of "Figure 1-5 The Oracle Options Tab of the Oracle ODBC Driver Configuration Dialog Box"

The following list is an explanation of the fields found on the Oracle Options tab shown in the preceding graphic:

• Fetch Buffer Size - The amount of memory used to determine how many rows of data the ODBC Driver will pre-fetch at a time from an Oracle database regardless of the number of rows the application program requests in a single query. However, the number of pre-fetched rows depends on the width and number of columns specified in a single query. Applications that typically fetch fewer than 20 rows of data at a time will see an improvement in response time, particularly over slow network connections or to heavily loaded servers. Setting this too high can actually make response time worse or consume large amounts of memory.

• Enable LOBs - Enables the writing of Oracle LOBs. If writing Oracle LOBs is not required for your application, LOB support can be disabled. There is a small performance penalty for insert and update statements when LOBs are enabled. LOB writing is enabled by default but disabled for Oracle databases that do not support the LOB data type.

• Enable Statement Caching - Enables statement caching feature, which will increase the performance of parsing the query, in case the user has to parse the same text of query and related parameters multiple times. The default is disabled.

• Cache Buffer Size - The statement cache has a maximum size (number of statements) that can be modified by an attribute on the service context, OCI_ATTR_STMTCACHESIZE. The default cache buffer size is 20 that get used only if statement caching option is enabled. Setting cache buffer size to 0 will disable statement caching feature.

• Max Token Size - Sets the token size to the nearest multiple of 1 KB (1024 bytes) beginning at 4 KB (4096 bytes). The default size is 8 KB (8192 bytes). The maximum value that can be set is 128 KB (131068 bytes).

• Translate ORA errors - Any migrated third party ODBC application, which is using the Babelfish feature, expects that errors returned by the server to be in their native database format, then users can enable this option to receive native errors based on the error translation registered with SQL Translation Profile.

• Convert Empty String - Any third party ODBC application that is migrated to Oracle Database requires handling empty string data (Oracle Database does not handle empty string data in table columns), then they can enable this option so that the application can insert empty string data or retrieve empty string data.

  
  

  Note: This feature is not implemented for release 12.1.

  
  

The Failover area of the Oracle Options tab contains the following fields:

• Enable Failover - Enables Oracle Fail Safe and Oracle Parallel Server failover retry. This option in an enhancement to the failover capabilities of Oracle Fail Safe and Oracle Parallel Server. Enable this option to configure additional failover retries. The default is enabled.

• Retry - The number of times the connection failover will be attempted. The default is 10 attempts.

• Delay - The number of seconds to delay between failover attempts. The default is 10 seconds.

The main configuration setup options are described in the Oracle ODBC Driver Configuration Dialog Box topic.

Workarounds Options

Figure 1-6 The Workarounds Options Tab of the Oracle ODBC Driver Configuration Dialog Box

Description of "Figure 1-6 The Workarounds Options Tab of the Oracle ODBC Driver Configuration Dialog Box"

The following list is an explanation of the fields found on the Workarounds Options tab shown in the preceding graphic:

• Bind TIMESTAMP as DATE - Check this box to force the Oracle ODBC Driver to bind SQL_TIMESTAMP parameters as the Oracle DATE type instead of as the Oracle TIMESTAMP type (the default). For more information, see Implementation of Data Types (Advanced).

• Force SQL_WCHAR Support - Check this box to enable SQLDescribeCol, SQLColumns, and SQLProcedureColumns to unconditionally return the data type of SQL_WCHAR for SQL_CHAR columns; SQL_WVARCHAR for SQL_VARCHAR columns; and SQL_WLONGVARCHAR for SQL_LONGVARCHAR columns. This feature enables Unicode support in applications that rely on the results of these ODBC calls (for example, ADO). This support is disabled by default.

• Disable Microsoft Transaction Server - Clear the check in this box to enable Microsoft Transaction Server (MTS) support. By default, MTS support is disabled.

• Set Metadata Id Default to SQL_TRUE - Check this box to change the default value of the SQL_ATTR_METADATA_ID connection and statement attribute at connection time to be SQL_TRUE. Under normal circumstances, SQL_ATTR_METADATA_ID would default to SQL_FALSE. ODBC calls made by the application to specifically change the value of the attribute after connection time will be unaffected by this option and will complete their functions as expected. By default, this option is off. The Implementation of ODBC API Functions topic provides some additional information about the SQL_ATTR_METADATA_ID attribute.

• Pre-fetch size for LONG column data - Set this value to prefetch LONG or LONG RAW data to improve performance of ODBC applications. This enhancement improves the performance of Oracle ODBC driver up to 10 times, depending on the pre-fetch size set by the user. The default value is 0. The maximum value that you can set is 64KB (65536 bytes).

  If the value of pre-fetch size is set to some value greater than 65536, the data fetched will only be 65536 bytes. If you have LONG or LONG RAW data in the database that is greater that 65536 bytes, the pre-fetch size should be set to 0 (the default value), which will result in single row fetch and complete LONG data can be fetched. If you pass a buffer size less than the pre-fetch size in non-polling mode, a data truncation error will occur if the LONG data size in the database is greater than the buffer size.

• Disable SQLDescribeParam - If the SQLDescribeParam function is enabled, the SQL_VARCHAR data type is returned for all parameters. If the Force SQL_WCHAR Support function is also enabled, the SQL_WVARCHAR data type is returned for all parameters. By default, this function is enabled.

• Bind NUMBER as FLOAT - Check this box to force the Oracle ODBC Driver to bind NUMBER column containing FLOAT data as Float instead of as the Binary Float (the default).

• Disable RULE Hint - Clear the check in this box to enable RULE Hint specified with catalogue queries. By default, RULE Hint option is disabled.

• Use OCIDescribeAny - Check this box to gain a performance improvement by forcing the driver to use OCIDescribeAny()when an application makes heavy calls to small packaged procedures that return REF CURSORS.

The main configuration setup options are described in the Oracle ODBC Driver Configuration Dialog Box topic.

SQL Server Migration Options

Figure 1-7 The SQL Server Migration Options Tab of the Oracle ODBC Driver Configuration Dialog Box

Description of "Figure 1-7 The SQL Server Migration Options Tab of the Oracle ODBC Driver Configuration Dialog Box"

The following list is an explanation of the fields found on the SQL Server Migration Options tab shown in the preceding graphic:

• EXEC Syntax Enabled - enables support for SQL Server EXEC syntax. A procedure (or function) call specified in an EXEC statement is translated to its equivalent Oracle procedure (or function) call before being processed by an Oracle database server. By default this option is disabled.

• Schema - the translated Oracle procedure (or function) is assumed to be defined in the user's default schema. However, if all procedures (or functions) from the same SQL Server database are migrated to the same Oracle schema with their database name as the schema name, this field should be set to database. By the same token, this field should be set to owner if all procedures (or functions) owned by the same SQL Server user are defined in the same Oracle schema. This field is empty by default.

The main configuration setup options are described in the Oracle ODBC Driver Configuration Dialog Box topic.

Reducing Lock Timeout

An Oracle server waits indefinitely for lock conflicts between transactions to be resolved. You can limit the amount of time that an Oracle server waits for locks to be resolved by setting the Oracle ODBC Driver's LockTimeOut entry in the oraodbc.ini file. The value you enter for the LockTimeOut parameter is the number of seconds after which an Oracle server will time out if it cannot obtain the requested locks. In the following example, the Oracle server will time out after 60 seconds:

[Oracle ODBC Driver Common]
LockTimeOut=60

Connecting to an Oracle Data Source

To connect to a Data Source, the Oracle ODBC Driver requires that the OCI client software be installed on your computer and the corresponding listener be running on the Oracle server. Oracle Net Services for Windows is a Dynamic Linked Library (DLL) based application. For more information about Oracle Net Services, see the Oracle Net Services documentation.

As part of the connection process, an application can prompt you for information. If an application prompts you for information about an Oracle data source, do the following:

1. In the TNS Service Name box, enter the name of the TNS service.

2. In the User Name box, enter the name you use to access an Oracle Database.

3. In the Password box, enter the password you use to access an Oracle Database.

4. Click OK.

An application must connect to a data source in order to access the data in it. Different applications connect to data sources at different times. For example, an application might connect to a data source only at your request, or it might connect automatically when it starts. For information about when an application connects to a data source, see the documentation for that application.

For additional information, click any of the following links.

Related Topic for All Users

Configuring the Data Source

Related Topics for Programmers

SQLDriverConnect Implementation

Data Source Configuration Options

Using the Oracle ODBC Driver for the First Time

Note: This information is for Microsoft Windows users only.

The following problems may occur when you use the Oracle ODBC Driver for the first time and attempt to attach to a database or table:

• OracleHome/bin must be in the system path for the Oracle ODBC Driver to function successfully

  This can be verified by typing PATH from a command prompt.

• Your machine or server system is missing required software

  
  Problem: Oracle ODBC Driver fails to work.
  Cause: Either OCI software is not installed on your machine or Oracle database software is not installed on your server system.
  Recommended Action: Install the required OCI software on your client machine or Oracle database software on your server system or both.

• Client/Server connectivity is incorrectly set up on your machine

  
  Problem: Cannot connect to the server system from your machine.
  Cause: Either the required transport software is not installed or is not configured correctly.
  Recommended Action: As a test, for example when using TCP/IP, make sure that your machine can ping to the server where the Oracle database resides. Use tnsping.exe located in the /orant/bin or /Oracle/ora90/bin directory to ensure connectivity to a specific database service. For example:
  C:/ORANT/BIN>tnsping database-service-name

• TNS Service Name does not match the name that was entered in the Oracle Net Configuration Assistant (NETCA)

  
  Problem: The user is returned an error message about the TNS Service Name while attempting to set up the Oracle ODBC Driver.
  Cause: The TNS Service Name does not match the name entered in NETCA.
  Recommended Action: Change the TNS Service Name in the Oracle ODBC Driver setup window to match the TNS Service Name in NETCA.
  
  

  Note: Refer to the pull-down menu on the Datasource Configuration screen to view a list of all valid TNS service names on the system as well as names entered by users.

  
  

• User does not have access to the database

  
  Problem: The user is returned an access denied error message when attempting to gain access to an Oracle database while using the Oracle ODBC Driver.
  Cause: You do not have access to an Oracle database.
  Recommended Action: Ensure the proper privileges are assigned for the user to gain access to the Oracle database.

• System Error: 182 or 193 when trying to create an ODBC DSN

  
  Problem: You are trying to create a Data Source Name with the ODBC Administrator but, after selecting the Oracle ODBC Driver, you received a System Error 182 or System Error 193.
  Cause: This error is due to a mismatch in the mfc42.dll provided by Microsoft.
  Recommended Action: Verified that OracleHome/bin is in your PATH and that you have no duplicate oci.dll outside the OracleHome/bin. If you still receive the error, copy the mfc42.dll from a working machine to the machine with the problem.

• Translator Library could not be loaded system error code 31

  
  Problem: On Windows with the Oracle ODBC Driver in the Microsoft ODBC Administrator trying to Add or Delete an Oracle ODBC DSN, you get the error "Translator Library could not be loaded System error code 31."
  Cause: In some cases, Windows machines may contain an outdated version of the mfc42.dll. Oracle ODBC is built against the newer version of mfc42.dll (specifically, version 6.0.8665.0).
  Recommended Action: An outdated version of mfc42.dll is causing this error to occur. Installing MDAC 2.5 SP1 does not take care of this problem. You need to obtain a newer version of the mfc42.dll (version 6.0.8665.0 or higher) and replace the outdated mfc42.dll in the %WINNT%/System32 directory by:
  -- Copying from another machine that works correctly
  -- Contact Microsoft to find out how to obtain the dll

Expired Password

This section contains information about expired passwords.

Expired Password Behavior

If you try to connect to the database and your password has expired, you are prompted to change your password. Upon making a successful password change, you will be connected to the database. However, if you try to connect to the database with a SQLDriverConnect call with a SQL_DRIVER_NOPROMPT parameter value, the Oracle ODBC Driver will not prompt you for the password change. Instead, an error condition results, producing an error message and number that indicates that the password has expired.

API Conformance

Oracle ODBC Driver release 9.2.0.0.0 and higher supports all Core, Level 2, and Level 1 functions.

In addition, Oracle ODBC Driver release 9.2.0.0.0 and higher supports translation DLLs.

The following topics describe the ODBC API functions implemented by the Oracle ODBC Driver.

Related Topic for Advanced Users

Error Messages

Related Topic for Programmers

Implementation of ODBC API Functions

Implementation of ODBC API Functions

Implementation of the ODBC SQL Syntax

If a comparison predicate has a parameter marker as the second expression in the comparison and the value of that parameter is set to SQL_NULL_DATA with SQLBindParameter, the comparison will fail. This is consistent with the null predicate syntax in ODBC SQL.

Implementation of Data Types (Programming)

For programmers, the noteworthy part of the implementation of the data types concerns the CHAR, VARCHAR, and VARCHAR2 data types.

For an fSqlType value of SQL_VARCHAR, SQLGetTypeInfo returns the Oracle database data type VARCHAR2. For an fSqlType value of SQL_CHAR, SQLGetTypeInfo returns the Oracle database data type CHAR.

Unicode Support Within the ODBC Environment

The Microsoft or unixODBC ODBC Driver Manager (Driver Manager) makes all ODBC drivers, regardless if they support Unicode, appear as if they are Unicode compliant. This allows ODBC applications to be written independent of the Unicode capabilities of underlying ODBC drivers.

The extent to which the Driver Manager can emulate Unicode support for ANSI ODBC drivers is limited by the conversions possible between the Unicode data and the local code page. Data loss is possible when the Driver Manager is converting from Unicode to the local code page. Full Unicode support is not possible unless the underlying ODBC driver supports Unicode. The Oracle ODBC Driver provides full Unicode support.

Unicode Support in ODBC API

The ODBC API supports both Unicode and ANSI entry points using the "W" and "A" suffix convention. An ODBC application developer does not need to explicitly call entry points with the suffix. An ODBC application that is compiled with the UNICODE and _UNICODE preprocessor definitions will generate the appropriate calls. For example, a call to SQLPrepare will be compiled as SQLPrepareW.

The C data type, SQL_C_WCHAR, was added to the ODBC interface to allow applications to specify that an input parameter is encoded as Unicode or to request column data returned as Unicode. The macro SQL_C_TCHAR is useful for applications that need to be built as both Unicode and ANSI. The SQL_C_TCHAR macro compiles as SQL_C_WCHAR for Unicode applications and as SQL_C_CHAR for ANSI applications.

The SQL data types, SQL_WCHAR, SQL_WVARCHAR, and SQL_WLONGVARCHAR, have been added to the ODBC interface to represent columns defined in a table as Unicode. Potentially, these values are returned from calls to SQLDescribeCol, SQLColAttribute, SQLColumns, and SQLProcedureColumns.

Unicode encoding is supported for SQL column types NCHAR, NVARCHAR2, and NCLOB. In addition, Unicode encoding is also supported for SQL column types CHAR and VARCHAR2 if the character semantics are specified in the column definition.

The ODBC Driver supports these SQL column types and maps them to ODBC SQL data types.

^Footnote 1 CHAR maps to SQL_WCHAR if the character semantics were specified in the column definition and if the character set for the database is Unicode.

^Footnote 2 VARCHAR2 maps to SQL_WVARCHAR if the character semantics were specified in the column definition and if the character set for the database is Unicode.

Unicode Functions in the Driver Manager

The Driver Manager performs the following functions when it detects that the underlying ODBC driver does not support Unicode:

• Convert Unicode function calls to ANSI function calls before calling the ANSI ODBC driver. String arguments will be converted from Unicode to the local code page. For example, a call to SQLPrepareW is converted to call SQLPrepare. The text of the SQL statement parameter is converted from Unicode to the local code page.

• Convert return parameters that are character data from the local code page to Unicode. For example, returning the column name through SQLColAttribute.

• Convert data from the local code page to Unicode for columns bound as SQL_C_WCHAR.

• Convert data from Unicode to the local code page for input parameters bound as SQL_C_WCHAR.

SQLGetData Performance

The SQLGetData function allows an ODBC application to specify the data type to receive a column as after the data has been fetched. OCI requires the Oracle ODBC Driver to specify the data type before it is fetched. In this case, the Oracle ODBC Driver uses the knowledge it has about the data type of the column as defined in the database to determine how to best default to fetching the column through OCI.

If a column that contains character data is not bound by SQLBindCol, the Oracle ODBC Driver needs to determine if it should fetch the column as Unicode or as the local code page. The driver could always default to receiving the column as Unicode, however, this may result in as many as two unnecessary conversions. For example, if the data were encoded in the database as ANSI, there would be an ANSI to Unicode conversion to fetch the data into the Oracle ODBC Driver. If the ODBC application then requested the data as SQL_C_CHAR, there would be an additional conversion to revert the data back to its original encoding.

The default encoding of the Oracle client will be used when fetching data. However, an ODBC application may overwrite this default and fetch the data as Unicode by binding the column or the parameter as the WCHAR data type.

Unicode Samples

As the Oracle ODBC Driver itself was implemented using TCHAR macros, it is recommended that ODBC application programs use TCHAR in order to take advantage of the driver.

The following links are program examples showing how to use TCHAR, which becomes the WCHAR data type in case you compile with UNICODE and _UNICODE.

• Example 1: Connection to Database

• Example 2: Simple Retrieval

• Example 3: Retrieval Using SQLGetData (Binding After Fetch)

• Example 4: Simple Update

• Example 5: Update and Retrieval for Long Data (CLOB)

Example 1: Connection to Database

No difference other than specifying Unicode literals for SQLConnect.

HENV envHnd;
HDBC conHnd
;
HSTMT stmtHnd;
RETCODE rc;

rc = SQL_SUCCESS;

 
// ENV is allocated
rc = SQLAllocEnv(&envHnd);
// Connection Handle is allocated
rc = SQLAllocConnect(envHnd, &conHnd);
rc = SQLConnect(conHnd, _T("stpc19"), SQL_NTS, _T("scott"), SQL_NTS, _T("tiger"),
 SQL_NTS);
.
.
.
if (conHnd)
SQLFreeConnect(conHnd);
if (envHnd)
  SQLFreeEnv(envHnd);

Example 2: Simple Retrieval

The following example retrieves the employee names and the job titles from the EMP table. With the exception that you need to specify TCHAR compliant data to every ODBC function, there is no difference to the ANSI case. If the case is a Unicode application, you have to specify the length of the buffer to the BYTE length when you call SQLBindCol (for example, sizeof(ename) ).

/*
** Execute SQL, bind columns, and Fetch.
** Procedure:
**
** SQLExecDirect
** SQLBindCol
** SQLFetch
** 
*/
static SQLTCHAR *sqlStmt = _T("SELECT ename, job FROM emp");
SQLTCHAR ename[50];
SQLTCHAR job[50];
SQLINTEGER enamelen, joblen;
 
_tprintf(_T("Retrieve ENAME and JOB using SQLBindCol 1.../n[%s]/n"), sqlStmt);
 
// Step 1: Prepare and Execute
rc = SQLExecDirect(stmtHnd, sqlStmt, SQL_NTS); // select
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
// Step 2: Bind Columns
rc = SQLBindCol(stmtHnd,
1,
SQL_C_TCHAR,
ename,
sizeof(ename),
&enamelen);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
rc = SQLBindCol(stmtHnd,
2,
SQL_C_TCHAR,
job,
sizeof(job),
&joblen);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
do 
{
// Step 3: Fetch Data
rc = SQLFetch(stmtHnd);
if (rc == SQL_NO_DATA)
break;
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
_tprintf(_T("ENAME = %s, JOB = %s/n"), ename, job);
} while (1);
_tprintf(_T("Finished Retrieval/n/n"));

Example 3: Retrieval Using SQLGetData (Binding After Fetch)

This example shows how to use SQLGetData. For those who are not familiar with ODBC programming, the fetch is allowed before binding the data using SQLGetData, unlike in an OCI program. There is no difference to the ANSI application in terms of Unicode-specific issues.

/*
** Execute SQL, bind columns, and Fetch.
** Procedure:
**
** SQLExecDirect
** SQLFetch
** SQLGetData
*/
static SQLTCHAR *sqlStmt = _T("SELECT ename,job FROM emp"); // same as Case 1.
SQLTCHAR ename[50];
SQLTCHAR job[50];
 
_tprintf(_T("Retrieve ENAME and JOB using SQLGetData.../n[%s]/n"), sqlStmt);
if (rc != SQL_SUCCESS)
{
_tprintf(_T("Failed to allocate STMT/n"));
goto exit2;
}
 
// Step 1: Prepare and Execute
rc = SQLExecDirect(stmtHnd, sqlStmt, SQL_NTS); // select
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 

do 
{

// Step 2: Fetch
rc = SQLFetch(stmtHnd);
if (rc == SQL_NO_DAT
break;

checkSQLErr(envHnd, conHnd, stmtHnd, rc);

 
// Step 3: GetData
rc = SQLGetData(st
mtHnd, 
1,
SQL_C_TCHAR,
(SQLPOINTER)ename,
sizeof(ename), 
NULL);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
rc = SQLGetData(stmtHnd, 
2,
SQL_C_TCHAR, 
(SQLPOINTER)job,
sizeof(job), 
NULL);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
_tprintf(_T("ENAME = %s, JOB = %s/n"), ename, job);
} while (1);
_tprintf(_T("Finished Retrieval/n/n"));

Example 4: Simple Update

This example shows how to update data. Likewise, the length of data for SQLBindParameter has to be specified with the BYTE length, even in the case of a Unicode application.

/
*
** Execute SQL, bind columns, and Fetch.
** Procedure:
**
** SQLPrepare
** SQLBindParameter
** SQLExecute
*/
static SQLTCHAR *sqlStmt = _T("INSERT INTO emp(empno,ename,job) VALUES(?,?,?)");
static SQLTCHAR *empno = _T("9876"); // Emp No
static SQLTCHAR *ename = _T("ORACLE"); // Name
static SQLTCHAR *job = _T("PRESIDENT"); // Job
 
_tprintf(_T("Insert User ORACLE using SQLBindParameter.../n[%s]/n"), sqlStmt);
 
// Step 1: Prepar
rc = SQLPrepare(stmtHnd, sqlStmt, SQL_NTS); // select
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
// Step 2: Bind Parameterrc = SQLBindParameter(stmtHnd, 
1, 
SQL_PARAM_INPUT,
SQL_C_TCHAR,
SQL_DECIMAL,
4, // 4 digit
0,
(SQLPOINTER)empno,
0,
NULL);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
rc = SQLBindParameter(stmtHnd, 
2, 
SQL_PARAM_INPUT,
SQL_C_TCHAR,
SQL_CHAR,
lstrlen(ename)*sizeof(TCHAR),
0,
(SQLPOINTER)ename,
lstrlen(ename)*sizeof(TCHAR),
NULL);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
rc = SQLBindParameter(stmtHnd, 
3, 
SQL_PARAM_INPUT,
SQL_C_TCHAR,
SQL_CHAR,
lstrlen(job)*sizeof(TCHAR),
0,
(SQLPOINTER)job,
lstrlen(job)*sizeof(TCHAR),
NULL);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
// Step 3: Execute
rc = SQLExecute(stmtHnd);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);

Example 5: Update and Retrieval for Long Data (CLOB)

This example may be the most complicated case to update and retrieve data for long data, like CLOB, in Oracle. Since the length of data should be always the BYTE length, lstrlen(TCHAR data)*sizeof(TCHAR) is needed to derive the BYTE length.

/*
** Execute SQL, bind columns, and Fetch.
** Procedure:
**
** SQLPrepare
** SQLBindParameter
** SQLExecute
** SQLParamData
** SQLPutData
**
** SQLExecDirect
** SQLFetch
** SQLGetData
*/
static SQLTCHAR *sqlStmt1 = _T("INSERT INTO clobtbl(clob1) VALUES(?)");
static SQLTCHAR *sqlStmt2 = _T("SELECT clob1 FROM clobtbl");
SQLTCHAR clobdata[1001];
SQLTCHAR resultdata[1001];
SQLINTEGER ind = SQL_DATA_AT_EXEC;
SQLTCHAR *bufp;
int clobdatalen, chunksize, dtsize, retchklen;
 
_tprintf(_T("Insert CLOB1 using SQLPutData.../n[%s]/n"), sqlStmt1);
 
// Set CLOB Data
{
int i;
SQLTCHAR ch;
for (i=0, ch=_T('A'); i< sizeof(clobdata)/sizeof(SQLTCHAR); ++i, ++ch)
{
if (ch > _T('Z'))
ch = _T('A');
clobdata[i] = ch;
}
clobdata[sizeof(clobdata)/sizeof(SQLTCHAR)-1] = _T('/0');
}
clobdatalen = lstrlen(clobdata); // length of characters
chunksize = clobdatalen / 7; // 7 times to put
 
// Step 1: Prepare
rc = SQLPrepare(stmtHnd, sqlStmt1, SQL_NTS);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
// Step 2: Bind Parameter with SQL_DATA_AT_EXEC
rc = SQLBindParameter(stmtHnd, 
1, 
SQL_PARAM_INPUT,
SQL_C_TCHAR,
SQL_LONGVARCHAR,
clobdatalen*sizeof(TCHAR),
0,
(SQLPOINTER)clobdata,
clobdatalen*sizeof(TCHAR),
&ind);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
// Step 3: Execute
rc = SQLExecute(stmtHnd);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
// Step 4: ParamData (initiation)
rc = SQLParamData(stmtHnd, (SQLPOINTER*)&bufp); // set value
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
for (dtsize=0, bufp = clobdata;
dtsize < clobdatalen;
dtsize += chunksize, bufp += chunksize)
{
int len;
if (dtsize+chunksize<clobdatalen)
len = chunksize;
else
len = clobdatalen-dtsize;
 
// Step 5: PutData
rc = SQLPutData(stmtHnd, (SQLPOINTER)bufp, len*sizeof(TCHAR));
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
}
 
// Step 6: ParamData (temination)
rc = SQLParamData(stmtHnd, (SQLPOINTER*)&bufp);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
rc = SQLFreeStmt(stmtHnd, SQL_CLOSE);
_tprintf(_T("Finished Update/n/n"));
rc = SQLAllocStmt(conHnd, &stmtHnd);
if (rc != SQL_SUCCESS)
{
_tprintf(_T("Failed to allocate STMT/n"));
goto exit2;
}
 
// Clear Result Data
memset(resultdata, 0, sizeof(resultdata));
chunksize = clobdatalen / 15; // 15 times to put
 
// Step 1: Prepare
rc = SQLExecDirect(stmtHnd, sqlStmt2, SQL_NTS); // select
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
// Step 2: Fetch
rc = SQLFetch(stmtHnd);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
for(dtsize=0, bufp = resultdata;
dtsize < sizeof(resultdata)/sizeof(TCHAR) && rc != SQL_NO_DATA;
dtsize += chunksize-1, bufp += chunksize-1)
{
int len; // len should contain the space for NULL termination
if (dtsize+chunksize<sizeof(resultdata)/sizeof(TCHAR))
len = chunksize;
else
len = sizeof(resultdata)/sizeof(TCHAR)-dtsize;
 
// Step 3: GetData
rc = SQLGetData(stmtHnd, 
1,
SQL_C_TCHAR,
(SQLPOINTER)bufp,
len*sizeof(TCHAR), 
&retchklen);
}
if (!_tcscmp(resultdata, clobdata))
{
_tprintf(_T("Succeeded!!/n/n"));
}
else
{
_tprintf(_T("Failed!!/n/n"));
}

General ODBC Programming Tips

This section describes some general programming tips to improve the performance of an ODBC application.

• Enable connection pooling if the application will frequently connect and disconnect from a data source. Reusing pooled connections is extremely efficient compared to reestablishing a connection.

• Minimize the number of times a statement needs to be prepared. Where possible, use bind parameters to make a statement reusable for different parameter values. Preparing a statement once and executing it several times is much more efficient than preparing the statement for every SQLExecute.

• Do not include columns in a SELECT statement of which you know the application will not retrieve; especially LONG columns. Due to the nature of the database server protocols, the ODBC Driver must fetch the entire contents of a LONG column if it is included in the SELECT statement, regardless if the application binds the column or does a SQLGetData.

• If you are performing transactions that do not update the data source, set the SQL_ATTR_ACCESS_MODE attribute of the ODBC SQLSetConnectAttr function to SQL_MODE_READ_ONLY.

• If you are not using ODBC escape clauses, set the SQL_ATTR_NOSCAN attribute of the ODBC SQLSetConnectAttr function or the ODBC SQLSetStmtAttr function to true.

• Use the ODBC SQLFetchScroll function instead of the ODBC SQLFetch function for retrieving data from tables that have a large number of rows.

• Enable OCI statement caching when the same SQL statements are used multiple times (StatementCache=T).

• Binding NUMBER columns as FLOAT speeds up query execution (BindAsFLOAT=T).

• While fetching LONG or LONG RAW set MaxLargeData=<value> for optimum performance.

• Setting UseOCIDescribeAny=T for applications making heavy calls to small packaged procedures that return Ref Cursor will see performance improvement.

Data Source Configuration Options

This topic discusses performance implications of the following ODBC data source configuration options:

• Enable Result Sets

• Enable LOBs

• Bind TIMESTAMP as DATE

• Enable Closing Cursors

• Enable Thread Safety

• Fetch Buffer Size

Enable Result Sets

This option enables the support of returning result sets (for example, RefCursor) from procedure calls. The default is enabling the returning of result sets.

The ODBC Driver must query the database server to determine the set of parameters for a procedure and their data types in order to determine if there are any RefCursor parameters. This query incurs an additional network round trip the first time any procedure is prepared and executed.

Enable LOBs

This option enables the support of inserting and updating LOBs. The default is enabled.

The ODBC Driver must query the database server to determine the data types of each parameter in an INSERT or UPDATE statement in order to determine if there are any LOB parameters. This query incurs an additional network round trip the first time any INSERT or UPDATE is prepared and executed.

Bind TIMESTAMP as DATE

Binds SQL_TIMESTAMP parameters as the appropriate Oracle data type. If this option is set to TRUE, SQL_TIMESTAMP binds as the Oracle DATE data type. If this option is set to FALSE, SQL_TIMESTAMP binds as the Oracle TIMESTAMP data type (which is the default).

Enable Closing Cursors

The SQL_CLOSE option of the ODBC function, SQLFreeStmt, is supposed to close associated cursors with a statement and discard all pending results. The application can reopen the cursor by executing the statement again without doing a SQLPrepare again. A typical scenario for this would be an application which expects to be idle for a while but will reuse the same SQL statement again. While the application is idle, it may want to free up any associated server resources.

The Oracle Call Interface (OCI), on which the Oracle ODBC Driver is layered, does not support the functionality of closing cursors. So, by default, the SQL_CLOSE option has no effect in the Oracle ODBC Driver. The cursor and associated resources remain open on the database server.

Enabling this option will cause the associated cursor to be closed on the database server. However, this results in the parse context of the SQL statement being lost. The ODBC application can execute the statement again without calling SQLPrepare. However, internally the ODBC Driver must prepare and execute the statement all over. Enabling this option will have a severe performance impact on applications that prepare a statement once and execute it repeatedly.

This option should only be enabled if freeing the resources on the server is absolutely necessary.

Enable Thread Safety

If an application is single threaded, this option can be disabled. By default, the ODBC Driver ensures that access to all internal structures (environment, connection, statement) are thread safe. Single threaded applications can eliminate some of the thread safety overhead by disabling this option. Disabling this option should show a minor performance improvement.

Fetch Buffer Size

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 application program requests in a single query, thus improving performance.

Applications that typically fetch fewer than 20 rows of data at a time will see an improvement in response time, particularly over slow network connections or to heavily loaded servers. Setting this too high can actually make response time worse or consume large amounts of memory. The default is 64,000 bytes. Choose a value that works best for your application.

DATE and TIMESTAMP Data Types

If a DATE column in the database is used in a WHERE clause and the column has an index, there can be an impact on performance. For example:

SELECT * FROM EMP WHERE HIREDATE = ?

In this example, an index on the HIREDATE column could be used to make the query execute quickly. But, because HIREDATE is actually a DATE value and the ODBC Driver is supplying the parameter value as TIMESTAMP, the Oracle server's query optimizer must apply a conversion function. To prevent incorrect results (as might happen if the parameter value had nonzero fractional seconds), the optimizer applies the conversion to the HIREDATE column resulting in the following statement:

SELECT * FROM EMP WHERE TO_TIMESTAMP(HIREDATE) = ?

Unfortunately, this has the effect of disabling the use of the index on the HIREDATE column and instead the server performs a sequential scan of the table. If the table has many rows, this can take a long time. As a workaround for this situation, the ODBC Driver has the connection option to Bind TIMESTAMP as DATE. When this option is enabled, the ODBC Driver binds SQL_TIMESTAMP parameters as the Oracle DATE data type instead of the Oracle TIMESTAMP data type. This allows the query optimizer to use any index on the DATE columns.

Related Topic

Implementation of Data Types (Advanced)