Writing CLOB to Oracle via JDBC
_Andy_
I'm trying to set the contents of a CLOB to a specific value (String).
Here's the line of Java code which sets the value of the CLOB:
-- begin extract --
statement.setAsciiStream(
2,
new ByteArrayInputStream( string.getBytes() ),
string.length() );
statement.execute()
-- end extract --
This works with short strings, but with large strings (>5000
characters) it fails with the following SQLException:
-- begin exception trace --
java.sql.SQLException: ORA-00600: internal error code, arguments:
[kokleva], [], [], [], [], [], [], []
at
oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:542)
at
oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1311)
at
oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:738)
at
oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1313)
at
oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1232)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithBatch(OracleStatement.java:1353)
at
oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java:1760)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1805)
at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:320)
at
oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:364)
-- end exception trace --
I've tried both of the following:
statement.setAsciiStream(
1,
new StringBufferInputStream( string ),
string.length() );
statement.setCharacterStream(
1
new StringReader( string ),
string.length() );
... but both produce exactly the same reaction.
Perhaps I should point out that I'm actually using one of my
own helper classes in which the setBigString() is implemented to set
the value of a CLOB. The full function is listed here:
-- begin function --
public void setBigString(
PreparedStatement statement,
int columnIndex,
String string) throws SQLException
{
if( string == null )
{
statement.setNull( columnIndex , Types.CLOB );
}
else
{
statement.setAsciiStream(
columnIndex,
new ByteArrayInputStream( string.getBytes() ),
string.length() );
}
}
-- end function --
Anyone know what I'm doing wrong?
_Andy_
Thanks, I got a reply via email which pointed me in the right
direction. As it is probably a frequent question, I've decided to post
the code to perform the task for future reference.
Oh yeah, and as I've been working on this all day like some bloody
monkey, it's here for any other monkeys. ;)
This code will set the value of an existing Oracle 8 CLOB to a
particular Java String value.
-- begin code extract --
// This SQL statement will read the row/colunm and lock it
String sql = "select text from item_description where item=" + mID + "
for update of text";
// Get our existing connection and turn off auto-commit
Connection connection = sqlService.getConnection();
connection.setAutoCommit( false );
// Prepare the statement and execute it
PreparedStatement statement = connection.prepareStatement( sql );
ResultSet results = statement.executeQuery();
if( results.next() )
{
// Get the Oracle CLOB object
CLOB clob = (CLOB)((OracleResultSet)results).getClob("text");
// Open a stream to the object and write the long string
Writer writer = clob.getCharacterOutputStream();
writer.write( itemDescription.toCharArray() );
writer.flush();
writer.close();
}
// Save the changes and turn auto-commit back on
connection.commit();
connection.setAutoCommit( true );
-- end code extract --
Important Notes
============
The documentation on Oracle site is as good as ever, so I'll post the
important bits.
If you wish to create a CLOB value from new... You create your row as
normal, but use the empty_clob() SQL function to initialise the CLOB
column. e.g. "INSERT INTO mytable (id,text) VALUES(123,empty_clob())"
From then on, you can use the above code to set the actual CLOB
contents.
You MUST read an Oracle CLOB (oracle.sql.CLOB) object in order to
update it.
The SQL used to select the row/column to update must lock the row. As
this is the case, you must disable setAutoCommit() if it is used, and
commit the action yourself.
You must use the OracleResultSet, and the Oracle CLOB objects to use
the proper members.
Using the setAsciiStream() and setCharacterStream() methods of the
PreparedStatement class don't work for longer strings - use the Writer
method described above with the Oracle objects located in oracle.sql.*
and oracle.jdbc.*
hth