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.

Info

LLBLGen Pro supports Oracle through two ADO.NET providers, ODP.NET and System.Data.Oracle. It's recommended you use the ODP.NET driver and not the System.Data.Oracle driver.

The managed provider (available in ODP.NET 12g+) is chosen first, if not found, the unmanaged provider is chosen.

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.

  • 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"