RE: PL/SQL and Java data corruption?

  • From: "Aschenbrenner, Alan" <alan.aschenbrenner@xxxxxxx>
  • To: "Valentin Minzatu" <valentinminzatu@xxxxxxxxx>, "Oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 19 Jun 2007 10:12:21 -0600

Valentin,

 

    Thanks for the response, but the problem is with the interaction of
the trigger (that calls a java function), to replace the "&auml;" with
the corresponding Umlaut character.  If I turn off the trigger, the
"&auml;" inserts into the table without problems (but as "&auml;", not
the actual character we want).  We need the entity reference to be
replaced with its Unicode equivalent character.

 

From SQL*Plus, the insert statement fires the trigger, which calls the
java function, and the proper Umlaut character is replaced into the
string and stored.

But, from SQL*Plus inside a PL/SQL block, that same insert puts garbage
in the field that the trigger/function modifies.

 

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.

________________________________

From: Valentin Minzatu [mailto:valentinminzatu@xxxxxxxxx] 
Sent: Tuesday, June 19, 2007 9:58 AM
To: Aschenbrenner, Alan; Oracle-l
Subject: Re: PL/SQL and Java data corruption?

 

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 <http://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: