1. Oracle Odbc Driver Configuration Dialog Box

Configuring Oracle ODBC DSN Configuring Oracle ODBC DSN Before you configure the data source, you must configure Net8 database services, so there is an entry for each Service Name. To do this start the Oracle Net8 Assistant:. either click on the Oracle for Windows NT program group and click on the Oracle Net8 Assistant icon.

or from the start bar select Programs, select Oracle for Windows NT, and select the Oracle Net8 Assistant. Using the Oracle Net8 Assistant, you can create, modify, or delete a Service Name or its associated network protocol information, and view configuration information. For example, to create a Service Name:. double click on the Service Names folder in the left hand side screen. click on the + (Create) icon on the tool bar, and follow the instructions provided in the Oracle Service Names Wizard.

Click on the Oracle Net8 Assistant help button for more information. To update configuration information for an existing service name:. open the Service Names folder. double click on the service name to display its current configuration information in the right hand side panel. edit the applicable parameters, and save your changes. Once you have created or updated the configuration information, Net8 is configured and you are ready to configure each data source.

Using SQLConfigDatasource with the ODBC Driver for Oracle. And the Microsoft ODBC Driver for Oracle. Minimum fetch buffer size up to 65535 bytes.

Block (ODBC, OLE DB, and Oracle). The Adaptive Server® Anywhere ODBC driver that comes with. The size of the actual fetch buffer is the product of the value. Oracle ODBC drivers provide connectivity for Windows users.

After Configuring Net8 database services, and before using the Oracle8 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 Oracle8 ODBC Driver as to which information you want to access.

The data source consists of the data that you want to access, and 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 Oracle8 ODBC Driver is an Oracle database and supports transports available under Net8. Use the to perform the configuration. Select the Oracle8 ODBC Driver in the list of installed drivers and click on OK. The Oracle8 ODBC Driver Setup dialog box is displayed. You must enter the Data Source Name and 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.

The Oracle8 ODBC Setup dialog box has the following options: Main Setup Options. Data Source Name - the name used to identify the data source to ODBC. For example, 'Personnel Data.' . Description - a description or comment about the data in the data source.

For example, 'Hire date, salary history, and current review of all employees.' .

Service Name - the location of the Oracle database from which the ODBC driver will retrieve data. This is same name entered in configuring Net8 database services using the Oracle Net8 Assistant. For more information, see the Net8 documentation and troubleshooting using the Oracle8 ODBC Driver for the first time. UserID - the UserID is the user name of the account on the server used to access the data. Database Options. Read Only - check this box to force read-only access.

Prefetch Count - a numeric value that sets the number of rows that the ODBC Driver will fetch at a time from an Oracle database. The default is 1 row. To tune your application, set a value that optimizes network use versus PC memory use. For example, if you only need to view the first couple of rows, set this value to 3 or 4, thus allowing a fetch of 3 or 4 rows in one operation versus fetching one row at a time and increasing network use; if you need to always view the first 25 rows, set this value to 25 to fetch 25 rows in one operation, but realize that you will use more PC memory to hold these rows. Note: The Oracle database from which your application is fetching rows must be release 8.0.3 or higher. WorkAround Options. Force Retrieval of Long Columns - check this box to read the entire Oracle long datatype into memory during SQLFetch.

Oracle restricts any read/write operations to be performed once the streaming of data from a long column has started. Therefore, the contents of a LOB column cannot be accessed until the data for all long columns has been retrieved. The Oracle ODBC Driver will force the entire read on the long column and place it into memory until the application is ready to receive it.

This will free up the application to do other read/write operations on the database. The size of the data in the long column determines the memory consumption on the client. Force Retrieval of Long Columns is disabled by default. Note: This feature could exhaust a PC of memory if the data in the long column is too big.

This feature degrades performance because the data for all long columns must be copied twice, once to internal buffers and once to the applications buffer. Disable MTS Support - check this box to disable Microsoft Transaction Server (MTS) support. By default MTS support is enabled. Application Options 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. Enable LOBs - enables the writing of Oracle LOBs. If writing Oracle LOBs is not required for your application then 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 datatype. Enable Result Sets - enables the processing of Oracle Result Sets.

If Result sets are not required for your application then 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 Failover - enables Oracle Fail Safe and Oracle Parallel Server failover capability. The Oracle client must be release 8.0.5 or higher. Retry Count - the number of times the connection failover will be attempted.

Delay - the number of seconds to delay between failover attempts. See the Oracle Fail Safe and Oracle Parallel Server documentation on how to set up and use both of these products. Enable Query Timeout - enables query timeout for SQL queries. By default the timeout value is zero (0), meaning there is no timeout for SQL queries. If this box is not checked, the Oracle8 ODBC Driver responds with a 'not capable' message. Enable Closing Cursors - enables closing cursors. By default closing cursors is disabled (box is not checked), 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. Translation Options.

Translation Option - a numeric value that is passed to the translation library when it is called by the Oracle8 ODBC Driver. Translation Library - the name of a translation library that is to be called by the Oracle8 ODBC Driver.

21.1 About Oracle ODBC Driver What is ODBC? Open Database Connectivity (ODBC) provides a standard interface that allows one application to access many different data sources. The application's source code does not have to be recompiled for each data source. A database driver links the application to a specific data source. A database driver is a dynamic-link library that an application can invoke on demand to gain access to a particular data source.

Therefore, the application can access any data source for which a database driver exists. The ODBC interface defines the following:. A library of ODBC function calls that allows an application to connect to a data source, execute structured query language (SQL) statements, and retrieve results. SQL syntax based on the SQL-99 specification.

A standard set of error codes. A standard way to connect to and log in to a data source. A standard representation for data types. Shows the components of the ODBC model. The model begins with an ODBC application making a call to the Driver Manager through the ODBC application program interface (API). The Driver Manager can be either the Microsoft Driver Manager or the unixODBC Driver Manager. Still using the ODBC API, the Driver Manager makes a call to the ODBC Driver.

The ODBC Driver accesses the database over a network communications link using the database API. Shows an ODBC application accessing three separate databases. 21.2.1.1 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 IBM Advanced Interactive eXecutive (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. Shows the Oracle ODBC Driver architecture as described in the preceding paragraphs. 21.2.1.5 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 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 must call SQLGetInfo with the appropriate information type to retrieve a list of SQL-99 supported features.

Note: The following configuration steps are for Windows users. Unix users must use the odbcupdateini.sh file to create a DSN. After installing the Oracle ODBC Driver and, 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:. From the start menu, select Programs, Administrative Tools, Data Sources (ODBC).

A list of installed drivers is displayed. Click Add in the Create New Data Source window and then select the Oracle ODBC Driver in the list of installed drivers. Click Finish. The 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. 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:. From the start menu, select Programs, Administrative Tools, Data Sources(ODBC). 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. In the, modify the option values as necessary and click OK. To delete an Oracle data source:.

From the start menu, select Programs, Administrative Tools, Data Sources(ODBC). In the ODBC Data Source Administrator dialog box, select the data source you want to delete from the Data Sources list. Click Remove, and then click Yes to confirm the deletion. Related Topics. Figure 21-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.

Oracle Odbc Driver Configuration Dialog Box

TNS Service Name - The location of the Oracle database from which the ODBC driver will retrieve data. This is the same name entered in using the Oracle Net Configuration Assistant (NETCA). For more information, see the NETCA documentation and 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 is an example of the Application Options tab found on the Oracle ODBC Driver Configuration dialog box. Figure 21-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 SQLATTRQUERYTIMEOUT 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. Note: There is an impact on performance each time a cursor is closed. 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). The main configuration setup options are described in the topic. Oracle Options is an example of the Oracle Options tab found on the Oracle ODBC Driver Configuration dialog box. Figure 21-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. Note: When LONG and LOB data types are present, the number of rows prefetched by the ODBC Driver is not determined by the Fetch Buffer Size. The inclusion of the LONG and LOB data types minimizes the performance improvement and could result in excessive memory use. The ODBC Driver disregards Fetch Buffer Size and prefetches a set number of rows only in the presence of the LONG and LOB data types. 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, OCIATTRSTMTCACHESIZE. 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 12 c 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.

Figure 21-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 SQLTIMESTAMP parameters as the Oracle DATE type instead of as the Oracle TIMESTAMP type (the default). For more information, see. Force SQLWCHAR Support - Check this box to enable SQLDescribeCol, SQLColumns, and SQLProcedureColumns to unconditionally return the data type of SQLWCHAR for SQLCHAR columns; SQLWVARCHAR for SQLVARCHAR columns; and SQLWLONGVARCHAR for SQLLONGVARCHAR 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 SQLTRUE - Check this box to change the default value of the SQLATTRMETADATAID connection and statement attribute at connection time to SQLTRUE.

Under normal circumstances, SQLATTRMETADATAID would default to SQLFALSE. 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. The topic provides additional information about the SQLATTRMETADATAID attribute. 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 SQLVARCHAR data type is returned for all parameters.

If the Force SQLWCHAR Support function is also enabled, the SQLWVARCHAR 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 OCIDescribeAnywhen an application makes heavy calls to small packaged procedures that return REF CURSORS.

The main configuration setup options are described in the topic. SQL Server Migration Options is an example of the SQL Server Migration Options tab found on the Oracle ODBC Driver Configuration dialog box. Figure 21-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. The main configuration setup options are described in the topic. 21.2.3.1 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 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.

21.2.4.1 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:. In the TNS Service Name box, enter the name of the TNS service.

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

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. For additional information, click any of these links. Related Topic for All Users Related Topics for Programmers.

Note: This information is only for Microsoft Windows users. For the Oracle ODBC Driver to function successfully, OracleHome /bin must be in the system path.

To verify this, type PATH from a command prompt. The first time you use Oracle ODBC Driver and attempt to connect to a database or table, you might see these messages:. 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/BINtnsping 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 DSN 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, a Windows machine contains 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 the Microsoft DLL mfc42.dll is causing this error. Installing MDAC 2.5 SP1 does solve this problem. You must 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. Either copy the DLL from another machine that works correctly or contact Microsoft to find out how to obtain the DLL.

21.2.5.2 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 are connected to the database. However, if you try to connect to the database with a SQLDriverConnect call with a SQLDRIVERNOPROMPT 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. 21.3.4 Implementation of Data Types (Advanced) Topics:.

DATE and TIMESTAMP The semantics of Oracle DATE and TIMESTAMP data types do not correspond exactly with the ODBC data types with the same names. The Oracle DATE data type contains both date and time information while the SQLDATE data type contains only date information. The Oracle TIMESTAMP data type also contains date and time information, but it has greater precision in fractional seconds. The ODBC Driver reports the data types of both Oracle DATE and TIMESTAMP columns as SQLTIMESTAMP to prevent information loss. Similarly the ODBC Driver binds SQLTIMESTAMP parameters as Oracle TIMESTAMP values. Floating Point Data Types When connected to a 10.1 or later Oracle server, the ODBC Driver maps the Oracle floating point data types BINARYFLOAT and BINARYDOUBLE to the ODBC data types SQLREAL and SQLDOUBLE, respectively.

In previous releases, SQLREAL and SQLDOUBLE mapped to the generic Oracle numeric data type. Related Topic. Table 21-3 Limitations Imposed on Data Types by the Oracle ODBC Driver and Oracle Database Limited Data Type Description Literals Oracle database limits literals in SQL statements to 4,000 bytes. SQLLONGVARCHAR and SQLWLONGVARCHAR Oracle's limit for SQLLONGVARCHAR data where the column type is LONG is 2,147,483,647 bytes. Oracle's limit for the SQLLONGVARCHAR data where the column type is CLOB is 4 gigabytes.

The limiting factor is the client workstation memory. SQLLONGVARCHAR and SQLLONGVARBINARY Oracle database allows only a single long data column per table. The long data types are SQLLONGVARCHAR ( LONG) and SQLLONGVARBINARY ( LONG RAW).

Oracle recommends you use CLOB and BLOB columns instead. There is no restriction on the number of CLOB and BLOB columns in a table.

21.3.6 Error Messages When an error occurs, the Oracle ODBC Driver returns the native error number, the SQLSTATE (an ODBC error code), and an error message. The driver derives this information both from errors detected by the driver and errors returned by the Oracle server. Native Error For errors that occur in the data source, the Oracle ODBC Driver returns the native error returned to it by the Oracle server.

When the Oracle ODBC Driver or the Driver Manager detects an error, the Oracle ODBC Driver returns a native error of zero. SQLSTATE For errors that occur in the data source, the Oracle ODBC Driver maps the returned native error to the appropriate SQLSTATE. When the Oracle ODBC Driver detects an error, it generates the appropriate SQLSTATE.

When the Driver Manager detects an error, it generates the appropriate SQLSTATE. Error Message For errors that occur in the data source, the Oracle ODBC Driver returns an error message based on the message returned by the Oracle server. For errors that occur in the Oracle ODBC Driver or the Driver Manager, the Oracle ODBC Driver returns an error message based on the text associated with the SQLSTATE.

Error messages have the following format: vendor ODBC-component data-source error-message The prefixes in brackets ( ) identify the source of the error. Shows the values of these prefixes returned by the Oracle ODBC Driver. When the error occurs in the data source, the vendor and ODBC-component prefixes identify the vendor and name of the ODBC component that received the error from the data source. Table 21-4 Error Message Values of Prefixes Returned by the Oracle ODBC Driver Error Source Prefix Value Driver Manager vendorODBC-componentdata-source Microsoft/unixODBCODBC Driver ManagerN/A Oracle ODBC Driver vendorODBC-componentdata-source ORACLEODBC DriverN/A Oracle server vendorODBC-componentdata-source ORACLEODBC DriverN/A For example, if the error message does not contain the Ora prefix shown in the following format, the error is an Oracle ODBC Driver error and should be self-explanatory. OracleODBCError message text here If the error message contains the Ora prefix shown in the following format, it is not an Oracle ODBC Driver error. Note: Although the error message contains the Ora prefix, the actual error may be coming from one of several sources.

OracleODBCOraError message text here If the error message text starts with the following prefix, you can obtain more information about the error in the Oracle server documentation. ORA- Oracle Net Services errors and Trace logging are located under the ORACLEHOME NETWORK directory on Windows systems or the ORACLEHOME /NETWORK directory on UNIX systems where the OCI software is installed and specifically in the log and trace directories respectively. Database logging is located under the ORACLEHOME RDBMS directory on Windows systems or the ORACLEHOME /rdbms directory on UNIX systems where the Oracle server software is installed. See the Oracle server documentation for more information about server error messages. 21.4.3 Reducing Lock Timeout in a Program The Oracle server waits indefinitely for lock conflicts between transactions to be resolved.

You can limit the amount of time that the Oracle server waits for locks to be resolved by calling the ODBC SQLSetConnectAttr function before connecting to the data source. Specify a nonzero value for the SQLATTRQUERYTIMEOUT attribute in the ODBC SQLSetStmtAttr function. If you specify a lock timeout value using the ODBC SQLSetConnectAttr function, it overrides any value specified in the oraodbc.ini file. Refer to for more information on specifying a value in the oraodbc.ini file. 21.4.8 Enabling EXEC Syntax If the syntax of your SQL Server EXEC statement can be readily translated to an equivalent Oracle procedure call without change, the Oracle ODBC Driver can translate it if you enable this option. The complete name of a SQL Server procedure consists of up to four identifiers:.

server name. database name. owner name. procedure name The format for the name is: server.database.ownername.procedurename During the migration of the SQL Server database to Oracle, the definition of each SQL Server procedure (or function) is converted to its equivalent Oracle syntax and is defined in a schema in Oracle. Migrated procedures are often reorganized (and created in schemas) in one of these ways:. All procedures are migrated to one schema (the default option).

All procedures defined in one SQL Server database are migrated to the schema named with that database name. All procedures owned by one user are migrated to the schema named with that user's name. To support these three ways of organizing migrated procedures, you can specify one of these schema name options for translating procedure names.

Object names in the translated Oracle procedure call are not case-sensitive. Table 21-7 How Oracle ODBC Driver Implements Specific Functions Function Description SQLConnect SQLConnect requires only a DBQ, user ID, and password. SQLDriverConnect SQLDriverConnect uses the DSN, DBQ, UID, and PWD keywords. SQLMoreResults Implements ODBC support for implicit results. This is a new API implemented for Oracle Database 12 c Release 1 (12.1.0.1). See for more information.

SQLSpecialColumns If SQLSpecialColumns is called with the SQLBESTROWID attribute, it returns the rowid column. SQLProcedures and SQLProcedureColumns See the information that follows. All catalog functions If the SQLATTRMETADATAID statement attribute is SQLTRUE, a string argument is treated as an identifier argument, and its case is not significant. In this case, the underscore (') and the percent sign ('%') are treated as the actual character, not as a search pattern character. On the other hand, if this attribute is SQLFALSE, it is either an ordinary argument or a pattern value argument and is treated literally, and its case is significant. 21.4.12.1 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. 21.4.12.2 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 need not explicitly call entry points with the suffix. An ODBC application that is compiled with the UNICODE and UNICODE preprocessor definitions generates the appropriate calls. For example, a call to SQLPrepare is compiled as SQLPrepareW. The C data type, SQLCWCHAR, 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 SQLCTCHAR is useful for applications that must be built as both Unicode and ANSI.

The SQLCTCHAR macro compiles as SQLCWCHAR for Unicode applications and as SQLCCHAR for ANSI applications. The SQL data types, SQLWCHAR, SQLWVARCHAR, and SQLWLONGVARCHAR, 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. Also, 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. Lists the supported SQL data types and the equivalent ODBC SQL data type. Table 21-8 Supported SQL Data Types and the Equivalent ODBC SQL Data Type SQL Data Types ODBC SQL Data Types CHAR SQLCHAR or SQLWCHAR VARCHAR2 SQLVARCHAR or SQLWVARCHAR NCHAR SQLWCHAR NVARCHAR2 SQLWVARCHAR NCLOB SQLWLONGVARCHAR Footnote 1 CHAR maps to SQLWCHAR 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 SQLWVARCHAR if the character semantics were specified in the column definition and if the character set for the database is Unicode. 21.4.12.3 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 are 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 SQLCWCHAR. Convert data from Unicode to the local code page for input parameters bound as SQLCWCHAR. 21.4.12.4 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 must determine if it must fetch the column as Unicode or as the local code page. The driver could 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 SQLCCHAR, there would be an additional conversion to revert the data back to its original encoding. The default encoding of the Oracle client is used when fetching data.

However, an ODBC application can overwrite this default and fetch the data as Unicode by binding the column or the parameter as the WCHAR data type. 21.4.13.1 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 must 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 if you know the application will not retrieve them; 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 SQLATTRACCESSMODE attribute of the ODBC SQLSetConnectAttr function to SQLMODEREADONLY. If you are not using ODBC escape clauses, set the SQLATTRNOSCAN 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= for optimum performance.

Setting UseOCIDescribeAny=T for applications making heavy calls to small packaged procedures that return Ref Cursor improves performance. 21.4.13.2 Data Source Configuration Options This topic discusses performance implications of the following ODBC data source configuration options: Topics:. 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 SQLTIMESTAMP parameters as the appropriate Oracle data type. If this option is TRUE, SQLTIMESTAMP binds as the Oracle DATE data type. If this option is FALSE, SQLTIMESTAMP binds as the Oracle TIMESTAMP data type (which is the default). Enable Closing Cursors The SQLCLOSE 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 SQLCLOSE 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 tab of the 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. 21.4.13.3 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 TOTIMESTAMP(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 SQLTIMESTAMP 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.