Re: UTF character set application problem

From: Matjaz Jordan <matjaz.jordan_at_hermes-plus.si>
Date: Wed, 29 Sep 2004 18:20:45 +0200
Message-ID: <415AE0DD.2070803@hermes-plus.si>

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 Wed Sep 29 2004 - 11:17:00 CDT