问题现象
在纯 SELECT SQL 中，如果 SELECT 中 function 返回值是复杂类型（如 TYPE 类型），则会报错。
obclient(ALVIN@oboraclegbk)[ALVIN]> SELECT fn_split('a,b,c',',') FROM dual;
ORA-00600: internal error code, arguments: -4007, Not supported feature or function
或 SELECT 中访问 TYPE 类型，也会报错。
obclient(ALVIN@oboraclegbk)[ALVIN]> SELECT typ_varchar_array('a','b','c') FROM dual;
ORA-00600: internal error code, arguments: -4007, dml with collection or record construction function is not supported
测试用例如下，
CREATE OR REPLACE TYPE typ_varchar_array IS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE FUNCTION fn_split (
pv_str IN VARCHAR2,
pv_delimiter IN VARCHAR2 DEFAULT ','
) RETURN typ_varchar_array IS
BEGIN
RETURN typ_varchar_array('a', 'b', 'c');
END;
/
SELECT fn_split('a,b,c',',') FROM dual;
SELECT typ_varchar_array('a','b','c') FROM dual;
SELECT * FROM table(fn_split('a,b,c',','));
SELECT * FROM table(typ_varchar_array('a','b','c'));
SET SERVEROUTPUT ON;
DECLARE
vt_typ_varchar typ_varchar_array := typ_varchar_array();
BEGIN
SELECT fn_split('a,b,c', ',') INTO vt_typ_varchar FROM dual;
FOR i IN 1..vt_typ_varchar.count LOOP
dbms_output.put_line(vt_typ_varchar(i));
END LOOP;
END;
/
在 OceanBase 数据库 V4.2.1 版本中测试结果如下。
#obclient -h${DB_HOST} -P2883 -uALVIN@oboraclegbk#obcluster421 -A -p${DB_PASSWORD}
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 542355
Server version: OceanBase 4.2.1.9 (r109000092024091919-9e4708744c35a06376fd4289b995cf1ae70698d7) (Built Sep 19 2024 19:51:11)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient(ALVIN@oboraclegbk)[ALVIN]> CREATE OR REPLACE TYPE typ_varchar_array IS TABLE OF VARCHAR2(4000);
Query OK, 0 rows affected (0.094 sec)
obclient(ALVIN@oboraclegbk)[ALVIN]> CREATE OR REPLACE FUNCTION fn_split (
pv_str IN VARCHAR2,
pv_delimiter IN VARCHAR2 DEFAULT ','
) RETURN typ_varchar_array IS
BEGIN
RETURN typ_varchar_array('a', 'b', 'c');
END;
/
Query OK, 0 rows affected (0.084 sec)
obclient(ALVIN@oboraclegbk)[ALVIN]> SELECT fn_split('a,b,c',',') FROM dual;
ORA-00600: internal error code, arguments: -4007, Not supported feature or function
obclient(ALVIN@oboraclegbk)[ALVIN]> SELECT last_trace_id() FROM dual;
+-----------------------------------+
| LAST_TRACE_ID() |
+-----------------------------------+
| YB42AC140C7B-000622EE13969B8D-0-0 |
+-----------------------------------+
1 row in set (0.003 sec)
obclient(ALVIN@oboraclegbk)[ALVIN]> SELECT typ_varchar_array('a','b','c') FROM dual;
ORA-00600: internal error code, arguments: -4007, dml with collection or record construction function is not supported
obclient(ALVIN@oboraclegbk)[ALVIN]> SELECT last_trace_id() FROM dual;
+-----------------------------------+
| LAST_TRACE_ID() |
+-----------------------------------+
| YBxxxx-xxxxxx-xxxxxx-xxxxxx |
+-----------------------------------+
1 row in set (0.001 sec)
obclient(ALVIN@oboraclegbk)[ALVIN]> SELECT * FROM table(fn_split('a,b,c',','));
+--------------+
| COLUMN_VALUE |
+--------------+
| a |
| b |
| c |
+--------------+
3 rows in set (0.060 sec)
obclient(ALVIN@oboraclegbk)[ALVIN]> SELECT * FROM table(typ_varchar_array('a','b','c'));
+--------------+
| COLUMN_VALUE |
+--------------+
| a |
| b |
| c |
+--------------+
3 rows in set (0.003 sec)
obclient(ALVIN@oboraclegbk)[ALVIN]> SET SERVEROUTPUT ON;
obclient(ALVIN@oboraclegbk)[ALVIN]> DECLARE
vt_typ_varchar typ_varchar_array := typ_varchar_array();
BEGIN
SELECT fn_split('a,b,c', ',') INTO vt_typ_varchar FROM dual;
FOR i IN 1..vt_typ_varchar.count LOOP
dbms_output.put_line(vt_typ_varchar(i));
END LOOP;
END;
/
Query OK, 1 row affected (0.131 sec)
a
b
c
obclient(ALVIN@oboraclegbk)[ALVIN]>
关键信息
SELECT fn_split('a,b,c',',') FROM dual 关键报错信息 [errcode=-4007] composite type use in pure sql context not supported。
全部报错信息如下。
# grep 'YBxxx-xxxxx-xxxxx-xxxxx' observer.log
[2024-09-26 19:45:53.721160] WDIAG [SQL.RESV] check_first_node_name (ob_dml_resolver.cpp:895) [21687][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=25][errcode=0] node_name input null(ret=0)
[2024-09-26 19:45:53.722098] INFO [SQL.SESSION] add_need_priv (ob_privilege_check.cpp:182) [21687][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=49] obj owner id -1(need_priv={db_name:"", grantee_id:18446744073709551615, obj_id:500118, obj_level:65535, obj_type:9, check_flag:0, obj_privs:16777216, owner_id:18446744073709551615, col_id_array:[]}, lbt()="0x1380da05 0xdb6b5c8 0xdb7010d 0xdb8cc6b 0xdb6eeb0 0x5b28e3b 0x5766fd0 0x5762db8 0x5546b96 0x552b88b 0x5524296 0x55171ca 0x550023c 0xa9151a4 0x13ac986f 0x7fd05ae9af1b 0x7fd05add21c0")
[2024-09-26 19:45:53.722141] WDIAG [SQL] fill_select_result_set (ob_sql.cpp:680) [21687][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=30][errcode=-4007] composite type use in pure sql context not supported!
[2024-09-26 19:45:53.722152] WDIAG [SQL] fill_result_set (ob_sql.cpp:319) [21687][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=9][errcode=-4007] fill select result set failed(ret=-4007)
[2024-09-26 19:45:53.722159] WDIAG [SQL] generate_plan (ob_sql.cpp:3084) [21687][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-4007] Failed to fill result set(ret=-4007)
[2024-09-26 19:45:53.722166] WDIAG [SQL] generate_physical_plan (ob_sql.cpp:3021) [21687][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-4007] failed to generate plan(ret=-4007)
[2024-09-26 19:45:53.722175] WDIAG [SQL] handle_physical_plan (ob_sql.cpp:4835) [21687][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=5][errcode=-4007] Failed to generate plan(ret=-4007, result.get_exec_context().need_disconnect()=false)
[2024-09-26 19:45:53.722183] WDIAG [SQL] handle_text_query (ob_sql.cpp:2571) [21687][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-4007] fail to handle physical plan(ret=-4007)
[2024-09-26 19:45:53.722190] WDIAG [SQL] stmt_query (ob_sql.cpp:222) [21687][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=6][errcode=-4007] fail to handle text query(stmt=SELECT fn_split('a,b,c',',') FROM dual, ret=-4007)
[2024-09-26 19:45:53.722203] WDIAG [SERVER] after_func (ob_query_retry_ctrl.cpp:947) [21687][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-4007] [RETRY] check if need retry(v={force_local_retry:false, stmt_retry_times:0, local_retry_times:0, err_:-4007, err_:"OB_NOT_SUPPORTED", retry_type:0, client_ret:-4007}, need_retry=false, THIS_WORKER.can_retry()=true, v.ctx_.multi_stmt_item_={is_part_of_multi_stmt:false, seq_num:0, sql:"SELECT fn_split('a,b,c',',') FROM dual", batched_queries:NULL, is_ps_mode:false, ab_cnt:0})
[2024-09-26 19:45:53.722222] WDIAG [SERVER] do_process (obmp_query.cpp:757) [21687][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=18][errcode=-4007] run stmt_query failed, check if need retry(ret=-4007, cli_ret=-4007, retry_ctrl_.need_retry()=0, sql=SELECT fn_split('a,b,c',',') FROM dual)
[2024-09-26 19:45:53.722249] WDIAG [SERVER] do_process (obmp_query.cpp:882) [21687][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=21][errcode=-4007] query failed(ret=-4007, session={this:0x7fcead450e68, id:3221633223, deser:false, tenant:"oboraclegbk", tenant_id:1002, effective_tenant:"oboraclegbk", effective_tenant_id:1002, database:"ALVIN", user:"ALVIN", consistency_level:3, session_state:2, autocommit:false, tx:null}, sql=SELECT fn_split('a,b,c',',') FROM dual, retry_ctrl_.need_retry()=0)
[2024-09-26 19:45:53.722270] INFO [SERVER] send_error_packet (obmp_packet_sender.cpp:319) [21687][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=16] sending error packet(ob_error=-4007, client error=600, extra_err_info=NULL, lbt()="0x1380da05 0xac5c4e0 0xac09750 0x552f7c8 0x5524296 0x55171ca 0x550023c 0xa9151a4 0x13ac986f 0x7fd05ae9af1b 0x7fd05add21c0")
[2024-09-26 19:45:53.722339] INFO [SERVER] send_error_packet (obmp_packet_sender.cpp:502) [21687][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=8] dump txn free route audit_record(value=5, session->get_sessid()=3221633223, session->get_proxy_sessid()=12399549727379760605)
SELECT typ_varchar_array('a','b','c') FROM dual 关键报错日志信息如下。
[errcode=-4007] dml with collection or record construction function is not supported(ret=-4007)
全部报错信息如下。
#grep 'YBxxxx-xxxxxx-xxxxxx-xxxxxx' observer.log
[2024-09-26 19:46:00.977549] WDIAG [SQL.RESV] check_first_node_name (ob_dml_resolver.cpp:895) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=33][errcode=0] node_name input null(ret=0)
[2024-09-26 19:46:00.978148] WDIAG [SQL.RESV] resolve_qualified_identifier (ob_dml_resolver.cpp:2712) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=21][errcode=-4007] dml with collection or record construction function is not supported(ret=-4007)
[2024-09-26 19:46:00.978163] WDIAG resolve_qualified_identifier (ob_dml_resolver.cpp:2713) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=12][errcode=-4007] ORA-00600: internal error code, arguments: -4007, dml with collection or record construction function is not supported
[2024-09-26 19:46:00.978170] WDIAG [SQL.RESV] resolve_columns (ob_dml_resolver.cpp:2574) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=6][errcode=-4007] resolve column ref expr failed(ret=-4007, q_name={database_name:"", table_name:"", column:"", dblink_name:"", is_star:false, ref_expr:{item_type:"T_REF_COLUMN", result_type:{meta:{type:"", collation:"binary", coercibility:"NUMERIC"}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, calc_type:{type:"NULL", collation:"invalid_type", coercibility:"INVALID"}}, expr_info:["IS_COLUMN", "CNT_COLUMN"], rel_id:[], table_id:18446744073709551615, column_id:18446744073709551615, database_name:"", table_name:"", synonym_name:"", synonym_db_name:"", column_name:"", column_flags:0, enum_set_values:[], is_lob_column:false, is_joined_dup_column:false, is_unpivot_mocked_column:false, is_hidden:false, from_alias_table:false, is_rowkey_column:false, is_unique_key_column:false, is_mul_key_column:false, is_strict_json_column:0, srs_id:18446744073709551615, udt_set_id:0}, parents_expr_info:[], parent_aggr_level:-1, access_idents:[{access_name:"TYP_VARCHAR_ARRAY", access_index:-1, type:2, params:[[{item_type:"T_QUESTIONMARK", result_type:{meta:{type:"CHAR", collation:"gbk_bin", coercibility:"COERCIBLE"}, accuracy:{length:1, precision:2, scale:-1}, flag:1, calc_type:{type:"NULL", collation:"invalid_type", coercibility:"INVALID"}}, expr_info:["IS_CONST", "IS_STATIC_PARAM", "CNT_CONST", "CNT_STATIC_PARAM"], rel_id:[], value:{"UNKNOWN":0}}, 0], [{item_type:"T_QUESTIONMARK", result_type:{meta:{type:"CHAR", collation:"gbk_bin", coercibility:"COERCIBLE"}, accuracy:{length:1, precision:2, scale:-1}, flag:1, calc_type:{type:"NULL", collation:"invalid_type", coercibility:"INVALID"}}, expr_info:["IS_CONST", "IS_STATIC_PARAM", "CNT_CONST", "CNT_STATIC_PARAM"], rel_id:[], value:{"UNKNOWN":1}}, 0], [{item_type:"T_QUESTIONMARK", result_type:{meta:{type:"CHAR", collation:"gbk_bin", coercibility:"COERCIBLE"}, accuracy:{length:1, precision:2, scale:-1}, flag:1, calc_type:{type:"NULL", collation:"invalid_type", coercibility:"INVALID"}}, expr_info:["IS_CONST", "IS_STATIC_PARAM", "CNT_CONST", "CNT_STATIC_PARAM"], rel_id:[], value:{"UNKNOWN":2}}, 0]]}], current_resolve_level:-1, is_access_root:true})
[2024-09-26 19:46:00.978275] WDIAG [SQL.RESV] resolve_sql_expr (ob_dml_resolver.cpp:1518) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=104][errcode=-4007] resolve columns failed(ret=-4007)
[2024-09-26 19:46:00.978287] WDIAG [SQL.RESV] resolve_field_list (ob_select_resolver.cpp:1884) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=7][errcode=-4007] resolve sql expr failed(ret=-4007)
[2024-09-26 19:46:00.978293] WDIAG [SQL.RESV] resolve_normal_query (ob_select_resolver.cpp:1101) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=5][errcode=-4007] fail to exec resolve_field_list(*(parse_tree.children_[PARSE_SELECT_SELECT]))(ret=-4007)
[2024-09-26 19:46:00.978299] WDIAG [SQL.RESV] resolve (ob_select_resolver.cpp:1274) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=5][errcode=-4007] resolve normal query failed(ret=-4007)
[2024-09-26 19:46:00.978304] WDIAG [SQL.RESV] select_stmt_resolver_func (ob_resolver.cpp:183) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=4][errcode=-4007] execute stmt_resolver failed(ret=-4007, parse_tree.type_=3299)
[2024-09-26 19:46:00.978328] WDIAG [SQL] generate_stmt (ob_sql.cpp:2866) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=13][errcode=-4007] failed to resolve(ret=-4007)
[2024-09-26 19:46:00.978348] WDIAG [SQL] generate_physical_plan (ob_sql.cpp:2987) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=18][errcode=-4007] Failed to generate stmt(ret=-4007, result.get_exec_context().need_disconnect()=false)
[2024-09-26 19:46:00.978358] WDIAG [SQL] handle_physical_plan (ob_sql.cpp:4835) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=7][errcode=-4007] Failed to generate plan(ret=-4007, result.get_exec_context().need_disconnect()=false)
[2024-09-26 19:46:00.978364] WDIAG [SQL] handle_text_query (ob_sql.cpp:2571) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=5][errcode=-4007] fail to handle physical plan(ret=-4007)
[2024-09-26 19:46:00.978371] WDIAG [SQL] stmt_query (ob_sql.cpp:222) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=5][errcode=-4007] fail to handle text query(stmt=SELECT typ_varchar_array('a','b','c') FROM dual, ret=-4007)
[2024-09-26 19:46:00.978387] WDIAG [SERVER] after_func (ob_query_retry_ctrl.cpp:947) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=12][errcode=-4007] [RETRY] check if need retry(v={force_local_retry:false, stmt_retry_times:0, local_retry_times:0, err_:-4007, err_:"OB_NOT_SUPPORTED", retry_type:0, client_ret:-4007}, need_retry=false, THIS_WORKER.can_retry()=true, v.ctx_.multi_stmt_item_={is_part_of_multi_stmt:false, seq_num:0, sql:"SELECT typ_varchar_array('a','b','c') FROM dual", batched_queries:NULL, is_ps_mode:false, ab_cnt:0})
[2024-09-26 19:46:00.978405] WDIAG [SERVER] do_process (obmp_query.cpp:757) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=17][errcode=-4007] run stmt_query failed, check if need retry(ret=-4007, cli_ret=-4007, retry_ctrl_.need_retry()=0, sql=SELECT typ_varchar_array('a','b','c') FROM dual)
[2024-09-26 19:46:00.978415] WDIAG [SERVER] do_process (obmp_query.cpp:882) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=7][errcode=-4007] query failed(ret=-4007, session={this:0x7fcead450e68, id:3221633223, deser:false, tenant:"oboraclegbk", tenant_id:1002, effective_tenant:"oboraclegbk", effective_tenant_id:1002, database:"ALVIN", user:"ALVIN", consistency_level:3, session_state:2, autocommit:false, tx:null}, sql=SELECT typ_varchar_array('a','b','c') FROM dual, retry_ctrl_.need_retry()=0)
[2024-09-26 19:46:00.978547] INFO [SERVER] send_error_packet (obmp_packet_sender.cpp:319) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=121] sending error packet(ob_error=-4007, client error=600, extra_err_info=NULL, lbt()="0x1380da05 0xac5c4e0 0xac09750 0x552f7c8 0x5524296 0x55171ca 0x550023c 0xa9151a4 0x13ac986f 0x7fd05ae9af1b 0x7fd05add21c0")
[2024-09-26 19:46:00.978597] INFO [SERVER] send_error_packet (obmp_packet_sender.cpp:502) [21687][T1002_L0_G0][T1002][YBxxxx-xxxxxx-xxxxxx-xxxxxx] [lt=10] dump txn free route audit_record(value=5, session->get_sessid()=3221633223, session->get_proxy_sessid()=12399549727379760605)
问题原因
在 OceanBase 数据库 V2.x/V3.x/V4.2.1 中，在纯 SELECT SQL 中不支持复杂类型（如 TYPE 类型）的使用， 即如果下面 function 返回值是复杂类型（如 TYPE 类型），则会报错。
SELECT fn_split('a,b,c',',') FROM dual;
OceanBase 数据库 V4.2.x 新版本中已支持，如在 OceanBase 数据库 V4.2.4 版本中测试通过。
Server version: OceanBase 4.2.4.0 (r100020052024082717-c0a277cc61ae96f21a70c11f26ae383e96cd4ad4) (Built Aug 27 2024 20:57:02)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient(ALVIN@oboraclegbk)[ALVIN]> CREATE OR REPLACE TYPE typ_varchar_array IS TABLE OF VARCHAR2(4000);
Query OK, 0 rows affected (0.133 sec)
obclient(ALVIN@oboraclegbk)[ALVIN]>
obclient(ALVIN@oboraclegbk)[ALVIN]> CREATE OR REPLACE FUNCTION fn_split (
pv_str IN VARCHAR2,
pv_delimiter IN VARCHAR2 DEFAULT ','
) RETURN typ_varchar_array IS
BEGIN
RETURN typ_varchar_array('a', 'b', 'c');
END;
/
Query OK, 0 rows affected (0.111 sec)
obclient(ALVIN@oboraclegbk)[ALVIN]>
obclient(ALVIN@oboraclegbk)[ALVIN]> SELECT fn_split('a,b,c',',') FROM dual;
+----------------------------------+
| FN_SPLIT('A,B,C',',') |
+----------------------------------+
| TYP_VARCHAR_ARRAY('a', 'b', 'c') |
+----------------------------------+
1 row in set (0.088 sec)
obclient(ALVIN@oboraclegbk)[ALVIN]> SELECT typ_varchar_array('a','b','c') FROM dual;
+----------------------------------+
| TYP_VARCHAR_ARRAY('A','B','C') |
+----------------------------------+
| TYP_VARCHAR_ARRAY('a', 'b', 'c') |
+----------------------------------+
1 row in set (0.004 sec)
obclient(ALVIN@oboraclegbk)[ALVIN]> SELECT * FROM table(fn_split('a,b,c',','));
+--------------+
| COLUMN_VALUE |
+--------------+
| a |
| b |
| c |
+--------------+
3 rows in set (0.004 sec)
obclient(ALVIN@oboraclegbk)[ALVIN]> SELECT * FROM table(typ_varchar_array('a','b','c'));
+--------------+
| COLUMN_VALUE |
+--------------+
| a |
| b |
| c |
+--------------+
3 rows in set (0.003 sec)
obclient(ALVIN@oboraclegbk)[ALVIN]> SET SERVEROUTPUT ON;
obclient(ALVIN@oboraclegbk)[ALVIN]> DECLARE
vt_typ_varchar typ_varchar_array := typ_varchar_array();
BEGIN
SELECT fn_split('a,b,c', ',') INTO vt_typ_varchar FROM dual;
FOR i IN 1..vt_typ_varchar.count LOOP
dbms_output.put_line(vt_typ_varchar(i));
END LOOP;
END;
/
Query OK, 1 row affected (0.068 sec)
a
b
c
问题的风险及影响
影响相对较小。虽然不支持在纯 SELECT SQL 中使用返回值是复杂类型（如 TYPE 类型），但可以通过 table() 函数访问 TYPE 类型或 function 返回值是 TYPE 类型的。
SELECT * FROM table(fn_split('a,b,c',','));
SELECT * FROM table(typ_varchar_array('a','b','c'));
也支持在 PL/SQL 中使用。
SET SERVEROUTPUT ON;
DECLARE
vt_typ_varchar typ_varchar_array := typ_varchar_array();
BEGIN
SELECT fn_split('a,b,c', ',') INTO vt_typ_varchar FROM dual;
FOR i IN 1..vt_typ_varchar.count LOOP
dbms_output.put_line(vt_typ_varchar(i));
END LOOP;
END;
/
影响租户
影响 OceanBase 数据库中的 Oracle 租户，对于 SYS 租户和 MySQL 租户无影响。
适用版本
OceanBase 数据库 V2.2.x、V3.2.x、V4.2.x 版本。
解决方法
方法一：通过 table() 函数访问。
通过 table() 函数访问 TYPE 类型或 function 返回值是 TYPE 类型的。
示例，如将：
SELECT fn_split('a,b,c',',') FROM dual; SELECT typ_varchar_array('a','b','c') FROM dual;改为如下：
SELECT * FROM table(fn_split('a,b,c',',')); SELECT * FROM table(typ_varchar_array('a','b','c'));方法二：通过 FOR LOOP 遍历 TYPE 类型或返回值是 TYPE 类型的 function。
示例如下。
SET SERVEROUTPUT ON; DECLARE vt_typ_varchar typ_varchar_array := typ_varchar_array(); BEGIN SELECT fn_split('a,b,c', ',') INTO vt_typ_varchar FROM dual; FOR i IN 1..vt_typ_varchar.count LOOP dbms_output.put_line(vt_typ_varchar(i)); END LOOP; END; /方法三：升级 OceanBase 数据库版本。
升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据 V4.2.4（oceanbase-4.2.4.0-100000252024070621）及之后版本。
规避方式
通过按如上三个方法改写 SQL 或升级 OceanBase 数据库版本。
