|
Using Oracle data access provider with UniDAC in Delphi |
|
|
|
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.
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 on the UniDAC Features 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.
To learn about Oracle database server compatibility, see the Compatibility section.
If your application is using the Direct mode, you don't need to install any additional software on the client machine. In the Client mode, you need to install the Oracle client.
When an application was built without runtime packages (Link with runtime packages set to False in Project Options), you do not need to deploy any BPL files with it. For more information, see Deployment.
Note that UniDAC Trial requires deployment of additional BPL files regardless of Link with runtime packages.
In addition to providing a unified interface to work with different database server, it also allows you to tune application behavior for each server individually. UniDAC provides server-specific options for fine-tuning specific database servers. These options can be applied to such components as TUniConnection, TUniQuery, TUniTable, TUniStoredProc, TUniSQL, and TUniScript using the SpecificOptions property, which is a string list. You can use the following syntax to assign a value to SpecificOptions:
UniConnection.SpecificOptions.Values['CharLength'] := '1';Below you will find the description of allowed options grouped by components.
| Option | Description |
|---|---|
CommandTimeout |
The wait time before a request is sent to the server to terminate the attempt to execute or fetch the current SQL statement. The wait time is specified in seconds. The default value is 0. The value of 0 indicates there are no time limits (an attempt to execute a statement will wait indefinitely).
|
NonBlocking |
Executes a SQL statement in a separate thread. The default value is False. |
StatementCache |
Enables statement caching. The default value is False. |
TemporaryLobUpdate |
Enables the use of a temporary LOB to write input and input/output LOB parameter into database when executing dataset's SQL statements. The default value is True.
|
| Option | Description |
|---|---|
AutoClose |
Closes the OCI cursor after fetching all rows. The option allows to reduce the number of opened cursors on the server. The default value is False.
|
CacheLobs |
Allocates a local memory buffer to hold a copy of the LOB content. When False, it is highly recommended to set the DeferredLobRead option to True. Otherwise, LOB values are fetched to the dataset, which may result in performance loss. The default value is True.
|
CommandTimeout |
The wait time before a request is sent to the server to terminate the attempt to execute or fetch the current SQL statement. The wait time is specified in seconds. The default value is 0. The default value 0 indicates there are no time limits (an attempt to execute a command will wait indefinitely).
|
DeferredLobRead |
When True, all Oracle 8 LOB values are only fetched when they are explicitly requested. When False, an entire record set, including LOB values, is returned when a dataset is opened. The CacheLobs option specifies whether LOB values are cached locally to be reused later. The default value is False.
|
ExtendedFieldsInfo |
Performs an additional query to get information about the returned fields and tables they belong to. This helps to generate correct updating SQL statements but may result in performance decrease. The default value is False. |
FetchAll |
When True, a query requests all records from a database server when opening a dataset. When False, records are retrieved when a data-aware component or an application requests them. The default value is False.
|
FieldsAsString |
Treats all non-BLOB fields as strings. The default value is False. |
HideRowId |
Hides the RowId service field (the Visible property is set to False). The default value is True.
|
KeySequence |
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 |
Executes a SQL statement in a separate thread. The default value is False. |
PrefetchLobSize |
Retrieves the LOB length and the chunk size, as well as the beginning of the LOB data and the locator during a regular fetch. The option specifies the size of LOB data to be prefetched. If the total LOB size is less than or equal to the value of PrefetchLobSize, then all LOB data is fetched without additional round trips during a regular fetch, which may significantly improve performance. The default value is 0
Note: LOB data prefetching is available in Oracle 11 and higher. |
PrefetchRows |
The number of rows to be prefetched during the execution of a query. Setting the property to a value greater than 0 reduces the server round-trip count and increases the performance of the application. The default value is 0 — the number of prefetched rows is determined automatically. To disable row prefetching, set the property to -1. Note: Some queries can return invalid rows count when prefetching is enabled — for example, SELECT * FROM DUAL CONNECT BY LEVEL <= 5 returns 1 row when prefetching is enabled, and 5 rows when it is disabled. |
ProcNamedParams |
Specifies a notation method of passing parameter values to the stored PL/SQL object. By default, positional notation is used. To enable named notation, set the option to True. Named notation allows passing parameter values in any order regardless of the position.
|
RawAsString |
Treats all RAW fields as hexadecimal strings. The default value is False.
|
ScrollableCursor |
When True, TUniDataSet does not cache data on the client side, but uses a scrollable server cursor (available since Oracle 9 only). This option can be used to reduce memory usage, since dataset stores only the current fetched block. Unlike the UniDirectional option, ScrollableCursor allows bidirectional dataset navigation. Note: Scrollable cursor is read-only by nature. |
SequenceMode |
Specifies the method to be used internally to generate a sequenced field.
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
The default value. 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, is overwritten.
|
StatementCache |
Enables statement caching. The default value is False. |
TemporaryLobUpdate |
Enables the use of a temporary LOB to write input and input/output LOB parameter into database when executing dataset's SQL statements. The default value is True
|
The TUniScript component has no Oracle-specific options.
| Option | Description |
|---|---|
DirectPath |
When True, data is loaded using the Oracle Direct Path Load interface. When False, data is loaded by executing an INSERT statement. The default value is True.
|
QuoteNames |
Quotes all database object names in automatically generated SQL statements, such as UPDATE statements. The default value is False.
|
TUniLoader has the following limitations when Oracle Direct Path Load is used:
The TUniDump component has no Oracle-specific options.
This section describes how to connect to Oracle in the Direct mode.
By default the Oracle provider uses the Oracle Call Interface (OCI) to connect to the Oracle database server. This is referred to as connecting in the Client mode, and is the usual way to develop Oracle applications with a third-generation language. All OCI routines are stored in external libraries, so the executables for applications that work through OCI are small. However, working through OCI requires the Oracle client software to be installed on client machines. It is rather inconvenient and causes additional installation and administration expenses. Furthermore, there are some situations where the installation of the Oracle client is not advisable or may be even impossible—for example, if you deploy an application to remote machines that are not overseen by a proficient system administrator.
To overcome these challenges, the Oracle provider includes an option to connect to Oracle directly over the network using the TCP/IP protocol. This is referred to as connecting in the Direct mode. Connecting in the Direct mode does not require Oracle client software to be installed on client machines. The only requirement for running an application that uses ODAC in the Direct mode, is that the operating system must support the TCP/IP protocol.
To connect to Oracle server in the Direct mode, set the Direct property of your TUniConnection instance to True, and the Server property to a string that contains the host address of the database server, port number, and Oracle Service Name (SN) or Oracle System Identifier (SID) in the following format:
if you connect to Oracle using Service Name:
Host:Port/ServiceName
Host:Port:sn=ServiceName
if you connect to Oracle using SID that is the same as Service Name:
Host:Port:SID
if you connect to Oracle using SID that is different from Service Name:
Host:Port:sid=SID
Host is the server's IP address or DNS name.
Port is the port number that the server listens to.
SID is a system identifier that specifies the name of an Oracle database instance.
ServiceName is a system alias to an Oracle database instance (or multiple instances).
Note that the syntax used to set up the Server property in the Direct mode is different from the Client mode. In the Client mode, this property must be set to the TNS name of the Oracle server.
Note that if sid= or sn= is not defined, the connection will be established using SID. If SID and Service Name are the same, then either of them can be used to set the TUniConnection.Server property.
An example below illustrates the connection to Oracle in the Direct mode. The IP address of the Oracle server is 205.227.44.44, the port number is 1521 (the most commonly used port for Oracle), and the SID is orcl (standard Oracle SID):
var
UniConnection: TUniConnection;
. . .
UniConnection.Username := 'Scott';
UniConnection.Password := 'tiger';
UniConnection.Server := '205.227.44.44:1521:orcl';
UniConnection.SpecificOptions.Values['Direct'] := 'True';
UniConnection.Connect;connecting to Oracle with Service Name:
...
UniConnection.Server := '205.227.44.44:1521/orcl';
...
or
...
UniConnection.Server := '205.227.44.44:1521:sn=orcl';
...
connecting to Oracle with SID:
...
UniConnection.Server := '205.227.44.44:1521:orcl';
...
or
...
UniConnection.Server := '205.227.44.44:1521:sid=orcl';
...
This is all you need to do to enable the Direct mode in your application. You do not have to rewrite other parts of your code.
To return to the OCI mode, set UniConnection.SpecificOptions.Values['Direct'] to 'False' and UniConnection.Server to the TNS name of your server.
You can connect to Multi-Threaded Server using the Direct mode. The server must be configured to use a specific port and the TTC protocol. This can help you avoid firewall conflicts.
Note: The Direct mode is available in UniDAC Professional Edition and UniDAC Trial. An attempt to set the UniConnection.SpecificOptions.Values['Direct'] property to 'True' in UniDAC Standard Edition will generate a "Feature is not supported" error.
Applications that use the Client mode and those that use the Direct mode have similar performance and file size. In terms of security, using the Direct mode is the same as using Oracle Client without Oracle Advanced Security. In the Direct mode, ODAC uses DES authentication and does not support Oracle Advanced Security.
Advantages of the Direct mode:
Limitations of the Direct mode:
A connection in the Direct mode is managed transparently by an instance of TUniConnection, and you can easily switch back to OCI in the Client mode at any time if the above limitations become critical to you.
We tested the Direct mode with all versions of Oracle server for Windows in a local network, but we do not guarantee compatibility with all Oracle servers on other platforms in different networks.
The following table lists the constants for mapping Oracle data types to Delphi data types. See Data Type Mapping for more information.
| Constant | Description |
|---|---|
| oraAnyData | Maps ANYDATA to Delphi data types. |
| oraBFile | Maps BFILE to Delphi data types. |
| oraBinaryDouble | Maps BINARY_DOUBLE to Delphi data types. |
| oraBinaryFloat | Maps BINARY_FLOAT to Delphi data types. |
| oraBlob | Maps BLOB to Delphi data types. |
| oraCFile | Maps CFILE to Delphi data types. |
| oraChar | Maps CHAR to Delphi data types. |
| oraClob | Maps CLOB to Delphi data types. |
| oraCursor | Maps CURSOR to Delphi data types. |
| oraDate | Maps DATE to Delphi data types. |
| oraDoublePrecision | Maps DOUBLE PRECISION to Delphi data types. |
| oraFloat | Maps FLOAT to Delphi data types. |
| oraInteger | Maps INTEGER to Delphi data types. |
| oraIntervalDS | Maps INTERVAL DAY TO SECOND to Delphi data types. |
| oraIntervalYM | Maps INTERVAL YEAR TO MONTH to Delphi data types. |
| oraLabel | Maps MLSLABEL to Delphi data types. |
| oraLong | Maps LONG to Delphi data types. |
| oraLongRaw | Maps LONG RAW to Delphi data types. |
| oraNChar | Maps NCHAR to Delphi data types. |
| oraNClob | Maps NCLOB to Delphi data types. |
| oraNumber | Maps NUMBER to Delphi data types. |
| oraNVarchar2 | Maps NVARCHAR2 to Delphi data types. |
| oraObject | Maps OBJECT to Delphi data types. |
| oraRaw | Maps RAW to Delphi data types. |
| oraReference | Maps REF to Delphi data types. |
| oraRowID | Maps ROWID to Delphi data types. |
| oraTimeStamp | Maps TIMESTAMP to Delphi data types. |
| oraTimeStampWithLocalTimeZone | Maps TIMESTAMP WITH LOCAL TIME ZONE to Delphi data types. |
| oraTimeStampWithTimeZone | Maps TIMESTAMP WITH TIME ZONE to Delphi data types. |
| oraUndefined | Maps UNDEFINED to Delphi data types. |
| oraURowID | Maps UROWID to Delphi data types. |
| oraVarchar2 | Maps VARCHAR2 to Delphi data types. |
| oraXML | Maps XML to Delphi data types. |