CODEWITHSUNDEEP
javadatabasejdbcoracle11g
user2478236
user2478236

Reputation: 687

Creating Sequence in Oracle using JDBC PreparedStatment

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.

Upvotes: 1

Views: 2884

Answers (1)

Alex Poole
Alex Poole

Reputation: 191285

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.

Upvotes: 4

Related Questions