用多字节字符集确定CLOB/NCLOB的字节长度

Question

我正在使用Oracle数据库，并希望使用多字节字符集(UTF-8)确定NCLOB的字节长度。

LENGTHB()不支持具有多字节字符集的CLOBs。我可以把NCLOB转换成一个BLOB，然后得到它的长度。但难道没有更好的方法来做到这一点吗？

Answer 1

甲骨文将CLOB存储在UTF-16中(或者您的NCHARACTER_SET?)。每个字符都存储为两个字节。

下面是查看原始数据的方法：

SQL> CREATE TABLE test_clob (c CLOB, v VARCHAR2(10 CHAR), nv NVARCHAR2(10));

Table created

SQL> INSERT INTO test_clob VALUES ('0123456789', '0123456789', '0123456789');

1 row inserted

SQL> SELECT dbms_rowid.rowid_relative_fno(ROWID),
  2         dbms_rowid.rowid_block_number(ROWID)
  3    FROM test_clob;

DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------ ------------------------------
                            13                          94314

SQL> alter system dump datafile 13 block 94314;

System altered

导航到您的USER_DUMP_DEST目录并打开跟踪文件，您将看到如下所示：

col  0: [56]
 00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 03 64 c6 a5 00 24 09 00 00
 00 00 00 00 14 00 00 00 00 00 01 00 30 00 31 00 32 00 33 00 34 00 35 00 36
 00 37 00 38 00 39
LOB
Locator:
  Length:        84(56)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.03.64.c6.a5
  Flags[ 0x02 0x0c 0x80 0x00 ]:
    Type: CLOB 
    [...]
    Inline data[20]
    [...]
col  1: [10]  30 31 32 33 34 35 36 37 38 39
col  2: [20]  00 30 00 31 00 32 00 33 00 34 00 35 00 36 00 37 00 38 00 39

如您所见，CLOB (列0)由几个头字节和与UTF-16 NVARCHAR2列相同的字节原始数据组成。

因此，我认为您必须将您的CLOB转换为UTF-8，以确定它在此字符集中的长度。

下面是我使用DBMS_LOB.converttoblob的一个例子

SQL> DECLARE
  2     l_clob         CLOB := 'abcdéfghij'; -- the é will take two bytes!
  3     l_blob         BLOB;
  4     l_dest_offset  NUMBER := 1;
  5     l_src_offset   NUMBER := 1;
  6     l_lang_context NUMBER := 0;
  7     l_warning      NUMBER;
  8  BEGIN
  9     dbms_lob.createtemporary(l_blob, FALSE, dbms_lob.call);
 10     dbms_lob.converttoblob(dest_lob     => l_blob,
 11                            src_clob     => l_clob,
 12                            amount       => dbms_lob.lobmaxsize,
 13                            dest_offset  => l_dest_offset,
 14                            src_offset   => l_src_offset,
 15                            blob_csid    => nls_charset_id('AL32UTF8'),
 16                            lang_context => l_lang_context,
 17                            warning      => l_warning);
 18     dbms_output.put_line('byte length:'||dbms_lob.getlength(l_blob));
 19     dbms_lob.freetemporary(l_blob);
 20  END;
 21  /

byte length:11

PL/SQL procedure successfully completed

您可以使用函数nls_charset_id转换到任何字符集。