UniDAC

Using Oracle data access provider with UniDAC in Delphi

This article provides a brief overview of the Oracle data access provider for UniDAC used to establish a connection to Oracle databases from Delphi and Lazarus. You will find the description of some useful features and how to get started quickly.

Overview

Oracle data access provider is based on the Oracle Data Access Components (ODAC) library, which is one of the best known Delphi data access solutions for Oracle. The main features of Oracle data access provider are:

The full list of Oracle provider features can be found at the UniDAC product page.

Both Professional and Standard Editions of UniDAC include the Oracle provider. For Express Edition of UniDAC, the Oracle provider can be installed with ODAC.

Compatibility

To learn about Oracle database server compatibility, refer to the Compatibility section.

Requirements

If your application is working in the Direct mode, it is not required to install any additional software on the client. For application that has Direct mode disabled, it is required to install the Oracle client.

Deployment

To deploy Win32 applications built with run-time packages, it is not required to deploy the odacXX.bpl file with UniDAC Professional Edition. But it is necessary to deploy the odacXX.bpl file with Express Edition of UniDAC. This happens because in the UniDAC Professional Edition functionality of odacXX.bpl is included in the correspondent oraproviderXX.bpl, when in Express Edition of UniDAC, oraproviderXX.bpl is just a wrapper on odacXX.bpl.

For more information about deployment of UniDAC-based applications, please, refer to the common Deployment topic.

Oracle-specific options

Though UniDAC is components that provide unified interface to work with different database servers, it also lets you tune behaviour for each server individually. For thin setup of a certain database server, UniDAC provides server-specific options. These options can be applied to such components as TUniConnection, TUniQuery, TUniTable, TUniStoredProc, TUniSQL, TUniScript via their SpecificOptions property. SpecificOptions is a sting list. Therefore you can use the following syntax to assign an option value:

  UniConnection.SpecificOptions.Values['CharLength'] := '1';

Below you will find the description of allowed options grouped by components.

TUniConnection

Option name Description
CharLength Serves for national languages support. Means the character size in bytes. Allowed values are in range [0..6]. Zero means that the actual character length will be requested from the Oracle server.
The default value is 1.
Charset Setups the character set which will be used to transfer character data between client and server.
Supported with Oracle 8 client only.
ClientIdentifier Use this property to determine the client identifier in the session. The client identifier can be set in the session handle at any time in the session. Then, on the next request to the server, the information is propagated and stored in the server session. The first character of the ClientIdentifier should not be ':'. If it is, an exception will be raised.
This property has no effect if you use the version of the server earlier than Oracle 9.
ConnectMode Specifies which system privileges to use when connecting to the server. The following values are supported for this option:

cmNormal (default)
Connect as an ordinary user.
cmSysOper
Connect with SYSOPER role.
cmSysDBA
Connect with SYSDBA role.
cmSysASM
Connect with SYSASM role.


User must have SYSOPER, SYSDBA, SYSASM or all three roles granted before he connects to the server and wishes to use any of these roles. ConnectMode is not supported for OCI 7.
ConnectionTimeout The time to wait for a connection to open before raising an exception. Works only when the Direct mode is set to True.
ConvertEOL Affects line break behavior in string fields and parameters. When fetching strings (including CLOBs and LONGs) with ConvertEOL = True dataset converts their line breaks from LF to CRLF form. And when posting strings to server with ConvertEOL turned on their line breaks converted from CRLF to LF form. By default, strings are not converted.
DateFormat Specifies the default date format used when Oracle makes conversions from internal date format into string values and vice versa. An example of valid expression for this property could be "MM/DD/YYYY".
DateLanguage Specifies the default language used when Oracle parses internal date format into string values and vice versa. Examples of valid expressions for this property could be "French", "German" etc.
Direct If set to True, connection is performed directly over TCP/IP, and does not require Oracle software on the client side. Otherwise, provider connects in Client mode.
EnableIntegers When set to True, the provider maps Oracle numbers with precision less than 10 to TIntegerField. If EnableIntegers is set to False, numbers are mapped to TFloatField or XXX.
EnableNumbers When set to True, the provider maps Oracle numbers with precision greater than 15 to TOraNumberField. Otherwise they are mapped to TFloatFiled.
HomeName Set the HomeName option to select which Oracle client will be used in your application. Use this property in cases when there is a number of Oracle clients on the machine. The Oracle provider searches all available homes in the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE registry folder. If the HomeName option is set to an empty string, the provider uses the first directory from the list of homes encountered in environment PATH variable as the default Oracle home.
IPVersion Use the IPVersion property to specify the Internet Protocol version. The default value is ivIPv4.

ivIPBoth
Specifies that either IPv6 or IPv4 Internet Protocol version is used.

Note: when the TIPVersion property is set to ivIPBoth, there occurs an attempt to connect via IPv6 (if it is enabled in the OS); if the attempt fails - there occurs an attempt to connect via IPv4.

ivIPv4
Specifies that the IPv4 Internet Protocol version is used
ivIPv6
Specifies that the IPv6 Internet Protocol version is used
OptimizerMode Use the OptimizerMode property to get or set the default optimizer mode for connection. The OptimizerMode property can have one of the following values:

omDefault
Session optimizer mode will not be changed.
omFirstRowsNN
Instruct Oracle to optimize a SQL statement for fast response. It instructs Oracle to choose the plan that returns the first NN rows most efficiently. If you use the version of the server earlier than Oracle 9.0, these values have the same effect as omFirstRows.
omFirstRows
This mode is retained for backward compatibility and plan stability. It optimizes for the best plan to return the first single row.
omAllRows
Explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
omChoose
Causes the optimizer to choose between the rule-based and cost-based approaches for a SQL statement. The optimizer selection is based on the presence of statistics for the tables accessed by the statement. If the data dictionary has statistics for at least one of these tables, then the optimizer uses the cost-based approach and optimizes with the goal of the best throughput. If the data dictionary does not have statistics for these tables, then it uses the rule-based approach.
omRule
Chooses rule-based optimization (RBO). Any other value causes the optimizer to choose cost-based optimization (CBO). The rule-based optimizer is the archaic optimizer mode from the earliest releases of Oracle Database.
PoolingType Pooling implementation type
optLocal - our own pooling implementation
optOCI - the management of the pool is done by OCI
optMTS - "shared server" pool is used
The default value is optLocal
PrecisionBCD This option allows dataset to represent fields as TBCDField if NUMBER field precision and scale less or equal than precision and scale specified in BCDPrecision. Value is interpreted like two coma separated digits (BCD precision and scale). The value of BCDPrecision cannot be greater then '14,4'. The default value is '14,4'.
PrecisionFloat This option allows dataset to represent fields as TFloatField if NUMBER field precision less or equal than PrecisionFloat. The default value is 0.
PrecisionFMTBCD This option allows dataset to represent fields as TFMTBCDField if NUMBER field precision and scale less or equal than precision and scale specified in PrecisionFMTBCD. Value is interpreted like two coma separated digits (FMTBCD precision and scale).
PrecisionInteger This option allows dataset to represent fields as TIntegerField if NUMBER field precision less or equal than PrecisionInteger. The default value is 9.
PrecisionLargeInt This allows dataset to represent fields as TLargeIntegerField if NUMBER field precision less or equal than PrecisionLargeInt. The default value is 18.
PrecisionFMTBCD This option allows dataset to represent fields as TFMTBCDField if NUMBER field precision and scale less or equal than precision and scale specified in PrecisionFMTBCD. Value is interpreted like two coma separated digits (FMTBCD precision and scale). The default value is '38,38'.
Schema Use the Schema property to change the current schema of the session to the specified schema. This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give you any additional system or object privileges for the session.
If TUniConnection.Connected is True, read this property to receive the name of the current schema.
StatementCache When set to True, the provider caches statement handles.
StatementCacheSize Statement handle cache size.
ThreadSafety Allows to use the OCI in multi-threaded environment. The ThreadSafety option must be True before any non blocking fetch of rows or SQL statement execution takes place.
UnicodeEnvironment Enables or disables using OCI Unicode Environment. When this option is enabled, Unicode characters can be used in SQL statements. Disable this option if you have encountered some problems with Unicode Environment.
UseOCI7 Use the UseOCI7 option to force TUniConnection use OCI 7 call style only. The default value is False.
UseUnicode Enables or disables Unicode support. Affects on character data fetched from the server. When set to True all character data is stored as WideStrings, and TStringField is replaced with TWideStringFiled. Supported starting with Oracle 8.

TUniSQL

Option name Description
NonBlocking Used to execute an SQL statement by a separate thread. Set the NonBlocking property to True to execute an SQL statement by a separate thread.
StatementCache When set to True, the provider caches statement handles.
TemporaryLobUpdate If True, a temporary LOB is used to write input and input/output LOB parameter into database when executing dataset's SQL statements.

TUniQuery, TUniTable, TUniStoredProc

Option name Description
AutoClose The OCI cursor will be closed after fetching all rows. Allows to reduce the number of opened cursors on the server.
CacheLobs If True (the default value), then local memory buffer is allocated to hold a copy of the Lob content. See notes below for further details. If this option is set to False, it is highly recommended to set DeferredLobRead option to True. Otherwise, LOB values are fetched to the dataset, and it can result in performance loss.
DeferredLobRead If True, all Oracle 8 Lob values are only fetched when they are explicitly requested. Otherwise entire record set with any Lob values is returned when dataset is opened. Whether Lob values are cached locally to be reused later or not is controlled by the CacheLobs option.
ExtendedFieldsInfo If True, an additional query is performed, to get information about returned fields and the tables they belong to. This helps to generate correct updating SQL statements but may result in performance decrease. The default value is False.
FetchAll If True, all records of the query are requested from database server when the dataset is being opened. If False, records are retrieved when a data-aware component or a program requests it. The default value is False.
FieldsAsString If True, all non-BLOB fields are treated as being of string datatype.
HideRowId Used to display the RowId service field. By default, the Visible property for this field is set to False.
KeySequence Use the KeySequence property to specify the name of a sequence that will be used to fill in a key field after a new record is inserted or posted to the database.
NonBlocking Used to execute an SQL statement and fetch rows by a separate thread. Set the NonBlocking property to True to execute an SQL statement and fetch rows by a separate thread.
PrefetchLobSize Use the PrefetchLobSize option to retrieve the LOB length and the chunk size as well as the beginning of the LOB data along with the locator during regular fetch. The PrefetchLobSize property specifies the size of LOB data that will be prefetched. If the total LOB size is less or equals to PrefetchLobSize, then all LOB data will be fetched during regular fetch without additional round trips that can improve performance greatly.
Note: Prefetching LOB data is available in Oracle 11 and higher.
RawAsString If True, all RAW fields are treated as being of string datatype, e.g. represented as hexadecimal string.
ScrollableCursor If True, TUniDataSet does not cache data on the client side but uses scrollable server cursor (available since Oracle 9 only). This option can be used to reduce memory usage, because dataset stores only current fetched block. Unlike the UniDirectional option ScrollableCursor allows bidirectional dataset navigation. Note that scrollable cursor is read-only by its nature.
SequenceMode Set the SequenceMode property to specify which method is used internally to generate sequenced field.
The following values are allowed for this property:

smInsert
New record is inserted into the dataset with the first key field populated with a sequenced value. Application may modify this field before posting the record to the database.
smPost
Database server populates key field with a sequenced value when application posts the record to the database. Any value put into key field before post will be overwritten.
StatementCache When set to True, the provider caches statement handles.
TemporaryLobUpdate If True, temporary LOB is used to write input and input/output LOB parameter into database when executing dataset's SQL statements.

TUniScript

The TUniScript component has no Oracle-specific options.

TUniLoader

Option name Description
DirectPath If True, data are loaded using the Oracle Direct Path Load interface. If False, data are loaded by executing an INSERT statement.

TUniLoader has the following limitations when Oracle Direct Path Load is used:

TUniDump

The TUniDump component has no Oracle-specific options.

Oracle-specific notes

This chapter describes several special cases of using Oracle data provider.

Connecting in Direct mode

By default, Oracle provider works with the server through Oracle Call Interface (Client mode). However, working through OCI requires Oracle client software to be installed on target workstations. This is inconvenient and causes additional installation and administration expenses. Furthermore, there are some situations in which installation of Oracle client is not advisable or may even be impossible. To overcome these problems, the Oracle provider includes an option to connect to Oracle directly over the network using the TCP/IP protocol (Direct mode). Connecting in Direct mode does not require Oracle client software to be installed on target machines. The only requirement for running an ODAC-based application that uses the Direct mode is that the operating system must support the TCP/IP protocol.

To connect to an Oracle server in Direct mode, set the Direct specific option of your TUniConnection object to True, and fill the TUniConnection.Server property with a string that contains the host address of the database server, port number, and the Oracle System Identifier (SID) or the Oracle Service Name in the following format: Host:Port:SID or Host:Port:sid=SID or Host:Port:sn=ServiceName.

Note: If sid or sn aren't set then SID is used by default. If SID and Service Name are equal then TOraSession.Server property can be set using SID or Service Name.

Note that the syntax used to set up the Server property is different in Direct mode and in Client mode. In Client mode, the Server property must be set to the TNS name of the Oracle server.

To return to working through OCI, just set the Direct specific option to False and fill the Server property with the TNS name of your server.

Applications that use Client mode and those that use Direct mode have similar size and performance. The security of using the Direct mode is the same as using Client without Oracle Advanced Security. However, Direct mode has certain limitations:

Please note that we do not guarantee Direct mode compatibility with all Oracle servers and in every network. We have tested Direct mode for all versions of Oracle servers for Windows on a local network. Other platforms may cause some incompatibility issues.

© 1997-2019 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback