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.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	✔	Callers are generated, package members as PackageMember. See below
Stored procedure result models	✘	See below
Synonyms	✔	Private synonyms in the current schema. Opt in with FilterSettings.IncludeSynonyms = true. See below
Multiple schemas per run	✘	See above
Temporal tables	n/a

Synonyms

With FilterSettings.IncludeSynonyms = true, the private synonyms owned by the schema you connect as are read, and each comes through under the synonym's name. This needs v4.0.53 of the template and efrpg 1.3.0 or later (dotnet tool update -g Efrpg); with anything older the setting has no effect on Oracle.

A synonym for	Is generated as
A table	An entity mapped to the synonym, with its target's columns and primary key
A view	A keyless entity, mapped with ToView
A procedure or function	A method named after the synonym
A package	One method per member, named after the synonym and the member, e.g. OrdersApiCancelOrder

Foreign keys are remapped to the synonym names on both sides, so a relationship between two tables reached through synonyms, or between one of your own tables and a synonym, gets its navigation properties. That covers the usual arrangement: an application schema that owns little and reaches another schema's objects through synonyms can be generated from in one pass.

Not covered yet:

• PUBLIC synonyms. A schema can see thousands of them.
• Synonyms over a database link (ALL_SYNONYMS.DB_LINK set). Their target lives in a remote dictionary.
• Synonyms for synonyms. Only one level is resolved.
• Indexes and comments behind a synonym. The entity has its target's keys, but no HasIndex and no comments.
• Triggers on a table in another schema. ALL_TRIGGERS hides them unless you have CREATE ANY TRIGGER, so such an entity gets no HasTrigger.
• A constraint reachable under more than one pair of names, such as a table with two synonyms, generates one relationship: through your own table where there is one, otherwise the alphabetically first synonym.

For anything not covered, the earlier workarounds still apply. Generate against the schema that owns the object, with a second .tt file as described above, or create a view over the synonym in your own schema. Views are read normally, but Oracle views carry no primary key, so tell the generator which columns identify a row - see Settings.ViewProcessing in the Settings Callbacks.

Stored procedures

A procedure is called with an anonymous PL/SQL block through Database.ExecuteSqlRaw:

Database.ExecuteSqlRaw("BEGIN \"EFRPGTEST\".\"COUNT_CHILDREN\"(:PARENT_KEY_ONE, :CHILD_COUNT, :RUNNING_TOTAL); END;", ...);

OUT and IN OUT parameters are out and ref arguments. The method returns int, the same shape as on SQL Server, but always 0: an Oracle procedure has no return status, and a failure throws.

Package members are generated as PackageMember methods: ORDER_PKG.TOUCH_PARENT becomes OrderPkgTouchParent. A package function is mapped with [DbFunction("\"SCHEMA\".\"PKG\".\"FUNC\"", IsBuiltIn = true)], because EF would otherwise quote PKG.FUNC as a single identifier.

A SYS_REFCURSOR OUT parameter comes back as an out object holding the OracleRefCursor. It can only be read while the connection is still open, so open it first:

db.Database.OpenConnection();
try
{
    db.GetChildrenByParent(1, 1, out var results);
    using var reader = ((OracleRefCursor) results).GetDataReader();
    while (reader.Read())
    {
        // ...
    }
}
finally
{
    db.Database.CloseConnection();
}

Versions before v4.0.53 generated SQL Server's EXEC syntax here, which did not compile.

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 local time zone	DateTime
timestamp with time zone	DateTimeOffset, the only type Oracle.EntityFrameworkCore maps it to
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