How to Connect to Oracle From Delphi With UniDAC

In the development of Delphi applications, a reliable connection to external databases is essential. UniDAC is a perfect solution for secure and high-performance integrations with advanced data platforms, such as Oracle. Through UniDAC, you can efficiently access your Oracle data sources, accelerating Delphi application development.

What is UniDAC

UniDAC (Universal Data Access Components) is a library of data access components developed by Devart for such rapid application development (RAD) tools as Delphi, C++Builder, and Lazarus. With its unified interface for connecting to various data sources, including Oracle databases, UniDAC provides developers with an effective environment for building modern data-driven software solutions that require specific data storage layers.

Connect to Oracle from Delphi via UniDAC

Configure the connection to Oracle via UniDAC

1. Start your RAD Studio.
2. Go to File > New > Windows VCL Application – Delphi to create a new Delphi project.

3. Add the TUniConnection component:
   • 3.1 Navigate to the component Palette and find the UniDAC category.
   • 3.2 Drag and drop the TUniConnection component onto the Form Designer.

4. Double-click the UniConnection1 icon on the form, and in the dialog that opens, configure the following TUniConnection properties:
   • In the Provider dropdown menu, select Oracle.
   • In the Server field, enter your Oracle server address.
   • In the Username field, enter your Oracle username.
   • In the Password field, enter your Oracle password.

5. Navigate to the Options tab of the UniConnection1 dialog and set the Direct option to True to enable access in Direct mode.

6. Click Connect.

Configure the connection from the Code Editor

You can use the TUniQuery or TUniTable UniDAC components to interact with Oracle by executing SQL queries and accessing data. Below is an example of how to configure a connection to an Oracle database in your code.

procedure TForm1.ConnectToOracle;
begin 
  UniConnection1.ProviderName := 'Oracle';
  UniConnection1.Server := 'your-oracle-server-address';
  UniConnection1.Username := 'your-username';
  UniConnection1.Password := 'your-password';
  UniConnection.SpecificOptions.Values['Direct'] := 'true';
try
  UniConnection1.Connect;
  ShowMessage('Connection successful!');
except
  on E: Exception do
    ShowMessage('Failed to connect: ' + E.Message);
  end;
end;

You can place this code within a method that you call when your form is created or when a specific event is triggered.

Universal Delphi Data Access Components

Access various databases and clouds from Delphi with a universal tool for Delphi developers.
Powerful functionality with fast and reliable support.

Download free trial

How to retrieve Oracle data via UniDAC

UniDAC fully supports SQL syntax in CRUD (Create, Read, Update, Delete) operations against Oracle databases.

To retrieve data from an Oracle database:

1. Add the TUniQuery and TUniDataSource components to the Form Designer. Go to the component Palette and find the UniDAC category. Then, drag and drop the components onto your form.
2. Add the TOracleUniProvider component from the UniDAC Providers category.
3. Similarly, add the TButton component from the Standard category and TDBGrid from the Data Controls category.

4. Double-click the UniQuery1 label in the Form Designer and enter a query to select the data from an Oracle database. You can use SQL syntax directly:

SELECT * FROM DEPT;

5. Click OK to save changes and close the dialog.

6. Click the DBGrid1 label in the Form Designer and set the DataSource property to UniDataSource1 in the Object Inspector.
7. Click the UniDataSource1 label in the Form Designer and set the DataSet property to UniQuery1 in the Object Inspector.
8. Call the Open method in your application to execute the SQL query: double-click the Button1 label, and in the Code Editor that opens, add the following code:

UniQuery1.Open;

The complete procedure code should look like this:

procedure TForm2.Button1Click(Sender: TObject);
begin
  UniQuery1.Open
end;

9. Click Run.
10. In the window that opens, click the button to display the result.

How to manipulate Oracle data via UniDAC

When you integrate your Oracle databases via UniDAC, you can manipulate Oracle data directly within your Delphi project.

Data manipulation in SQL Editor

You can insert, update, or delete data visually in RAD Studio using the SQL Editor feature:

1. Click the UniQuery label on the form.
2. In the Object Inspector, locate the SQL property.
3. Click the ellipsis (...) next to the SQL property to open the SQL editor.
4. Enter the INSERT, UPDATE, or DELETE query into the SQL editor and click OK.

5. Execute the query by setting the Active property of TUniQuery to True in the Object Inspector.

Data manipulation using code

When working with Oracle data via UniDAC, you can execute CRUD operations using standard SQL commands.

INSERT data

UniQuery1.SQL.Text := 'INSERT INTO DEPT (DEPTNO, DNAME, LOC) ' +
                      'VALUES (60, ''FINANCE'', ''NEW YORK'');';

UniQuery1.Execute;

UPDATE data

UniQuery1.SQL.Text := 'UPDATE DEPT SET LOC = ''NEW YORK'' WHERE DNAME
= ''FINANCE'';';

UniQuery1.Execute;

DELETE data

UniQuery1.SQL.Text := 'DELETE FROM DEPT WHERE DNAME = ''FINANCE'';';

UniQuery1.Execute;

Handling Oracle-specific features

In addition to the above-mentioned Direct mode, which requires no client software to be installed, UniDAC supports other useful Oracle-specific features.

Support for Oracle RAC servers

Oracle RAC (Real Application Clusters) represents cluster software that enables server clustering to create highly available database environments. UniDAC has built-in support for connecting to Oracle RAC in Delphi applications.

To connect to a RAC server using UniDAC, follow the same procedure recommended for connecting to an Oracle server in Direct mode, however, use your RAC server address when filling in the connection information in the TUniConnection window. Alternatively, you can connect to your RAC server using the Code Editor as follows:

procedure TForm1.ConnectToOracleRAC;
begin 
  UniConnection1.ProviderName := 'Oracle';
  UniConnection1.Server := 'your-oracle-rac-server-address';
  UniConnection1.Username := 'your-username';
  UniConnection1.Password := 'your-password';
  UniConnection.SpecificOptions.Values['Direct'] := 'true';
try
  UniConnection1.Connect;
  ShowMessage('Connection successful!');
except
  on E: Exception do
    ShowMessage('Failed to connect: ' + E.Message);
  end;
end;

Support for OS authentication

Oracle supports operating system authentication (OS authentication) that allows you to connect to the Oracle database using your OS credentials rather than providing a separate username and password. When you connect to Oracle via UniDAC, you can benefit from OS authentication, too, and use your Windows/Linux credentials to access your Oracle server.

Secure connection with SSL, SSH, and HTTP/HTTPS tunneling

Besides connecting to the Oracle server via TCP/IP in Direct mode, you can take advantage of connection methods that ensure stronger security: SSL, SSH, and HTTP/HTTPS tunneling. UniDAC supports all these methods, allowing you to establish a secure connection to Oracle over untrusted networks.

Connecting via SSL

To connect to an Oracle server via SSL, double-click the UniConnection1 label, navigate to the Options tab, select Oracle from the Provider list, and specify the locations of your SSL key and certificate files.

Then, enter your Oracle credentials in the Connect tab and establish the connection.

Connecting via SSH

To use SSH as a method of establishing an encrypted connection to your Oracle server, you need an SSH client and an SSH server. You can create them using SecureBridge, Devart's solution that embeds the SSH functionality into your Delphi application and configures an SSH server. Before establishing an SSH connection, make sure you have performed the following steps:

1. Install SecureBridge.
2. Install the TCRSSHIOHandler component in RAD Studio.

To establish an SSH connection to Oracle, follow the steps below:

1. Create a new Delphi project in RAD Studio.
2. Add the following components to the Form Designer:
   • TDBGrid
   • TButton
   • TCRSSHIOHandler
   • TOracleUniProvider
   • TUniConnection
   • TUniDataSource
   • TUniQuery
   • TScFileStorage
   • TScSSHClient
3. Click the CRSSHIOHandler label and set the Client property to ScSSHClient1 in the Object Inspector.
4. Click the DBGrid label and set the DataSource property to UniDataSource1.
5. Click the UniDataSource label and set the DataSet property to UniQuery1.
6. Click the ScFileStorage label and specify the location of SSH keys in the Path property.
7. Click the ScSSHClient label and set the following properties:
   • Authentication: Define the authentication method applicable for your SSH server, atPassword or atPublicKey.
   • HostKeyName: Specify the filename of the SSH server public key.
   • Hostname: Enter the host name or IP address of the SSH server.
   • KeyStorage: Use the ScFileStorage1 value.
   • Password: Enter your password for the SSH server account.
   • Port: Specify the SSH port.
   • PrivateKeyName: Specify the filename of the client private key, if the atPublicKey authentication method is chosen.
   • User: Specify the username for the SSH server account.
8. Click the UniConnection label and set the IOHandler property to CRSSHIOHandler1.
9. Click the UniQuery label and set the Connection property to UniConnection1.
10. Double-click the UniConnection component and fill in the Oracle server connection details in the Connect tab.
11. Open the Options tab and set the Direct options to True.
12. Click Connect to establish the connection, then click OK to close the dialog.

HTTP/HTTPS tunneling

When you cannot connect to the Oracle server directly due to security restrictions, you can use an HTTP or HTTPS tunnel to connect.

Connecting via an HTTP tunnel

UniDAC supports HTTP tunneling with a PHP script, tunnel.php, which you can find in the HTTP subfolder of your UniDAC folder: %Program Files%\Devart\UniDac for Delphi X\HTTP\tunnel.php. Deploy the script before establishing a connection.

In the UniConnection options, set the Direct option to True and specify the tunneling script URL in the HttpUrl option. If the access to the website folder with the tunneling script is only available for registered users, enter your username and password in the HttpUsername and HttpPassword options.

Now you can connect to the Oracle server via HTTP.

Connecting via an HTTP/HTTPS tunnel and a proxy

If the client and the server are on different networks, you need to use a proxy to establish a connection. Navigate to the Options tab of the UniConnection window and configure the following proxy-specific options in addition to your HTTP/HTTPS details:

• ProxyHostname: Proxy hostname.
• ProxyPort: Proxy port.
• ProxyUsername: Proxy username.
• ProxyPassword: Proxy password.

Support for DML arrays

In UniDAC, you can take advantage of DML array binding, an Oracle's ability to execute data manipulation statements with multiple arrays of parameters in a single operation. You can prepare parameter arrays and then pass them in a single statement. For example, the following INSERT statement uses parameters instead of values:

INSERT INTO DEPT VALUES(:deptno_p, :dname_p, :loc_p);

If you define multiple values for each parameter, you can insert all of them by executing the above INSERT statement only once.

Support for Oracle alerts and pipes with the TUniAlerter component

In Oracle, alerts and pipes are methods of real-time communication between applications. An alert notifies an Oracle session about a predefined event occurring in the database. A pipe serves as a channel for sending and receiving messages between Oracle sessions.

UniDAC provides the TUniAlerter component that enables subscription to Oracle alerts and pipes for the application to respond to certain events. You can set up alerts and pipes by defining the list of Oracle alerts and/or pipes you wish to subscribe to in the Events property of the TUniAlerter component. Then, your application will receive an alert whenever one of the specified events occurs.

Support for Oracle scrollable cursor

A scrollable cursor allows you to navigate a database in both forward and backward directions, as opposed to the traditional cursor that only permits forward fetching of table rows. With the scrollable cursor enabled, you can fetch either the next or the previous row, as well as the first or the last row of the database table.

UniDAC supports scrollable cursors by providing the ScrollableCursor option in the configuration of the UniQuery, UniTable, and UniStoredProc components. When this option is set to True, the scrollable cursor is enabled.

Conclusion

UniDAC provides direct high-performance access to Oracle databases, enabling their use in the creation of applications in Delphi and C++. Through the support of SQL, it allows developers to interact with Oracle databases via SQL queries, improving their efficiency and creating an intuitive development environment. Follow the guidance in this article to get started with UniDAC and establish a fully-functional connection to your Oracle databases.