Related Topics

Configuring the Data Source

Connecting to a Data Source

Driver Conformance Levels

New and Changed Features

Files Created by the Installation

Topics:

• New Features for Oracle ODBC Driver Release 19c, Version 19.1.0.0.0

• New Features for Oracle ODBC Driver Release 18c, Version 18.1.0.0.0

• New Features for Oracle ODBC Driver Release 12.2.0.1.0

• New Features for Oracle ODBC Driver Release 12.1.0.2.0

• New Features for Oracle ODBC Driver Release 12.1.0.1.0

• New Features for Oracle ODBC Driver Release 11.2.0.1.0

• New Features for Oracle ODBC Driver Release 11.1.0.1.0

• New Features for Oracle ODBC Driver Release 10.1.0.2.0

• Changes for Oracle ODBC Driver Release 10.1.0.2.0

New Features for Oracle ODBC Driver Release 19c, Version 19.1.0.0.0

There are no new features for Oracle ODBC Driver release 19c, version 19.1.0.0.0 software for the Microsoft Windows Server 2008, Windows Server 2008 R2, Windows Server 2012, Windows Server 2012 R2, Windows 7, Windows 8, Windows 8.1, Windows 10, Linux X86-64 (32-bit, 64-bit), Sun Solaris SPARC64 (32-bit, 64-bit), IBM AIX 5L (32-bit, 64-bit), Sun Solaris X64 (32-bit, 64-bit), HPUX IA64 (32-bit, 64-bit), ZLinux (32-bit, 64-bit) operating systems.

New Features for Oracle ODBC Driver Release 18c, Version 18.1.0.0.0

New Features for Oracle ODBC Driver Release 12.2.0.1.0

Features of the Oracle ODBC Driver Release 12.2.0.1.0 software for the Microsoft Windows Server 2008, Windows Server 2008 R2, Windows Server 2012, Windows Server 2012 R2, Windows 7, Windows 8, Windows 8.1, Windows 10, 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:

• Support is added for long identifiers.

  Oracle ODBC Driver now supports object lengths of 128 bytes. In previous releases, the object length limit was 30 bytes.

• Support is added for time stamp with time zone and time stamp with local time zone.

  This features does not require changes to the existing ODBC application where ODBC TIMESTAMP data type is used. If an existing application uses ODBC TIMESTAMP data type and the database column is TIMESTAMP, the current behavior is preserved.

  For database column TIMESTAMP WITH TIMEZONE or TIMESTAMP WITH LOCAL TIMEZONE, the time component in the ODBC TIMESTAMP_STRUCT is in the user’s session time zone. This behavior is transparent to the user’s application, requiring no change to the ODBC application.

New Features for Oracle ODBC Driver Release 12.1.0.2.0

Features of the Oracle ODBC Driver Release 12.1.0.2.0 software for the Microsoft Windows Server 2008, Windows Server 2008 R2, Windows Server 2012, Windows 7, Windows 8, Windows 10, 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:

Microsoft Windows 10 platform is added.

New Features for Oracle ODBC Driver 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 Server 2012, Windows 7, Windows 8, 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.

• 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 SQL Translation Framework 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 SQL Translation Framework mode. After error translation is registered, then ODBC application users can enable this option, SQLTranslateErrors = T, to receive native errors according to their registration.

  See Table 24-4 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.

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

  • SQL_COLUMN_AUTO_INCREMENT

    Starting from Oracle Database 12c Release 1 (12.1.0.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 12c Release 1 (12.1.0.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 12c Release 1 (12.1.0.1)

  • SQLMoreResults()

    Implements ODBC support for implicit results.

New Features for Oracle ODBC Driver 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 and 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 must add the registry key MaxLargeData in the data source name (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 64 KB (65536 bytes).

  If the value of MaxLargeData is greater than 65536, the data fetched is only 65536 bytes. If your database has LONG or LONG RAW data that is greater that 65536 bytes, then set MaxLargeData to 0 (the default value), which causes single-row fetch and fetches complete LONG data. If you pass a buffer size less than the MaxLargeData size in nonpolling mode, a data truncation error occurs 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 Oracle ODBC Driver 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 IBM 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 is not 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 performance improves when users parse the same statement multiple times in the same connection. The default value for the statement cache flag is FALSE.

New Features for Oracle ODBC Driver 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 Oracle ODBC Driver 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.

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.

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.

Before Configuring the Data Source, you must configure network database services so there is an entry for each Transparent Network Substrate (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.

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

The following screenshot shows an example of the Oracle ODBC Driver Configuration dialog box.

Figure 24-3 Oracle ODBC Driver Configuration Dialog Box

Description of "Figure 24-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 (DSN) - The name used to identify the data source to ODBC. For example, "odbc-pc". You must enter a DSN.

• 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 About 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 DSN 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 DSN 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 these links:

Application Options

Oracle Options

Workarounds Options

SQL Server Migration Options

Application Options

The following screenshot shows an example of the Application Options tab found on the Oracle ODBC Driver Configuration dialog box.

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

Description of "Figure 24-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. Enable closing cursors when you want 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 the numeric settings that 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).

Oracle Options

The following screenshot shows an example of the Oracle Options tab found on the Oracle ODBC Driver Configuration dialog box.

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

Description of "Figure 24-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 prefetches 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 prefetched 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 improve their response time, particularly over slow network connections or to heavily loaded servers. Setting Fetch Buffer Size too high can 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 increases 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 are used only if statement caching option is enabled. Setting cache buffer size to 0 disables 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 SQL Translation Framework 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 Oracle Database 12c Release 1 (12.1.0.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 is attempted. The default is 10 attempts.

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

Workarounds Options

The following screenshot shows an example of the Workarounds Options tab found on the Oracle ODBC Driver Configuration dialog box.

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

Description of "Figure 24-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).

• 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 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 are unaffected by this option and complete their functions as expected. By default, this option is off.

• Prefetch 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 prefetch size set by the user. The default value is 0. The maximum value that you can set is 64 KB (65536 bytes).

  If the value of prefetch size is greater than 65536, the data fetched is only 65536 bytes. If you have LONG or LONG RAW data in the database that is greater that 65536 bytes, then set the prefetch size to 0 (the default value), which causes single-row fetch and fetches complete LONG data. If you pass a buffer size less than the prefetch size in nonpolling mode, a data truncation error occurs 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.

SQL Server Migration Options

The following screenshot shows an example of the SQL Server Migration Options tab found on the Oracle ODBC Driver Configuration dialog box.

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

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

The fields of the SQL Server Migration Options tab in the preceding graphic are:

• EXEC Syntax Enabled, which enables support for SQL Server EXEC syntax. A subprogram call specified in an EXEC statement is translated to its equivalent Oracle subprogram call before being processed by an Oracle database server. By default this option is disabled.

• Schema, which is the translated Oracle subprogram assumed to be defined in the user's default schema. However, if all subprograms from the same SQL Server database are migrated to the same Oracle schema with their database name as the schema name, then set this field to database. If all subprograms owned by the same SQL Server user are defined in the same Oracle schema, then set this field to owner. This field is empty by default.

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 times out if it cannot obtain the requested locks. In the following example, the Oracle server times out after 60 seconds:

[Oracle ODBC Driver Common]
LockTimeOut=60

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:

An application must connect to a data source 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.

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 are 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 does 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.

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

Also, 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.

As the Oracle ODBC Driver itself was implemented using TCHAR macros, Oracle recommends that ODBC application programs use TCHAR 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.

SQLHENV envHnd;
SQLHDBC conHnd;
SQLHSTMT stmtHnd;
RETCODE rc;

rc = SQL_SUCCESS;

// ENV is allocated
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &envHnd);
// Connection Handle is allocated
rc = SQLAllocHandle(SQL_HANDLE_DBC, envHnd, &conHnd);
rc = SQLConnect(conHnd, _T("stpc19"), SQL_NTS, _T("scott"), SQL_NTS, _T("tiger"),
 SQL_NTS);
.
.
.
if (conHnd)
{
  SQLDisconnect(conHnd);
  SQLFreeHandle(SQL_HANDLE_DBC, conHnd);
}
if (envHnd)
  SQLFreeHandle(SQL_HANDLE_ENV, 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 must 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_DATA)
    break;

  checkSQLErr(envHnd, conHnd, stmtHnd, rc);

  /* Step 3: GetData */
  rc = SQLGetData(stmtHnd, 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: Prepare */

rc = SQLPrepare(stmtHnd, sqlStmt, SQL_NTS);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
/* Step 2: Bind Parameter */

rc = SQLBindParameter(stmtHnd, 1, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_DECIMAL,4, 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. Because the length of data must be 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;
SQLTCHAR ch;
int clobdatalen, chunksize, dtsize, retchklen, i, len;
 
_tprintf(_T("Insert CLOB1 using SQLPutData.../n[%s]/n"), sqlStmt1);
 
/* Set CLOB Data *

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);
chunksize = clobdatalen / 7;
 
/* 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);
sdhamoth: Continuation:

 
/* Step 4: ParamData (initiation) */
rc = SQLParamData(stmtHnd, (SQLPOINTER*)&bufp);
checkSQLErr(envHnd, conHnd, stmtHnd, rc);
 
for (dtsize=0, bufp = clobdata; dtsize < clobdatalen; dtsize += chunksize, bufp += chunksize)
{
  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 (termination) */
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)
{
  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"));
}

Topics:

• 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 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 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 TRUE, SQL_TIMESTAMP binds as the Oracle DATE data type. If this option is 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 is an application that is idle for a while but reuses the same SQL statement. While the application is idle, it might free up 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 causes 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 severely impacts performance of applications that prepare a statement once and execute it repeatedly.

Enable this option only 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 typically shows 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 determines how many rows of data at a time the ODBC Driver prefetches 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 improve their response time, particularly over slow network connections or to heavily loaded servers. Setting this too high can worsen response time or consume large amounts of memory. The default is 64,000 bytes. Choose a value that works best for your application.