Introduction

This article details points of interests when using an Oracle Database as the persistence layer of a CodeFluent generated applications. Thanks to its Oracle Database Producer, CodeFluent can generate an Oracle Database which can be consumed using a generated Business Object Model (BOM). Please note that the BOM is independent from the persistence layer: the source code of the BOM will be the same wether you're using a Microsoft SQL Server database or an Oracle Database.

Pre-Requisites

Before being able to produce or consume your database you must make sure your environment meets those pre-requisites:

Server: Oracle Database 10 or upper must be installed,
Client: Oracle Instant Client (OCI) is always needed.

Regarding .NET, Oracle Data Provider for .NET (ODP.NET) is supported at production and execution. On the other hand, System.Data.OracleClient is also supported but only at production time. At runtime, the BOM will need ODP.NET.

Consequently, clients must have OCI and ODP.NET on their machines.

Installing Oracle Components

Oracle Instant Client (OCI)

This is the basic and compulsory method to access the server using a client. It's a set of native dlls which can be retrieved from Oracle's web site (e.g. http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html). They are shipped as an archive which needs to be uncompressed on the machine.

Note: Basic lite versions are sufficient such as instantclient-basiclite-win32-11.0.7.0.zip

Oracle Data Provider for .NET (ODP.NET)

This is the most standard data access method in .NET and is provided by Oracle.

Several ways are available to install ODP.NET on its machine. Among the different means, one can download the ODAC110621Xcopy.zip (or upper) available here: http://www.oracle.com/technology/software/tech/windows/odpnet/utilsoft.html

Foreach .NET application (Web site, Windows Forms, WPF, etc.) you'll have to reference this library on top in addition to the CodeFluent runtime.

It's not required to deploy this dll in the Global Assembly Cache, in fact, doing so might complexify future upgrades, however you need to ensure your application has access to that assembly (Oracle.DataAccess.dll).

The ODP.NET assembly is located in the odp.net20\odp.net\bin\2.x folder.

Note: ODP.NET 1.x isn't supported by CodeFluent.

Note 2: If one desires a native execution on a 64 bits platform (not to be confused with a WOW execution which is a 32 bit process running on a 64 bits platform), one needs to install a 64 bits OCI version.

Note 3: Other providers than the ODP.NET exist (please note that OCI is still and always required), however they're not supported by CodeFluent. Feel free to contact us for a specific request on that matter.

Note 4: The .NET Framework ships an Oracle provider (please note that OCI is still required). However this provider was declared obsolete by Microsoft (http://msdn.microsoft.com/en-us/library/77d8yct7(v=VS.100).aspx).

Still, this provider can be used by the Oracle Database Producer. Nevertheless, if ever DBA privileges are needed (which is not supported by this provider) or if blob instances have to be manipulated, the producer will use ODP.NET. Finally, this provider is not supported at runtime by CodeFluent generated applications.

Architecture

Here's a figure summing-up the architecture:

Click to Enlarge

Using the Oracle Database Producer

All information regarding the use of the CodeFluent producer (pre-requisites, configuration, features, etc.) is detailed in the Oracle Database Producer article.

Using an Oracle Database as the persistence layer

The generated Business Object Model (BOM) is independent from the persistence layer, that is to say that its generated source code is the same wether a Microsoft SQL Server or an Oracle Database is used as the persistence layer. However, for this to be possible, the application must be correctly configured.

For information on how to configure its application to consume an Oracle Database can be found in the Application Configuration article.

Note: As of today, the Oracle Database Producer doesn't support all features supported by the SQL Server Producer. Among those yet-to-be-shipped features are the following:

Differential engine:

The differential engine that is available in the SQL Server Producer, isn't available yet for the Oracle Database platform. Ergo, tables are dropped and then created at each generation. However, since the instances concept is supported, the database can be populated with data at each generation.

Patterns:

The HierarchyDeepLoad and TextSearch patterns are not supported by the Oracle Database Producer for the moment.

Search CFQL method:
The Search CFQL method isn't supported yet by the Oracle Database Producer.

Modeling PL/SQL Code

CodeFluent allows developers to add platform specific code in models. Consequently, a given model supports specific PL/SQL Oracle code; just as well it supports specific T-SQL SQL Server code. Moreover, CodeFluent models can support simultaneously both platform codes.

Example:

Non Portable Method	Copy Code
<SysInfo> ... <cf:method name="GetServerProcessId" body="raw(string name)" returnTypeName="int"> SELECT spid FROM sys.sysprocesses WHERE loginame = @name </cf:method> ... </SysInfo>

The method above will be generated "as is" and will obviously not run on a Oracle Database platform.

Here is the same method, re-written to be portable, by using the canonical declaration form of a method body.

Portable Method	Copy Code
<SysInfo> ... <cf:method name="GetServerProcessId" returnTypeName="int"> <cf:body language="tsql" body="raw(string name)" > SELECT spid FROM sys.sysprocesses WHERE loginame = @name </cf:body> <cf:body language="psql" body="raw(string name)" > OPEN V_CF_CURSOR FOR SELECT SID FROM V$SESSION WHERE USERNAME = "#name"; </cf:body> </cf:method> ... </SysInfo>

Portable Method

Copy Code

<SysInfo>
...
 <cf:method name="GetServerProcessId" returnTypeName="int">
   <cf:body language="tsql" body="raw(string name)" >
      SELECT spid FROM sys.sysprocesses WHERE loginame = @name
   </cf:body>
   <cf:body language="psql" body="raw(string name)" >
      OPEN V_CF_CURSOR FOR SELECT SID FROM V$SESSION WHERE USERNAME = "#name";
   </cf:body>
 </cf:method>
...
</SysInfo>

We can see that each method body declares its corresponding code language. tsql and psql are keywords understood by the Microsoft SQL Server Producer, and the Oracle Database Producer respectively.

PL-SQL stored procedures issuing SELECT statements must add the OPEN V_CF_CURSOR FOR (name and case must be respected) before the SELECT. The actual declaration of the variable is automatically added to all generated procedures. For instance, here is the generated code corresponding to the GetServerProcessId method declared in our previous example:

Generated PL/SQL Stored Procedure	Copy Code
PROCEDURE GetServerProcessId(CF_CURSOR OUT "TEST"."CF_#Runtime".CF_CURSOR_TYPE, "#name" VARCHAR2) AS V_CF_CURSOR "TEST"."CF_#Runtime".CF_CURSOR_TYPE; BEGIN OPEN V_CF_CURSOR FOR SELECT SID FROM V$SESSION WHERE USERNAME = "#name"; CF_CURSOR := V_CF_CURSOR; END;

Generated PL/SQL Stored Procedure