PL/SQL and Java data corruption?

Hey everyone,

    A developer I work with recently ran into a problem when trying to insert special characters into an Oracle 9.2.0.5 AL32UTF8 database as follows:

Insert into junktable values ('some data',3,'more data ä',0);

Inserting into "junktable" (above) fires a trigger that looks for the existence of an entity reference - '%&%;%'. If the pattern is found, a java stored function is called that replaces the entity reference with the correct Unicode character (ä translates to an Umlaut Accent character in that example). It works great when we run that insert command from SQL*Plus. However, he wants to run this via an "Execute Immediate" command. As everyone knows, you have to run that from PL/SQL. That's where the problem comes in. The entire returned string, for that column, is garbage. In fact, if I take out the execute immediate, and just run it inside a PL/SQL block:

Begin
Insert into junktable values ('some data',3,'more data ä',0); End;
/

...the string is also returned as garbage. So, it appears to be a PL/SQL / java interaction problem. Has anyone else run into this problem? Any suggestions or work-arounds?

Thanks,

Alan

--

Alan Aschenbrenner

Senior Database Administrator, IHS
Engineering

alan.aschenbrenner_at_ihs.com
www.ihs.com  

Confidentiality Notice: The information in this e-mail may be confidential and / or privileged. This e-mail is intended to be reviewed by only the individual or organization named in the e-mail address. If you are not the intended recipient, you are hereby notified that any review, dissemination or copying of this e-mail and attachments, if any, or the information contained herein, is strictly prohibited.
--

http://www.freelists.org/webpage/oracle-l Received on Tue Jun 19 2007 - 10:04:37 CDT