本文介绍 type nclob not supported 问题处理。
适用版本
OceanBase V2.x、V3.x 版本。
问题现象
执行以下 SQL 失败。
obclient > SELECT concat(c.col_clob,v.col_nvarchar) col_value FROM test_nclob c JOIN test_nvarchar v ON (c.TEST_ID=v.TEST_ID);
报错信息如下。
ErrorCode = 600, SQLState = 0A000, Details = ORA-00600: internal error code, arguments: -4007, type nclob not supported
测试 case 如下。
obclient > CREATE TABLE test_nclob (
test_id INT,
col_clob CLOB);
obclinet > CREATE TABLE test_nvarchar (
test_id INT,
col_nvarchar NVARCHAR2(1024));
obclinet > SELECT concat(c.col_clob,v.col_nvarchar) col_value FROM test_nclob c JOIN test_nvarchar v ON (c.test_id=v.test_id);
NCLOB 目前不支持。
CLOB 类型 := CLOB 类型 || NVARCHAR2 类型 这样的拼串会报下面的错误。
ORA-00600: internal error code, arguments: -4007, type nclob not supported
解决方法
NVARCHAR2 转成 CLOB 或 VARCHAR2。
如：
obclinet > SELECT concat(c.col_clob,to_clob(v.col_nvarchar)) col_value FROM test_nclob c JOIN test_nvarchar v ON (c.test_id=v.test_id);
或
obclient> SELECT c.col_clob || v.col_nvarchar col_value FROM test_nclob c JOIN test_nvarchar v ON (c.test_id=v.test_id);
测试结果：
Server version: OceanBase 3.2.3.3 (r107040032023032910-c59670fcc6f48d0f6d7ba107e85d43efe85e6615) (Built Mar 29 2023 10:50:14)
Copyright (c) 2000, 2020, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient> CREATE TABLE test_nclob (
test_id INT,
col_clob CLOB);
Query OK, 0 rows affected (0.03 sec)
obclient> CREATE TABLE test_nvarchar (
test_id INT,
col_nvarchar NVARCHAR2(1024));
Query OK, 0 rows affected (0.02 sec)
obclient> SELECT concat(c.col_clob,v.col_nvarchar) col_value FROM test_nclob c JOIN test_nvarchar v ON (c.test_id=v.test_id);
ORA-00600: internal error code, arguments: -4007, type nclob not supported
obclient> SELECT concat(c.col_clob,to_clob(v.col_nvarchar)) col_value FROM test_nclob c JOIN test_nvarchar v ON (c.test_id=v.test_id);
Empty set (0.00 sec)
obclient> SELECT c.col_clob || v.col_nvarchar col_value FROM test_nclob c JOIN test_nvarchar v ON (c.test_id=v.test_id);
Empty set (0.00 sec)
另外，如下变形也会报错：
obclient> SELECT c.col_clob col_value FROM test_nclob c
UNION ALL
SELECT c.col_nvarchar col_value FROM test_nvarchar c;
测试结果：
obclient> SELECT c.col_clob col_value FROM test_nclob c
-> UNION ALL
-> SELECT c.col_nvarchar col_value FROM test_nvarchar c;
ORA-01790: expression must have same datatype as corresponding expression
改成如下方式则不报错：
obclient> SELECT to_clob(c.col_clob) col_value FROM test_nclob c
UNION ALL
SELECT to_clob(v.col_nvarchar) col_value FROM test_nvarchar v;
或
obclient> SELECT c.col_clob || '' col_value FROM test_nclob c
UNION ALL
SELECT to_clob(v.col_nvarchar) col_value FROM test_nvarchar v;
测试结果：
obclient> SELECT to_clob(c.col_clob) col_value FROM test_nclob c
UNION ALL
SELECT to_clob(v.col_nvarchar) col_value FROM test_nvarchar v;
Empty set (0.01 sec)
obclient> SELECT c.col_clob || '' col_value FROM test_nclob c
UNION ALL
SELECT to_clob(v.col_nvarchar) col_value FROM test_nvarchar v;
Empty set (0.00 sec)
