Problems with Oracle JDBC and Unicode data inserts
Wes Gamble
Thanks for any help in advance.
I have recently learned how to properly insert CLOB values into an
Oracle database using JDBC and the Oracle thin driver.
I am inserting a row into a table with two NCLOB columns.
Everything works fine until I attempt to insert Unicode characters to
verify that the NCLOB will take them. I can set the NCLOB column to
any string which contains characters up to and including Unicode 00c1
(decimal 193), but if there is a Unicode character >= 00c2 anywhere in
the string, then I get the following error:
java.io.IOException: ORA-22993: specified input amount is greater than
actual source amount
Here is the code I use to update the NCLOB column:
CLOB policyStatementTextCLOB =
((OracleResultSet) rs).getCLOB("PolicyStatementText");
sr = new StringReader(policyStatementText);
char[] buffer = new char[policyStatementTextCLOB.getBufferSize()];
out = policyStatementTextCLOB.getCharacterOutputStream();
int length = 0;
while ((length = sr.read(buffer)) != -1) {
out.write(buffer, 0, length);
}
The error comes when I flush the variable out (output stream). I
basically went with an example from _Java Programming with Oracle
JDBC_ (O'Reilly).
I thought that perhaps the problem had to do with the fact that
Reader.read(cbuf) returns bytes, but Writer.write expects # of
characters in the length field. I haven't been able to verify this
yet.
If you have any insight, please let me know.
Thanks,
Wes Gamble
Sebastiano Pilla
> I am inserting a row into a table with two NCLOB columns.
> Everything works fine until I attempt to insert Unicode characters to
> verify that the NCLOB will take them. I can set the NCLOB column to
> any string which contains characters up to and including Unicode 00c1
> (decimal 193), but if there is a Unicode character >= 00c2 anywhere in
> the string, then I get the following error:
>
> java.io.IOException: ORA-22993: specified input amount is greater than
> actual source amount
Can you try the insert with some other tool, like SQL*Plus or TOAD? If
you get the same error when using non-Java based tools, it may be that
your database was created with a character set different from the one
that your data is in.
If possible, have a DBA run "select * from nls_database_parameters;" in
SQL*Plus and then look at the result: it should tell you what character
set is your database in.
Sebastiano Pilla
Wes Gamble
This was a good idea, just to verify that things were as they should
be.
As I expected, a direct insert of Unicode data into my test table
works just fine. Using NCLOB relieves you of the need to be concerned
about which character set the database is using.
Wes Gamble