Copyright

DataDirect Connect ODBC for Oracle

© 1999 MERANT Solutions, Inc. All rights reserved.

INTERSOLV, DataDirect, SequeLink, and Middleware are registered trademarks of MERANT Solutions, Inc. DataDirect Connect ODBC, DataDirect Connect OLE DB, DataDirect Reflector, and WebDBLink, are trademarks of MERANT Solutions, Inc. Other company or product names mentioned herein may be trademarks or registered trademarks of their respective companies.


About Connect ODBC for Oracle

Connect ODBC for Oracle (the “Oracle driver”) supports the Oracle7 database system. It also supports the Oracle8 database system when using the Oracle8 client.

Connect ODBC for Oracle8 (the “Oracle8 driver”) supports the Oracle8 database system.

The Oracle driver file name is IVOR7nn.DLL, where nn is the revision level. The Oracle8 driver file name is IVOR8nn.DLL, where nn is the revision level.


System Requirements

Important: You must have all components of the Oracle client software installed; otherwise, the driver will not operate properly.

Oracle

The Oracle SQL*Net product is required to access remote Oracle databases.

The appropriate DLLs for the current version of SQL*Net and OCIW32.DLL must be on your path or in your Windows NT \SYSTEM32 or Windows 9x \SYSTEM directory. For example, SQL*Net 2.3 requires ORA73.DLL, CORE35.DLL, NLSRTL32.DLL, and CORE35O.DLL, as well as OCIW32.DLL.

Oracle8

The Oracle Net8 Client 8.0.4, or higher, is required to access remote Oracle8 databases, 8.0.3 or higher.

On Intel systems, the appropriate DLLs for the Oracle Net8 Client must be on your path, for example, ORA804.DLL, PLS804.DLL, and OCI.DLL.

Enabling MTS

The Oracle8 driver can take advantage of Microsoft Transaction Server (MTS) capabilities, specifically, the Distributed Transaction Coordinator (DTC). Refer to the help file of the "MicroSoft Transaction Server SDK" for details. You must be accessing 8.0.4 or higher servers using Net8 Client 8.0.5 or higher.


Configuring Data Sources

To configure an Oracle data source, do the following:

1	Start the ODBC Administrator to display a list of data sources.

2	If you are configuring an existing data source, select the data source name and click Configure to display the ODBC Oracle Driver Setup dialog box.

If you are configuring a new data source, click Add to display a list of installed drivers. Select Oracle7 tables and click Finish to display the ODBC Oracle Driver Setup dialog box.

3	Specify a data source name, a server name and optionally, a description. Click Apply.

4	Click Test Connect to attempt to connect to the data source using the connection properties specified in the Driver Setup dialog box.

5	Click the Advanced tab to configure optional data source settings, such as packet size. Click Apply.

6	Click Translate to display the Select Translator dialog box, which lists the translators specified in the ODBC Translators section of the system information. DataDirect provides a translator named "OEM to ANSI" that translates your data from the IBM PC character set to the ANSI character set.

Select a translator, then click OK to close this dialog box and perform the translation.

7	Click OK or Cancel. If you click OK, the values you have specified become the defaults when you connect to the data source. You can change these defaults by using this procedure to reconfigure your data source. You can override these defaults by connecting to the data source using a connection string with alternate values.


Test Connection

At any point during the configuration process, you can click Test Connect to attempt to connect to the data source using the connection properties specified in the Driver Setup dialog box. A Logon dialog box is displayed. After providing connection information, click OK.

•	If the driver can connect, it releases the connection and displays a "connection established" message. Click OK.

•	If the driver cannot connect because of an improper environment or incorrect connection value, it will display an appropriate error message.

Verify that the all required client software is properly installed. If it is not, you will see the message:

Specified driver could not be loaded due to system error [xxx].

Click OK.


Connecting to Data Source Using a Logon Dialog
Connecting to a Data Source Using a Logon Dialog

Some ODBC applications display a logon dialog box when you are connecting to a data source. In these cases, the data source name has already been specified.

In the Logon dialog box, do the following:

1	Type the client connection string of the computer containing the Oracle database tables you want to access, or select the string from the Server Name drop-down list box, which displays the names you specified in the setup dialog box.

2	If required, type your Oracle user name.

3	If required, type your Oracle password.

4	Click OK to log on to the Oracle database installed on the server you specified and to update the values in the system information.

Note: Oracle has a feature that allows you to connect to Oracle via the operating system user name and password.  To connect, use a slash ( / ) for the user name and leave the password blank.  To configure the Oracle server/client, refer to the Oracle server documentation.


Connecting to Data Source Using a Connection String
Connecting to a Data Source Using a Connection String

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section of the system information to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information. These values are not written to the system information.

You can specify either long or short names in the connection string. The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for Oracle is:

DSN=Accounting;SRVR=X:IVSRVR;UID=JOHN;PWD=XYZZY

If the server name contains a semicolon, enclose it in quotation marks:

DSN=Accounting;SRVR="X:IV;SRVR";UID=JOHN;PWD=XYZZY

The paragraphs that follow give the long and short names for each attribute, as well as a description. The defaults listed are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is the default.

ApplicationUsingThreads (AUT): ApplicationUsingThreads={0 | 1}. Ensures that the driver works with multi-threaded applications. The default is 1, which makes the driver thread-safe. When using the driver with single-threaded applications, you may set this option to 0 to avoid additional processing required for ODBC thread safety standards.

When you specify ApplicationUsingThreads=1, SQLGetInfo(SQL_ASYNC_MODE) returns SQL_AM_NONE, SQLSetConnectAttr(SQL_ATTR_ASYNC_ENABLE) returns “optional feature not implemented," and SQLSet/GetStmtAttr(SQL_ATTR_ASYNC_ENABLE) returns “optional feature not implemented." Asynchronous execution is not supported by the Oracle client in a multi-threaded environment.

ArraySize (AS): The number of bytes the driver uses for fetching multiple rows. Values can be 1 up to 4 GB. The initial default is 60000.  Larger values increase throughput by reducing the number of times the driver fetches data across the network. Smaller values increase response time, as there is less of a delay waiting for the server to transmit data.

CatalogOptions (CO): CatalogOptions={0 | 1}. Specifies whether the result column REMARKS for the catalog functions SQLTables and SQLColumns and COLUMN_DEF for the catalog function SQLColumns have meaning for Oracle. If you want to obtain the actual default value, set CO=1. The default is 0.

DataSourceName (DSN): A string that identifies an Oracle data source configuration in the system information. Examples include "Accounting" or "Oracle-Serv1."

EnableDescribeParam (EDP): EnableDescribeParam={0 | 1}. Enables the ODBC API function SQLDescribeParam, which results in all parameters being described with a data type of SQL_VARCHAR. This option should be set to 1 when using Microsoft Remote Data Objects (RDO) to access data. The default is 0.

LockTimeOut (LTO) ): LockTimeOut={0 | -1}. A value that specifies whether Oracle should wait for a lock to be freed before raising an error when processing a Select...For Update Of statement. Values can be -1 (wait forever, the initial default) or 0 (do not wait).

LogonID (UID): The logon ID (user name) that the application uses to connect to your Oracle database. A logon ID is required only if security is enabled on your database. If so, contact your system administrator to get your logon ID. To use your operating system user name, see Connecting to a Data Source Using a Logon Dialog .

PacketSize (PS) Oracle7 only: PacketSize={1024 | 2048 | 4096 | 8192}. A value that controls the packet size for TCP/IP connections. Any values other than 1024, 2048, 4096, or 8192 are ignored.

The PacketSize option is used only when the connection string specified in the Server Name option contains T for TCP/IP as the driver_prefix. See the ServerName option for more information.

Password (PWD): The password that the application uses to connect to your Oracle database. To use your operating system password, see Connecting to a Data Source Using a Logon Dialog .

ProcedureRetResults (PRR): ProcedureRetResults={0 | 1}. Values are Off (0) and On (1). The default is 0. When the option is on, the driver will return result sets from stored procedures/functions. If this option is on and you execute a stored procedure that does not return result sets, you will incur a small performance penalty. See Stored Procedure Results .

ServerName (SRVR): The client connection string designating the server and database to be accessed. The information required varies depending on the SQL*Net driver you are using. For remote servers, the SQL*Net connection string has the following form:

driver_prefix:computer_name[:sid]

driver_prefix is a letter identifying the network protocol being used. The driver prefix can be as follows: P (named pipes), X (SPX), B (NetBIOS), T (TCP/IP), D (DECNet), A (Oracle Async), or AT (AppleTalk) or TNS (SQL* net 2.0). Check your Oracle documentation for other protocols.

computer_name is the name of the Oracle Listener on your network.

sid is the Oracle System Identifier and refers to the instance of Oracle running on the host. This item is required when connecting to systems that support more than one instance of an Oracle database.

For local servers, the SQL*Net connection string has the form:

database_name

database_name identifies your Oracle database.

If the SQL*Net connection string contains semicolons, enclose it in quotation marks. See your SQL*Net documentation for more information.

Oracle8

For Oracle8 remote servers, the Net8 Client connection string has the following form:

TNSNAME

TNSNAME is the alias name of the Oracle Listener on your network.

If the Net8 Client connection string contains semicolons, enclose it in quotation marks. See your Net8 Client documentation for more information.


Data Types

The Oracle data types are mapped to the standard ODBC data types as follows:

Oracle	ODBC
Char	SQL_CHAR
Date	SQL_TYPE_TIMESTAMP
Long	SQL_LONGVARCHAR
Long Raw	SQL_LONGVARBINARY
Number	SQL_DOUBLE
Number(p,s)	SQL_DECIMAL
Raw	SQL_VARBINARY
Varchar2	SQL_VARCHAR

Oracle8

When connecting to Oracle8 servers, the following additional Oracle8 data types are mapped to the standard ODBC data types:

Oracle8	ODBC
Bfile	SQL_LONGVARBINARY*
Blob	SQL_LONGVARBINARY
Clob	SQL_LONGVARCHAR
•	* Not supported for Standard Engine Databases

The Oracle8 driver does not support any Abstract Data Types. When the driver encounters an Abstract Data Type during data retrieval, it will return an Unknown Data Type error (SQL State HY000). It also does not support asynchronous operations, due to constraints in the current Oracle8 client.


Stored Procedure Results

When the option Procedure Returns Results is active, the driver returns result sets from stored procedures/functions. In addition, SQLGetInfo(SQL_MULT_RESULTS_SETS) will return “Y” and SQLGetInfo(SQL_BATCH_SUPPORT) will return SQL_BS_SELECT_PROC. If this option is on and you execute a stored procedure that does not return result sets, you will incur a small performance penalty.

This feature requires that stored procedures be in a certain format.  First, a package must be created to define all of the cursors used in the procedure, then the procedure can be created using the new cursor. For example:

Create or replace package GEN_PACKAGE as

  CURSOR G1 is select CHARCOL from GTABLE2;

  type GTABLE2CHARCOL is ref cursor return G1%rowtype;

end GEN_PACKAGE;

Create or replace procedure GEN_PROCEDURE1 (

  rset IN OUT GEN_PACKAGE.GTABLE2

  CHARCOL, icol INTEGER) as

begin

  open rset for select CHARCOL from GTABLE2

    where INTEGERCOL <= icol order by INTEGERCOL;

end;

When executing the stored procedures with resultsets, do not include the resultset arguments in the list of procedure arguments.  The previously described example would be executed as:

{call GEN_PROCEDURE1 (?)}

where ? is the parameter for the icol argument.

For more information consult your Oracle SQL manual.


Isolation and Lock Levels Supported

Oracle supports isolation level 1 (read committed) and isolation level 3 (serializable isolation—if the server version is Oracle 7.3 or higher or Oracle 8.x). Oracle supports record-level locking.


ODBC Conformance Level

The API functions supported are listed in “Supported ODBC Functions,” found in the General Help section of Connect ODBC Help. They support SQLSetPos as well as scrollable cursors with SQLFetchScroll and SQLExtendedFetch. The drivers also support SQLDescribeParam if EnableDescribeParam=1.

The Oracle driver supports the following functions:

SQLProcedures

SQLProcedureColumns

SQLPrimaryKeys

SQLForeignKeys

SQLTablePrivileges

SQLColumnPrivileges

SQLSetPos (SQL_ADD)

The Oracle driver supports the core SQL grammar.


Number of Connections and Statements Supported

The Oracle drivers support multiple connections and multiple statements per connection.


General Tab, ODBC Oracle Driver Setup

Use the ODBC Oracle  Driver Setup dialog box to create new Oracle data sources or configure existing data sources.

Data Source Name: A string that identifies this Oracle data source configuration in the system information. Examples include "Accounting" or "Oracle-Serv1."

Description: An optional long description of a data source name. For example, "My Accounting Database" or "Oracle on Server number 1."

Server Name: The client connection string designating the server and database to be accessed. The information required varies depending on the client driver you are using. See the ServerName option described in Connecting to Oracle Using a Connection String for the format of the client connection string.

Advanced Tab

Displays the Advanced tab, where you can configure optional data source settings, such as packet size.

OK

Cancel

Apply

Test Connect


Advanced Tab, ODBC Oracle Driver Setup

Use the Advanced tab on the ODBC Oracle Driver Setup dialog box to specify optional settings when you create new Oracle data sources or configure existing data sources.

Server List: The list of client connection strings that will appear in the logon dialog box. Separate the strings with commas. If the client connection string contains a comma, enclose it in quotation marks; for example, "Serv,1", "Serv,2", "Serv,3."

Default User Name: The default user name used to connect to your Oracle database. A default user name is required only if security is enabled on your database. Your ODBC application may override this value or you may override this value in the logon dialog box or connection string.

Lock Timeout: A value that specifies whether Oracle should wait for a lock to be freed before raising an error when processing a Select...For Update Of statement. Values can be -1 (wait forever) or 0 (don't wait).  The default is -1.

Array Size: The number of bytes the driver uses for fetching multiple rows. Values can be 1 up to a maximum 4 GB; the default is 60000. Larger values increase throughput by reducing the number of times the driver fetches data across the network. Smaller values increase response time, as there is less of a delay waiting for the server to transmit data.

Packet Size (Oracle7 only): A value that controls the packet size for TCP/IP connections. Enter one of the following packet sizes: 1024, 2048, 4096, or 8192. Any other values are ignored.

The Packet Size option is used only when the connection string specified in the Server Name option contains T for TCP/IP as the driver prefix. See the ServerName option described in Connecting to Oracle Using a Connection String for the format of the client connection string.

Catalog Options: Check this box if you want the result column REMARKS for the catalog functions SQLTables and SQLColumns, and COLUMN_DEF for the catalog function SQLColumns to have meaning for Oracle. The default is not checked, which returns SQL_NULL_DATA for the result column COLUMN_DEF and REMARKS columns. Checking this box reduces the performance of your queries.

Enable SQLDescribeParam: Check this box to enable the SQLDescribeParam function, which results in all parameters being described with a data type of SQL_VARCHAR. This option should be checked when using Microsoft Remote Data Objects (RDO) to access data.

Application Using Threads: A setting that ensures that the driver works with multi-threaded applications. You can clear this check box when using the driver with single-threaded applications. Turning off this setting avoids additional processing required for ODBC thread safety standards.

When Application Using Threads is enabled, SQLGetInfo(SQL_ASYNC_MODE) returns SQL_AM_NONE, SQLSetConnectAttr(SQL_ATTR_ASYNC_ENABLE) returns “optional feature not implemented," and SQLSet/GetStmtAttr(SQL_ATTR_ASYNC_ENABLE) returns “optional feature not implemented." Asynchronous execution is not supported by the Oracle client in a multi-threaded environment.

Procedure Returns Results: Check this box to enable the driver to return result sets from stored procedures/functions. If this option is on and you execute a stored procedure that does not return result sets, you will incur a small performance penalty. See Stored Procedure Results .

Translate Button

Displays the Select Translator dialog box, where you can translate your data from one character set to another. Choose the "OEM to ANSI" translator to translate your data from the IBM PC character set to the ANSI character set.

OK

Cancel

Apply

Test Connect


Logon to Oracle Dialog

Server Name: Type the client connection string of the computer containing the Oracle database tables you wish to access, or select the string from the Server Name drop-down list, which displays the names you specified in the ODBC Oracle Driver Setup dialog box.

User Name: If required, type your Oracle user name.

Password: If required, type your Oracle password.

Note: Oracle has a feature that allows you to connect to Oracle via the operating system user name and password.  To connect, use a slash ( / ) for the user name and leave the password blank.  To configure the Oracle server/client, refer to the Oracle server documentation.


Apply Button

Writes the settings you have specified to the system information. These settings remain in effect until you change them in this dialog box. Clicking Cancel does not affect settings that have been applied.


OK Button

Writes the settings you have specified to the system information and closes the dialog box.


Cancel Button

Closes the dialog box without saving settings that have not been applied.


Contacting Technical Support

MERANT provides technical support for all registered users of this product, including limited installation support for the first 30 days. If you need support after that time, contact us using one of the methods below or purchase further support by enrolling in the SupportNet program. For more information about SupportNet, contact your sales representative.

World Wide Web
	http://www.merant.com/datadirect/support The MERANT Web site contains up-to-date technical support information under SupportNet. SupportNet Online is our global service network that provides access to valuable tools and information in an online community for users. Our SupportNet Community shares information via the Web, automatic email notification, newsgroups, and regional user groups. SupportNet Online also includes a KnowledgeBase which allows you to search on keywords for technical bulletins and how-to information. You can also download fix releases for your DataDirect products.
Internet
	Australia and New Zealand	australia.answerline@merant.com
	EMEA	int.datadirect.answerline@merant.com
	Japan	jpn.answerline@merant.com.jp
	US and Canada	datadirect.answerline@merant.com
Telephone
Australia	1 800 335 664 or 9816 9977 for Melbourne Metro	8:30-5:30 p.m. Local Melbourne Time (LMT)
Belgium	0800 724 61	9:00-6:30 p.m. CET
France	0800 91 56 07	9:00-6:30 p.m. CET
Germany	0130 822 496 or  +44 1727 812898	9:00-6:30 p.m. CET
Japan	81-3-5401-9660	9:00-12:00, 1:00-5:00 p.m. JST
The Netherlands	0800 022 1609	9:00-6:30 p.m. CET
New Zealand	1 800 335 664	8:30-5:30 p.m. LMT
United Kingdom and Ireland	+44 1727 811881	8:00-5:30 p.m. GMT
USA and Canada	1 800 443 1601	8:30-8:00 p.m. EST
Fax US	1 919 461 4527
Fax Int.	+32-15-320919
Mail	1500 Perimeter Park Drive, Suite 100, Morrisville, NC 27560 USA

Information to Provide

When you contact us, include this information:

•	The product serial number located on the Product Registration Information card or on a product serial number card in the box. The number will be checked to verify your eligibility to receive support. If you do not have a current SupportNet contract, we will ask that you speak with a sales representative.

•	Your name and organization. On a first-time call, you may be asked for full customer information including location and contact details.

•	The version number of the DataDirect product you are using.

•	The type and version of the operating system you are using.

•	Any third party software and other environmental information necessary to understand the problem.

•	A brief description of the problem and the steps necessary to re-create it. Specific error messages are needed. Depending on the complexity of the problem, you may be asked to submit a recreatable example demonstrating the problem.

•	An assessment of the severity level of the reported problem.