I have a table with a auto-incrementing id. From time to time I want to insert rows to this table, but want to be able to know what the pk of the newly inserted row is. One way I could do this is:
SQL> variable var1 number;
SQL> insert into test (name) values ('test value') returning id into :var1;
1 row created.
SQL> print var1;
13
As best as I can write it, that in java should be:
String query = "insert into test (name) values ('test') returning id into :var1";
OracleCallableStatement cs = (OracleCallableStatement) conn.prepareCall(query);
cs.registerOutParameter(1, OracleTypes.NUMBER );
cs.execute();
System.out.println(cs.getInt(1));
The problem is that when I run it, I get an error:
java.sql.SQLException: Protocol violation
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:764)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
at restitution.shared.Sandbox2.run(Sandbox2.java:25)
at restitution.shared.Sandbox2.main(Sandbox2.java:11)
According to their website, this is a bug:
What does "Protocol Violation" mean?
The Thin driver throws this exception when it reads something from the RDBMS that it did not expect. This means that the protocol engine in the Thin driver and the protocol engine in the RDBMS are out of synch. There is no way to recover from this error. The connection is dead. You should try to close it, but that will probably fail too.
If you get a reproducible test case that generates this error, please file a TAR with Oracle Global Support. Be sure to specify the exact version numbers of the JDBC driver and the RDBMS, including any patches.
Can someone tell me what I'm doing wrong? Is there any other ways to do a insert / get key in one sql query ?
EDIT:
I was able to fix it by adding:
query = "BEGIN " + query + "; END;"
before execution