Organizational Research By

Surprising Reserch Topic

Experts Most Trusted Topic


oracles returning into usage in java jdbc prepared statement


oracles returning into usage in java jdbc prepared statement  using -'java,oracle,jdbc,plsql,prepared-statement'

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:


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?


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


Thank you,
  AG.
    

asked Sep 24, 2015 by maurya
0 votes
12 views



Related Hot Questions



Walkin Jobs Opening



Government Jobs Opening


...