oracle sequence number

austinoyoung — Fri, 31 Oct 2025 15:54:58 GMT

Dear All,

I am trying to use jdbc driver to connect to an oracle database and append a new record to a table. The table has a column needs to be populated with a sequence number. I've been trying to use select `<sequence_name>.nextval` to get the sequence number but got the `ORA-02287: sequence number not allowed here` error. I did some research online and some said we can't do this with pyspark so I wonder if anyone was able to do this without writing pure python code? Thanks so much!

Re: oracle sequence number

Louis_Frolio — Fri, 31 Oct 2025 17:31:17 GMT

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)

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()

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.

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.

Re: oracle sequence number

austinoyoung — Fri, 31 Oct 2025 17:56:03 GMT

Thank you, Louis, for the information!

I really like the identity column solution, but I have to reach out to our dba for implementation.🤞

Thanks again!

topic oracle sequence number in Data Engineering

oracle sequence number

Re: oracle sequence number

Re: oracle sequence number