heather zhang, February 13, 2017 - 2:26 am UTC
Thanks Connor.
in case of just sql or like java (not pl/sql), how can the identify value inserted be retrieved.
thanks
Heather
February 13, 2017 - 10:40 pm UTC
Same as the SQL Plus example - you provide a host variable.
SQLPlus not available over JDBC
Vladimir, August 23, 2017 - 9:24 pm UTC
Can't make the solution work over JDBC. It seems that to declare a variable we need SQLPlus but that is unfortunately not available through JDBC. Can you post a JDBC example?
Do you know of any other idea to retrieve the identity value?
The other workaround would be to create one PL/SQL procedure for each identity column, but this seems overkill just for simple inserts.
Maybe I'm missing something.
Thank you,
Vladimir
August 24, 2017 - 1:36 am UTC
Here's a simple example
SQL> create table t ( x int, y varchar2(10) default 'HELLO');
Table created.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.time.ZonedDateTime;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.internal.OraclePreparedStatement;
public class JDBCreturning {
public static void main(String[] args) {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:scott/tiger@//localhost:1521/db122");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
try {
OraclePreparedStatement pstmt = (OraclePreparedStatement)con.prepareStatement(
"insert into t ( x ) values ( 1 ) returning y into ?");
pstmt.registerReturnParameter(1, OracleTypes.VARCHAR, 20);
boolean done;
done = pstmt.execute();
ResultSet rset = pstmt.getReturnResultSet();
while(rset.next())
{
String name = rset.getString(1);
System.out.print(name);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
C:\temp>java JDBCreturning
HELLO
Variant
Racer I., August 25, 2017 - 1:03 pm UTC
Hi,
I recently discovered that this is supported by JDBC more directly/abstractly :
stmt = connection.prepareStatement("INSERT...(without RETURNING)", new String[] { "ID" });
stmt.executeBatch();
generatedKeys = stmt.getGeneratedKeys();
while (generatedKeys.next()) {
ID = generatedKeys.getLong(1);
}
I assume this is handled with RETURNING if the DB is Oracle. I assume order is the same as the INSERT (for bulking).
It works with SEQUENCE_NEXTVAL in the Statement.
I guess it will also work with Trigger or Identity.
August 26, 2017 - 3:22 am UTC
Nice input.
But I think you only get access to the keys, not the other columns.
Variant with Generated Keys
Tech Wanker, July 24, 2018 - 4:40 am UTC
Variant
August 25, 2017 - 1:03 pm UTC
Reviewer: Racer I.
wrote about using generated keys.
However the generated keys are ROWIDs and the toLong is not going to return the generated primary key.