Skip to content
Skills Library
Programming · Conversion & formats

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

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

Migrates .NET/C# data access code from Oracle to PostgreSQL (Npgsql). Replaces Oracle NuGet packages, rewrites OracleConnection/OracleCommand/OracleDataReader usage, fixes DbType mappings, updates stored procedure invocation patterns, and adapts connection string configuration. Use when migrating the application code layer of a .NET project during an Oracle-to-PostgreSQL database migration.

Licence
MIT
Last repository change
26.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 26.10.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Migrates .NET/C# data access code from Oracle to PostgreSQL (Npgsql). Replaces Oracle NuGet packages, rewrites OracleConnection/OracleCommand/OracleDataReader usage, fixes DbType mappings, updates stored procedure invocation patterns, and adapts connection string configuration. Use when migrating the application code layer of a .NET project during an Oracle-to-PostgreSQL database migration.

Original author description

Migrates .NET/C# data access code from Oracle to PostgreSQL (Npgsql). Replaces Oracle NuGet packages, rewrites OracleConnection/OracleCommand/OracleDataReader usage, fixes DbType mappings, updates stored procedure invocation patterns, and adapts connection string configuration. Use when migrating the application code layer of a .NET project during an Oracle-to-PostgreSQL database migration.

How it works

  1. Prerequisites
  2. Workflow
  3. EF Core projects
  4. Key Constraints
Original SKILL.md headings · 134 lines, 1,009 words
SKILL.md preview
---
name: migrating-oracle-to-postgres-data-access-code
description: 'Migrates .NET/C# data access code from Oracle to PostgreSQL (Npgsql). Replaces Oracle NuGet packages, rewrites OracleConnection/OracleCommand/OracleDataReader usage, fixes DbType mappings, updates stored procedure invocation patterns, and adapts connection string configuration. Use when migrating the application code layer of a .NET project during an Oracle-to-PostgreSQL database migration.'
---

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

Versions

The database has no other skills named migrating-oracle-to-postgres-data-access-code.

Database snapshot: 13.11.2026 · package version 150c75eea652 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100planning-oracle-to-postgres-migration-integration-testing
    Creates an integration testing plan for .NET data access artifacts during Oracle-to-PostgreSQL database migrations. Analyzes a single project to identify repositories, DAOs, and service layers that interact with the database, then produces a structured testing plan. Use when…
  • 100planning-oracle-to-postgres-migration-integration-testing
    Creates an integration testing plan for .NET data access artifacts during Oracle-to-PostgreSQL database migrations. Analyzes a single project to identify repositories, DAOs, and service layers that interact with the database, then produces a structured testing plan. Use when…
  • 100creating-oracle-to-postgres-migration-integration-tests
    Creates integration test cases for .NET data access artifacts during Oracle-to-PostgreSQL database migrations. Generates DB-agnostic xUnit tests with deterministic seed data that validate behavior consistency across both database systems. Use when creating integration tests for…
  • 100creating-oracle-to-postgres-master-migration-plan
    Discovers all projects in a .NET solution, classifies each for Oracle-to-PostgreSQL migration eligibility, and produces a persistent master migration plan. Use when starting a multi-project Oracle-to-PostgreSQL migration, creating a migration inventory, or assessing which .NET…
  • 98migrate-oracle-app
    Assess and convert Oracle-dependent codebases to PostgreSQL. Use for architecture and Mermaid diagrams, Oracle JDBC, SQL, PL/SQL calls, ORM mappings, missing dependencies, missing PostgreSQL objects, Docker integration tests, synthetic data, code coverage, packaging, and…
  • 84migrate-database
    Move an existing Oracle, MySQL or PostgreSQL database to ScalarDB: schema analysis, migration documentation, stored procedure and trigger conversion to Java, AQ integration. Use for any database-to-ScalarDB migration; routes to the engine-specific workflow.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.