I'm trying to advance a sequence by a negative number but I keep getting the following error:
ORA-02286: no options specified for ALTER SEQUENCE
So far no amount of Googling has helped. The statement that I use is
ALTER SEQUENCE %s.nextval INCREMENT BY ?
The sequence name is generally schema.table_name_seq. Including the schema name is unavoidable because the connection is not necessarily to the same one.
Appending an inconsequential option like minvalue 1 did nothing to satisfy it either.
My second attempt was to drop .nextval from the query and run ALTER directly on the sequence (just throwing stuff at the wall to see what sticks at this point), ie:
ALTER SEQUENCE %s INCREMENT BY ?
which results in
ORA-01722: invalid number
Next I supplied a positive integer and negated it in the query (INCREMENT BY -?) which produced the same error. The query is prepared as such:
num = -1 * Math.abs(num);
stmt = conn.prepareStatement(sql);
stmt.setLong(1, num); //also tried setInt()
stmt.execute();
The idea is to set the .nextval sequence for a given table back to an arbitrary number (but always more than minval).
JustinCave's comment was most illuminating:
alter sequenceis DDL. You cannot use bind variables in DDL. You would need to dynamically build the DDL statement you want and execute that and it wouldn't make sense to use a PreparedStatement to do so.
Recognizing that there are better ways to do it, but acknowledging time constraints, I constructed the following SQL statement:
ALTER SEQUENCE %s INCREMENT BY %d
which works like a charm. Yes, the number to advance by is hardcoded in each request. Not the best solution but I cannot create stored procedures at this point, so it'll have to do.
Thank you Justin.