Migrating-Oracle-To-Postgres-Data-Access-Code

# Migrating .NET Data Access Code from Oracle to PostgreSQL

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`.

Prerequisites

- The `.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.

Workflow

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:

- Remove: `Oracle.ManagedDataAccess.Core`, `Oracle.EntityFrameworkCore` (and any other `Oracle.*` packages)

- Add: `Npgsql` (for ADO.NET) and/or `Npgsql.EntityFrameworkCore.PostgreSQL` (for EF Core)

- Keep version pinning consistent with the target .NET version; do not introduce newer package versions than what the solution already uses for similar packages.

- If `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**

- Locate the Oracle connection string in `appsettings.json`, `appsettings.{env}.json`, `web.config`, `app.config`, or environment variable configuration.

- Replace with a Npgsql-compatible connection string: `Host=localhost;Port=5432;Database=mydb;Username=myuser;Password=mypassword`

- Do not hardcode credentials — use the same configuration mechanism already in use (e.g., environment variables, secrets manager, `IConfiguration`).

- Update any named connection string keys only if they were Oracle-specific (e.g., `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:

- **Com