Creating Sequence in Oracle using JDBC PreparedStatment

Question

PreparedStatement updateSeq = null;
String createQuery ="CREATE SEQUENCE AVIA START WITH ? INCREMENT BY 1";
try {
    con.setAutoCommit(false);
    updateSeq = con.prepareStatement(createQuery);
    updateSeq.setLong(1, 1000000000000000l);
    updateSeq.executeUpdate();
    con.commit();
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    try {
        updateSeq.close();
        con.setAutoCommit(true);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

This gives me "java.sql.SQLSyntaxErrorException: ORA-01722: invalid number" what am i doing wrong.

Do you really need to use a parameterised query for this? You're hardcoding the value of the parameter anyway, so you may as well hardcode it into the string instead. — JonK, May 27 '16 at 10:56
@ JonK yes i had to create new statement based on schema , for each schema i had to change the index value , everything remain the same. as you have mentioned i will create the string. — user2478236, May 30 '16 at 06:07

Alex Poole · Accepted Answer · 2016-05-27T11:36:47.217

You can only parameterise run-time values, not elements of the statement that have to be known and fixed at parse time. For data definition (DDL) statements that effectively means you cannot use parameters at all.

The bind placeholder ? is seen as a literal character at parse time - no bind substitution is being done - and as the error says a question mark is not a valid number and can't be implicitly converted to one. You haven't shown the error stack, but it should be coming from the prepareStatement() call, not the executeUpdate() call.

Although the advice is usually (quite correctly) to use parameters, sometimes it just isn't possible, so you'll have to concatenate the start value:

createQuery ="CREATE SEQUENCE AVIA START WITH " + startVal + " INCREMENT BY 1";

In this case as the value you're using seems to be fixed you might as well just include that in the string:

createQuery ="CREATE SEQUENCE AVIA START WITH 1000000000000000 INCREMENT BY 1";

And then you might as well use a plain statement rather than a prepared statement, and just execute it.

Incidentally, DDL also implicitly commits, so both setting auto-commit off and then manually committing are pointless.

@ Alex Poole thank you for the detailed explanation , as you have mentioned for my requirement concatenating might be the way to go. — user2478236, May 30 '16 at 06:05

Creating Sequence in Oracle using JDBC PreparedStatment

1 Answers1