Main features

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 Int64 for 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 Variant type (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 TNSNAME.ORA file; 
  • 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;
  • TQuery emulation 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.

End-user distribution

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.

Units presentation

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 SynPDF);
- 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 architecture.

Classes involved

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 TSQLDBConnection classes:

And finally, it does rely on TSQLDBStatement classes to implement the actual SQL request.

Sample code

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 external databases.
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.