Using Oracle Database with Lazarus/Free Pascal.
The famous database from Oracle is available for free for Personal Computers. This tutorial is about the access of Oracle Database 23ai Free from a Lazarus/Free Pascal application. It is supposed that you have installed Oracle Database (and, if you want to use the tutorial sample application as such, the Oracle Sample Database) on your computer; for help, please, have a look at my tutorial Web development environment setup on MS Windows: Oracle database server. The sample application has been developed and tested on Windows 11 Professional; it should also work with the latest releases of Windows 10 Professional (Oracle Database is not supported on Windows Home Edition!). I used Lazarus 3.0 64-bit (with FPC 3.2.2); other, in particular newer versions of Lazarus, should also work fine; the same should be the case for newer versions of Oracle Database.
Accessing Oracle Database from a Lazarus/Free Pascal application has nothing complicated. Lazarus comes with the TOracleConnection component, and using it together with a TSQLTransaction and a TSQLQuery component is more or less the same coding as using MySQL with a TMySQLConnection.
The tutorial shows, how to implement a simple Lazarus application, reading and displaying the last name, first name and job title of accountant employees in the Oracle Sample Database "employees" table. It is supposed that you are familiar with Lazarus and Free Pascal. The tutorial details are limited to the database specific aspects. Click the following link if you want to download the Lazarus/Free Pascal source code of the sample application.
The screenshot below shows the form layout of the application. The program should connect to Oracle Database at startup, and fill in the string grid with the employee data when the "Query" button is pushed. The edit field at the left of the "Query" button is used for success/error messages.
![]() |
The components allowing access to a variety of databases can be found in the Lazarus SQLdb menu. There are 3 of them, that we need to connect to Oracle Database:
- TSQLTransaction. This component encapsulates the transaction on the database server. A TXXXConnection object always needs at least one TSQLTransaction associated with it, so that the transaction of its queries is managed.
- TSQLQuery: This component is a descendant of TDataset, and provides the data as a table from the SQL SELECT query, that you submit. It can also be used to execute other SQL statements (e.g. INSERT INTO, stored procedures...).
- TOracleConnection. This is the component, that takes the requests of the TSQLQuery and TSQLTransaction components and translates them into requests specifically tailored for the database that we are using.
Add theses 3 components to the form as you do with any other items from the Lazarus menus. As you can see on the screenshot, I renamed them respectively to dbSQLTransaction, dbSQLQuery, and dbOracleConnection.
We can "connect" these 3 components by filling in some data into their property sheets.
- In the TOracleConnection object property sheet, set Transaction to the name of the transaction object ("dbSQLTransaction").
- Idem for the TSQLQuery object: Set Transaction to the name of the transaction object ("dbSQLTransaction"). Doing so, its Database property should be automatically set to the name of the TOracleConnection object's name ("dbOracleConnection").
- The Database property of the TSQLTransaction object should now also be set to the name of the TOracleConnection object's name ("dbOracleConnection").
As I said above, the application should connect to the Oracle Sample Database when it starts up. Thus, the connection related code has to be part of the
TfOracle.FormCreate method ("fOracle" being the name of my form). Here is the source:
procedure TfOracle.FormCreate(Sender: TObject);
begin
if dbOracleConnection.Connected then
dbOracleConnection.Close;
// Set the connection parameters
dbOracleConnection.HostName := 'localhost';
dbOracleConnection.UserName := 'aly';
dbOracleConnection.Password := 'oracle';
dbOracleConnection.DatabaseName := 'FREEPDB1';
// Connect to the database
try
dbOracleConnection.Open;
edMess.Color := clDefault;
edMess.Text := 'Connected to Oracle SAMPLE database';
except
on E: ESQLDatabaseError do begin
edMess.Color := clRed;
edMess.Text := E.Message;
end;
end;
end;
To connect to the database, use the method TOracleConnection.Open. Obvious, that before doing so, you have to set the connection parameters. I do it here in the program source; an alternative is to do it in the component's property sheet. Making the method part of a try ... except statement, we can use ESQLDatabaseError to catch the error message in the case where the connection fails.
The employees, who work in accounting, should be displayed when we push the "Query" button. Here is the source of the
TfOracle.btQueryClick method ("fOracle" being the name of my form; "btQuery" being the name of the button):
procedure TfOracle.btQueryClick(Sender: TObject);
var
Count: Integer;
Query: string;
begin
if dbOracleConnection.Connected then begin
sgEmployees.Clean(0, 1, 2, 8, []);
// Create query
Query := 'SELECT last_name AS LName, first_name AS FName, job_title AS Job FROM employees ';
Query += 'WHERE job_title LIKE ''%Account%'' ';
Query += 'ORDER BY last_name, first_name';
// Query the database
dbSQLQuery.SQL.Text := Query; Count := 0;
try
dbSQLQuery.Open;
dbSQLQuery.First;
while not dbSQLQuery.EOF do begin
Inc(Count);
sgEmployees.Cells[0, Count] := dbSQLQuery.FieldByName('LName').AsString;
sgEmployees.Cells[1, Count] := dbSQLQuery.FieldByName('FName').AsString;
sgEmployees.Cells[2, Count] := dbSQLQuery.FieldByName('Job').AsString;
dbSQLQuery.Next;
end;
dbSQLQuery.Close;
// Display count of employees
if Count = 0 then
edMess.Text := 'No employees found in database!'
else
edMess.Text := 'Number of accounting employees in database = ' + IntToStr(Count);
edMess.Color := clDefault;
except
on E: ESQLDatabaseError do begin
edMess.Text := E.Message;
edMess.Color := clRed;
end;
end;
end;
end;
To execute a SELECT query, set the TSQLQuery.SQL.Text property to the SQL statement, then use the method TSQLQuery.Open to start the query. The rows of the result dataset can be retrieved using the methods TSQLQuery.First and TSQLQuery.Next. As before, ESQLDatabaseError can be used to catch the error message if the query fails.
Finally, when pushing the "Exit" button, the application should disconnect from the database. Here is the source of the TfOracle.btExitClick
method ("fOracle" being the name of my form; "btExit" being the name of the button):
procedure TfOracle.btExitClick(Sender: TObject);
begin
if dbOracleConnection.Connected then
dbOracleConnection.Close;
Close;
end;
The screenshot on the left shows how I tried to access the database using a bad password. The screenshot on the right shows the successful query of the accountant employees' data.
![]() |
![]() |
If you find this text helpful, please, support me and this website by signing my guestbook.