RE: UTF character set application problem

From: Marc Perkowitz <mperkowitz_at_comcast.net>
Date: Tue, 19 Oct 2004 16:30:01 -0500
Message-ID: <004701c4b622$d01a62f0$34099943@MTPSYS>

Mike,

Yes, we still had the problem until I changed the CHAR columns back to = being
allocated based on bytes. We are on 9.2.0.5 (Linux), so I am not sure = if
this bug is the problem or not. What I do know if that our JDBC-based application did not work after we changed the parameter and executed the PL/SQL script shown below.
=20
My message is -- be aware that these changes are not necessarily = harmless.

Marc.

-----Original Message-----
From: mhthomas [mailto:qnxodba_at_gmail.com]=20 Sent: Tuesday, October 19, 2004 12:35 AM To: mperkowitz_at_comcast.net
Cc: Matjaz Jordan; oracle-l_at_freelists.org Subject: Re: UTF character set application problem

Hi,

Ref: NLS_LENGTH_SEMANTICS is not supported in JDBC.

Just curious, do you still have the problem?

The note says fixed in 9.2.0.4, and 10g?

HTH Regards,

Mike

On Mon, 18 Oct 2004 18:18:02 -0500, Marc Perkowitz <mperkowitz_at_comcast.net> wrote:
> Matjaz,

>=20
> Thanks for your suggestion. We implemented this and unfortunately =
just
> found out that there is a bug with this in the Oracle 9.2 when used =
with
> JDBC. We have had to revert back to byte semantics for now. It was a
> significant issue with the CHAR columns that we were using as one-byte
> "flag" columns. All of our "flag" columns appeared as false in the
> application.

>=20
> The Metalink note is 2758545.8 about Bug 2758545 NLS_LENGTH_SEMANTICS =
is
> not supported in JDBC.

>=20

> Marc.

>=20
>=20
>=20

> -----Original Message-----
> From: Matjaz Jordan [mailto:matjaz.jordan_at_hermes-plus.si]
> Sent: Wednesday, September 29, 2004 11:21 AM
> To: mperkowitz_at_comcast.net
> Cc: oracle-l_at_freelists.org
> Subject: Re: UTF character set application problem

>=20

> Marc Perkowitz wrote:

>=20


> > Once we increased the size and set the NLS_LANG

> > correctly, everything was fine.

> >
> > Thank you Justin and Mike for hints that lead to us finding this =
out.
> >
> > Marc Perkowitz.
> >

>=20

> you don't need to increase size of columns.
> Just assure that init parameter NLS_LENGTH_SEMANTICS is properly set.

>=20

> SQL> sho parameter nls_length_semantics

>=20


> NAME                        TYPE   VALUE

> -------------------- ----------- -------

> nls_length_semantics      string    CHAR

>=20

> I guess your setting is BYTE.

>=20

> Once you set it properly, you MUST modify all columns, which were done
> with BYTE semantics. For example:

>=20

> BEGIN
> FOR cc IN (SELECT 'ALTER TABLE '||c.table_name
> ||' MODIFY '||c.column_name||' '
> ||c.data_type||'('||char_length||')' str
> ,c.column_name cn
> , c.table_name tn
> FROM user_tab_columns c
> ,user_tables t
> --joined to get only tables and skip views
> WHERE c.char_used=3D'B'
> AND c.table_name=3Dt.table_name)
> LOOP
> DBMS_OUTPUT.PUT('modifying column '||rpad(cc.cn,30)
> ||' on table '||rpad(cc.tn,30));
> EXECUTE IMMEDIATE cc.str;
> DBMS_OUTPUT.PUT_LINE(' >> OK');
> END LOOP;
> EXCEPTION
> WHEN OTHERS THEN
> DBMS_OUTPUT.PUT_LINE(' >> FAILED TO CONVERT FROM BYTE TO CHAR');
> RAISE;
> END;
> /

>=20

> Regards, Matjaz

>=20

> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 19 2004 - 16:25:43 CDT