Hey @austinoyoung , 

Short answer: Don’t try to pull the sequence in your Spark insert. Let Oracle assign it.

Why this happens (ORA-02287: sequence number not allowed here😞

Spark’s JDBC writer generates parameterized INSERT statements like:

INSERT INTO your_table (id, col_a, col_b) VALUES (?, ?, ?)

The ? parameters must be literal values. Oracle sequences (e.g., myseq.nextval) are expressions and cannot be used in that context. That’s why Oracle rejects the insert.

Recommended approaches (no pure Python loops)

1. Identity column (Oracle 12c+)

   If you can alter the table, make the key auto-generated by Oracle:

ALTER TABLE your_table
  MODIFY (id NUMBER GENERATED BY DEFAULT AS IDENTITY);

Then drop the id column before writing from Spark:

df.drop("id").write \
    .format("jdbc") \
    .option("url", jdbcUrl) \
    .option("dbtable", "your_table") \
    .option("user", user) \
    .option("password", pwd) \
    .mode("append") \
    .save()

2. Default value backed by a sequence (also Oracle 12c+)

   Keep your sequence but make the column default to it:

ALTER TABLE your_table
  MODIFY (id NUMBER DEFAULT your_seq.NEXTVAL);

Then, again, don’t send id from Spark.

3. BEFORE INSERT trigger (works on older Oracle versions)

   If identity/default isn’t allowed:

CREATE OR REPLACE TRIGGER your_table_bi
BEFORE INSERT ON your_table
FOR EACH ROW
WHEN (NEW.id IS NULL)
BEGIN
  :NEW.id := your_seq.NEXTVAL;
END;
/

Omit id in your DataFrame and Oracle will set it automatically.

Why not generate IDs in Spark?

Functions like monotonically_increasing_id() or UUIDs won’t increment the Oracle sequence, and can cause gaps or ordering issues if other systems rely on Oracle’s sequence.

Summary

• Don’t try to select the sequence in your Spark insert.
• Let Oracle handle the ID via identity, default+sequence, or a trigger.
• Just drop the id column from your DataFrame before writing.

Hope this helps, Louis.