---
title: "Agent Skill to Migrating Oracle To Postgres Data Access Code"
platform: "Promptally"
canonical_url: "https://app.promptally.dev/p/skill-migrating-oracle-to-postgres-data-access-code"
modality: "SKILL"
category: "Fullstack & Backend Engineering"
compatible_models: [Universal / Model Agnostic]
access_tier: "Free Public Artifact (No Auth Required)"
license: "MIT"
---

# Agent Skill to Migrating Oracle To Postgres Data Access Code

**Summary:** Agent Skill to Migrating Oracle To Postgres Data Access Code is a verified SKILL blueprint on Promptally in Fullstack & Backend Engineering optimized for leading AI models. 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. Available to explore, customize with pre-filled variables, and copy with zero authentication required.

## Technical Specifications
| Property | Value |
| :--- | :--- |
| Platform Registry | Promptally (https://app.promptally.dev) |
| Modality | SKILL |
| Category | Fullstack & Backend Engineering |
| Compatible Engines | Universal / Model Agnostic |
| Dynamic Variables | None (Static Blueprint) |
| Approx. Token Length | 2,015 |
| Access Tier | Free Public Artifact (No Auth Required) |
| License | MIT |

### Pre-Filled Template & Production Source

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

````

## Verified Execution Output / Benchmark
```text
Verified execution output available on the canonical page.
```

## Related Blueprints on Promptally
- [Agent Skill to Knowledge Ops](https://app.promptally.dev/p/skill-knowledge-ops)
- [Agent Skill to Content Hash Cache Pattern](https://app.promptally.dev/p/skill-content-hash-cache-pattern)
- [Agent Skill to Ck](https://app.promptally.dev/p/skill-ck)
- [Agent Skill to Swift Protocol Di Testing](https://app.promptally.dev/p/skill-swift-protocol-di-testing)
- [Agent Skill to Prisma Patterns](https://app.promptally.dev/p/skill-prisma-patterns)
- [Agent Skill to Agentic Engineering](https://app.promptally.dev/p/skill-agentic-engineering)
- [Agent Skill to Python Patterns](https://app.promptally.dev/p/skill-python-patterns)
- [Agent Skill to Docs Governance](https://app.promptally.dev/p/skill-docs-governance)