# Core Oracle driver (required for both Dapper and ADO.NET)
dotnet add package Oracle.ManagedDataAccess.Core

# For Dapper
dotnet add package Dapper

# For ADO.NET — same Oracle driver, no extra package needed

{
  "Database": {
    "ConnectionString": "User Id=myuser;Password=mypass;Data Source=myserver:1521/MYDB;"
  }
}

using Oracle.ManagedDataAccess.Client;
using Microsoft.Extensions.Options;

// .NET 8: primary constructor — no boilerplate field assignment
public sealed class DbConnectionFactory(IOptions<DatabaseOptions> opts)
{
    private readonly string _connectionString = opts.Value.ConnectionString;

    // Returns OracleConnection directly — no IDbConnection wrapper needed.
    // Dapper accepts OracleConnection (it implements IDbConnection internally).
    public OracleConnection Create() => new(_connectionString);
}

// Strongly-typed options class (registered via appsettings.json)
public sealed record DatabaseOptions
{
    public required string ConnectionString { get; init; }
}

// Register in Program.cs (.NET 8 minimal hosting)
builder.Services.Configure<DatabaseOptions>(
    builder.Configuration.GetSection("Database"));
builder.Services.AddSingleton<DbConnectionFactory>();

CREATE TABLE EMPLOYEES (
    ID        NUMBER(10)    PRIMARY KEY,
    NAME      VARCHAR2(100) NOT NULL,
    DEPT      VARCHAR2(50),
    SALARY    NUMBER(10,2),
    HIRE_DATE DATE          DEFAULT SYSDATE
);

-- Audit table referenced in transaction examples
CREATE TABLE AUDIT_LOG (
    ID        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    EVENT     VARCHAR2(100),
    ROW_COUNT NUMBER,
    LOGGED_AT DATE DEFAULT SYSDATE
);

-- Error log table used in FORALL SAVE EXCEPTIONS examples
CREATE TABLE IMPORT_ERRORS (
    ROW_INDEX  NUMBER,
    ERROR_CODE NUMBER,
    LOGGED_AT  DATE DEFAULT SYSDATE
);

-- Global Temporary Table for bulk update/upsert via temp table pattern
CREATE GLOBAL TEMPORARY TABLE EMPLOYEE_UPDATES (
    ID     NUMBER(10),
    NAME   VARCHAR2(100),
    SALARY NUMBER(10,2)
) ON COMMIT DELETE ROWS; -- auto-cleared after each transaction commit

// .NET 8 record — immutable, value-based equality, concise syntax.
// Property names match Oracle parameter names (:Id, :Name, :Dept, :Salary).
// Used as-is with Dapper, decomposed into arrays for ADO.NET Array Binding.
public record Employee(
    int     Id,
    string  Name,
    string? Dept,
    decimal Salary
);

// Sample data — used throughout all examples
List<Employee> employees = Enumerable.Range(1, 10_000)
    .Select(i => new Employee(
        Id:     i,
        Name:   $"Employee {i}",
        Dept:   i % 2 == 0 ? "Engineering" : "Sales",
        Salary: 50_000 + (i * 10)
    ))
    .ToList();

public async Task DeleteByIdsAsync(IEnumerable<int> ids)
{
    // Oracle + Dapper: IN clause needs parentheses around the parameter.
    // Dapper expands the array into individual bind variables automatically.
    const string sql = "DELETE FROM EMPLOYEES WHERE ID IN (:Ids)";

    await using var conn = db.Create();
    await conn.OpenAsync();
    await conn.ExecuteAsync(sql, new { Ids = ids.ToArray() });
}

public async Task DeleteChunkedAsync(List<int> allIds, int chunkSize = 900)
{
    // Open once and reuse across all chunks — avoid reconnect overhead
    await using var conn = db.Create();
    await conn.OpenAsync();

    // Split into 900-item chunks (safely under Oracle's 1,000 IN-list limit)
    var chunks = allIds
        .Select((id, i) => (id, i))
        .GroupBy(x => x.i / chunkSize)
        .Select(g => g.Select(x => x.id).ToArray());

    foreach (var chunk in chunks)
        await conn.ExecuteAsync(
            "DELETE FROM EMPLOYEES WHERE ID IN (:Ids)",
            new { Ids = chunk });
}

// Delete all temporary employees hired more than 5 years ago.
// One SQL statement handles all qualifying rows atomically.
public async Task<int> DeleteStaleTempsAsync()
{
    await using var conn = db.Create();
    await conn.OpenAsync();
    return await conn.ExecuteAsync(@"
        DELETE FROM EMPLOYEES
        WHERE  DEPT      = :Dept
          AND  HIRE_DATE < :CutoffDate",
        new { Dept = "Temp", CutoffDate = DateTime.UtcNow.AddYears(-5) });
}

public async Task BulkDeleteArrayBindAsync(int[] ids)
{
    await using var conn = db.Create();
    await conn.OpenAsync();

    await using var cmd = conn.CreateCommand();
    cmd.CommandText    = "DELETE FROM EMPLOYEES WHERE ID = :Id";
    cmd.ArrayBindCount = ids.Length;

    cmd.Parameters.Add("Id", OracleDbType.Int32, ids, ParameterDirection.Input);

    int deleted = await cmd.ExecuteNonQueryAsync();
    Console.WriteLine($"Deleted: {deleted} rows");
}

FOR EACH row IN source_data:
    IF EXISTS (SELECT 1 FROM target WHERE target.ID = row.ID):
        UPDATE target SET ... WHERE target.ID = row.ID   -- WHEN MATCHED
    ELSE:
        INSERT INTO target ...                             -- WHEN NOT MATCHED

-- Oracle does this for ALL rows in ONE SQL statement.
-- You never write the IF/ELSE yourself — MERGE does it for you.

public async Task BulkUpsertAsync(IEnumerable<Employee> employees)
{
    // SELECT :params FROM DUAL as source — one MERGE execution per employee.
    // Dapper iterates the collection and binds each Employee's properties.
    const string sql = @"
        MERGE INTO EMPLOYEES target
        USING (
            SELECT :Id     AS ID,
                   :Name   AS NAME,
                   :Dept   AS DEPT,
                   :Salary AS SALARY
            FROM DUAL
        ) source
        ON (target.ID = source.ID)
        WHEN MATCHED THEN
            UPDATE SET
                target.NAME   = source.NAME,
                target.DEPT   = source.DEPT,
                target.SALARY = source.SALARY
        WHEN NOT MATCHED THEN
            INSERT (ID, NAME, DEPT, SALARY)
            VALUES (source.ID, source.NAME, source.DEPT, source.SALARY)";

    await using var conn = db.Create();
    await conn.OpenAsync();
    await conn.ExecuteAsync(sql, employees);
}

public async Task BulkUpsertViaTempAsync(List<Employee> employees)
{
    await using var conn = db.Create();
    await conn.OpenAsync();
    await using var tx = (OracleTransaction)await conn.BeginTransactionAsync();

    try
    {
        // 1. BulkCopy into the GTT.
        //    UseInternalTransaction: BulkCopy commits its own mini-transaction
        //    so the GTT rows are visible to the same session for the MERGE below.
        using var bulk = new OracleBulkCopy(conn,
            OracleBulkCopyOptions.UseInternalTransaction)
        {
            DestinationTableName = "EMPLOYEE_UPDATES"
        };
        // DataTableConverter.ToDataTable is the generic helper from Module 9.
        await bulk.WriteToServerAsync(DataTableConverter.ToDataTable(employees));

        // 2. MERGE from GTT → EMPLOYEES (runs on the main tx)
        await using var cmd = conn.CreateCommand();
        cmd.Transaction = tx;
        cmd.CommandText = @"
            MERGE INTO EMPLOYEES tgt
            USING EMPLOYEE_UPDATES src ON (tgt.ID = src.ID)
            WHEN MATCHED THEN
                UPDATE SET tgt.NAME = src.NAME, tgt.SALARY = src.SALARY
            WHEN NOT MATCHED THEN
                INSERT (ID, NAME, SALARY)
                VALUES (src.ID, src.NAME, src.SALARY)";

        await cmd.ExecuteNonQueryAsync();
        await tx.CommitAsync(); // GTT auto-cleared (ON COMMIT DELETE ROWS)
    }
    catch { await tx.RollbackAsync(); throw; }
}

// ── Approach A: use empty string for nullable strings ─────────────
// Oracle treats '' as NULL for VARCHAR2, so this is safe.
string[] depts = employees
    .Select(e => e.Dept ?? "")  // null → "" → stored as NULL in Oracle VARCHAR2
    .ToArray();

// ── Approach B: use OracleParameterStatus for explicit NULL control ─
// Required when you need to distinguish between empty string and NULL,
// or when working with nullable numeric/date columns.
decimal?[] nullableSalaries = employees
    .Select(e => (decimal?)e.Salary)  // keep as nullable decimal
    .ToArray();

var salaryValues = nullableSalaries
    .Select(s => s.HasValue ? s.Value : (decimal)0)  // placeholder value for nulls
    .ToArray();

var salaryStatus = nullableSalaries
    .Select(s => s.HasValue
        ? OracleParameterStatus.Success       // use the value
        : OracleParameterStatus.NullInsert)    // ignore value, insert NULL
    .ToArray();

var param = new OracleParameter
{
    ParameterName = "Salary",
    OracleDbType  = OracleDbType.Decimal,
    Direction     = ParameterDirection.Input,
    Value         = salaryValues,
    Status        = salaryStatus    // Status overrides Value wherever NullInsert
};
cmd.Parameters.Add(param);

public async Task CallBulkProcAsync(List<Employee> employees)
{
    int[]    ids   = employees.Select(e => e.Id).ToArray();
    string[] names = employees.Select(e => e.Name).ToArray();

    await using var conn = db.Create();
    await conn.OpenAsync();

    await using var cmd = conn.CreateCommand();
    cmd.CommandText = "PKG_EMPLOYEE.BULK_INSERT";  // Package.Procedure
    cmd.CommandType = CommandType.StoredProcedure;

    // Set ArrayBindCount BEFORE adding parameters
    cmd.ArrayBindCount = employees.Count;

    // Input arrays — one per IN collection parameter in the proc
    cmd.Parameters.Add("p_ids",   OracleDbType.Int32,   ids,   ParameterDirection.Input);
    cmd.Parameters.Add("p_names", OracleDbType.Varchar2, names, ParameterDirection.Input);

    // Scalar OUTPUT parameter — use named-property syntax, not positional constructor
    cmd.Parameters.Add(new OracleParameter
    {
        ParameterName = "p_result",
        OracleDbType  = OracleDbType.Varchar2,
        Size          = 200,
        Direction     = ParameterDirection.Output
    });

    await cmd.ExecuteNonQueryAsync();
    Console.WriteLine(cmd.Parameters["p_result"].Value);
}

using Oracle.ManagedDataAccess.Client;

public async Task LoadFromCsvAsync(string csvPath)
{
    // Parse CSV into DataTable
    var table = new DataTable();
    table.Columns.Add("ID",     typeof(int));
    table.Columns.Add("NAME",   typeof(string));
    table.Columns.Add("DEPT",   typeof(string));
    table.Columns.Add("SALARY", typeof(decimal));

    foreach (var line in File.ReadLines(csvPath).Skip(1)) // skip header
    {
        var cols = line.Split(',');
        table.Rows.Add(int.Parse(cols[0]), cols[1], cols[2], decimal.Parse(cols[3]));
    }

    await using var conn = db.Create();
    await conn.OpenAsync();

    using var bulk = new OracleBulkCopy(conn,
        OracleBulkCopyOptions.UseInternalTransaction)  // auto-commit per batch
    {
        DestinationTableName = "EMPLOYEES",
        BatchSize            = 10_000,
        BulkCopyTimeout      = 300
    };

    // Wire up progress notifications every 5000 rows
    bulk.NotifyAfter = 5000;
    bulk.OracleRowsCopied += (s, e) =>
        Console.WriteLine($"  → {e.RowsCopied:N0} rows copied so far...");

    await bulk.WriteToServerAsync(table);
    Console.WriteLine($"Done! {table.Rows.Count:N0} rows loaded.");
}

// IDataReader lets OracleBulkCopy stream rows without loading all into memory.
// Use EnumerableDataReader<T> defined in the List-of-Objects module.
public async Task StreamBulkCopyAsync(IAsyncEnumerable<Employee> source)
{
    await using var conn = db.Create();
    await conn.OpenAsync();

    // OracleBulkCopy implements IDisposable only — using (not await using) is correct
    using var bulk = new OracleBulkCopy(conn) {
        DestinationTableName = "EMPLOYEES",
        BatchSize            = 5000
    };

    // Materialize IAsyncEnumerable into List (no System.Linq.Async package needed)
    var list = new List<Employee>();
    await foreach (var emp in source)
        list.Add(emp);
    var reader = new EnumerableDataReader<Employee>(list,
        ("ID",     e => (object)e.Id),
        ("NAME",   e => e.Name),
        ("DEPT",   e => e.Dept),
        ("SALARY", e => e.Salary)
    );

    await bulk.WriteToServerAsync(reader);
}

using Dapper;
using Oracle.ManagedDataAccess.Types;

public class OracleDateHandler : SqlMapper.TypeHandler<DateTime>
{
    // Called when writing DateTime → Oracle parameter
    public override void SetValue(IDbDataParameter parameter, DateTime value)
        => parameter.Value = new OracleDate(value);

    // Called when reading Oracle DATE → DateTime
    public override DateTime Parse(object value)
        => ((OracleDate)value).Value;
}

// ── Register in Program.cs (.NET 8 minimal hosting) ──────────────
// Call this ONCE before any Dapper queries run
var builder = WebApplication.CreateBuilder(args);
SqlMapper.AddTypeHandler(new OracleDateHandler()); // ← before builder.Build()
var app = builder.Build();

public async Task ChunkedBulkInsertAsync(
    List<Employee> employees,
    int batchSize = 1000)
{
    await using var conn = db.Create();
    await conn.OpenAsync();
    await using var tx = (OracleTransaction)await conn.BeginTransactionAsync();

    try
    {
        for (int i = 0; i < employees.Count; i += batchSize)
        {
            // Materialize to List so Count doesn't re-enumerate the LINQ chain
            var batch = employees.Skip(i).Take(batchSize).ToList();
            await conn.ExecuteAsync(
                "INSERT INTO EMPLOYEES (ID,NAME,DEPT,SALARY) VALUES (:Id,:Name,:Dept,:Salary)",
                batch,
                transaction: tx);

            Console.WriteLine($"Batch {i/batchSize + 1}: inserted {batch.Count} rows");
        }

        await tx.CommitAsync();
    }
    catch { await tx.RollbackAsync(); throw; }
}

-- Step 1: Define a collection type (array of numbers, array of strings, etc.)
CREATE OR REPLACE TYPE t_id_list     AS TABLE OF NUMBER;
CREATE OR REPLACE TYPE t_name_list   AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE TYPE t_salary_list AS TABLE OF NUMBER(10,2);

-- Step 2: Stored procedure that receives collections and uses FORALL
CREATE OR REPLACE PROCEDURE bulk_insert_employees (
    p_ids     IN t_id_list,
    p_names   IN t_name_list,
    p_salaries IN t_salary_list
)
AS
BEGIN
    -- FORALL replaces: FOR i IN 1..p_ids.COUNT LOOP INSERT... END LOOP;
    -- It sends ALL rows to the SQL engine in ONE operation.
    FORALL i IN 1 .. p_ids.COUNT
        INSERT INTO EMPLOYEES (ID, NAME, SALARY)
        VALUES (p_ids(i), p_names(i), p_salaries(i));

    COMMIT;
END;
/

-- 1. Range: process all elements
FORALL i IN 1 .. p_ids.COUNT
    INSERT INTO EMPLOYEES (ID) VALUES (p_ids(i));

-- 2. Range: process a subset (rows 501 to 1000)
FORALL i IN 501 .. 1000
    INSERT INTO EMPLOYEES (ID) VALUES (p_ids(i));

-- 3. INDICES OF: skip deleted/null elements (sparse collections)
FORALL i IN INDICES OF p_ids
    UPDATE EMPLOYEES SET SALARY = p_salaries(i) WHERE ID = p_ids(i);

-- 4. VALUES OF: use another collection as the index list
--    l_index_list is a PLS_INTEGER collection of indices into p_ids
--    (must be declared separately, e.g. TYPE t_idx_list IS TABLE OF PLS_INTEGER)
FORALL i IN VALUES OF l_index_list
    DELETE FROM EMPLOYEES WHERE ID = p_ids(i);

-- 5. SAVE EXCEPTIONS: don't stop on first error, collect all errors
FORALL i IN 1 .. p_ids.COUNT SAVE EXCEPTIONS
    INSERT INTO EMPLOYEES (ID, NAME) VALUES (p_ids(i), p_names(i));

-- How many rows each DML affected (populated after FORALL)
FOR i IN 1 .. p_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Row ' || i || ': ' || SQL%BULK_ROWCOUNT(i) || ' rows affected');
END LOOP;

DECLARE
    TYPE t_emp_ids   IS TABLE OF EMPLOYEES.ID%TYPE;
    TYPE t_emp_names IS TABLE OF EMPLOYEES.NAME%TYPE;

    l_ids   t_emp_ids;
    l_names t_emp_names;

    -- Cursor for the chunked LIMIT example below
    CURSOR l_cur IS
        SELECT ID, NAME FROM EMPLOYEES WHERE DEPT = 'Engineering';
BEGIN
    -- Pattern 1: BULK COLLECT without LIMIT (loads ALL rows into memory)
    -- Only safe for small-to-medium result sets (< ~100k rows)
    SELECT ID, NAME
    BULK COLLECT INTO l_ids, l_names
    FROM   EMPLOYEES
    WHERE  DEPT = 'Engineering';

    -- Now process with FORALL (e.g. copy to archive table)
    FORALL i IN 1 .. l_ids.COUNT
        INSERT INTO EMP_ARCHIVE (ID, NAME, ARCHIVED_AT)
        VALUES (l_ids(i), l_names(i), SYSDATE);

    -- Pattern 2: BULK COLLECT with LIMIT = chunked fetch (memory-safe)
    -- Use for large result sets — fetch + process 1,000 rows at a time
    OPEN l_cur;
    LOOP
        FETCH l_cur BULK COLLECT INTO l_ids, l_names LIMIT 1000;
        EXIT WHEN l_ids.COUNT = 0;
        FORALL i IN 1 .. l_ids.COUNT
            INSERT INTO EMP_ARCHIVE (ID, NAME, ARCHIVED_AT)
            VALUES (l_ids(i), l_names(i), SYSDATE);
    END LOOP;
    CLOSE l_cur;

    COMMIT;
END;
/

public async Task CallBulkInsertProcAsync(List<Employee> employees)
{
    // Decompose List<Employee> into typed arrays
    int[]     ids      = employees.Select(e => e.Id).ToArray();
    string[]  names    = employees.Select(e => e.Name).ToArray();
    decimal[] salaries = employees.Select(e => e.Salary).ToArray();

    await using var conn = db.Create();
    await conn.OpenAsync();

    await using var cmd = conn.CreateCommand();
    cmd.CommandText = "bulk_insert_employees";
    cmd.CommandType = CommandType.StoredProcedure;

    // ArrayBindCount tells the driver: these are array params
    cmd.ArrayBindCount = employees.Count;

    // Each parameter maps to a collection parameter in the PL/SQL proc
    cmd.Parameters.Add("p_ids",      OracleDbType.Int32,   ids,      ParameterDirection.Input);
    cmd.Parameters.Add("p_names",    OracleDbType.Varchar2, names,    ParameterDirection.Input);
    cmd.Parameters.Add("p_salaries", OracleDbType.Decimal,  salaries, ParameterDirection.Input);

    await cmd.ExecuteNonQueryAsync();
    // Oracle receives arrays → PL/SQL proc uses FORALL internally
    Console.WriteLine($"Sent {employees.Count} objects to FORALL proc");
}

-- 1. Define a row type (like a struct)
CREATE OR REPLACE TYPE t_employee_obj AS OBJECT (
    ID     NUMBER(10),
    NAME   VARCHAR2(100),
    SALARY NUMBER(10,2)
);

-- 2. Define a table of that row type (like List<EmployeeObj>)
CREATE OR REPLACE TYPE t_employee_list AS TABLE OF t_employee_obj;

-- 3. Procedure that takes the whole list
CREATE OR REPLACE PROCEDURE bulk_insert_emp_udt (
    p_employees IN t_employee_list
)
AS
BEGIN
    -- Access properties of each object with dot notation
    FORALL i IN 1 .. p_employees.COUNT
        INSERT INTO EMPLOYEES (ID, NAME, SALARY)
        VALUES (
            p_employees(i).ID,
            p_employees(i).NAME,
            p_employees(i).SALARY
        );
    COMMIT;
END;
/

// ── Correct approach for Oracle UDT parameters ──────────────────
// Oracle UDTs require a custom mapping class. You cannot pass a plain
// object[] or OracleString[] — the driver won't know how to serialize it.
// The pattern is:
//
//   1. Implement IOracleCustomType on a C# class (e.g. EmployeeUdt)
//   2. Implement IOracleCustomTypeFactory on a factory class
//   3. Decorate with [OracleCustomTypeMapping("SCHEMA.T_EMPLOYEE_OBJ")]
//   4. Pass an array of EmployeeUdt[] as OracleDbType.Array
//
// Because this requires ~100+ lines of mapping boilerplate PER type,
// the recommended alternative for new code is Strategy A (array params).
// See: https://docs.oracle.com/database/121/ODPNT/featUDTs.htm

// ── Recommended alternative: use Strategy A (separate arrays) ────
// Same Oracle-side FORALL performance, zero custom type boilerplate:
cmd.Parameters.Add("p_ids",      OracleDbType.Int32,   ids,      ParameterDirection.Input);
cmd.Parameters.Add("p_names",    OracleDbType.Varchar2, names,    ParameterDirection.Input);
cmd.Parameters.Add("p_salaries", OracleDbType.Decimal,  salaries, ParameterDirection.Input);

CREATE OR REPLACE PROCEDURE bulk_insert_safe (
    p_ids   IN t_id_list,
    p_names IN t_name_list
)
AS
    bulk_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(bulk_errors, -24381); -- ORA-24381: error(s) in array DML
BEGIN
    FORALL i IN 1 .. p_ids.COUNT SAVE EXCEPTIONS
        INSERT INTO EMPLOYEES (ID, NAME)
        VALUES (p_ids(i), p_names(i));

    COMMIT;

EXCEPTION
    WHEN bulk_errors THEN
        -- SQL%BULK_EXCEPTIONS holds all the errors
        FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(
                'Error at index ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                ': '              || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)
            );
        END LOOP;
        COMMIT; -- commit the SUCCESSFUL rows, skip the bad ones
END;
/

-- ❌ SLOW: Regular FOR loop — context switch on every iteration
FOR i IN 1 .. p_ids.COUNT LOOP
    INSERT INTO EMPLOYEES (ID, NAME, SALARY)
    VALUES (p_ids(i), p_names(i), p_salaries(i));
    -- PL/SQL engine → SQL engine (switch) — repeated N times
END LOOP;

-- ✅ FAST: FORALL — single context switch for all rows
FORALL i IN 1 .. p_ids.COUNT
    INSERT INTO EMPLOYEES (ID, NAME, SALARY)
    VALUES (p_ids(i), p_names(i), p_salaries(i));
    -- PL/SQL engine sends ALL rows → SQL engine at once

-- Key restriction: FORALL body can only be a SINGLE DML statement.
-- You cannot put IF/ELSIF or multiple statements inside FORALL.
-- If you need conditional logic, filter your collection BEFORE FORALL.

public async Task BulkInsertForallInlineAsync(List<Employee> employees)
{
    int[]     ids      = employees.Select(e => e.Id).ToArray();
    string[]  names    = employees.Select(e => e.Name).ToArray();
    decimal[] salaries = employees.Select(e => e.Salary).ToArray();

    await using var conn = db.Create();
    await conn.OpenAsync();

    await using var cmd = conn.CreateCommand();

    // ── How this works ───────────────────────────────────────────────
    // ArrayBindCount tells the Oracle driver: "these are array parameters".
    // The driver sends all rows in one network packet.
    // The PL/SQL anonymous block receives them as associative array bind
    // variables and passes them directly to FORALL — no manual copying needed.
    // Oracle executes the FORALL as a single bulk DML against the SQL engine.
    cmd.CommandText = @"
        BEGIN
            -- FORALL uses the bind arrays directly — no local collection copy needed.
            -- Oracle driver delivers :p_ids, :p_names, :p_salaries as associative arrays.
            -- INDICES OF iterates over every populated index in the bind array.
            FORALL i IN INDICES OF :p_ids
                INSERT INTO EMPLOYEES (ID, NAME, SALARY)
                VALUES (:p_ids(i), :p_names(i), :p_salaries(i));

            COMMIT;
        END;";

    // Set ArrayBindCount BEFORE adding parameters
    cmd.ArrayBindCount = employees.Count;

    cmd.Parameters.Add("p_ids",      OracleDbType.Int32,   ids,      ParameterDirection.Input);
    cmd.Parameters.Add("p_names",    OracleDbType.Varchar2, names,    ParameterDirection.Input);
    cmd.Parameters.Add("p_salaries", OracleDbType.Decimal,  salaries, ParameterDirection.Input);

    await cmd.ExecuteNonQueryAsync();
    Console.WriteLine($"FORALL inline: inserted {employees.Count} rows");
}

// EmployeeSync carries an IsNew flag to distinguish inserts from updates.
public record EmployeeSync(int Id, string Name, decimal Salary, bool IsNew);

public async Task ConditionalBulkAsync(List<EmployeeSync> records)
{
    // ── Step 1: Split in C# ─────────────────────────────────────────────
    // This is the correct approach. FORALL allows only a single DML statement
    // as its body — no IF/ELSE, no routing. Do the split here in C# where
    // it is readable, testable, and does not require any extra Oracle syntax.
    var toInsert = records.Where(r =>  r.IsNew).ToList();
    var toUpdate = records.Where(r => !r.IsNew).ToList();

    await using var conn = db.Create();
    await conn.OpenAsync();
    await using var tx = (OracleTransaction)await conn.BeginTransactionAsync();

    try
    {
        // ── Step 2: FORALL INSERT for new records ──────────────────────────
        if (toInsert.Count > 0)
        {
            int[]     insIds   = toInsert.Select(r => r.Id).ToArray();
            string[]  insNames = toInsert.Select(r => r.Name).ToArray();
            decimal[] insSal   = toInsert.Select(r => r.Salary).ToArray();

            await using var insCmd = conn.CreateCommand();
            insCmd.Transaction    = tx;
            insCmd.CommandText    = @"
                BEGIN
                    FORALL i IN INDICES OF :p_ids
                        INSERT INTO EMPLOYEES (ID, NAME, SALARY)
                        VALUES (:p_ids(i), :p_names(i), :p_sal(i));
                END;";
            insCmd.ArrayBindCount = toInsert.Count;
            insCmd.Parameters.Add("p_ids",   OracleDbType.Int32,   insIds,  ParameterDirection.Input);
            insCmd.Parameters.Add("p_names", OracleDbType.Varchar2, insNames,ParameterDirection.Input);
            insCmd.Parameters.Add("p_sal",   OracleDbType.Decimal,  insSal,  ParameterDirection.Input);
            await insCmd.ExecuteNonQueryAsync();
        }

        // ── Step 3: FORALL UPDATE for existing records ─────────────────────
        if (toUpdate.Count > 0)
        {
            int[]     updIds   = toUpdate.Select(r => r.Id).ToArray();
            string[]  updNames = toUpdate.Select(r => r.Name).ToArray();
            decimal[] updSal   = toUpdate.Select(r => r.Salary).ToArray();

            await using var updCmd = conn.CreateCommand();
            updCmd.Transaction    = tx;
            updCmd.CommandText    = @"
                BEGIN
                    FORALL i IN INDICES OF :p_ids
                        UPDATE EMPLOYEES
                        SET    NAME   = :p_names(i),
                               SALARY = :p_sal(i)
                        WHERE  ID     = :p_ids(i);
                END;";
            updCmd.ArrayBindCount = toUpdate.Count;
            updCmd.Parameters.Add("p_ids",   OracleDbType.Int32,   updIds,  ParameterDirection.Input);
            updCmd.Parameters.Add("p_names", OracleDbType.Varchar2, updNames,ParameterDirection.Input);
            updCmd.Parameters.Add("p_sal",   OracleDbType.Decimal,  updSal,  ParameterDirection.Input);
            await updCmd.ExecuteNonQueryAsync();
        }

        await tx.CommitAsync(); // inserts + updates commit atomically
    }
    catch { await tx.RollbackAsync(); throw; }
}

public async Task BulkInsertWithErrorCollectionAsync(List<Employee> employees)
{
    int[]    ids   = employees.Select(e => e.Id).ToArray();
    string[] names = employees.Select(e => e.Name).ToArray();

    await using var conn = db.Create();
    await conn.OpenAsync();
    await using var cmd = conn.CreateCommand();

    cmd.CommandText = @"
        DECLARE
            bulk_errors EXCEPTION;
            PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
        BEGIN
            FORALL i IN INDICES OF :p_ids SAVE EXCEPTIONS
                INSERT INTO EMPLOYEES (ID, NAME) VALUES (:p_ids(i), :p_names(i));

            COMMIT;
        EXCEPTION
            WHEN bulk_errors THEN
                -- Log failed rows, commit the successful ones
                FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
                    INSERT INTO IMPORT_ERRORS (ROW_INDEX, ERROR_CODE, LOGGED_AT)
                    VALUES (
                        SQL%BULK_EXCEPTIONS(i).ERROR_INDEX,
                        SQL%BULK_EXCEPTIONS(i).ERROR_CODE,
                        SYSDATE
                    );
                END LOOP;
                COMMIT; -- commit successes + error log together
        END;";

    cmd.ArrayBindCount = employees.Count;
    cmd.Parameters.Add("p_ids",   OracleDbType.Int32,   ids,   ParameterDirection.Input);
    cmd.Parameters.Add("p_names", OracleDbType.Varchar2, names, ParameterDirection.Input);

    await cmd.ExecuteNonQueryAsync();
    // Errors were logged to IMPORT_ERRORS table by the PL/SQL block itself
}

✅ USE INLINE WHEN:
   · Logic lives only in this .NET application
   · You want Git to be the single source of truth for all logic
   · Rapid development — no DBA deployment cycle needed
   · One-off data migration / import script
   · Need conditional routing (INSERT some, UPDATE others) in same batch
   · Prototyping before deciding if a stored proc is warranted

✅ USE STORED PROCEDURE WHEN:
   · Same bulk logic is called from multiple apps / services
   · DBA team manages and owns the database-side logic
   · Security model requires EXECUTE privilege, not direct table access
   · Oracle's plan caching for the specific proc body is critical
   · You want the logic to run even if the .NET app is replaced

Your data shape?
  ├─ List<T> objects    → Dapper (direct) OR decompose to arrays (ADO.NET)
  └─ Raw arrays         → Array Binding directly

Need to INSERT large data?
  ├─ <1,000 rows        → Dapper ExecuteAsync (List<T> directly)
  ├─ 1k–100k rows       → ADO.NET Array Binding (project List to arrays)
  ├─ 100k+ rows         → OracleBulkCopy (List → DataTable or IDataReader)
  ├─ CSV / ETL file     → OracleBulkCopy + IDataReader (streaming)
  └─ Via stored proc    → FORALL in PL/SQL + Array Binding from .NET

Need to UPDATE many rows?
  ├─ Same value for all → Single UPDATE WHERE condition
  ├─ Different per row  → ADO.NET Array Binding
  └─ Very large set     → Temp Table + UPDATE JOIN

Need to UPSERT (insert or update)?
  └─ Always             → Oracle MERGE statement
      ├─ Small set      → Dapper MERGE with :params
      └─ Large set      → BulkCopy to GTT + MERGE

Need to DELETE many rows?
  ├─ By condition       → Single DELETE WHERE
  ├─ By IDs (<1000)    → Dapper IN (:Ids)
  └─ By IDs (>1000)    → Array Binding or chunk loop