Oracle (through ODP.NET)

Requirements

Oracle through ODP.NET support relies on the ODP.NET ADO.NET provider (managed or unmanaged, v10.x or higher) provided by Oracle through OTN. To be able to create a model from an existing Oracle schema, you have to have access to an Oracle instance, version 9i or higher.

Supported features

Feature	Supported
Minimal database version	Oracle 9i
Database types	All built-in types
User defined types	No
Foreign key constraints	Yes
Primary key constraints	Yes
Unique constraints	Yes
Check constraints	No
Default values	Yes
Unique indexes	No
Tables	Yes
Views	Yes
Stored Procedures	Yes, including REF CURSOR output parameters
Table Valued Functions	Yes
(System versioned) Temporal tables	No
Synonyms	Yes
Cascade delete rules	Yes
Cascade update rules	No
Identity fields	Yes (12g or higher)
System sequences	Yes (12g or higher)
Schema based sequences	Yes
Multiple catalogs per project	No
Multiple schemas per catalog	Yes
Linked servers	No

Type mappings

Below you'll find the two type mapping tables used by the Oracle (ODP.NET) driver of LLBLGen Pro. One is used for Database First development, the other is used for Model First development. In case of Model First development, you can add your own type shortcuts to define a different target database type, in case the default mappings aren't what you need in a particular case.

If the target framework is Entity Framework Core 3.x or higher, the driver will use different type mappings, as the Entity Framework Core provider for Oracle maps some types differently. These are specified in the tables below.

Global model-first type overruling

For Model first, there are often multiple database types defined per .NET Type and a given filter. To prefer one over the other on a global level, please look at the following settings in the Relational model data element construction section of the Project Settings:

• Prefer natural character types
• Prefer decimal over currency types
• Prefer variable length types

Database first type mappings

You can control the .NET type during reverse engineering in database first development by defining Type Conversions in the Project Settings, which are based on Type Converters.

These are the conventional mappings used for LLBLGen Pro Runtime Framework and NHibernate.

Database type	.NET Type
BFile	System.Byte[]
BinaryDouble	System.Double
BinaryFloat	System.Single
BinaryInteger	System.Int32
Blob	System.Byte[]
Boolean	System.Byte
Character	System.String
Char	System.String
Clob	System.String
Date	System.DateTime
Decimal	System.Decimal
Dec	System.Decimal
DoublePrecision	System.Decimal
Float4	System.Double
Float(precision) where precision < 15	System.Double
Float(precision) where precision >= 15	System.Decimal
Integer	System.Int32
IntervalDayToSecond	System.TimeSpan
IntervalYearToMonth	System.Int64
Int	System.Int32
LongRaw	System.Byte[]
Long	System.String
NChar	System.String
NClob	System.String
NVarChar2	System.String
NaturalN	System.Int32
Natural	System.Int32
Number(precision,0) where 0 <= precision < 5	System.Int16
Number(precision,0) where 5 <= precision < 10	System.Int32
Number(precision,0) where 10 <= precision < 19	System.Int64
Number(precision,0) where precision >= 19	System.Decimal
Number(precision, scale) where 0 <= precision < 8 and scale > 0	System.Single
Number(precision, scale) where 8 <= precision < 16 and scale > 0	System.Double
Number(precision, scale) where precision >= 16 and scale > 0	System.Decimal
PlsInteger	System.Int32
PositiveN	System.Int32
Positive	System.Int32
Raw	System.Byte[]
Real	System.Single
RefCursor	System.object
RowId	System.String
SignType	System.Int32
SmallInt	System.Int16
String	System.String
TimeStampWithLocalTimeZone	System.DateTime
TimeStampWithTimeZone	System.DateTime
TimeStamp	System.DateTime
URowId	System.String
VarChar2	System.String
VarChar	System.String
XmlType	System.String

The mappings above are also used for Entity Framework Core 3.x+ except the mappings below. The table below shows the differences for Entity Framework Core:

Database type	.NET Type
IntervalYearToMonth	System.String
Number(precision,0) where precision == 1	System.Boolean
Number(precision,0) where 1 < precision < 5	System.Byte
Number(precision,0) where precision == 5	System.Int16
Number(precision,0) where 5 < precision <= 10	System.Int32
Number(precision,0) where 10 < precision <= 19	System.Int64
Number(precision,0) where precision >= 19	System.Decimal
Number(precision, scale) where scale > 0	System.Decimal
TimeStampWithLocalTimeZone	System.DateTimeOffset
TimeStampWithTimeZone	System.DateTimeOffset

Model first type mappings

If specified, the filter is combined with the .NET type to determine the database type. The elements length, precision and scale are the max. length, precision and scale of an entity field with the particular .NET Type. For instance a field with type System.String, max. length set to 50, will with all defaults enabled, a database type of NVarChar(50).

If a controlling setting is specified, that setting has to be set to the specified value (in the Relational model data element construction section of the Project Settings) to make the designer select that database type.

.NET Type	Filter	Database type	Controlling setting and value
System.Byte[]	0 < length < 2000	Raw
System.Byte[]	length==0 or length >= 2000	Blob
System.DateTime		Date
System.Decimal	precision >= 16	Number(precision, scale)
System.Decimal	precision >= 15	Float
System.Double		BinaryDouble
System.Int16		Number(4, 0)
System.Int32		Number(9, 0)
System.Int64		Number(18, 0)
System.Single		BinaryFloat
System.String	length==0 or length >= 4000	Clob
System.String	0 < length <= 2000	Char(length)	Prefer natural character types set to false and Prefer variable length types set to false
System.String	0 < length <= 2000	NChar(length)	Prefer natural character types set to true and Prefer variable length types set to false
System.String	0 < length <= 2000	VarChar2(length)	Prefer natural character types set to false and Prefer variable length types set to true
System.String	0 < length <= 2000	NVarChar2(length)	Prefer natural character types set to true and Prefer variable length types set to true
System.String	2000 < length < 4000	NChar(length)	Prefer variable length types set to false
System.String	2000 < length < 4000	NVarChar2(length)	Prefer variable length types set to true
System.TimeSpan		IntervalDayToSecond

The mappings above are also used for Entity Framework Core 3.x+ except the mappings below. The table below shows the differences for Entity Framework Core:

.NET Type	Filter	Database type	Controlling setting and value
System.DateTimeOffset		TimeStampWithLocalTimeZone
System.Decimal	scale > 0	Number(precision, scale)
System.Decimal	precision > 19	Number(precision, 0)
System.Boolean		Number(1,0)
System.Byte		Number(4, 0)
System.Int16		Number(5, 0)
System.Int32		Number(10, 0)
System.Int64		Number(19, 0)

Supported target frameworks

The following target frameworks are supported with SQL Server:

• LLBLGen Pro Runtime Framework
• Entity Framework Core 3.x+
• NHibernate

Registering ODP.NET in the GAC and machine.config

Starting with ODAC v19, Oracle's 'universal installer' doesn't install the ADO.NET provider dlls in the GAC anymore nor does it add an entry in the .NET's machine.config file for the DbProviderFactory. To fix that please use the following command line statements on an elevated command prompt. You have to replace <your user name> with the username you specified during installation, and the version with the one you're using (e.g. 19.0.0 to 21.0.0)

• Go to: C:\app\client\<your user name>\product\19.0.0\client_1\odp.net\managed\x64
• To register the managed provider in the GAC, type:

OraProvCfg.exe /action:gac /providerpath:"C:\app\client\<your user name>\product\19.0.0\client_1\odp.net\managed\common\Oracle.ManagedDataAccess.dll"

• To register the DbProviderFactory in the machine.config, type:

OraProvCfg.exe /action:config /force /product:odpm /frameworkversion:v4.0.30319 /providerpath:"C:\app\client\<your user name>\product\19.0.0\client_1\odp.net\managed\common\Oracle.ManagedDataAccess.dll"