migrating-oracle-to-postgres-data-access-code

Imported from github/awesome-copilot (skills/migrating-oracle-to-postgres-data-access-code/SKILL.md). Install upstream with npx skills add github/awesome-copilot --skill migrating-oracle-to-postgres-data-access-code. Copyright stays with the author.

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:

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:

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:

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.