update using 'returning' clause with JDBC using oracle

[ayrobins]

Has anyone tried using update with a 'returning' clause in jdbc with oracle.
I'm trying to do something like:

String sql = "update longtest2 set col1='b', col3='b' returning col1 into
:col1";
ResultSet rs = s.executeQuery(sql);

But this does not seem to work. I get a stack trace as follows:
java.sql.SQLException: ORA-01043: user side memory corruption [], [], [], []
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:1597)
at oracle.jdbc.oci8.OCIDBAccess.executeFetch(OCIDBAccess.java:1209)
at
oracle.jdbc.oci8.OCIDBAccess.parseExecuteFetch(OCIDBAccess.java:1321)

at
oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.ja
va:1446)
at
oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.jav
a:1371)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
nt.java:1900)
at
oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:

Any ideas?

-- anthony

[AV]

<from metalink.oracle.com>

As of JDBC Release 817, DML Returning is not supported by any
of the JDBC drivers (thin, OCI, or kernel).
Although there is no native support for DML Returning in the
drivers, you can still implement it via PL/SQL from JDBC.

Below is code snippet example of implementing DML Returning
via pl/sql from JDBC:

[snip]
static private void testCallable(String longStr, int id)
throws SQLException {

CallableStatement cstmt = null;
try {
cstmt = conn.prepareCall
("BEGIN INSERT INTO TEST (ID, VAL) " +
"VALUES (" + id + ",?) RETURNING ID INTO ?; END;");
cstmt.setString(1, longStr);
cstmt.registerOutParameter(2, Types.INTEGER);
int rowsUpdated = cstmt.executeUpdate();
String idStr = cstmt.getString(2);
conn.commit();
System.out.println("Callable statement test inserted " +
rowsUpdated + " row and returned ID=" +
idStr);
}
catch ( SQLException e ) {
conn.rollback();
descException(e, "testCallable()");
}
finally {
if (cstmt != null) cstmt.close();
}
}
</from metalink.oracle.com>

AlexV.

P.S. I hope it is not illegal to copy from this site.(???)

"ayrobins" <ant...@lumos.com> wrote in message
news:wLVw8.816$M6.5...@dca1-nnrp2.news.algx.net...

[crappy]

i've never seen that error message before, it doesn't sound too good.

but in general, to do this you can use an anonymous pl/sql block like
so:

Connection conn = getConnection();
conn.setAutoCommit(false);
CallableStatement stmt = conn.prepareCall("begin update longtest2 set
col1='b', col3='b' returning col1 into ?; end;");
stmt.registerOutParameter(1, Types.VARCHAR); // or whatever col1 is
stmt.execute();
System.out.println(stmt.getString(1));

...

however, i notice that you have no 'where' clause for your update; are
you trying to return a resultset of updated values? this can be done
too, but you will need to create a sql collection type and then use
the 'bulk collect' directive in the returning clause.

"ayrobins" <ant...@lumos.com> wrote in message news:<wLVw8.816$M6.5...@dca1-nnrp2.news.algx.net>...

[ayrobins]

thanx for the response.

So besides using a stored procedure, there is no other way of
getting return values in an update statement?

-- anthony

"AV" <avek_...@videotron.ca> wrote in message
news:Am0x8.23791$jI6.9...@weber.videotron.net...