ORA 22922 nonexistent lob value with stored procedure

From: Patric Mueller <patric.mueller_at_xplain.ch>
Date: Tue, 29 Nov 2005 18:50:13 +0100
Message-ID: <lzr78z2vje.fsf@bhaak.dyndns.org>

Hi

We have the following code which works flawlessly with Oracle 9.2, but throws an exception with Oracle 10g.

The blobl zip_desc is filled with data in the java based prepared call f_getLoginZip. In f_getLoginZip the blob is filled without problems and testing code shows that it contains the right data.

But after cstmt.execute() completes, the blob can no longer be referenced with zip_desc. The thrown exception is "ORA-22922: nonexistent lob value".

Is there another solution but to rewrite f_getLoginZip to return a BLOB? Bye
Patric

cstmt = (OracleCallableStatement) dbConn.prepareCall("{?=call f_getLoginZip(?,?,?,?)}");

cstmt.registerOutParameter (1, Types.INTEGER);
cstmt.setString(2, username);
cstmt.setString(3, application);
cstmt.setBLOB(4, zip_desc);
cstmt.setCLOB(5, xlogin_desc);

System.out.println(zip_desc.length()); // -> 0 cstmt.execute();
System.out.println(zip_desc.length()); // -> Exception: ORA 22922 Received on Tue Nov 29 2005 - 11:50:13 CST