Oracle's RETURNING INTO usage in Java (JDBC, Prepared Statement)

21
votes

I'm using JDBC to execute Oracle statement which looks like this:

"INSERT INTO MYTABLE(MYDATA) VALUES(?) RETURNING MY_CALCULATED_DATA INTO ?"
// MYTABLE's def makes MY_CALCULATED_DATA be auto-generated by DB on insert

I found several ways to call the statement above in Java, mainly:

  • Using OraclePreparedStatement:

    ps = (OraclePreparedStatement)conn.prepareStatement(sql);
ps.setString(1, "myvalue");
ps.registerReturnParameter(2, Types.VARCHAR);
ps.execute();
rs = ps.getReturnResultSet();
rs.next();
System.out.print(rs.getString(1));

  • Using CallableStatement:

    cs = conn.prepareCall(sql);
cs.setString(1, "myvalue");
cs.registerOutParameter(2, Types.VARCHAR);
cs.execute();
System.out.print(cs.getString(1));

Questions:

  1. Method #2 throws "SQLException: Not all return parameters registered", BUT, if I wrap the SQL statement into "BEGIN..END;" - then method #2 works just fine.
    • Why method #1 works without "BEGIN..END" but method #2 requires "BEGIN..END" to work?
    • What kind of "magic" "BEGIN..END" does to the statement so that "not all parameters registered" problem suddenly solves itself?

  2. Is there some third, better method of doing the above?

Thank you, AG.

2
javaoraclejdbcplsqlprepared-statement
Method 1 is returning a result set, so ps.getRetrunResultSet() works correctly. The begin/end creates an anonymous pl/sql block, and no result set it returned, which is why you need to execute() call. – OldProgrammer
I have execute() call in both methods, the question is why begin/end causes the second method to work and what is the "ultimate best" way to do what I wanted to. Thanks. – Wanna Know All

2 Answers

6
votes

Because parameters specified in returning clauses are handled in a different way compared to normal output parameters(getReturnResultSet vs getResultSet vs returning parameters in a callablestatement).
They need to be handled with OraclePreparedStatement. In the second case when you wrap the insert statement in begin..end the insert is handled by the database itself and al jdbc sees is an anonymous plsql block.
http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraint.htm#BABJJDDA

4
votes

To get auto generated key we have method getGeneratedKeys method in preparestatement which return resultset that contain key value all we need is pass key column name to preparestatement 

pstm = con.prepareStatement("insert query",new String[]{primarykeycolumnname});
int i = pstm.executeUpdate();
if (i > 0) 
{
    ResultSet rs = pstm.getGeneratedKeys();
    while(rs.next())
    {
        System.out.println(rs.getString(1)); 
    }
}