DataDirect Connect ODBC for Oracle

Copyright 1998 INTERSOLV Inc. All rights reserved. INTERSOLV and DataDirect are registered trademarks of INTERSOLV, Inc. Connect ODBC is a trademark of INTERSOLV, 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 Oracle 7 database system.

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

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


Oracle 7 System Requirements
Oracle System Requirements

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 CORE350.DLL, as well as OCIW32.DLL.  If you attempt to configure an Oracle 7 data source and you do not have these DLLs on your path, a message similar to the following appears:

The setup routines for the INTERSOLV 3.00 32-BIT Oracle7 ODBC driver could not be loaded due to system error code 126.

When you click OK, the following message appears:

Could not load the setup or translator library.

Oracle 8

The Oracle Net8 Client version 8.0.4, or greater, is required to access remote Oracle 8 databases. For Alpha NT systems, version 8.0.3 is required.

On Intel systems, the appropriate DLLs for the Oracle Net8 Client must be on your path or in your Windows NT SYSTEM32 or Windows 9x SYSTEM directory, for example, ORA804.DLL, PLS804.DLL, and OCI.DLL. If you attempt to configure an Oracle 8 data source and you do not have these DLLs on your path, a message similar to the following appears:

The setup routines for the INTERSOLV 3.00 32-BIT Oracle driver could not be loaded due to system error code 126.

When you click OK, the following message appears:

Could not load the setup or translator library.


Configuring Oracle Data Sources
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 the Advanced tab  to configure optional data source settings, such as packet size. Click Apply.

5	Click Translate to display the Select Translator dialog box, which lists the translators specified in the ODBC Translators section of the system information. INTERSOLV provides a translator named INTERSOLV OEM 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.

6	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.


Connecting to Oracle 7 Using a Logon Dialog Box
Connecting to a Data Source Using a Logon Dialog Box

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 Oracle 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 your 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 0 to 65536. 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."

DefaultIsolationLevel (DIL) Oracle 8 only: DefaultIsolationLevel=string values.  Specifies the default isolation level for concurrent transactions. SQL_TXN_READ_COMMITTED and SQL_TXN_READ_SERIALIZABLE are the values. The default is SQL_TXN_READ_COMMITTED.

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.

EnableScrollableCursors (ESC): EnableScrollableCursors={0 | 1}. Enables scrollable cursors for the data source. Both Keyset and Static cursors are enabled. This option may need to be set to 1 when using Microsoft Foundation Classes for database access. The default is 0.

LockTimeOut (LTO) ) Oracle 7 only: 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 (don't 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 Box .

PacketSize (PS) Oracle 7 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 Box .

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.

Oracle 8

For Oracle 8 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.


Oracle 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
Oracle 8

In addition to the standard Oracle data types, the Oracle 8 data types are mapped to the standard ODBC data types as follows:

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

The Oracle 8 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 Oracle 8 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.GTABLE2CHARCOL, icol INTEGER) as begin open rset for select CHARCOL from GTABLE2 where INTEGERCOL <= icol order by INTEGERCOL;

end;

For more information consult your Oracle SQL manual.


Isolation and Lock Levels Supported by Oracle
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 greater or Oracle 8.x). Oracle supports record-level locking.


Oracle ODBC Conformance Level
ODBC Conformance Level

The API functions supported are listed in “Supported ODBC Functions,” found in the General Help on DataDirect ODBC Drivers. The Oracle drivers also support SQLDescribeParam if EnableDescribeParam=1. If EnableScrollableCursors=1, they support SQLSetPos as well as scrollable cursors with SQLFetchScroll and SQLExtendedFetch.

The Oracle driver supports the following X/Open level functions:

SQLProcedures

SQLProcedureColumns

SQLPrimaryKeys

SQLForeignKeys

SQLTablePrivileges

SQLColumnPrivileges

SQLSetPos (SQL_ADD)

The Oracle driver supports the core SQL grammar.


Number of Connections and Statements Supported by Oracle
Number of Connections and Statements Supported

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


Oracle ODBC Setup Dialog Box
ODBC Oracle Driver Setup Dialog Box

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


Advanced Options for Configuring Data Sources
Advanced Tab, ODBC Oracle Driver Setup Dialog Box

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 (Oracle 7 only): 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 0 to 65536; 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.

Isolation Level (Oracle 8 only): Specifies the default isolation level for concurrent transactions. SQL_TXN_READ_COMMITTED and SQL_TXN_READ_SERIALIZABLE are the values. The default is SQL_TXN_READ_COMMITTED.

Packet Size (Oracle 7 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.

Enable Scrollable Cursors: Check this box to enable scrollable cursors for the data source. Both Keyset and Static cursors are enabled. This option may need to be checked when using Microsoft Foundation Classes for database access.

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 INTERSOLV OEM ANSI translator to translate your data from the IBM PC character set to the ANSI character set.

OK

Cancel

Apply


Logon to Oracle Dialog Box

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.