RE: PL/SQL and Java data corruption?

From: Aschenbrenner, Alan <alan.aschenbrenner_at_ihs.com>
Date: Tue, 19 Jun 2007 10:12:21 -0600
Message-ID: <DEE5A3388F457E48B35AAF9666A357D8013C9B15@s255478hz1ew05.hosted.internal.corp>

Valentin,

Thanks for the response, but the problem is with the interaction of the trigger (that calls a java function), to replace the "ä" with the corresponding Umlaut character. If I turn off the trigger, the "ä" inserts into the table without problems (but as "ä", 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_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.

From: Valentin Minzatu [mailto:valentinminzatu_at_yahoo.com] 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 \ä',0); 3* End;
SQL> / PL/SQL procedure successfully completed.

SQL> select * from junktable;

TEXT1                   ID1 TEXT2                   ID2


---------------- ---------- ---------------- ----------

some data                 3 more data &auml;          0

SQL> Valentin

Original Message ---- From: "Aschenbrenner, Alan" <alan.aschenbrenner_at_ihs.com> To: Oracle-l <oracle-l_at_freelists.org> 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 ä',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 <http://www.ihs.com/>

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jun 19 2007 - 11:12:21 CDT