Laravel OCI8 Bulk Insert using collection POC

EmailFactory.php

<?php

namespace Database\Factories;

use Illuminate\Database\Eloquent\Factories\Factory;

/**
 * @extends \Illuminate\Database\Eloquent\Factories\Factory<\App\Models\Email>
 */
class EmailFactory extends Factory
{
    /**
     * Define the model's default state.
     *
     * @return array<string, mixed>
     */
    public function definition(): array
    {
        return [
            'user_id' => fake()->userName(),
            'name' => fake()->name(),
            'email' => fake()->safeEmail(),
        ];
    }
}

exec-sp.php

<?php

$collection = EmailFactory::new()->count(10)->make()->toArray();

/** @var Yajra\Oci8\Oci8Connection $conn */
$conn = DB::connection();

/** @var Yajra\Pdo\Oci8 $pdo */
$pdo = $conn->getPdo();

$nestedTable = $pdo->getNewCollection('TEMP_NT');
foreach ($collection as $item) {
    $nestedTable->append(json_encode($item) ?: '');
}
$conn->executeProcedure('update_address_book', [
    'p_nt' => ['value' => $nestedTable, 'type' => SQLT_NTY],
]);

return DB::table('emails')->count(); // 10

nested-table-loop.php

<?php

Route::get('nt', function () {
    Schema::dropIfExists('emails');
    Schema::create('emails', function (OracleBlueprint $table) {
        $table->id();
        $table->string('user_id');
        $table->string('name');
        $table->string('email');
    });

    DB::statement('CREATE OR REPLACE TYPE EMAILS_NT AS TABLE OF VARCHAR2(4000)');

    DB::statement('
            create or replace procedure update_address_book(
                p_emails in EMAILS_NT
            ) is
            begin
                for i in 1..p_emails.count loop
                    insert into emails (user_id, name, email)
                    select * from json_table(p_emails(i), \'$\' columns (
                        user_id VARCHAR2(255) path \'$.user_id\',
                        name VARCHAR2(255) path \'$.name\',
                        email VARCHAR2(255) path \'$.email\'
                    ));
                end loop;
            end update_address_book;
        ');

    $data = EmailFactory::new()->count(10)->make();

    /** @var Yajra\Pdo\Oci8 $pdo */
    $pdo = DB::getPdo();
    if (! $collection = $pdo->getNewCollection('EMAILS_NT')) {
        throw new Exception('Cannot create collection');
    }

    for ($i = 0; $i < count($data); $i++) {
        $collection->append(json_encode($data[$i]));
    }

    $statement = $pdo->prepare('begin update_address_book(:collection); end;');
    $statement->bindParam(':collection', $collection, SQLT_NTY);
    $statement->execute();

    return DB::table('emails')->count(); // 10
});

nested-table-single-insert.php

<?php

Route::get('nt', function () {
    Schema::dropIfExists('emails');
    Schema::create('emails', function (OracleBlueprint $table) {
        $table->id();
        $table->string('user_id');
        $table->string('name');
        $table->string('email');
    });

    DB::statement('CREATE OR REPLACE TYPE TEMP_NT AS TABLE OF VARCHAR2(4000)');

    DB::statement("
        create or replace procedure update_address_book(
            p_nt in TEMP_NT
        ) is
        begin
            insert into emails (user_id, name, email)
            select
                json_value(COLUMN_VALUE, '$.user_id'),
                json_value(COLUMN_VALUE, '$.name'),
                json_value(COLUMN_VALUE, '$.email')
            from table(p_nt);
        end update_address_book;
    ");

    $data = EmailFactory::new()->count(100_000)->make();

    /** @var Yajra\Pdo\Oci8 $pdo */
    $pdo = DB::getPdo();
    if (! $collection = $pdo->getNewCollection('TEMP_NT')) {
        throw new Exception('Cannot create collection');
    }

    for ($i = 0; $i < count($data); $i++) {
        $collection->append(json_encode($data[$i]));
    }

    $statement = $pdo->prepare('begin update_address_book(:collection); end;');
    $statement->bindParam(':collection', $collection, SQLT_NTY);
    $statement->execute();

    return DB::table('emails')->count(); // 100k
});