Migrate the C# data access layer of a single .Postgres-copy project from Oracle (Oracle.ManagedDataAccess) to PostgreSQL (Npgsql). Work item by item through Reports/{ProjectName}/MigrationChecklist.md.
.Postgres project copy exists (created in Phase 5 setup).Reports/{ProjectName}/MigrationChecklist.md exists and is the source of truth for what to change.Reports/{ProjectName}/OracleRiskAnalysis.md exists for cross-referencing behavioral differences.Progress:
- [ ] Step 1: Replace NuGet packages
- [ ] Step 2: Update connection string configuration
- [ ] Step 3: Rewrite ADO.NET type references
- [ ] Step 4: Fix DbType mappings
- [ ] Step 5: Migrate stored procedure invocation
- [ ] Step 6: Address Oracle-specific SQL and syntax
- [ ] Step 7: Build and verify
Step 1: Replace NuGet packages
In the .csproj of the .Postgres project:
Oracle.ManagedDataAccess.Core, Oracle.EntityFrameworkCore (and any other Oracle.* packages)Npgsql (for ADO.NET) and/or Npgsql.EntityFrameworkCore.PostgreSQL (for EF Core)System.Data abstractions (IDbConnection, IDbCommand) are used project-wide, the surface-level code may need fewer changes — identify them first.Step 2: Update connection string configuration
appsettings.json, appsettings.{env}.json, web.config, app.config, or environment variable configuration.Host=localhost;Port=5432;Database=mydb;Username=myuser;Password=mypassword
IConfiguration).OracleConnection). Prefer keeping the same key name to minimize application config changes.Step 3: Rewrite ADO.NET type references
Replace Oracle-specific ADO.NET types with Npgsql equivalents:
| Oracle type | Npgsql replacement |
|---|---|
OracleConnection |
NpgsqlConnection |
OracleCommand |
NpgsqlCommand |
OracleDataReader |
NpgsqlDataReader |
OracleDataAdapter |
NpgsqlDataAdapter |
OracleParameter |
NpgsqlParameter |
OracleTransaction |
NpgsqlTransaction |
OracleException |
NpgsqlException |
OracleDbType |
NpgsqlDbType (from NpgsqlTypes namespace) |
Update using directives accordingly (using Oracle.ManagedDataAccess.Client → using Npgsql).
If the codebase uses IDbConnection/IDbCommand abstractions registered via DI, update only the DI registration and connection string — the consuming code may not need changes.
Step 4: Fix DbType and NpgsqlDbType mappings
Oracle parameter types do not map 1:1 to Npgsql. Review every OracleParameter (now NpgsqlParameter) that sets an explicit type:
| Oracle type | Notes |
|---|---|
OracleDbType.Varchar2 |
Use NpgsqlDbType.Varchar or omit (Npgsql infers from value) |
OracleDbType.Clob |
Use NpgsqlDbType.Text |
OracleDbType.Number |
Use NpgsqlDbType.Numeric or NpgsqlDbType.Integer depending on precision |
OracleDbType.Date |
Use NpgsqlDbType.Date (date only) or NpgsqlDbType.Timestamp (if time component used) |
OracleDbType.TimeStamp |
Use NpgsqlDbType.Timestamp |
OracleDbType.RefCursor |
Use NpgsqlDbType.Refcursor — see Step 5 |
OracleDbType.Char |
Use NpgsqlDbType.Char |
For parameters where Oracle inferred the type from the value, Npgsql also infers — explicit type setting is often unnecessary and can be removed.
Step 5: Migrate stored procedure invocation
Oracle and PostgreSQL stored procedure invocation differ significantly:
CommandType.StoredProcedure for function calls. For procedures that use OUT parameters, PostgreSQL requires CommandType.Text with CALL proc_name(...) syntax in some versions of Npgsql — verify against the target Npgsql version.RETURNS TABLE / RETURNS SETOF, use ExecuteReader() directly — no cursor parameter needed.RETURNS refcursor, call within a transaction, read the cursor name from the output parameter, then issue FETCH ALL IN "<cursor_name>".OracleRefCursor).INOUT or function return values. Verify parameter direction matches the migrated procedure signature.NEXTVAL: Replace SELECT {SEQUENCE}.NEXTVAL FROM DUAL with SELECT nextval('{sequence_name}').@param_name; Oracle used :param_name. Update all parameter name prefixes.Step 6: Address Oracle-specific SQL and C# patterns
Review inline SQL strings and query builders for Oracle-specific constructs and replace:
| Oracle construct | PostgreSQL replacement |
|---|---|
ROWNUM <= n |
LIMIT n |
ROWNUM = 1 |
LIMIT 1 |
NVL(x, y) |
COALESCE(x, y) |
DECODE(expr, v1, r1, ...) |
CASE WHEN expr = v1 THEN r1 ... END |
SYSDATE / SYSTIMESTAMP |
NOW() or CURRENT_TIMESTAMP |
TO_CHAR(date, fmt) |
TO_CHAR(date, fmt) (mostly compatible; verify format strings) |
TO_DATE(str, fmt) |
TO_DATE(str, fmt) (verify format strings) |
TO_NUMBER(str) |
CAST(str AS NUMERIC) or str::NUMERIC |
| ` | |
DUAL table |
Remove FROM DUAL; PostgreSQL evaluates SELECT expr without a table |
CONNECT BY hierarchy |
Rewrite using recursive CTEs (WITH RECURSIVE) |
MERGE INTO |
Rewrite as INSERT ... ON CONFLICT DO UPDATE |
Empty string '' as NULL |
Oracle treats '' as NULL; PostgreSQL does not — check comparisons and IS NULL guards |
VARCHAR2 |
VARCHAR or TEXT |
Step 7: Build and verify
After addressing all checklist items:
dotnet build on the .Postgres project. Fix any remaining compilation errors.Oracle.ManagedDataAccess, OracleConnection, OracleCommand, :param patterns.Reports/{ProjectName}/MigrationChecklist.md.If the project uses Oracle.EntityFrameworkCore:
DbContext configuration: .UseOracle(...) → .UseNpgsql(...)
OracleDbContextOptionsBuilder references.OnModelCreating for Oracle-specific configurations (e.g., HasColumnType("NUMBER") → HasColumnType("numeric")).modelBuilder.HasSequence<int>("seq_name").StartsAt(1).IncrementsBy(1) syntax is compatible; verify column defaults referencing sequences..Postgres copy — never modify the original Oracle-targeting project.