SynDBOracle: Open Source native Oracle access
(this is an update of the article published in 2011/07)
For our mORMot framework, and in completion to our SynOleDB unit, we added a new Open Source unit, named SynDBOracle. It allows direct access to any remote Oracle server, using the Oracle Call Interface.
Oracle Call Interface (OCI) is the most comprehensive, high
performance, native unmanaged interface to the Oracle Database that exposes the
full power of the Oracle Database. We wrote a direct call of the
oci.dll library, using our DB abstraction classes introduced for
We tried to implement all best-practice patterns detailed in the official Building High Performance Drivers for Oracle document
Resulting speed is quite impressive: for all requests, SynDBOracle is 3 to 5 times faster than a SynOleDB connection using the native OleDB Provider supplied by Oracle. We noted also that our implementation is 10 times faster than the one provided with ZEOS/ZDBC, which is far from optimized.
You can use the latest version of the Oracle Instant Client
provided by Oracle - see this
link - which allows you to run your applications without installing
the standard (huge) Oracle client or having an
dll files in the same directory than your application,
and it will work.
Here are the main features of this unit:
- Direct access to the Oracle Call Interface (OCI) client, with no BDE, Midas, DBExpress, nor OleDB or ODBC provider necessary;
- Dedicated to work with any version of the Oracle OCI interface, starting from revision 8;
- Optimized for the latest features of Oracle 11g (e.g. using native
Int64for retrieving NUMBER fields with no decimal);
- Able to work with the Oracle Instant Client for No Setup applications;
- Natively Unicode (uses internal UTF-8 encoding), for all version of Delphi, with special handling of each database char-set;
- Tried to achieve best performance available from every version of the Oracle client, and the lowest memory use;
- Designed to work under any version of Windows, either in 32 or 64 bit architecture;
- Late-binding access to column names, using a new dedicated
Varianttype (similar to Ole Automation runtime properties);
- Connections are multi-thread ready with low memory and CPU resource overhead;
- Can use connection strings like
'//host[:port]/[service_name]', avoiding use of the
- Use Rows Array and BLOB fetching, for best performance (ZEOS/ZDBC did not handle this, for instance);
- Very fast insertion, deletion or updates, using batch-mode sending (aka Array Binding) - more than 50,000 inserts per second;
TQueryemulation class, for direct re-use with existing code, in replacement to the BDE;
- Handle Prepared Statements - but by default, we rely on OCI-side statement cache, if available;
- Native export to JSON methods, which will be the main entry point for our mORMot framework;
- By changing one class, you can switch to another SynDB*.pas unit, like OleDB, ODBC, or even a direct SQLite3 embedded database;
- Compatible with Delphi 5 up to XE3;
- Since it doesn't use the DB unit, nor DBExpress or such other technologies, works with any edition of Delphi (even Delphi XE/XE2/XE3 Stater or Delphi 7 Personal);
- Open Source, released under a MPL/GPL/LGPL license.
In comparison to the the default Delphi database implementations, here is how our SynDBOracle classes can be distributed, on the customer side:
It appears that one of the most difficult support issue with Oracle, which
is the client itself, is solved by our classes.
Using the official Oracle Instant Client is a much better option that using the .dcu provided by some vendors, which are in fact a deprecated OCI client for Oracle 8.
Here are the units involved in this library:
SynCommons is used for all low-level stuff (like UTF-8 or
dynamic arrays), and is common to all our units (including mORMot or
SynDB is the main unit, providing abstract classes to implement
connection properties, connections, and statements;
SynDBOracle will implement all Oracle specific classes, hidding
most of the complexity of the OCI interface;
SynOleDB is not required, but can be used to connect to other
databases (like Microsoft SQL Server), from the same class
First of all, the solution expect to use a
TSQLDBConnectionProperties sub-class, which will contain all
connection settings (server, user and password, general parameters like
CodePage or custom internal buffer size).
Here are the classes handling the connection properties:
Then you can open a per-thread connection using those
And finally, it does rely on
TSQLDBStatement classes to
implement the actual SQL request.
Once you have a
TOleDBConnectionProperties instance, you can
execute a statement on it directly, as such:
procedure Test(Props: TOleDBConnectionProperties; const aName: RawUTF8); var I: ISQLDBRows; Customer: Variant; begin I := Props.Execute('select * from Domain.Customers where Name=?',[aName],@Customer); while I.Step do writeln(Customer.Name,' ',Customer.FirstName,' ',Customer.Address); end;
var Props: TOleDBConnectionProperties; begin Props := TSQLDBOracleConnectionProperties.Create( 'TnsName','UserName','Password',CODEPAGE_US); try Test(Props,'Smith'); finally Props.Free; end; end;
I think this above code is not difficult to follow...
The late-binding of column names via our custom Variant type allows to write code using expressions like
Customer.Name - Delphi can be quite
powerful, can't it?
Of course, our mORMot framework uses this unit for direct access to
Oracle, for its ORM feature for
But you do not need to use the mORMot's ORM: our SynDB*.pas units are designed to be used stand-alone.
This unit is now in stable state, and is available as part as mORMot.
Feedback and comments are welcome on our forum, just as usual.