Overview

The Microsoft ODBC driver for Oracle conforms to the Open Database Connectivity (ODBC) specification described in the ODBC Programmer's Reference (Version 2.0) for your platform. The Oracle driver allows you to connect your ODBC-compliant application to an Oracle database.

This help file describes how to set up, configure, and use the Oracle driver, and includes the following sections:

•	System Requirements

•	Adding and Modifying Data Sources

•	Connecting to a Data Source

•	ODBC Conformance Levels

•	Mapping Data Types


System Requirements

To use the Oracle driver, you must have the following Oracle software installed on your system:

•	Oracle Client Software, version 7.2 or higher.

The Microsoft Oracle ODBC Driver supports only SQL*Net 2.3 or later. For more information on Oracle products, refer to your Oracle documentation set.


Adding and Modifying Data Sources

A data source identifies a path to data that may include a network library, server, database, and other attributes—in this case, the data source is the path to an Oracle database. In order to connect to a data source, the Driver Manager looks at your ODBC.ini file for specific connection information.

The ODBC.ini file created by ODBC Administrator in the Windows directory is an initialization file used by the ODBC Driver Manager and ODBC drivers. This file contains information about each data source and its associated driver. Before you can connect to a data source, its connection information must be added to this file. For more information, see:

•	Adding and Modifying Data Sources for Windows


Adding and Modifying Data Sources for Windows

If you're developing on Windows, you add and configure data sources using the ODBC Administrator. The ODBC Administrator then updates your ODBC.ini file, located in the WINDOWS directory, to reflect your data source connection information. As you add data sources, the ODBC Administrator adds the information to this file for you; you should never modify the ODBC.ini file directly.

Adding a Data Source for Windows

1	To start the ODBC Administrator, double-click the ODBC icon in the Windows Control Panel. The Data Sources dialog box appears.

2	When you see the Data Sources dialog box, click the Add button. The Add Data Source dialog box appears.

3	Select the Oracle driver, and then click OK. The ODBC Oracle Setup dialog box appears.

4	In the Data Source Name box, enter the name of the data source you want to access. It can be any name that you choose.

5	In the Description box, enter the description for the driver. This is an optional field that describes the database driver that the data source connects to. It can be any name that you choose.

6	In the User Name box, enter your database user name. The user name is your database user id.

7	In the SQL*Net Connect String box, enter the SQL*Net connect string for the Oracle Server engine. The SQL*Net connect string identifies the Oracle Server engine that you want to access.

8	Click OK to add this data source. The Data Sources dialog box appears, and the ODBC Administrator updates your ODBC.ini file. The User Name and SQL*Net connect string that you enter become the default data source connection values for this data source. That is, when you connect to the data source using either a dialog box or connection string, these values become the default entries for the data source connection.

9	Click Add to add another data source or click Close to exit.

Modifying a data source for Windows

1	Invoke the ODBC Administrator. The Data Sources dialog box appears.

2	In the Data Sources dialog box, select the Oracle data source you want to modify and then click Setup. The ODBC Oracle Setup dialog box appears.

3	Modify the applicable data source fields, and then click OK.

When you have finished modifying the information in this dialog box, the ODBC Administrator updates your ODBC.ini file.


Connecting to a Data Source

An ODBC application can pass connection information in a number of ways. For example, the application might have the driver always prompt the user for connection information. Or the application might expect a connection string that specifies the data source connection. How you connect to a data source depends on the connection method that your ODBC applications uses.

One common way of connecting to a data source is through the Data Source dialog box. If your ODBC application is set up to use a dialog box, that dialog box is displayed and prompts you for the appropriate data source connection information.

Another way is through use of the connection string.

Connecting to a data source using a dialog box

1	When you see the Data Source dialog box, select an Oracle data source and then click OK. The Connect dialog boxes appears.

2	Fill in the appropriate information for the Connect dialog box, and then click OK.

Once the connection information is verified, your application can access the information that the data source contains using the Oracle driver.

Connection String Attributes

Some applications may require a connection string that specifies data source connection information instead of using a dialog box to obtain this information. The connection string is made up of a number of attributes that specify how a driver connects to a data source. An attribute identifies a specific piece of information that the driver needs to know before it can make the appropriate data source connection. Each driver may have a different set of attributes but the connection string format is always the same. A connection string has the following format:

“DSN=data-source-name[;SERVER=value] [;PWD=value] [;UID=value] [;<Attribute>=<value>]”

You must specify the data-source-name. However, all other attributes are optional. If you do not specify an attribute, that attribute defaults to the one that is specified in the data source specifications section (for the data source specified in the connection string) of your ODBC.ini file. The attribute value might be case-sensitive.

The attributes for the connection string are as follows:

Attribute	Description	Default value
DSN	The data source name. This name is listed in the ODBC Data Sources section and has its own Data Source Specifications section in the .ODBC.ini file.	“”
PWD	The password for the Oracle server that you want to access.	“”
SERVER	The SQL*Net connect string for the Oracle Server that you want to access.	“”
UID	The Oracle Server user name. Depending on your system, this attribute may not be optional—that is, certain databases and tables may require this attribute for security purposes.	“”
BUFFERSIZE		40000
ROWLIMIT		400
SYSTEMTABLE		.F.
SQLCOLSYN		.F.
SQLIGNCOM		.F.
For example, a connection string that connects to the Employees data source using the employees server, mickey host machine, and TCP/IP network would have the following attributes in the connection string:

"DSN=Employees;SERVER=t:mickey:employees;UID=cindy;PWD=secret"


ODBC Conformance Levels

ODBC defines two types of conformance standards for drivers—the API conformance standard and the SQL grammar conformance standard. API conformance refers to the functions that a driver supports. SQL conformance refers to the SQL grammar that the driver supports. Each conformance standard is made up of levels.

API Conformance Level

The Oracle driver supports the Core and Level 1 API. The driver also supports the following Level 2 functions:

•	SQLBrowseConnect( )

•	SQLDataSources( )

•	SQLDrivers( )

•	SQLForeignKeys( )

•	SQLNativeSql( )

•	SQLNumParams( )

•	SQLPrimaryKeys( )

•	SQLSetScrollOptions( )

•	SQLSetPos( )

•	SQLProcedureColumns( )

•	SQLExtendedFetch( )

•	SQLProcedures( )

Supported Options

The driver supports the following options for the SQLGetConnectOption( ) and SQLSetConnectOption( ) Level 1 functions:

•	SQL_ACCESS_MODE (SQLGetConnectOption( ) only)

•	SQL_AUTOCOMMIT

•	SQL_ODBC_CURSORS

•	SQL_OPT_TRACE

•	SQL_OPT_TRACEFILE

•	SQL_TRANSLATE_DLL

•	SQL_TRANSLATE_OPTION

•	SQL_TXN_ISOLATION

The driver supports the following options for the SQLGetStmtOption( ) and SQLSetStmtOption( ) Level 1 functions:

•	SQL_BIND_TYPE

•	SQL_CONCURRENCY

•	SQL_CURSOR_TYPE

•	SQL_ROWSET_TYPE

•	SQL_SIMULATE_CURSOR

SQL Conformance Level

The Oracle driver supports the Minimum SQL grammar and Core SQL grammar and also supports the following ODBC extensions to SQL:

•	Date, time, and timestamp data

•	Outer joins

•	The following numeric functions:

abs	floor	pi	sin
ceiling	log	power	sqrt
cos	log10	round	tan
exp	mod	sign	truncate
•	The following date functions:

curdate	dayofweek	monthname	second
curtime	dayofyear	minute	week
dayname	hour	now	year
dayofmonth	month	quarter
•	The following string functions:

ascii	left	right	ucase
char	length	rtrim
concat	ltrim	soundex
lcase	replace	substring


Mapping Data Types

The Oracle Server supports a set of data types. The Oracle driver maps these data types to their appropriate ODBC SQL data types. The following table lists the Oracle Server data type and its corresponding ODBC SQL data type.

Oracle Server Data Type	ODBC SQL Data Type
CHAR	SQL_CHAR
DATE	SQL_TIMESTAMP
FLOAT	SQL_DOUBLE
LONG	SQL_LONGVARCHAR
LONG RAW	SQL_LONGVARBINARY
NUMBER	SQL_DECIMAL
NUMBER(P)	SQL_DECIMAL
NUMBER(P,S)	SQL_DECIMAL
RAW	SQL_VARBINARY
VARCHAR2	SQL_VARCHAR
INTEGER	SQL_DECIMAL
Note   ODBC SQL data types do not support the MLSLABEL Oracle data type.


Error Message Title

Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah,