williamlimasilva/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:

• Command type: Retain 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.
• RefCursor handling: Oracle returns ref cursors as output parameters; PostgreSQL returns them differently:
  • For RETURNS TABLE / RETURNS SETOF, use ExecuteReader() directly — no cursor parameter needed.
  • For RETURNS refcursor, call within a transaction, read the cursor name from the output parameter, then issue FETCH ALL IN "<cursor_name>".
  • Remove any Oracle-specific cursor-wrapping code (e.g., OracleRefCursor).
• OUT parameters: PostgreSQL stored procedures use INOUT or function return values. Verify parameter direction matches the migrated procedure signature.
• Sequence NEXTVAL: Replace SELECT {SEQUENCE}.NEXTVAL FROM DUAL with SELECT nextval('{sequence_name}').
• Named parameters: Npgsql uses @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 | | || string concat | || (compatible) | | 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:

1. Run dotnet build on the .Postgres project. Fix any remaining compilation errors.
2. Verify no Oracle-specific namespaces remain: search for Oracle.ManagedDataAccess, OracleConnection, OracleCommand, :param patterns.
3. Mark completed items in Reports/{ProjectName}/MigrationChecklist.md.

EF Core projects

If the project uses Oracle.EntityFrameworkCore:

• Replace the provider registration in DbContext configuration: .UseOracle(...) → .UseNpgsql(...)
• Replace OracleDbContextOptionsBuilder references.
• Review OnModelCreating for Oracle-specific configurations (e.g., HasColumnType("NUMBER") → HasColumnType("numeric")).
• Sequence configuration: modelBuilder.HasSequence<int>("seq_name").StartsAt(1).IncrementsBy(1) syntax is compatible; verify column defaults referencing sequences.
• Do not run EF Core migrations — schema is managed externally via DDL scripts (Phase 4).

Key Constraints

• Work only within the .Postgres copy — never modify the original Oracle-targeting project.
• Keep to existing .NET and C# versions; do not introduce newer language or runtime features.
• Preserve comments and application logic; change only what is necessary for PostgreSQL compatibility.
• Oracle is the source of truth — behavioral differences must be documented as bug reports, not silently altered.