今天在某个金融客户群看到客户咨询“在Oceanbase数据库中oracle租户，更新clob字段超过4000字符怎么弄？”，我们知道clob可以存储像Varchar类型的字符，如果不使用dbms_lob包处理，可以简单当varchar处理，而varchar的长度又有上限，在oracle和oceanbase中这个问题答案是不同的，下面我来演示。
Oracle
SQL> select length(lpad('a',4000,'x')) from dual;
LENGTH(LPAD('A',4000,'X'))
--------------------------
4000
SQL> select length(lpad('a',8000,'x')) from dual;
LENGTH(LPAD('A',8000,'X'))
--------------------------
4000
SQL> @pd max_string
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ---------- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
3472 D90 _max_string_size_bypass 0 controls error checking for the max_string_size parameter
3473 D91 _qkslvc_extended_bind_sz 1 controls error checking for the max_string_size parameter
4226 1082 max_string_size STANDARD controls maximum size of VARCHAR2, NVARCHAR2, and RAW types in SQL
SQL>
SQL> select to_clob(lpad('a',8000,'x')||lpad('a',8000,'x')) from dual;
ERROR:
ORA-01489: result of string concatenation is too long
no rows selected
SQL> select length(lpad('a',4000,'x')) from dual;
LENGTH(LPAD('A',4000,'X'))
--------------------------
4000
SQL> select length(lpad('a',8000,'x')) from dual;
LENGTH(LPAD('A',8000,'X'))
--------------------------
4000
SQL> @pd max_string
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ---------- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
3472 D90 _max_string_size_bypass 0 controls error checking for the max_string_size parameter
3473 D91 _qkslvc_extended_bind_sz 1 controls error checking for the max_string_size parameter
4226 1082 max_string_size STANDARD controls maximum size of VARCHAR2, NVARCHAR2, and RAW types in SQL
SQL>
SQL> select to_clob(lpad('a',8000,'x')||lpad('a',8000,'x')) from dual;
ERROR:
ORA-01489: result of string concatenation is too long
no rows selected
SQL> ho oerr ora 1489
01489, 00000, "result of string concatenation is too long"
// *Cause: String concatenation result is more than the maximum size.
// *Action: Make sure that the result is less than the maximum size.
SQL> @pvalid max_string_size
Display valid values for multioption parameters matching "max_string_size"...
PAR# PARAMETER ORD VALUE DEFAULT
------ -------------------------------------------------- ---------- ------------------------------ -------
4226 max_string_size 1 STANDARD DEFAULT
max_string_size 2 EXTENDED
Note:
在oracle 的标准字符长度下，varchar上限是4000(当然我们在19c中max_string_size改为EXTENDED，来提升到32k的上限)。 另外看到to_clob也是varchar的上限4000, 如果在oracle中处理超4000,需要使用dbms_lob包。
Oceanbase
在去年我整理过一篇《Oceanbase BLOB/CLOB LO(Large Object）注意事项》介绍过LOB对象的上限和oracle的不同，这里不在复述，仅测试to_clob.
OB v4.4
CLOB用于存储单字节和多字节字符数据。CLOB支持固定宽度和可变宽度字符集，且都使用数据库字符集。CLOB可存储字节的长度上限是 536870910 字节，字符集与租户字符集一致。VARCHAR2类型字段的最大长度为 32767 字节。如果需要保存的字段长度大于 32767 字节，可以使用CLOB类型。
obclient(SYS@orcl)[SYS]> select * from v$version;
+---------------------------------------------------------------------------------------------------------------+--------+
| BANNER | CON_ID |
+---------------------------------------------------------------------------------------------------------------+--------+
| OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29) | 0 |
+---------------------------------------------------------------------------------------------------------------+--------+
1 row in set (0.008 sec)
-- test anbob.com
obclient(SYS@orcl)[SYS]> select length(lpad('a',4000,'x')) from dual;
+----------------------------+
| LENGTH(LPAD('A',4000,'X')) |
+----------------------------+
| 4000 |
+----------------------------+
1 row in set (0.004 sec)
obclient(SYS@orcl)[SYS]> select length(lpad('a',8000,'x')) from dual;
+----------------------------+
| LENGTH(LPAD('A',8000,'X')) |
+----------------------------+
| 8000 |
+----------------------------+
1 row in set (0.002 sec)
obclient(SYS@orcl)[SYS]> select length(lpad('a',32767,'x')) from dual;
+-----------------------------+
| LENGTH(LPAD('A',32767,'X')) |
+-----------------------------+
| 32767 |
+-----------------------------+
1 row in set (0.002 sec)
obclient(SYS@orcl)[SYS]> select length(lpad('a',32768,'x')) from dual;
+-----------------------------+
| LENGTH(LPAD('A',32768,'X')) |
+-----------------------------+
| 32767 |
+-----------------------------+
1 row in set (0.002 sec)
obclient(SYS@orcl)[SYS]> create table tclob(t clob);
Query OK, 0 rows affected (3.727 sec)
obclient(SYS@orcl)[SYS]> select * from v$version;
+---------------------------------------------------------------------------------------------------------------+--------+
| BANNER | CON_ID |
+---------------------------------------------------------------------------------------------------------------+--------+
| OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29) | 0 |
+---------------------------------------------------------------------------------------------------------------+--------+
1 row in set (0.008 sec)
obclient(SYS@orcl)[SYS]> select length(lpad('a',4000,'x')) from dual;
+----------------------------+
| LENGTH(LPAD('A',4000,'X')) |
+----------------------------+
| 4000 |
+----------------------------+
1 row in set (0.004 sec)
obclient(SYS@orcl)[SYS]> select length(lpad('a',8000,'x')) from dual;
+----------------------------+
| LENGTH(LPAD('A',8000,'X')) |
+----------------------------+
| 8000 |
+----------------------------+
1 row in set (0.002 sec)
obclient(SYS@orcl)[SYS]> select length(lpad('a',32767,'x')) from dual;
+-----------------------------+
| LENGTH(LPAD('A',32767,'X')) |
+-----------------------------+
| 32767 |
+-----------------------------+
1 row in set (0.002 sec)
obclient(SYS@orcl)[SYS]> select length(lpad('a',32768,'x')) from dual;
+-----------------------------+
| LENGTH(LPAD('A',32768,'X')) |
+-----------------------------+
| 32767 |
+-----------------------------+
1 row in set (0.002 sec)
obclient(SYS@orcl)[SYS]> create table tclob(t clob);
Query OK, 0 rows affected (3.727 sec)
obclient(SYS@orcl)[SYS]> insert into tclob values(lpad('a',32767,'x'));
Query OK, 1 row affected (0.170 sec)
obclient(SYS@orcl)[SYS]> insert into tclob values(to_clob(lpad('a',32767,'x')));
Query OK, 1 row affected (0.005 sec)
obclient(SYS@orcl)[SYS]> select 'x'||'y' from dual;
+----------+
| 'X'||'Y' |
+----------+
| xy |
+----------+
1 row in set (0.004 sec)
obclient(SYS@orcl)[SYS]> insert into tclob values(lpad('a',32767,'x')||lpad('a',32767,'x'));
ORA-01489: result of string concatenation is too long
obclient(SYS@orcl)[SYS]> insert into tclob values(to_clob(lpad('a',32767,'x')||lpad('a',32767,'x')));
ORA-01489: result of string concatenation is too long
obclient(SYS@orcl)[SYS]> select length(t) from tclob;
+-----------+
| LENGTH(T) |
+-----------+
| 32767 |
| 32767 |
+-----------+
2 rows in set (0.049 sec)
Note:
在oceanbase oracle租户中varchar的最大长度是32767（类似oracle 的 max_string_size=EXTENDED）, 所以to_clob的最大长度是32767. 如果写入更大的值就和oracle数据库中的一样，要求使用dbms_lob package. 如下
obclient(SYS@orcl)[SYS]> DECLARE
-> large_text CLOB;
-> BEGIN
-> -- 创建LOB对象
-> large_text := NULL;
-> DBMS_LOB.createtemporary(large_text, TRUE);
->
-> -- 追加数据
-> DBMS_LOB.append(large_text, lpad('a',32768,'x'));
-> DBMS_LOB.append(large_text, lpad('a',32768,'x'));
-> DBMS_LOB.append(large_text, lpad('a',32768,'x'));
->
-> -- 查看结果
-> DBMS_OUTPUT.put_line(DBMS_LOB.getlength(large_text));
-> insert into tclob values(large_text);
->
-> -- 关闭LOB对象
-> DBMS_LOB.freetemporary(large_text);
-> END;
-> /
Query OK, 1 row affected (0.227 sec)
98301
obclient(SYS@orcl)[SYS]> select length(t) from tclob;
+-----------+
| LENGTH(T) |
+-----------+
| 32767 |
| 32767 |
| 98301 |
+-----------+
3 rows in set (0.005 sec)
小结
在oceanbase数据库中使用to_clob的最大字符也是varchar的长度上限32767, 而oracle数据库中如果没有修改 ax_string_size=EXTENDED时，to_clob最大长度是4000.
— over