Oracle

Oracle

Fully supported from v4. 🚀

In v3 Oracle was under development and needed Oracle.ManagedDataAccess.dll installed into the GAC plus an ODP.NET entry in machine.config. None of that applies now. The efrpg tool carries Oracle.ManagedDataAccess.Core as an ordinary NuGet dependency:

dotnet tool install -g Efrpg

If you followed the v3 instructions, you can gacutil /u Oracle.ManagedDataAccess and delete the ODP.NET, Managed Driver entry you added to machine.config. Nothing in v4 reads them. See Upgrading from v3 to v4.

Settings

Settings.DatabaseType     = DatabaseType.Oracle;
Settings.TemplateType     = TemplateType.EfCore10;   // or EfCore9, EfCore8
Settings.GeneratorType    = GeneratorType.EfCore;
Settings.ConnectionString = "User Id=northwind;Password=***;Data Source=localhost:1521/pdb1;";

Your project needs the Oracle.EntityFrameworkCore NuGet package. The generated OnConfiguring calls optionsBuilder.UseOracle(...).

The user is the schema

Oracle has no Database= or Schema= connection string parameter. Whoever you connect as determines what is read: every query is scoped to SYS_CONTEXT('USERENV','CURRENT_SCHEMA').

Connect as the owner of the schema you want to reverse engineer. Connecting as SYSTEM and expecting to see the NORTHWIND schema will not work.

Only one schema per run is read. SQL Server and PostgreSQL span schemas in a single pass; Oracle does not. If you need two schemas, use two .tt files, one per schema, each with its own connection string and its own Settings.DbContextName, and set Settings.PocoNamespace so they do not collide.

Connection string shapes

// Easy Connect - host:port/service_name. Simplest, no tnsnames.ora required.
"User Id=northwind;Password=***;Data Source=db.example.com:1521/ORCLPDB1;"

// TNS alias, resolved from tnsnames.ora in the folder named by the TNS_ADMIN environment variable
"User Id=northwind;Password=***;Data Source=MYALIAS;"

// Full descriptor inline
"User Id=northwind;Password=***;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDB1)));"

// Autonomous Database with a wallet. Point TNS_ADMIN at the unzipped wallet folder first.
"User Id=northwind;Password=***;Data Source=mydb_high;"

Oracle.ManagedDataAccess.Core is fully managed, so there is no Oracle Client to install. It does read TNS_ADMIN from the environment, and the efrpg tool inherits the environment of whatever launched it. If a TNS alias resolves in a terminal but not from Visual Studio, restart Visual Studio so it picks up the environment variable.

Permissions

GRANT CREATE SESSION TO your_schema_user;

plus visibility of the objects you want to read. The ALL_* dictionary views are granted to PUBLIC on a stock install, and they only show objects the connected user can already see. In practice, connecting as the schema owner with CREATE SESSION is the normal and simplest arrangement.

What gets read

Feature	Supported	Notes
Tables, views, columns	✔	Current schema only
Primary and foreign keys	✔	Including composite
Indexes	✔
Identity columns	✔	GENERATED ... AS IDENTITY
Virtual (computed) columns	✔	The default is suppressed, as with SQL Server computed columns
Column defaults	✔
Column comments	✔	ALL_COL_COMMENTS is read and emitted as an XML doc comment
Sequences	✔	From ALL_SEQUENCES. Wire them to columns with Settings.HiLoSequences
Triggers	✔
Packages, procedures, functions	✔	Signatures and parameters, so callers are generated
Stored procedure result models	✘	See below
Synonyms	✘	Not read. See below
Multiple schemas per run	✘	See above
Temporal tables	n/a

Synonyms

Oracle synonyms are not read. FilterSettings.IncludeSynonyms is supported on SQL Server only, and setting it has no effect here: a synonym will not appear in the generated model, and neither will the object behind it unless that object is itself in the schema you are generating from. Tracked as issue #886.

If you rely on synonyms to reach a table, you have two options:

• Generate against the schema that owns the object. Use a second .tt file pointing at that schema, as described above.
• Create a view over the synonym in your own schema. Views are read normally, so the table appears in the model under the view's name. You will need to tell the generator which columns identify a row, because Oracle views carry no primary key - see Settings.ViewProcessing in the Settings Callbacks.

Stored procedure result models

Procedure and function signatures are read and callers are generated, but no return model class is produced. Either map the procedure onto an existing entity:

Settings.StoredProcedureReturnTypes.Add("SALES_BY_YEAR", "SummaryOfSalesByYear");

or write the result class yourself and call it with FromSql.

Case and naming

Oracle folds unquoted identifiers to upper case, so ORDER_LINE_ITEM is what the dictionary holds.

Keep Settings.UsePascalCase = true (the default) and you get OrderLineItem. Two related points:

Filters match the real, upper-case name. RegexIncludeFilter("^Order.*") matches nothing. Use "^ORDER.*", or pass RegexOptions.IgnoreCase.

All-caps names and pluralisation. With pluralisation on, an all-caps CATEGORIES singularises to CATEGORy. Lower-case it first:

Settings.TableRename = delegate(string name, string schema, bool isView)
{
    return Inflector.MakeLowerIfAllCaps(name);
};

Type mapping highlights

Oracle's NUMBER is one type with precision and scale, so the reader maps it by precision:

Oracle	C#
number(1)	bool
number(3)	byte
number(5)	short
number(10)	int
number(19)	long
number (anything else)	decimal
varchar2, nvarchar2, clob, nclob, long	string
blob, raw, long raw	byte[]
date, timestamp, timestamp with time zone	DateTime
interval year to month, interval day to second	decimal
binary_float	float
binary_double	double
xmltype	string
rowid, urowid	string
sdo_geometry	Spatial. Set Settings.DisableGeographyTypes = true to skip

If a number(1) column is really a small integer rather than a flag, override it:

Settings.UpdateColumn = delegate(Column column, Table table, List<EnumDefinition> enumDefinitions, List<JsonColumnMapping> jsonColumnMappings)
{
    if (table.DbName == "ORDERS" && column.DbName == "PRIORITY")
        column.PropertyType = "int";
};

See also

• Connection strings
• Filtering
• Settings Reference
• Upgrading from v3 to v4
• Oracle ODP.NET documentation