Oracle mechanics

27.08.2012

Преобразование PL/SQL типов в CAST(COLLECT…) при миграция на многобайтовую кодировку (UTF)

Filed under: bugs,Oracle,PL/SQL,SQL — Игорь Усольцев @ 23:37
Tags: , ,

Проблема проявляется после миграции на UTF:

SQL> desc l

 Name   Type
 -----  ----------------
 C1     VARCHAR2(1 CHAR)

SQL> create or replace type l_tbl is table of varchar2(1 char);
 2 /

Type created.

SQL> select cast(collect(l.c1) as l_tbl) as collection from l;
select cast(collect(l.c1) as l_tbl) as collection from l
 *
ERROR at line 1:
ORA-22814: attribute or element value is larger than specified in type

До миграции (в бд с однобайтовой кодировкой CL8MSWIN1251) та же синтаксическая конструкция работает без ошибок:

11.2.0.3.@ SQL> select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               CL8MSWIN1251

SQL> create table l(c1 varchar2(1 char));

Table created

SQL> insert into l select 'Ё' from dual;

1 row inserted

— запрос с cast(collect…) выполняется успешно, если используемый тип определён как таблица столбцов varchar2(1 char):

SQL> create or replace type l_tbl is table of varchar2(1 char);
  2  /

Type created

SQL> select cast(collect(l.c1) as l_tbl) as collection from l;

COLLECTION
---------------------------------------------------------------------------------------
L_TBL('Ё')

Содержимое столбца хранится в 1 байте, т.е. по-хорошему в определении типа varchar2(1 char) равнозначно varchar2(1 byte) и проблема скрыта:

SQL> SELECT DUMP(c1) from l;

DUMP(C1)
--------------------------------------------------------------------------------
Typ=1 Len=1: 168

SQL> select chr(168) from dual;

CHR(168)
--------
Ё

Если выполнить тот же сценаций на бд с многобайтной кодировкой (utf8):

C:\sql\sqlplus>chcp 1251
Текущая кодовая страница: 1251

C:\sql\sqlplus>set NLS_LANG=AMERICAN_CIS.CL8MSWIN1251

C:\sql\sqlplus>sqlplus scott/tiger

SQL> @%NLS_LANG%
SP2-0310: unable to open file "AMERICAN_CIS.CL8MSWIN1251" -- проверка правильности установки локальной кодовой страницы

11.2.0.3.ORCL112@SCOTT SQL> select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

PARAMETER        VALUE
---------------- --------
NLS_CHARACTERSET AL32UTF8     --  параметры создания бд

SQL> select * from v$nls_parameters where parameter in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');

PARAMETER         VALUE
----------------- ---------
NLS_LANGUAGE      AMERICAN     --  параметры сессии
NLS_TERRITORY     CIS
NLS_CHARACTERSET  AL32UTF8

SQL> drop table l;

Table dropped.

SQL> create table l(c1 varchar2(1 char));

Table created.

SQL> insert into l select 'Ё' from dual;

1 row created.

SQL> create or replace type l_tbl is table of varchar2(1 char);
  2  /

Type created.

SQL> select cast(collect(l.c1) as l_tbl) as collection from l;
select cast(collect(l.c1) as l_tbl) as collection from l
            *
ERROR at line 1:
ORA-22814: attribute or element value is larger than specified in type

– получаем проблему, связанную с тем, что в UTF буква Ё (1 char) занимает уже 2 байта:

SQL> SELECT c1 from l;

C
-
Ё

SQL> SELECT DUMP(c1) from l;

DUMP(C1)
--------------------
Typ=1 Len=2: 208,129

SQL> SELECT length(c1) from l;

LENGTH(C1)
----------
         1 -- char

SQL> SELECT lengthB(c1) from l;

LENGTHB(C1)
-----------
          2 -- bytes

Просто на всякий случай можно проверить влияние параметра nls_length_semantics:

SQL> @param nls_length_semantics

NAME                 VALUE IS_DEF   IS_MOD  DSC
-------------------- ----- -------- ---------- ---------------------------------------------------
nls_length_semantics BYTE  TRUE     FALSE   create columns using byte or char semantics by default

SQL> alter session set nls_length_semantics=char;

Session altered.

SQL> create or replace type l_tbl is table of varchar2(1 char);
  2  /

Type created.

SQL> select cast(collect(l.c1) as l_tbl) as collection from l;
select cast(collect(l.c1) as l_tbl) as collection from l
            *
ERROR at line 1:
ORA-22814: attribute or element value is larger than specified in type

– не должен по определению и действительно не влияет при полном определении типа

Для избежания ошибок в процессе миграции на многобайтовую кодировку PL/SQL типы, хранимые в бд, придётся перезоздавать из расчёта 1 CHAR = 4 BYTES:

SQL> create or replace type l_tbl is table of varchar2(4);
  2  /

Type created.

SQL> select cast(collect(l.c1) as l_tbl) as collection from l;

COLLECTION
----------
L_TBL('Ё')

Определение типа:

create or replace type ... as table of varchar2(X CHAR)

воспринимается Oracle без ошибок, но фактически ничем не отличается конечно же отличается от:

create or replace type ... as table of varchar2(X BYTE)

, в том числе и  в словаре данных:

SQL> create or replace type l_tbl_1_char is table of varchar2(1 char);
  2  /

Type created.

SQL> create or replace type l_tbl_1_byte is table of varchar2(1 byte);
  2  /

Type created.

SQL> select NLS_CHARSET_NAME(c.charsetid) as "NLS_CHARSET_NAME",
  2         o.NAME,
  3         nvl2(c.synobj#,
  4              (select o.name
  5                 from sys."_CURRENT_EDITION_OBJ" o
  6                where o.obj# = c.synobj#),
  7              decode(ct.typecode,
  8                     9,
  9                     decode(c.charsetform, 2, 'NVARCHAR2', eo.name),
 10                     96,
 11                     decode(c.charsetform, 2, 'NCHAR', eo.name),
 12                     112,
 13                     decode(c.charsetform, 2, 'NCLOB', eo.name),
 14                     eo.name)) as "ELEM_TYPE_NAME",
 15         c.length,
 16         decode(c.charsetform,
 17                1,
 18                'CHAR_CS',
 19                2,
 20                'NCHAR_CS',
 21                3,
 22                NLS_CHARSET_NAME(c.charsetid),
 23                4,
 24                'ARG:' || c.charsetid) as "CHARACTER_SET_NAME",
 25         c.properties
 26    from sys.type$                  ct,
 27         sys.collection$            c,
 28         sys."_CURRENT_EDITION_OBJ" o,
 29         sys."_CURRENT_EDITION_OBJ" eo
 30   where o.NAME like 'L_TBL_1%'
 31     and c.elem_toid = ct.tvoid
 32     and o.oid$ = c.toid
 33     and o.subname IS NULL -- only the most recent version
 34     and o.type# <> 10 -- must not be invalid
 35     and c.elem_toid = eo.oid$
 36  /

NLS_CHARSET_NAME  NAME          ELEM_TYPE_NAME   LENGTH    CHARACTER_SET_NAME  PROPERTIES
----------------- ------------- --------------- ---------- ------------------- ----------
AL32UTF8          L_TBL_1_CHAR  VARCHAR2         1         CHAR_CS                   4100
AL32UTF8          L_TBL_1_BYTE  VARCHAR2         1         CHAR_CS                      4

В определении типа предел размера VARCHAR2 определяется ограничениями PL/SQL:

SQL> create or replace type l_tbl is table of varchar2(32767);
  2  /

Type created.

При этом в PL/SQL блоках определение type as table of varchar2(N char) отрабатывает корректно и предсказуемо:

SQL> set serveroutput on
SQL> declare
  2  v2 varchar2(1 char);
  3  begin
  4    select c1 into v2 from l;
  5    dbms_output.put_line(v2);
  6  end;
  7  /
Ё

PL/SQL procedure successfully completed.

SQL> declare
  2  type l_tbl is table of varchar2(1 char);
  3  v2 l_tbl;
  4  begin
  5  select c1 bulk collect into v2 from l;
  6  dbms_output.put_line(v2(1));
  7  end;
  8  /
Ё

PL/SQL procedure successfully completed.

SQL> declare
  2  type l_tbl is table of varchar2(1 byte);
  3  v2 l_tbl;
  4  begin
  5  select c1 bulk collect into v2 from l;
  6  dbms_output.put_line(v2(1));
  7  end;
  8  /
declare
*
ERROR at line 1:                           -- ожидаемая ошибка при определении типа varchar2(1 byte)
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 5

Проблема похожа на фичу на стыке SQL и PL/SQL

UPD: Как совершенно верно написал в комметарии Саян Малакшинов, проблема связана не с определением PL/SQL типов, а с преобразованими типов, выполняемыми collect и cast. Определение типов работают в соответствии с документацией:

SQL> create or replace type l_tbl is table of varchar2(1 char);
  2  /

Type created.

SQL> select cast(collect(l.c1) as l_tbl) as collection from l;
select cast(collect(l.c1) as l_tbl) as collection from l
            *
ERROR at line 1:
ORA-22814: attribute or element value is larger than specified in type

SQL> select type_name from dba_types where type_name like 'SYSTP%';

TYPE_NAME
------------------------------
SYSTP/oiIbPX6SAe8lnHfneFNQQ==

SQL> select text from dba_source s where s.name='SYSTP/oiIbPX6SAe8lnHfneFNQQ==';

TEXT
--------------------------------------------------------------------
TYPE         "SYSTP/oiIbPX6SAe8lnHfneFNQQ==" AS TABLE OF VARCHAR2(4)

SQL> CREATE TABLE t_nt (nt1 l_tbl) NESTED TABLE nt1 STORE AS nt1_tab;

Table created.

SQL> desc t_nt
 Name  Null?    Type
 ----- -------- --------
 NT1            L_TBL

SQL> insert into t_nt values (l_tbl('Ё','Я'));

1 row created.

SQL> select * from t_nt;

NT1
---------------
L_TBL('Ё', 'Я')

SQL> col dump for a40
SQL> SELECT COLUMN_VALUE, dump(COLUMN_VALUE) as "dump"  FROM t_nt t1, TABLE(t1.nt1) t2;

C dump
- ----------------------------------------
Ё Typ=1 Len=2: 208,129
Я Typ=1 Len=2: 208,175

Ссылки по теме:

Забавный факт о collect

Pre-defined collection types in Oracle:

SQL> select owner, type_name, coll_type, elem_type_name, length
  2    from dba_coll_types
  3   where type_name in ('DBMS_DEBUG_VC2COLL', 'KU$_VCNT');

OWNER  TYPE_NAME           COLL_TYPE  ELEM_TYPE_NAME     LENGTH
------ ------------------- ---------- -------------- ----------
SYS    DBMS_DEBUG_VC2COLL  TABLE      VARCHAR2             1000
SYS    KU$_VCNT            TABLE      VARCHAR2             4000

SQL> select * from table(sys.dbms_debug_vc2coll('Э', 'Ю' , 'Я'));

COLUMN_VALUE
------------
Э
Ю
Я

SQL> select * from table(sys.ku$_vcnt(1, 2, 'Э', 'Ю' , 'Я'));

COLUMN_VALUE
------------
1
2
Э
Ю
Я

4 комментария »

  1. Игорь, это проблема не типа, а collect и cast. Сам collect создает свой тип, я об этом писал в http://www.xt-r.com/2012/04/collect.html
    Например, на вашем примере у меня создался тип:

    DB11G/XTENDER> select type_name from dba_types where type_name like 'SYSTP%';
    
    TYPE_NAME
    ------------------------------
    SYSTPiOeSKuu3SXa30Sc/TRb/rA==
    

    Смотрим его описание:

    DB11G/XTENDER> select text from dba_source s where s.name='SYSTPiOeSKuu3SXa30Sc/TRb/rA==';
    
    TEXT
    ------------------------------------------------------------------------------------------
    TYPE           "SYSTPiOeSKuu3SXa30Sc/TRb/rA==" AS TABLE OF VARCHAR2(4)
    

    Поэтому приведение именно результата collect, т.е. table of varchar2(4) к table of varchar2(1 char) выдает ошибку.
    Проверить работоспособность без collect можно так:

    create table t_nt_test
    ( nt sys.ku$_vcnt )
    nested table nt 
    store as t_nt_table
    /
    insert into t_nt_test 
    select sys.ku$_vcnt('Ё','Я') coll from dual
    /
    DB11G/XTENDER> select cast (nt as l_tbl)
      2  from t_nt_test;
    
    CAST(NTASL_TBL)
    ----------------------------------------
    
    L_TBL('Ё', 'Я')
    

    комментарий от Саян Малакшинов — 28.08.2012 @ 00:25 | Ответить

    • Спасибо, Саян — исправил (включая название)

      комментарий от Igor Usoltsev — 28.08.2012 @ 23:52 | Ответить

      • Игорь, пойдете завтра(т.е. уже сегодня) на дб дэй? Хотя и слушал вас на руоге весной, все равно хочется познакомиться очно.

        комментарий от Саян Малакшинов — 29.08.2012 @ 00:17 | Ответить

        • Взаимно, но я как-то не в курсе мероприятия

          комментарий от Igor Usoltsev — 29.08.2012 @ 08:44 | Ответить


RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: