Easysoft ODBC-Oracle Driver User's Guide - Configuration

Configuring the Easysoft ODBC-Oracle Driver

The Easysoft ODBC-Oracle Driver is usually installed on the machines where your applications are running. ODBC applications access ODBC drivers through the ODBC driver manager and a data source which tells the driver manager which ODBC driver to load, which database to connect to and how to connect to it. This chapter describes how you create data sources, use DSN-less connections and configure the Easysoft ODBC-Oracle Driver.

Before setting up a data source, the Easysoft ODBC-Oracle Driver must have been successfully installed on the machines where your ODBC application is running.

Instructions for installing the Easysoft ODBC-Oracle Driver on Windows and Unix platforms are provided in Installation.

Chapter Guide

DSN-less connections

In addition to using a data source, you can also connect to a database by using a DSN-less connection string of the form:

SQLDriverConnect(..."DRIVER={ORACLE};DB=pubs;UID=demo;PWD=easysoft;"...)

where pubs is the database name, demo is the user name with which to connect to the database, and easysoft is the password for the demo user.

Further Easysoft ODBC-Oracle Driver attribute settings, as described in this section, can also be added to the connection string using the same "PARAMETER=value;" format.

Refer to the section relevant to your platform to continue:

OS Authentication

The Easysoft ODBC-Oracle Driver can be configured to connect to an Oracle database using OS Authentication. To do this, you must first ensure that the database and client are configured and working correctly. More details on the use of OS Authentication and its implications can be found in your Oracle documentation. To establish that the system is configured correctly, we recommend using a tool such as sqlplus, before attempting to configure your driver. Once the Oracle software is configured, the driver can be set up by leaving the username and password fields empty.

Windows Setup

To connect an ODBC application on a Windows machine to an Oracle database on a remote server:

1.  In Control Panel, double-click Administrative Tools and then Data Sources (ODBC).


9x

In Control Panel, double-click ODBC Data Sources (32bit).



NT

In Control Panel, double-click Data Sources (ODBC).



64-bit Windows

The Easysoft installer program installs both a 32-bit and a 64-bit version of the Easysoft ODBC-Oracle Driver. If you want to use a 64-bit ODBC application, you need to use the 64-bit Easysoft ODBC-Oracle Driver. If you want to use a 32-bit ODBC application, you need to use the 32-bit Easysoft ODBC-Oracle Driver.

There is both a 32-bit and a 64-bit version of ODBC Administrator. The 64-bit ODBC Administrator is located in Control Panel under Administrative tools. To access the 32-bit ODBC Administrator, in the Windows Run dialog box, type:

%windir%\syswow64\odbcad32.exe

Easysoft ODBC-Oracle Driver data sources created in the 64-bit ODBC Administrator will specify the 64-bit version of the Easysoft ODBC-Oracle Driver. Easysoft ODBC-Oracle Driver data sources created in the 32-bit ODBC Administrator will specify the 32-bit version of the Easysoft ODBC-Oracle Driver.

For more information about ODBC on 64-bit Windows platforms see:

http://www.easysoft.com/developer/interfaces/odbc/64-bit.html


 The ODBC Data Source Administrator dialog box is displayed:

Figure 5: The ODBC Data Source Administrator

2.  To create a data source that is only available to the user currently logged into this machine, select the User DSN tab.

¯ OR ¯

To create a data source that is available to any user who logs into this machine, select the System DSN tab.

3.  Click Add... to add a new data source.

The Create New Data Source dialog box displays a list of drivers:

Figure 6: The Create New Data Source dialog box

4.  Select Easysoft ODBC-Oracle Driver and click Finish.

The Easysoft ODBC-Oracle Driver DSN configuration dialog box is displayed.

Figure 7: The DSN Setup dialog box - Connection tab

The attributes on this dialog box are split into four tabs, arranged by functionality, from left to right:

Test allows you to check that the client is able to connect to the specified server data source.

Connection

5.  In the DSN box enter a name for this data source.

 Choose carefully because you will not be able to change this after pressing OK.

6.  In the Description box enter something that would help a user faced with a choice of data sources.

7.  Do one of the following:

//host:port/service_name

where host is the fully qualified domain name or IP address of the server on which the Oracle database is installed, port is the Oracle listener port or the alias name mapped to the port in the /etc/services file and service_name is the local net service name. For example, //my_host:1521/my_database.

¯ OR ¯

This is the local net service name defined in your tnsnames.ora file.

8.  In the Username and Password boxes enter the user name and password of a valid account in the Oracle target database.

Settings

Figure 8: The DSN Setup dialog box - Settings tab

For details of the attributes that can be set on this dialog box, see Attribute Fields

Optimization

Figure 9: The DSN Setup dialog box - Optimization tab

For details of the attributes that can be set on this dialog box, see Attribute Fields

OCI Pooling

Figure 10: The DSN Setup dialog box - OCI Pooling tab

For details of the attributes that can be set on this dialog box, see Attribute Fields

9.  Now click Test.

The Easysoft ODBC-Oracle Driver attempts to connect to the database and send an ODBC request, displaying the results in a window.

10.  If an error message is displayed then check the entries in the DSN setup fields.

 If the target database can be successfully accessed by running SQL*Plus, the problem can be assumed to be on the client machine, rather than on the server machine.

¯ OR ¯

An information message will be displayed if you have successfully connected to the server:

Figure 11: The Easysoft Setup Test DSN dialog box

11.  Click OK in the Easysoft Setup Test dialog box and OK in the Easysoft ODBC-Oracle Driver DSN Setup dialog box.

 The connection has been made.

Connecting a Client Application

There is now a data source on the Windows machine that connects via the Easysoft ODBC-Oracle Driver to an Oracle database on a remote server machine.

An example ODBC application can now be connected to the local data source to demonstrate that the Easysoft ODBC-Oracle Driver is functioning correctly.


NB

This section requires access to Microsoft Access.


1.  Start Microsoft Access (for example) and create a blank database.

2.  Do one of the following:

The Link dialog box displays the existing databases on your system.

3.  Do one of the following:

The Select Data Source dialog box displays the existing data sources on your system:

Figure 12: The Select Data Source dialog box Machine Data Source tab

4.  Click the Machine Data Source tab and find the local data source you created in the list (note that your description of the data source is displayed beside it).

5.  Select your data source and click OK.

Microsoft Access connects (through the ODBC Driver Manager) to the Easysoft ODBC-Oracle driver and retrieves a list of available tables.

The Link Tables window is displayed, showing a list of available datasets:

6.  Click on a table and then click OK.

After a short wait, you are returned to the Database window.

7.  Double-click on one of the tables to open and browse it.

Unix Setup

Data Source Attributes

There are two options when setting up a data source to your Oracle data:

¯ OR ¯

This is exactly the same mechanism as is used on the ODBC Data Source Administrator on Windows platforms (see Windows Setup).


NB

If you have user and system data sources with the same name the driver manager will use the user data source.


The default Easysoft ODBC-Oracle Driver installation will create a system DSN named [ORACLE]. If you are using unixODBC that came with this distribution the system odbc.ini file will be /etc.

If you have built unixODBC yourself, or installed it from some other source then system data sources are stored in the path specified with the configure option --sysconfdir=directory. If sysconfdir was not specified when unixODBC was configured and built it defaults to /usr/local/etc).

User data sources are stored in the file .odbc.ini in the current user's home directory (e.g. $HOME/.odbc.ini). .


NB

By default, you must be logged in as root to edit a system data source defined in /etc/odbc.ini.



NB

odbcinst -j will show you where the driver manager stores system and user data sources.


You may either change some of the initial configuration parameter values for the sample data source or add extra data sources.

Each section of the odbc.ini file starts with a data source name in square brackets [ ] followed by a number of attribute=value pairs.


NB

Attribute names in odbc.ini are not case sensitive.


For the addition of a data source, all the settings in the following example must be appended to the relevant odbc.ini file. The Driver attribute names the ODBC driver in the odbcinst.ini file to use for this data source. When the Easysoft ODBC-Oracle Driver is installed into unixODBC it places an ORACLE entry into the odbcinst.ini file so you should always have Driver = ORACLE in your Easysoft ODBC-Oracle Driver data sources.


NB

More details of configuring data sources with unixODBC can be found at www.unixodbc.org.


To configure your Oracle DSN in your odbc.ini file, you will need to edit:

¯ OR ¯

Instant Client Example

[ORACLE]

Driver = ORACLE

Database = //testhost:1521/testdb

User = system

Password = manager

Standard Client Example

[ORACLE]

Driver = ORACLE

Database = testdb

User = system

Password = manager

Other optional attribute values may be set in the odbc.ini file, and are described in Attribute Fields.

Driver Attributes

There are a number of driver attributes that are stored in the odbcinst.ini file. By default this file is installed in /etc. If you have built unixODBC yourself, then it will be whatever path you specified in the sysconfdir=directory configure option (if sysconfdir has not been specified then the path will default to /usr/local/etc).


NB

By default, you must be logged in as root to edit a system data source defined in /etc/odbcinst.ini,.


If the applications using the driver does not use threads, you can set Threads to be 0. This will provide a performance enhancement, by disabling thread safety in the driver.

Environment

For the Easysoft ODBC-Oracle Driver to function correctly the following environment variables must be created and given values specific to the directory structure on the target machine.

Instant Client

This must be the Instant Client software directory. For example:

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/instantclient_10_2

export LD_LIBRARY_PATH

Note that on some platforms you need to use SHLIB_PATH or LIBPATH rather than LD_LIBRARY_PATH.

Standard Client

This must be the Oracle Client software directory. For example:

ORACLE_HOME=/home/oracle/OraHome1

export ORACLE_HOME

This must always be set to the directory $ORACLE_HOME/lib. For example:

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/oracle/OraHome1/lib

export LD_LIBRARY_PATH

Note that on some platforms you need to use SHLIB_PATH or LIBPATH rather than LD_LIBRARY_PATH.

Easysoft ODBC-Oracle Driver

The Easysoft ODBC-Oracle Driver must also be able to find the following shared objects which it installs:

By default, this will always be located in /usr/local/easysoft/unixODBC/lib/:

By default, this will always be located in /usr/local/easysoft/lib/

By default, this will always be located in /usr/local/easysoft/lib

It may be necessary to set up and export LD_LIBRARY_PATH, SHLIB_PATH or LIBPATH (depending on the operating system and run-time linker) to include the directories where libodbcinst.so, libeslicshr.so and libeslicshr.so are located.


NB

The shared object file extension (.so) varies across operating systems ( .so, .a, or .sl).


Establishing a Test Connection

Run the isql query tool to prove that the Easysoft ODBC-Oracle Driver is working:

1.  Change directory into

 /usr/local/easysoft/unixODBC/bin

2.  Type ./isql -v DSN

 where DSN is the name of the target data source

3.  Enter SQL commands to query the database, such as:

 SQL> select table_name from user_tables

4.  or enter 'help' to return a list of tables

 SQL> help

Trouble-shooting a test connection

Some Easysoft ODBC-Oracle distributions contain our checksys diagnostic tool which can report configuration and environment problems and suggest corrective action. It can be found in the /usr/local/easysoft/oracle directory. To run the tool:

cd /usr/local/easysoft/oracle

./checksys -d DSN

Attribute Fields

This section lists the attributes which can be set for the Easysoft ODBC-Oracle Driver in a table showing:

Attributes which are text fields are displayed as "value".

Attributes which are logical fields can contain either 0 (to set to off) or 1 (to set to on) and are displayed as "0|1".

If an attribute can contain one of several specific values then each possible entry is displayed and separated by a pipe symbol.

For example, in the statement:

Data_Type_Map=0|1|2

the value entered may be "0", "1" or "2".

DSN

The name of the User or System data source to be created, as used by the application when calling the SQLConnect or SQLDriverConnect functions.

Interface Value
DSN Dialog Box (Windows) DSN
odbc.ini file (Unix) [value]
Connect String DSN=value

Description

Descriptive text which may be retrieved by certain applications to describe the data source.

Interface Value
DSN Dialog Box (Windows) Description
odbc.ini file (Unix) Description=value
Connect String Not Used

Database

If you are using the Instant Client, a SQL connect URL string. Use the following format:

//host:port/service_name

where host is the fully qualified domain name or IP address of the server on which the Oracle database is installed, port is the Oracle listener port or the alias name mapped to the port in the /etc/services file and service_name is the local net service name. For example, //my_host:1521/my_database.

If you are using the standard Oracle Client (or the Instant Client with a tnsnames.ora file by setting TNS_ADMIN), the logical name used to identify the Oracle target database. This is the local net service name defined in your tnsnames.ora file. For example, my_database.

Interface Value
DSN Dialog Box (Windows) Database
odbc.ini file (Unix) Database=value
Connect String DB=value

Username

The name of the user that will be supplied to Oracle to authenticate the connection.

If a value is specified in the connect string then any entry in the DSN will be ignored.

Interface Value
DSN Dialog Box (Windows) Username
odbc.ini file (Unix) User=value
Connect String UID=value

Password

The password supplied to Oracle to authenticate the connection

Interface Value
DSN Dialog Box (Windows) Password
odbc.ini file (Unix) Password=value
Connect String PWD=value

Note that passwords are case sensitive for new or modified accounts in Oracle 11g.

If a value is specified in the connect string then any entry in the DSN will be ignored.

MetaData ID

When ON (i.e. set to 1 or checked), the default value of the Connection Attribute SQL_ATTR_METADATA_ID is set to SQL_TRUE (see StarOffice 5.2).

If SQL_TRUE, the string arguments of catalog functions are treated as identifiers. The case is not significant. For non-delimited strings, the driver removes any trailing spaces, and the string is folded to uppercase. For delimited strings, the driver removes leading and trailing spaces, and takes literally whatever is between the delimiters.


NB

Setting this can cause failures in applications which expect the default to be SQL_FALSE and do not check the attribute.


The default for METADATA_ID is off (0)



Interface Value
DSN Dialog Box (Windows) MetaData ID
odbc.ini file (Unix) MetaData_ID=0|1
Connect String METADATA_ID=0|1

Preserve Metadata Case

When ON (i.e. set to 1 or checked), the case of the parameter values passed to metadata calls will not change.

The default for METADATA_DONT_CHANGE_CASE is off (0).



Interface Value
DSN Dialog Box (Windows) Preserve MetaData Case
odbc.ini file (Unix) MetaData_Dont_Change_Case=0|1
Connect String METADATA_DONT_CHANGE_CASE=0|1

Trim Trailing Varchar Spaces

When ON (i.e. set to 1 or checked), the driver trims trailing spaces from varchar types when passed as bound parameters. If VarcharTrimTrailingSpaces is set to 1 then trailing spaces are removed from the end of the data.

The default behaviour is to not trim spaces.

Interface Value
DSN Dialog Box (Windows) Trim Trailing Varchar Spaces
odbc.ini file (Unix) VarcharTrimTrailingSpaces=0|1
Connect String VARCHARTRIMTRAILINGSPACES=0|1

No Metadata Schema

When ON (i.e. set to 1 or checked), schema names are not returned by metadata calls. This works around some problem applications which do not handle SCHEMA names properly (see OpenOffice.org 1.0)..

Interface Value
DSN Dialog Box (Windows) No Metadata Schema
odbc.ini file (Unix) Metadata_Dont_Do_Schema=0|1
Connect String METADATA_DONT_DO_SCHEMA=0|1

Use Longs

When ON (i.e. set to 1 or checked), information on LONG data types will be returned in the result set from the SQLGetTypeInfo function call.

Restrictions with LONG data types in Oracle databases (such as only permitting one column per table to be defined) often cause errors to occur, and this attribute can be used to include LONG within the list of valid data types which can be used by an application.

The default for USE_LONGS is off (0).



Interface Value
DSN Dialog Box (Windows) Use Longs
odbc.ini file (Unix) Use_Longs=0|1
Connect String USE_LONGS=0|1

Enable Synonyms

When ON (i.e. set to 1 or checked), table name synonyms are returned in metadata result sets.

By default synonyms are not returned. If you do not need to see synonyms, leave ENABLE_SYNONYMS set to its default value. Enabling synonyms in metadata calls may greatly increase the size of metadata result sets for ODBC API calls such as SQLTables.

Interface Value
DSN Dialog Box (Windows) Enable Synonyms
odbc.ini file (Unix) Enable_Synonyms=0|1
Connect String ENABLE_SYNONYMS=0|1

Enable User Catalog

When ON (i.e. set to 1 or checked), this limits the driver to returning metadata (e.g. tables) for the current Oracle user.

The driver default is to see metadata for all users (OFF). Many ODBC applications will never need this amount of catalog data. Setting ENABLE_USER_CATALOG to ON reduces the number of rows returned by SQLTables calls.

Note that the default for data sources configured in the Windows Easysoft ODBC-Oracle Driver dialog box is ON.

Interface Value
DSN Dialog Box (Windows) Enable User Catalog
odbc.ini file (Unix) Enable_User_Catalog=0|1
Connect String ENABLE_USER_CATALOG=0|1

Describe Parameters

Oracle does not support the describing of parameters, so the driver does not support the SQLDescribeParam ODBC call. However, if this ini setting is set to 1 (or checked) then the driver will describe any parameters as varchar.

The default for DESCRIBE_PARAM_AS_STRINGS is off (0).



Interface Value
DSN Dialog Box (Windows) Enable User Catalog
odbc.ini file (Unix) Describe_Param_As_Strings=0|1
Connect String DESCRIBE_PARAM_AS_STRINGS=0|1

Datatype Map

The following values can be set in order to map Oracle data types onto ODBC data types:

Value Oracle Data Type ODBC Data Type
0

NUMBER <= 4 digits

SQL_SMALLINT


NUMBER <= 9 digits

SQL_INTEGER


NUMBER = n digits

SQL_NUMERIC


NUMBER = n,m digits

SQL_NUMERIC

1

NUMBER <= 4 digits

SQL_SMALLINT


NUMBER <= 9 digits

SQL_INTEGER


NUMBER = n digits

SQL_DOUBLE


NUMBER = n,m digits

SQL_DOUBLE

2

NUMBER <= 4 digits

SQL_SMALLINT


NUMBER = n digits

SQL_INTEGER


NUMBER <= 19 digits

SQL_BIGINT


NUMBER n,m digits

SQL_DOUBLE

3

NUMBER <= 4 digits

SQL_SMALLINT


NUMBER <= 9 digits

SQL_INTEGER


NUMBER <= 19 digits

SQL_BIGINT


NUMBER >9 digits

SQL_NUMERIC

The default for Data_Type_Map is 0.



Interface Value
DSN Dialog Box (Windows) Datatype Map
odbc.ini file (Unix) Data_Type_Map=0|1|2
Connect String DATA_TYPE_MAP=0|1|2

DBI Long Size

Any value specified will override the maximum size of a LONG column (in bytes).

Perl DBI tries to allocate a buffer the size of a LONG column and, as this is rather large, it can cause problems which setting DBI_LONG_SIZE can resolve (see Perl DBI).



Interface Value
DSN Dialog Box (Windows) DBI Long Size
odbc.ini file (Unix) DBI_Long_Size=value
Connect String DBI_LONG_SIZE=value

Connect SQL

This setting defines an Oracle SQL statement that will be run immediately after the driver has established a connection to the database.



Interface Value
DSN Dialog Box (Windows) Connect SQL
odbc.ini file (Unix) Connect_SQL=value
Connect String CONNECT_SQL=value

No Lobs

When ON (i.e. set to 1 or checked), increases the performance of the Easysoft ODBC-Oracle Driver if there are no CLOB or BLOB data types in use. This is only applicable to Oracle version 8.1.7.

The default is off (0).



Interface Value
DSN Dialog Box (Windows) No Lobs
odbc.ini file (Unix) No_LOBS=0|1
Connect String NO_LOBS=0|1

No Parse

When ON (i.e. set to 1 or checked) this stops the Easysoft ODBC-Oracle Driver from preparsing the SQL (passed to SQLPrepare and SQLExecDirect) to convert ODBC escapes and parameter markers. Setting this option will provide a small speed increase but will prevent your application from using ODBC escapes sequences and parameter markers.



Interface Value
DSN Dialog Box (Windows) No Parse
odbc.ini file (Unix) No_Parse=0|1
Connect String NO_PARSE=0|1

Prefetch Rows

Indicates the number of rows returned from a single "fetch" call made to the server.

For example, if the value of OCI_ATTR_PREFETCH_ROWS attribute is set to ten, then ten rows will be fetched from the database server and the next call to SQLFetch will not need to make a call to the server as the required row will be held by the client already. The default value is 10. Increasing this value can reduce the number of round trip network calls to the server needed to return result sets from the server at the expense of greater memory use.



Interface Value
DSN Dialog Box (Windows) Prefetch Rows
odbc.ini file (Unix) OCI_Attr_Prefetch_Rows=value
Connect String OCI_ATTR_PREFETCH_ROWS=value

Prefetch Memory

Indicates the number of bytes of memory used on the client to store records returned from a single SQLFetch call made to a driver.

This controls the number of records returned, which will be the total required in order to fill the allocated memory area.

For example, if the available memory can store two rows then the next call to SQLFetch will not need to make a call to the server, as the required row will be held by the client already.



Interface Value
DSN Dialog Box (Windows) Prefetch Memory
odbc.ini file (Unix) OCI_Attr_Prefetch_Memory=value
Connect String OCI_ATTR_PREFETCH_MEMORY=value

OCI_ATTR_PREFETCH_ROWS and OCI_ATTR_PREFETCH_MEMORY update the Oracle Statement attributes of the same name:

e.g.

OCI_ATTR_PREFETCH_ROWS = 10

¯ OR ¯

OCI_ATTR_PREFETCH_MEMORY = 100

These two settings allow a user to specify as an alternative either the number of rows to be fetched or the amount of memory to be used when fetching data.


NB

These attributes are different methods of specifying the quantity of data returned in one fetch. You should only set one but if you set both attributes OCI_ATTR_PREFETCH_ROWS will take precedence.


Increasing this value can reduce the number of round trip network calls to the server needed to return result sets from the server at the expense of greater memory use.

Statement Caching

This attribute enables Oracle Statement caching. Oracle statement caching establishes and manages a cache of statements within a session. It improves performance by efficiently using prepared cursors on the Oracle server and eliminating repetitive statement parsing. To enable caching, set this attribute to the size of the required cache, i.e. the attribute value should specify the number of statements to cache. Setting the attribute to 0 will switch statement caching off. For more details on Oracle Statement Caching, please consult you Oracle documentation. The default is no statement caching.

Interface Value
DSN Dialog Box (Windows) Statement Caching
odbc.ini file (Unix) Stmt_Caching=value
Connect String STMT_CACHING=value

Fake_Clob_Length

When connecting to Oracle 10g or later from a Unix platform, the Easysoft ODBC-Oracle Driver reports the length of BLOB, BFILE, CLOB data types as "0". The driver does this because for these versions of Oracle, the maximum LOB size is 128 terabytes, which is too large a length for the ODBC API to handle.

To change this default behaviour, set FAKE_CLOB_LENGTH to 1. When ON (i.e. set to 1), the Easysoft ODBC-Oracle Driver sets the length to the largest value that the integer used to report the length is capable of holding. (Note that this is the default behaviour for the Easysoft ODBC-Oracle Driver on Windows, which is not affected by FAKE_CLOB_LENGTH.)

The default for FAKE_CLOB_LENGTH is OFF (0).



Interface Value
DSN Dialog Box (Windows) Not available.
odbc.ini file (Unix) Fake_Clob_Length=0|1
Connect String FAKE_CLOB_LENGTH=0|1

Pull_Lobs_Locally

If your application crashes when selecting from multiple CLOB columns, try adding:

pull_lobs_locally=1

to your ODBC data source or connection string. When set to 1, the Easysoft ODBC-Oracle Driver returns the entire contents of the CLOB data to an internally allocated buffer. This enables the Easysoft ODBC-Oracle Driver to determine the byte length of the CLOB. This avoids the application being given a short length when the character length for the data is not the same as the byte length.

By default, pull_lobs_locally is OFF (set to 0).



Interface Value
DSN Dialog Box (Windows) Not available
odbcinst.ini file (Unix) Pull_Lobs_Locally=0|1
Connect String PULL_LOBS_LOCALLY=0|1

OCI_UTF_Flag

When ON (i.e. set to 1), the Easysoft ODBC-Oracle Driver does additional conversion when reading LOB data. The Easysoft ODBC-Oracle Driver does this to compensate for non-conformant OCILobRead behaviour when reading multibyte character data. When OFF (i.e. set to 0), the Easysoft ODBC-Oracle Driver assumes that the OCILobRead behaviour conforms to the Oracle documentation.

Setting OCI_UTF_FLAG to 1 may provide a workaround if you experience problems when reading UTF-8 LOB data in parts (i.e. the buffer size passed to SQLGetData is not large enough to hold the entire LOB) and you are using the Instant Client 11.1.

The default for OCI_UTF_FLAG is OFF (0).



Interface Value
DSN Dialog Box (Windows) Not available.
odbc.ini file (Unix) OCI_UTF_Flag=0|1
Connect String OCI_UTF_FLAG=0|1

With_Unicode

When ON (i.e. set to 1), the Easysoft ODBC-Oracle Driver attempts to detect whether the national character set for the current environment is AL16UTF16. If this is the case, the Easysoft ODBC-Oracle Driver:

To check what national character set the Easysoft ODBC-Oracle Driver has detected, set With_Unicode to 1, enable Easysoft ODBC-Oracle Driver logging by adding the entry LOG = /tmp/oracle.log to your data source, and then run a query against a table containing a NCHAR, NVARCHAR2 or NCLOB column. Look in the log file for text similar to:

Looking at column of type 1 with charset_id of 2000 against al16utf16_csid = 2000

If the charset_id and al16utf16_csid values do not match, setting With_Unicode will have no effect.

The default for With_Unicode is OFF (0).



Interface Value
DSN Dialog Box (Windows) With Unicode
odbc.ini file (Unix) With_Unicode=0|1
Connect String WITH_UNICODE=0|1

Pool Type

This is used to define the type of pooling required. This can be SESSION or CONNECTION.



Interface Value
DSN Dialog Box (Windows) Pool Type
odbc.ini file (Unix) Pool_Type=Session|Connection
Connect String POOL_TYPE=SESSION|CONNECTION

Pool Scope

This can be GLOBAL or ENV. This is used to associate the pool with the ODBC environment or make it a global resource.



Interface Value
DSN Dialog Box (Windows) Pool Scope
odbc.ini file (Unix) Pool_Scope=Global|Env
Connect String POOL_SCOPE=GLOBAL|ENV

Pool Initial

This is used to define the number of sessions or connections that are created when the pool is created.



Interface Value
DSN Dialog Box (Windows) Pool Initial
odbc.ini file (Unix) Pool_Initial=value
Connect String POOL_INITIAL=value

Pool Max

This is used to specify the maximum number of sessions or connections that the pool can contain.



Interface Value
DSN Dialog Box (Windows) Pool Max
odbc.ini file (Unix) Pool_Max=value
Connect String POOL_MAX=value

Pool Increment

This is the number that the session or connection count is incremented by when required.



Interface Value
DSN Dialog Box (Windows) Pool Increment
odbc.ini file (Unix) Pool_Increment=value
Connect String POOL_INCREMENT=value

Pool Username

This is the database username that is to be used to authenticate the sessions or connections.

Interface Value
DSN Dialog Box (Windows) Pool Username
odbc.ini file (Unix) Pool_Username=value
Connect String POOL_USERNAME=value

Pool Password

This is the database password that is to be used to authenticate the sessions or connections.

Interface Value
DSN Dialog Box (Windows) Pool Password
odbc.ini file (Unix) Pool_Password=value
Connect String POOL_PASSWORD=value

Pool Database

This is the name of the database against which the pools are to be created.

Interface Value
DSN Dialog Box (Windows) Pool Database
odbc.ini file (Unix) Pool_DB=value
Connect String POOL_DB=value

Pool_Connection_Class

Database Resident Connection Pooling (DRCP) guarantees that pooled servers are never shared across different users. Setting Pool_Connection_Class allows for further separation between the sessions of a given user by defining a connection class. A connection class lets different applications (connecting as the same database user) identify their sessions using a logical name that corresponds to the application. OCI then ensures that sessions belonging to a particular connection class are not shared outside of the connection class.

OCI supports a maximum connection class length of 1024 bytes. The asterisk character (*) is a special character and is not allowed in the connection class name.



Interface Value
DSN Dialog Box (Windows) Not available
odbc.ini file (Unix) Pool_Connection_Class=value
Connect String POOL_CONNECTION_CLASS=value

Pool_Purity

Whether the application requests a brand new session or reuses a session from the DRCP pool.

To request a new session, set POOL_PURITY to NEW.

If you connect to a DRCP-enabled Oracle database server without setting POOL_PURITY, sessions are reused. When reusing a session from the pool, the NLS attributes of the server take precedence over that of the client.



Interface Value
DSN Dialog Box (Windows) Not available
odbc.ini file (Unix) Pool_Purity=SELF | NEW
Connect String POOL_PURITY=SELF | NEW

XA_Connection_String

The name of the database specified with the DB field in the xa_open string. For example, you specify a database named "payroll" with the following xa_open string clause:

DB=payroll

You also need to specify "payroll" as the value for the XA_Connection_String attribute field:

XA_Connection_String=payroll

XA_Connection_String is only necessary if you are using the Easysoft ODBC-Oracle Driver to connect to Oracle in the context of an XA transaction and the Transaction Manager specifies a named database in the xa_open string.



Interface Value
DSN Dialog Box (Windows) Not available
odbc.ini file (Unix) XA_Connection_String=value
Connect String XA_CONNECTION_STRING=value

XaoswName

Sets the XA entry point for the Easysoft ODBC-Oracle Driver. Note that this is set in the odbcinst.ini file, which is normally located in /etc. The default value is xaosw.



Interface Value
DSN Dialog Box (Windows) Not applicable
odbcinst.ini file (Unix) XaoswName=xaosw_name
Connect String Not applicable