.nextval JDBC insert problem
27,688
The problem is that the first column is a numeric data type, but your prepared statement is submitting a string/VARCHAR data type. The statement is run as-is, there's no opportunity for Oracle to convert your use of nextval to get the sequence value.
Here's an alternative via Java's PreparedStatement syntax:
sql = "INSERT INTO USER
(USER_PK, ACCOUNTNUMBER, FIRSTNAME, LASTNAME, EMAIL )
VALUES
(user.nextval, ?, ?, ?, ?)";
ps = conn.prepareStatement(sql);
ps.setString(1, accountNumber);
ps.setString(2, firstName);
ps.setString(3, lastName);
ps.setString(4, email);
This assumes that user
is an existing sequence -- change to suit.
Author by
jasonfungsing
Updated on July 18, 2022Comments
-
jasonfungsing almost 2 years
I try to insert into table with sequence .nextval as primary key, the sql in Java is
sql = "INSERT INTO USER (USER_PK, ACCOUNTNUMBER, FIRSTNAME, LASTNAME, EMAIL ) VALUES (?,?,?,?,?)"; ps = conn.prepareStatement(sql); ps.setString(1, "User.nextval"); ps.setString(2, accountNumber); ps.setString(3, firstName); ps.setString(4, lastName); ps.setString(5, email);
However, the error is
ORA-01722: invalid number
All the other fields are correct, I think it is the problem of sequence, is this correct?
-
OMG Ponies over 13 yearsThough personally, this would be an Oracle function if it were up to me. Then the sequence reference would be in the function, just supply the same parameters for insertion.
-
Skizo-ozᴉʞS about 7 yearsWorked like a charm THANKS! :D