Databricks Community

austinoyoung · 2 weeks ago

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!

Louis_Frolio · 2 weeks ago

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.

View solution in original post

Louis_Frolio · 2 weeks ago