2026-09-01 · run 2026-09-01-seed-sql-oracle-port

seed-sql.sh ported to Oracle (CLOB credential + CONNECT BY)

What was done

bin/seed-sql.sh now supports oracle. Two Oracle-specific mechanics, both of which are load-bearing rather than cosmetic:

1. Row generation — CONNECT BY LEVEL. Oracle has no generate_series and no recursive CTE (well, it has recursive WITH, but the idiomatic generator is SELECT lo + LEVEL - 1 FROM dual CONNECT BY LEVEL <= (hi-lo+1)).
2. Credential copy — a PL/SQL block. SECRET_DATA / CREDENTIAL_DATA are CLOB on Oracle (confirmed from all_tab_columns; the changelog creates them as CLOB and only the fill SQL is Oracle-specific, because Oracle's CONCAT takes exactly two args). A scalar subquery returning a CLOB is rejected (ORA-00932/ORA-22848), so the template credential is read into PL/SQL CLOB variables with SELECT … INTO and then referenced by the INSERT — the value never crosses the shell boundary (important: the argon2 secret_data contains $argon2id$…).

Also engine-specific: EMAIL_VERIFIED/ENABLED are native BOOLEAN (insert TRUE), USERNAME/FIRST_NAME/LAST_NAME/VALUE are NVARCHAR2, CREATED_TIMESTAMP is NUMBER (ms epoch via ROUND((SYSDATE - DATE '1970-01-01') * 86400000)), and identifiers are UPPERCASE (shared with the mysql/mariadb branch). Stats are gathered with DBMS_STATS.GATHER_TABLE_STATS and the planner estimate is all_tables.num_rows; size is user_segments.

Result (verified at 100 users, then extension + multi-batch + --no-analyze)

Gotcha worth recording

The deterministic sql-<32-digit> ids are a table-wide primary key, not per-realm. Seeding a second realm with the same --start/--users range collides (ORA-00001 … ID already exists) — this is true on every engine, not Oracle-specific, and is the reason --start exists. Re-running an overlapping range is not idempotent; the script assumes each range is seeded once.

Verification