RE: UTF character set application problem

From: Marc Perkowitz <mperkowitz_at_comcast.net>
Date: Mon, 18 Oct 2004 18:18:02 -0500
Message-ID: <00d301c4b568$bc7c8580$34099943@MTPSYS>

Matjaz,

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.

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

Marc.

-----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

Marc Perkowitz wrote:

> 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.
>

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

SQL> sho parameter nls_length_semantics

NAME                        TYPE   VALUE
-------------------- ----------- -------
nls_length_semantics      string    CHAR

I guess your setting is BYTE.

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

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='B'
                 AND c.table_name=t.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;
/

Regards, Matjaz

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

Received on Mon Oct 18 2004 - 18:13:52 CDT