PL/SQL and Java data corruption?

  • From: "Aschenbrenner, Alan" <alan.aschenbrenner@xxxxxxx>
  • To: "Oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 19 Jun 2007 09:04:37 -0600

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 &auml;',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 (&auml; 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 &auml;',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@xxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2007