Migrate .NET/C# data access code from Oracle.ManagedDataAccess or Oracle.EntityFrameworkCore to PostgreSQL with Npgsql. Use when replacing OracleConnection, OracleCommand, OracleDataReader, OracleDbType mappings, stored procedure calls, connection strings, inline SQL, and EF Core provider configuration during an Oracle-to-PostgreSQL migration.
Migrates the C# data access layer of a .Postgres project copy from Oracle providers to Npgsql while preserving behavior, using the migration reports as the source of truth, and validating with a build and Oracle-specific searches.
When to invoke
"Migrate this .NET data access code from Oracle to PostgreSQL."
"Replace Oracle.ManagedDataAccess with Npgsql."
"Fix OracleConnection and OracleCommand usage after migration."
"Convert Oracle stored procedure calls and RefCursor handling to PostgreSQL."
"Update Oracle.EntityFrameworkCore code to Npgsql.EntityFrameworkCore.PostgreSQL."
Prerequisites and context
Work only within the .Postgres project copy; never modify the original Oracle-targeting project.
Reports/{ProjectName}/MigrationChecklist.md exists and is the source of truth for work items.
Reports/{ProjectName}/OracleRiskAnalysis.md exists for behavioral differences.
Keep existing .NET and C# versions; do not introduce newer language or runtime features.
Oracle behavior is the source of truth; document behavioral differences as bug reports, not silent changes.
Procedure
Replace Oracle NuGet packages in the .csproj.
Update connection string configuration without hardcoding credentials.
Rewrite Oracle-specific ADO.NET type references and using directives.
Fix explicit OracleDbType mappings to NpgsqlDbType or remove unnecessary explicit typing.
Migrate stored procedure, refcursor, OUT parameter, sequence, and named parameter patterns.
Replace Oracle-specific inline SQL and query-builder syntax.
Build and verify; mark completed items in Reports/{ProjectName}/MigrationChecklist.md.
Package and configuration changes
Oracle dependency or setting
PostgreSQL replacement
Oracle.ManagedDataAccess.Core
Npgsql for ADO.NET.
Oracle.EntityFrameworkCore
Npgsql.EntityFrameworkCore.PostgreSQL for EF Core.
Oracle.* packages
Remove unless another non-data-access feature still requires them.
Oracle connection strings in appsettings.json, appsettings.{env}.json, web.config, app.config, or environment variable configuration
Npgsql string such as Host=localhost;Port=5432;Database=mydb;Username=myuser;Password=....
OracleConnection key names
Keep the same key unless Oracle-specific naming forces a change.
IConfiguration or secrets manager
Continue using the existing mechanism; do not hardcode credentials.
If IDbConnection or IDbCommand abstractions are registered through DI, update the DI registration and connection string first; consuming code may need fewer changes.
ADO.NET type mapping
Oracle type
Npgsql replacement
OracleConnection
NpgsqlConnection
OracleCommand
NpgsqlCommand
OracleDataReader
NpgsqlDataReader
OracleDataAdapter
NpgsqlDataAdapter
OracleParameter
NpgsqlParameter
OracleTransaction
NpgsqlTransaction
OracleException
NpgsqlException
OracleDbType
NpgsqlDbType from the NpgsqlTypes namespace
using Oracle.ManagedDataAccess.Client
using Npgsql
OracleRefCursor
Remove cursor wrapping and use PostgreSQL refcursor or set-returning function handling.
DbType and SQL mappings
Oracle construct
PostgreSQL or Npgsql replacement
OracleDbType.Varchar2
NpgsqlDbType.Varchar or omit and let Npgsql infer.
OracleDbType.Clob
NpgsqlDbType.Text.
OracleDbType.Number
NpgsqlDbType.Numeric or NpgsqlDbType.Integer depending on precision.
OracleDbType.Date
NpgsqlDbType.Date for date only or NpgsqlDbType.Timestamp when time is used.
OracleDbType.TimeStamp
NpgsqlDbType.Timestamp.
OracleDbType.RefCursor
NpgsqlDbType.Refcursor; call inside a transaction and FETCH ALL IN "<cursor_name>".
OracleDbType.Char
NpgsqlDbType.Char.
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)
Mostly compatible; verify format strings.
TO_DATE(str, fmt)
Verify format strings.
TO_NUMBER(str)
CAST(str AS NUMERIC) or str::NUMERIC.
`
SELECT {SEQUENCE}.NEXTVAL FROM DUAL
SELECT nextval('{sequence_name}'); remove FROM DUAL.
CONNECT BY hierarchy
Rewrite with recursive CTEs using WITH RECURSIVE.
MERGE INTO
Rewrite as INSERT ... ON CONFLICT DO UPDATE.
Empty string '' as NULL
PostgreSQL does not treat '' as NULL; review comparisons and IS NULL guards.
VARCHAR2
VARCHAR or TEXT.
Oracle named parameter :param_name
Npgsql named parameter @param_name.
Stored procedures and EF Core
Area
Rule
CommandType.StoredProcedure
Retain for function calls when supported by target Npgsql version.
Procedures with OUT parameters
PostgreSQL may require CommandType.Text with CALL proc_name(...); verify against the target Npgsql version.
RETURNS TABLE / RETURNS SETOF
Use ExecuteReader() directly; no cursor parameter needed.
RETURNS refcursor
Open a transaction, execute function, read cursor name, then FETCH ALL IN "<cursor_name>".
OUT / INOUT
Verify ParameterDirection matches the migrated signature.
EF Core provider
Replace .UseOracle(...) with .UseNpgsql(...).
EF Core builder
Remove OracleDbContextOptionsBuilder references.
OnModelCreating
Review Oracle-specific HasColumnType("NUMBER") and use HasColumnType("numeric") or PostgreSQL type names.
Sequences
modelBuilder.HasSequence<int>("seq_name").StartsAt(1).IncrementsBy(1) is compatible; verify column defaults.
EF migrations
Do not run EF Core migrations; schema is managed externally via DDL scripts from Phase 4.
Fix compilation errors, then mark completed items in Reports/{ProjectName}/MigrationChecklist.md.
Gotchas
Do not migrate outside the .Postgres copy: the Oracle project must remain available for comparison.
Do not assume stored procedures map 1:1: refcursor, OUT, INOUT, and set-returning functions require different patterns.
Do not silently change empty-string behavior: Oracle treats '' as NULL; PostgreSQL does not.
Do not add newer packages just because they exist: keep version pinning consistent with the solution and target .NET version.
Migration compatibility notes
Check System.Data abstractions such as IDbConnection and IDbCommand before broad rewrites; project-wide, surface-level changes may be enough. In DbContext configuration, replace Oracle provider setup. Search for combined patterns such as OracleConnection/OracleCommand/OracleDataReader, :param, and cursor-wrapping code. Use Reports/{ProjectName}/OracleRiskAnalysis.md for cross-referencing; SELECT expr replaces Oracle's dummy table pattern. Use Npgsql ADO.NET and/or EF Core packages as needed.
Reports/{ProjectName}/MigrationChecklist.md drove the work and was updated.
Oracle packages were removed and Npgsql packages added consistently.
Connection strings use Npgsql format and existing secret mechanisms.
Oracle.ManagedDataAccess, OracleConnection, OracleCommand, OracleDataReader, OracleDbType, and OracleRefCursor no longer remain unless justified.
Stored procedure, refcursor, OUT, INOUT, sequence, and :param_name patterns were reviewed.
dotnet build passes or every failure is reported with evidence.
1---2name: migrating-oracle-to-postgres-data-access-code-23description: Migrate .NET/C# data access code from Oracle.ManagedDataAccess or Oracle.EntityFrameworkCore to PostgreSQL with Npgsql. Use when replacing OracleConnection, OracleCommand, OracleDataReader, OracleDbType mappings, stored procedure calls, connection strings, inline SQL, and EF Core provider configuration during an Oracle-to-PostgreSQL migration.4---56# Migrating Oracle to PostgreSQL data access code78Migrates the C# data access layer of a `.Postgres` project copy from Oracle providers to Npgsql while preserving behavior, using the migration reports as the source of truth, and validating with a build and Oracle-specific searches.910## When to invoke1112- "Migrate this .NET data access code from Oracle to PostgreSQL."13- "Replace Oracle.ManagedDataAccess with Npgsql."14- "Fix OracleConnection and OracleCommand usage after migration."15- "Convert Oracle stored procedure calls and RefCursor handling to PostgreSQL."16- "Update Oracle.EntityFrameworkCore code to Npgsql.EntityFrameworkCore.PostgreSQL."1718## Prerequisites and context1920- Work only within the `.Postgres` project copy; never modify the original Oracle-targeting project.21- `Reports/{ProjectName}/MigrationChecklist.md` exists and is the source of truth for work items.22- `Reports/{ProjectName}/OracleRiskAnalysis.md` exists for behavioral differences.23- Keep existing .NET and C# versions; do not introduce newer language or runtime features.24- Oracle behavior is the source of truth; document behavioral differences as bug reports, not silent changes.2526## Procedure27281. Replace Oracle NuGet packages in the `.csproj`.292. Update connection string configuration without hardcoding credentials.303. Rewrite Oracle-specific ADO.NET type references and `using` directives.314. Fix explicit `OracleDbType` mappings to `NpgsqlDbType` or remove unnecessary explicit typing.325. Migrate stored procedure, refcursor, OUT parameter, sequence, and named parameter patterns.336. Replace Oracle-specific inline SQL and query-builder syntax.347. Build and verify; mark completed items in `Reports/{ProjectName}/MigrationChecklist.md`.3536## Package and configuration changes3738| Oracle dependency or setting | PostgreSQL replacement |39| --- | --- |40| `Oracle.ManagedDataAccess.Core` | `Npgsql` for ADO.NET. |41| `Oracle.EntityFrameworkCore` | `Npgsql.EntityFrameworkCore.PostgreSQL` for EF Core. |42| `Oracle.*` packages | Remove unless another non-data-access feature still requires them. |43| Oracle connection strings in `appsettings.json`, `appsettings.{env}.json`, `web.config`, `app.config`, or environment variable configuration | Npgsql string such as `Host=localhost;Port=5432;Database=mydb;Username=myuser;Password=...`. |44| `OracleConnection` key names | Keep the same key unless Oracle-specific naming forces a change. |45| `IConfiguration` or secrets manager | Continue using the existing mechanism; do not hardcode credentials. |4647If `IDbConnection` or `IDbCommand` abstractions are registered through DI, update the DI registration and connection string first; consuming code may need fewer changes.4849## ADO.NET type mapping5051| Oracle type | Npgsql replacement |52| --- | --- |53| `OracleConnection` | `NpgsqlConnection` |54| `OracleCommand` | `NpgsqlCommand` |55| `OracleDataReader` | `NpgsqlDataReader` |56| `OracleDataAdapter` | `NpgsqlDataAdapter` |57| `OracleParameter` | `NpgsqlParameter` |58| `OracleTransaction` | `NpgsqlTransaction` |59| `OracleException` | `NpgsqlException` |60| `OracleDbType` | `NpgsqlDbType` from the `NpgsqlTypes` namespace |61| `using Oracle.ManagedDataAccess.Client` | `using Npgsql` |62| `OracleRefCursor` | Remove cursor wrapping and use PostgreSQL refcursor or set-returning function handling. |6364## DbType and SQL mappings6566| Oracle construct | PostgreSQL or Npgsql replacement |67| --- | --- |68| `OracleDbType.Varchar2` | `NpgsqlDbType.Varchar` or omit and let Npgsql infer. |69| `OracleDbType.Clob` | `NpgsqlDbType.Text`. |70| `OracleDbType.Number` | `NpgsqlDbType.Numeric` or `NpgsqlDbType.Integer` depending on precision. |71| `OracleDbType.Date` | `NpgsqlDbType.Date` for date only or `NpgsqlDbType.Timestamp` when time is used. |72| `OracleDbType.TimeStamp` | `NpgsqlDbType.Timestamp`. |73| `OracleDbType.RefCursor` | `NpgsqlDbType.Refcursor`; call inside a transaction and `FETCH ALL IN "<cursor_name>"`. |74| `OracleDbType.Char` | `NpgsqlDbType.Char`. |75| `ROWNUM <= n` | `LIMIT n`. |76| `ROWNUM = 1` | `LIMIT 1`. |77| `NVL(x, y)` | `COALESCE(x, y)`. |78| `DECODE(expr, v1, r1, ...)` | `CASE WHEN expr = v1 THEN r1 ... END`. |79| `SYSDATE` / `SYSTIMESTAMP` | `NOW()` or `CURRENT_TIMESTAMP`. |80| `TO_CHAR(date, fmt)` | Mostly compatible; verify format strings. |81| `TO_DATE(str, fmt)` | Verify format strings. |82| `TO_NUMBER(str)` | `CAST(str AS NUMERIC)` or `str::NUMERIC`. |83| `||` string concat | Compatible. |84| `SELECT {SEQUENCE}.NEXTVAL FROM DUAL` | `SELECT nextval('{sequence_name}')`; remove `FROM DUAL`. |85| `CONNECT BY` hierarchy | Rewrite with recursive CTEs using `WITH RECURSIVE`. |86| `MERGE INTO` | Rewrite as `INSERT ... ON CONFLICT DO UPDATE`. |87| Empty string `''` as NULL | PostgreSQL does not treat `''` as NULL; review comparisons and `IS NULL` guards. |88| `VARCHAR2` | `VARCHAR` or `TEXT`. |89| Oracle named parameter `:param_name` | Npgsql named parameter `@param_name`. |9091## Stored procedures and EF Core9293| Area | Rule |94| --- | --- |95| `CommandType.StoredProcedure` | Retain for function calls when supported by target Npgsql version. |96| Procedures with `OUT` parameters | PostgreSQL may require `CommandType.Text` with `CALL proc_name(...)`; verify against the target Npgsql version. |97| `RETURNS TABLE` / `RETURNS SETOF` | Use `ExecuteReader()` directly; no cursor parameter needed. |98| `RETURNS refcursor` | Open a transaction, execute function, read cursor name, then `FETCH ALL IN "<cursor_name>"`. |99| `OUT` / `INOUT` | Verify `ParameterDirection` matches the migrated signature. |100| EF Core provider | Replace `.UseOracle(...)` with `.UseNpgsql(...)`. |101| EF Core builder | Remove `OracleDbContextOptionsBuilder` references. |102| `OnModelCreating` | Review Oracle-specific `HasColumnType("NUMBER")` and use `HasColumnType("numeric")` or PostgreSQL type names. |103| Sequences | `modelBuilder.HasSequence<int>("seq_name").StartsAt(1).IncrementsBy(1)` is compatible; verify column defaults. |104| EF migrations | Do not run EF Core migrations; schema is managed externally via DDL scripts from Phase 4. |105106## Validation107108```bash109dotnet build110grep -R "Oracle.ManagedDataAccess\|OracleConnection\|OracleCommand\|OracleDataReader\|OracleDbType\|OracleRefCursor" <PostgresProject>111grep -R ":[A-Za-z_][A-Za-z0-9_]*" <PostgresProject>112```113114Fix compilation errors, then mark completed items in `Reports/{ProjectName}/MigrationChecklist.md`.115116## Gotchas117118- **Do not migrate outside the `.Postgres` copy**: the Oracle project must remain available for comparison.119- **Do not assume stored procedures map 1:1**: refcursor, `OUT`, `INOUT`, and set-returning functions require different patterns.120- **Do not silently change empty-string behavior**: Oracle treats `''` as NULL; PostgreSQL does not.121- **Do not add newer packages just because they exist**: keep version pinning consistent with the solution and target .NET version.122123## Migration compatibility notes124125Check `System.Data` abstractions such as `IDbConnection` and `IDbCommand` before broad rewrites; `project-wide`, `surface-level` changes may be enough. In `DbContext` configuration, replace Oracle provider setup. Search for combined patterns such as `OracleConnection/OracleCommand/OracleDataReader`, `:param`, and `cursor-wrapping` code. Use `Reports/{ProjectName}/OracleRiskAnalysis.md` for `cross-referencing`; `SELECT expr` replaces Oracle's dummy table pattern. Use Npgsql ADO.NET and/or EF Core packages as needed.126127## Output template128129```markdown130### Oracle to PostgreSQL data access migration result131132**Status:** migrated | partially migrated | blocked133**Project:** `<.Postgres project>`134**Checklist:** `Reports/{ProjectName}/MigrationChecklist.md`135136| Step | Files changed | Notes |137| --- | --- | --- |138| NuGet packages | `<.csproj>` | <removed Oracle packages and added Npgsql packages> |139| Connection strings | `<config file>` | <key and secret handling> |140| ADO.NET types | `<files>` | <type replacements> |141| DbType mappings | `<files>` | <mapping decisions> |142| Stored procedures | `<files>` | <CALL/refcursor/returns handling> |143| SQL syntax | `<files>` | <Oracle constructs replaced> |144145**Validation**146- `dotnet build`: pass | fail147- Oracle namespace/type search: pass | fail148- `Reports/{ProjectName}/MigrationChecklist.md` updated: yes | no149```150151## Quality gate152153- [ ] Only the `.Postgres` copy was modified.154- [ ] `Reports/{ProjectName}/MigrationChecklist.md` drove the work and was updated.155- [ ] Oracle packages were removed and Npgsql packages added consistently.156- [ ] Connection strings use Npgsql format and existing secret mechanisms.157- [ ] `Oracle.ManagedDataAccess`, `OracleConnection`, `OracleCommand`, `OracleDataReader`, `OracleDbType`, and `OracleRefCursor` no longer remain unless justified.158- [ ] Stored procedure, refcursor, `OUT`, `INOUT`, sequence, and `:param_name` patterns were reviewed.159- [ ] `dotnet build` passes or every failure is reported with evidence.
Run npx skillmds@latest add paulasilvatech/migrating-oracle-to-postgres-data-access-code-2 in your terminal (requires Node.js), paste this page's agent-chat prompt into Claude, Cursor, or any MCP-connected agent, or download the SKILL.md file and copy it into your agent's skills directory.
Migrate .NET/C# data access code from Oracle.ManagedDataAccess or Oracle.EntityFrameworkCore to PostgreSQL with Npgsql. Use when replacing OracleConnection, OracleCommand, OracleDataReader, OracleDbType mappings, stored procedure calls, connection strings, inline SQL, and EF Core provider configuration during an Oracle-to-PostgreSQL migration. It is listed under Data & Analytics on SkillMD.
This skill has not completed SkillMD's automated safety review yet. SkillMD never runs a skill's scripts for you; review the SKILL.md before installing.
This skill is tagged as working with Claude Code, Claude.ai, OpenAI Codex. SKILL.md is an open format, so most agents that read a skills directory can load it too.
Yes. Installing skills from SkillMD is free, and the skill stays under its author's original license.
paulasilvatech (@paulasilvatech) published this skill. Their other Agent Skills are listed on their SkillMD profile.
paulasilvatech