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.
Oracle Database Express Edition
Oracle Data Provider for.NET - Version 10.2.0.1.0 and later Microsoft Windows (32-bit) Microsoft Windows x64 (64-bit) Purpose. To help guide customers on how to install, configure, and test both the 32-bit and 64-bit Oracle Data Provider for.Net (ODP), Oracle Provider for OLE DB (OLEDB), and Oracle ODBC Driver (ODBC) Oracle Software on 64-bit. (#0) MicrosoftDriver Manager ODBC Function SQLSetConnectAttr driver failed. (#0)-i tried to connect with Access external Data. If i try to connect with Oracle instant client driver 32bit then it works fine! Is there anything i can do to make Microsoft ODBC for Oracle driver work in Windows10? Any help would be very appreciated. 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). May 31, 2017 64-bit ODAC for Windows consists of Oracle client side drivers, including ODP.NET, ODBC, OLE DB, and Oracle Services for MTS.
Software Delivery Cloud
Oracle also provides all supported database release software for all platforms on the Oracle Software Delivery Cloud: https://edelivery.oracle.com
Previous Database Release Software
Oracle Database 10.2 and 11.1 are no longer available for download. The software is available as a media or FTP request for those customers who own a valid Oracle Database product license for any edition. To request access to these releases, follow the instructions in Oracle Support Document 1071023.1 (Requesting Physical Shipment or Download URL for Software Media) from My Oracle Support. NOTE: for Oracle Database 10.2, you should request 10.2.0.1 even if you want to install a later patch set. Once you install 10.2.0.1 you can then apply any 10.2 patch set. Similarly, for 11.1 request 11.1.0.6 which must be applied before installing 11.1.0.7. Patch sets can be downloaded from the Patches and Updates tab on My Oracle Support.
The script content on this page is for navigation purposes only and does not alter the content in any way.
This chapter contains the following sections:
About Oracle ODBC Driver
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:
Figure 24-1 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. Figure 24-1 shows an ODBC application accessing three separate databases.
Figure 24-1 Components of the ODBC Model
Description of 'Figure 24-1 Components of the ODBC Model' For All UsersOracle ODBC DriverWhat Is the Oracle ODBC Driver
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 24-2 shows the Oracle ODBC Driver architecture as described in the preceding paragraphs.
Figure 24-2 Oracle ODBC Driver Architecture
Description of 'Figure 24-2 Oracle ODBC Driver Architecture'
* The Oracle ODBC Resource data definition language (DLL) file (
sqres xx .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.
New and Changed Features
New Features for Oracle ODBC Driver Release 12.2.0.2.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:
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:
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:
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:
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:
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:
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:
See Also:
Features Not Supported
Features Not Supported by the Oracle ODBC 3.0 Driver.
The Oracle ODBC Driver does not support the following ODBC 3.0 features:
The Oracle ODBC Driver does not support the following SQL string functions:
The Oracle ODBC Driver does not support the following SQL numeric functions
The Oracle ODBC Driver does not support the following SQL time, date, and interval functions:
Oracle Drivers Windows 10 Update
Files Created by the Installation
The following table describes the files that are installed by the Oracle ODBC Driver kit.
Table 24-1 Files Installed by the Oracle ODBC Driver Kit
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.
See the unixODBC readme and INSTALL files for the list of files that are installed with unixODBC Driver Manager.
See Also:
Driver Conformance Levels
ODBC defines Conformance Levels for drivers in two areas:
The Oracle ODBC Driver supports all core API functionality and a limited set of Level 1 and Level 2 functionality.
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.
See Also:
API Conformance for more information about core API functionality support
Known Limitations
The following are not supported by Oracle ODBC driver:
Configuration TasksConfiguring Oracle Net Services
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.
Configuring the Data Source
Note:
The following configuration steps are for Windows users. Unix users must use the
odbc_update_ini.sh file to create a DSN.
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.
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:
( don't know whether intel has seen my blog and released the drivers ) well i still believe that intel can add little more openGL features to the GMA 950. On your GMA 950 u can see the above blog and also the following group. Intel gma 950 windows 7. I am looking forgooddrivers in future for both windows 7 and vista.And for those who are interested in playing games like crysis, witcher etc. I am not sure why people are saying thatGMA is end of maintance since it can support windows 7.
You can change or delete a data source at any time. The following subtopics explain how to add, change, or delete a data source.
Oracle ODBC Driver Configuration Dialog Box
Note:
The Oracle ODBC Driver Configuration dialog box is available only for Microsoft Windows users.
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.
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:
The following screenshot shows an example of the Application Options tab found on the Oracle ODBC Driver Configuration dialog box.
Sharp tv sky remote control codes. 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:
Note:
There is an impact on performance each time a cursor is closed.
See Also:
Oracle ODBC Driver Configuration Dialog Box for the main configuration setup 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:
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.
The Failover area of the Oracle Options tab contains the following fields:
Note:
See the Oracle Fail Safe and Oracle Parallel Server documentation on how to set up and use both of these products.
See Also:
Oracle ODBC Driver Configuration Dialog Box for the main configuration setup 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:
See Also:
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:
See Also:
Oracle ODBC Driver Configuration Dialog Box for the main configuration setup options
Modifying the oraodbc.ini FileReducing 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:
Connecting to a Data SourceConnecting 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:
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:
TroubleshootingAbout Using the Oracle ODBC Driver for the First Time
Describes useful information about using the Oracle ODBC Driver for the first time.
See the Oracle ODBC Driver developer home ODBC Developer Center where you can find additional information about Oracle ODBC Driver features, resources, such as the Oracle Instant Client ODBC Installation Guide, the Oracle Instant Client ODBC download site, the Oracle ODBC discussion forum, the Oracle ODBC Driver Development Guide and information about some related technologies.
Expired Password
This section contains information about expired passwords.
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.
For Advanced UsersCreating Oracle ODBC Driver TNS Service Names
To create Oracle ODBC Driver TNS Service Names with Oracle Net Services, use the Oracle Net Configuration Assistant (NETCA), which is installed when you install Oracle Net Services. NETCA creates Oracle ODBC Driver TNS Service Name entries in the tnsnames.ora file.
SQL Statements
The Oracle ODBC Driver is broadly compatible with the SQL-99 Core specification which is a superset of the SQL-92 Entry Level specification. In addition to Oracle's grammar, the vendor-specific escape sequences outlined in Appendix C of the ODBC specifications are also supported. In accordance with the design of ODBC, the Oracle ODBC Driver passes native SQL syntax to the Oracle database.
See Also:
Data Types
The Oracle ODBC Driver maps Oracle database data types to ODBC SQL data types.
Note:
All conversions in Appendix D of the Microsoft ODBC 3.52 Software Development Kit and Programmer's Reference are supported for the ODBC SQL data types listed from a call to
SQLGetInfo with the appropriate information type.
See Also:
Implementation of Data Types (Advanced)
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 SQL_DATE 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 SQL_TIMESTAMP to prevent information loss. Similarly the ODBC Driver binds SQL_TIMESTAMP parameters as Oracle TIMESTAMP values.
When connected to a 10.1 or later Oracle server, the ODBC Driver maps the Oracle floating point data types
BINARY_FLOAT and BINARY_DOUBLE to the ODBC data types SQL_REAL and SQL_DOUBLE , respectively. In previous releases, SQL_REAL and SQL_DOUBLE mapped to the generic Oracle numeric data type.
Limitations on Data Types
The Oracle ODBC Driver and the Oracle database impose limitations on data types. Table 24-2 describes these limitations.
Table 24-2 Oracle ODBC Driver and Oracle Database Limitations on Data Types
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.
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.
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 .
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:
![]()
The prefixes in brackets ( [ ] ) identify the source of the error. Table 24-3 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 24-3 Error Message Values of Prefixes Returned by the Oracle ODBC Driver
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.
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.
If the error message text starts with the following prefix, you can obtain more information about the error in the Oracle server documentation.
Oracle Net Services errors and Trace logging are located under the
ORACLE_HOME NETWORK directory on Windows systems or the ORACLE_HOME /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 ORACLE_HOME RDBMS directory on Windows systems or the ORACLE_HOME /rdbms directory on UNIX systems where the Oracle server software is installed.
See the Oracle server documentation for more information about server error messages.
For ProgrammersFormat of the Connection String
The following table describes keywords that can be included in the connection string argument of the
SQLDriverConnect function call. Missing keywords are read from the Administrator entry for the data source. Values specified in the connection string override those contained in the Administrator entry. See the Microsoft ODBC 3.52 Software Development Kit and Programmer's Reference for more information about the SQLDriverConnect function.
Oracle Odbc Driver Windows 10 32 Bit
Table 24-4 Keywords that Can Be Included in the Connection String Argument of the SQLDriverConnect Function Call
If the following keyword is specified in the connection string, the Oracle ODBC Driver does not read values defined from the Administrator:
Examples of valid connection strings are:
See Also:
SQLDriverConnect Implementation
Table 24-5 describes the keywords required by the SQLDriverConnect connection string.
Table 24-5 Keywords Required by the SQLDriverConnect Connection String
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 SQL_ATTR_QUERY_TIMEOUT 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.
See Also:
Reducing Lock Timeout for more information on specifying a value in the
oraodbc.ini file
Linking with odbc32.lib (Windows) or libodbc.so (UNIX)
For Windows platforms, when you link your program, you must link it with the import library
odbc32.lib .
For UNIX platforms, an ODBC application must be linked to
libodbc.so .
Information About rowids
The ODBC
SQLSpecialColumns function returns information about the columns in a table. When used with the Oracle ODBC Driver, it returns information about the Oracle rowids associated with an Oracle table.
Rowids in a WHERE Clause
Rowids can be used in the
WHERE clause of an SQL statement. However, the rowid value must be presented in a parameter marker.
Enabling Result Sets
Oracle reference cursors (Result Sets) allow an application to retrieve data using stored procedures and stored functions. The following information identifies how to use reference cursors to enable Result Sets through ODBC.
The following example application shows how to return a Result Set using the Oracle ODBC Driver:
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:
The format for the name is:
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:
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.
Enabling Event Notification for Connection Failures in an Oracle RAC Environment
If the
SQL_ORCLATTR_FAILOVER_CALLBACK and SQL_ORCLATTR_FAILOVER_HANDLE attributes of the SQLSetConnectAttr function are set when a connection failure occurs in an Oracle Real Application Clusters (Oracle RAC) Database environment, event notification is enabled. Both attributes are set using the SQLSetConnectAttr function. The symbols for the new attributes are defined in the file sqora.h.
The
SQL_ORCLATTR_FAILOVER_CALLBACK attribute specifies the address of a routine to call when a failure event takes place.
The
SQL_ORCLATTR_FAILOVER_HANDLE attribute specifies a context handle that is passed as a parameter in the callback routine. This attribute is necessary for the ODBC application to determine which connection the failure event is taking place on.
The function prototype for the callback routine is:
The 'handle' parameter is the value that was set by the
SQL_ORCLATTR_FAILOVER_HANDLE attribute. Null is returned if the attribute has not been set.
The
fo_code parameter identifies the failure event which is taking place. The failure events map directly to the events defined in the OCI programming interface. The list of possible events is:
The following is a sample program which demonstrates using this feature:
Using Implicit Results Feature Through ODBC
Use this option when you migrate any third party ODBC application to Oracle Database and you want to use implicit results functionality as supported by the previous vendor. Oracle ODBC driver supports implicit results with stored procedures or an anonymous PL/SQL block. For the current release, implicit results are returned only for
SELECT statements.
The following code example shows an example ODBC test case using an anonymous SQL script for implicit results.
About Supporting Oracle TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE Column Type in ODBCOracle Drivers Windows 10 For Free
The time zone is dictated by the system variable
ORA_SDTZ . The system variable can be set to 'OS_TZ' , 'DB_TZ' , or a valid time zone value. When ORA_SDTZ is set to 'OS_TZ' , the operating system time zone is used. If it is set to 'DB_TZ' , the default time zone set in the database is used.
By default when
ORA_SDTZ is not set, the operating system time zone is used.
Note:
When setting the
ORA_SDTZ variable in a Microsoft Windows environment -- in the Registry, among system environment variables, or in a command prompt window -- do not enclose the time zone value in quotes.
See Also:
Oracle Database Globalization Support Guide for information about Datetime data types and time zone support
Fetching Data from These Time Zone Columns Using the Variable of ODBC Data Type TIMESTAMP_STRUCT
The following example demonstrates how to fetch data from
TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE column using the variable of ODBC datatype TIMESTAMP_STRUCT .
Example 24-1 How to Fetch Data from TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE Columns Using the Variable of ODBC Data Type TIMESTAMP_STRUCT
Example 24-2 How to Insert Data into TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE Columns
About the Effect of Setting ORA_SDTZ in Oracle Clients (OCI, SQL*Plus, Oracle ODBC driver, and Others)
Describes the effect of setting the system variable
ORA_SDTZ in Oracle Clients.
The time zone is dictated by the system variable
ORA_SDTZ .
The following sections describe the effects of not setting and setting the system variable
ORA_SDTZ in Oracle Clients (OCI, SQL*Plus, Oracle ODBC Driver, and others). The examples in these sections are run in India (GMT+5:30) time zone.
See Also:
Oracle Database Globalization Support Guide for more information about setting the session time zone
Environment Setup
To set up the environment, create the following table with
TSLTZ (TIMESTAMP WITH LOCAL TIME ZONE) column and insert the value of 01/01/2016 00:00 GMT into the TSLTZ column as follows:
Example 24-3 How to Set Up the Environment
The following example sets up the environment for the example sections that follow.
When ORA_SDTZ Is Not Set in the Environment
When
ORA_SDTZ is not set in the environment, then the operating system (OS) time zone setting is taken as the default time zone for Oracle Clients. For example:
Example 24-4 What Happens When ORA_SDTZ Is Not Set
Setting ORA_SDTZ to the Operating System (OS) Timezone in the Environment
When
ORA_SDTZ is set to the operating system (OS) Time zone, the Oracle Client’s user session is set to the OS time zone setting. You can either unset it in the environment or set ORA_SDTZ to OS_TZ . For example:
Example 24-5 What Happens When ORA_SDTZ Is Set to the Operating System (OS) Timezone
Setting ORA_SDTZ to a Specific Time Zone in the Environment
The Oracle Client can be set to retrieve the time stamp value adjusted to a specific time zone (for example, Helsinki Time Zone). To do this, you can set to the Oracle Time Zone region name for the corresponding time zone (Oracle Time Zone Region Name for Helsinki Time Zone is
Europe/Helsinki ). For example:
Example 24-6 What Happens When ORA_SDTZ Is Set to a Specific Time Zone
Supported FunctionalityAPI Conformance
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.
See Also:
Implementation of ODBC API Functions
The following table describes how the Oracle ODBC Driver implements specific functions:
Table 24-6 How Oracle ODBC Driver Implements Specific 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
SQL_NULL_DATA with SQLBindParameter , the comparison fails. 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 SupportUnicode 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 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,
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 must 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 . 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.
Table 24-7 lists the supported SQL data types and the equivalent ODBC SQL data type.
Table 24-7 Supported SQL Data Types and the Equivalent ODBC SQL Data Type
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:
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 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 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.
Unicode Samples
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 .
No difference other than specifying Unicode literals for
SQLConnect .
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) ).
Cctv software free download. 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.
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.
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.
Performance and TuningGeneral ODBC Programming Tips
This section describes some general programming tips to improve the performance of an ODBC application.
Data Source Configuration Options
This topic discusses performance implications of the following ODBC data source configuration options:
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.
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.
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).
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.
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.
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.
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 the Fetch Buffer Size and prefetch a set number of rows in the presence of the LONG and LOB data types.
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:
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:
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.
Note:
This option is intended for use only with Microsoft Access or other similar programs that bind
DATE columns as TIMESTAMP columns. Do not use this option when there are actual TIMESTAMP columns present or when data loss may occur. Microsoft Access executes such queries using whatever columns are selected as the primary key.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |