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:

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.