Re: PL/SQL and Java data corruption?

  • From: Valentin Minzatu <valentinminzatu@xxxxxxxxx>
  • To: alan.aschenbrenner@xxxxxxx, Oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 19 Jun 2007 08:57:30 -0700 (PDT)

Hi Alan,

I am not sure how you escape the '&' sign, but if escaped properly and escape 
is set to ON, your PL/SQL block works fine - at least in my env (10.1.0.4) - 
see the output below:

SQL> create table junktable(text1 varchar2(64), id1 number, text2 varchar2(64), 
id2 number);

Table created.

SQL> set escape on
  1  Begin
  2  Insert into junktable values ('some data',3,'more data \&auml;',0);
  3* End;
SQL> /

PL/SQL procedure successfully completed.

SQL> select * from junktable;
TEXT1                   ID1 TEXT2                   ID2
---------------- ---------- ---------------- ----------
some data                 3 more data &auml;          0                         
                        0

SQL>

Valentin


----- Original Message ----
From: "Aschenbrenner, Alan" <alan.aschenbrenner@xxxxxxx>
To: Oracle-l <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, June 19, 2007 11:04:37 AM
Subject: 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 &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: