PL/SQL 字符串文字太长

Question

以下是我的Oracle SQL语句。该SQL 语句是要将字符串分割成两列。有几个字符串就有几行
比如，输入一个字符串'ZBTJ/2021-05-15 22:52:00',将得到一行两列结果集

输入'YSSY/2021-05-16 07:13:37,ZSAM/2021-05-16 07:13:37' 这显示两行两列

如果用户输入的参数很大，则SQL报错，提示字符串文字太长。如下SQL

SELECT
    to_date( regexp_substr( str, '[^/]+', 1, 2 ), 'yyyy-mm-dd hh24:mi:ss' ) flightDate,
    regexp_substr( str, '[^/]+', 1, 1 ) airportCode 
FROM
    (
    SELECT
        REGEXP_SUBSTR( 'ZBTJ/2021-05-15 22:52:00,ZSAM/2021-05-15 23:19:00,ZSAM/2021-05-15 16:02:00,ZGHA/2021-05-15 18:37:00,ZGGG/2021-05-15 09:58:00,EHAM/2021-05-15 19:21:16,ZSAM/2021-05-15 17:18:00,ZJHK/2021-05-15 16:15:00,ZSAM/2021-05-15 06:51:00,ZSHC/2021-05-15 09:37:00,ZHCC/2021-05-15 16:43:26,ZSAM/2021-05-15 09:02:00,ZSAM/2021-05-15 06:57:00,ZSLG/2021-05-15 14:22:00,ZGHY/2021-05-15 20:26:00,ZLIC/2021-05-15 11:23:00,ZSAM/2021-05-15 07:27:00,ZSSH/2021-05-15 17:37:00,ZGBH/2021-05-15 09:51:00,KLAX/2021-05-16 12:27:19,ZSAM/2021-05-15 22:32:00,ZSAM/2021-05-15 12:17:00,ZSAM/2021-05-15 20:23:00,ZSAM/2021-05-15 12:30:00,ZSAM/2021-05-15 10:59:00,ZSAM/2021-05-15 18:06:00,ZSAM/2021-05-15 23:42:00,ZSAM/2021-05-16 05:56:00,ZSAM/2021-05-15 22:20:00,WSSS/2021-05-15 13:18:43,ZSAM/2021-05-16 03:12:00,ZSAM/2021-05-15 23:36:00,WSSS/2021-05-15 15:31:07,ZBAD/2021-05-15 17:35:00,ZSAM/2021-05-15 08:38:00,ZBYN/2021-05-15 11:14:00,ZUUU/2021-05-15 14:53:00,ZSAM/2021-05-15 20:10:00,ZUUU/2021-05-15 07:01:00,ZGHA/2021-05-15 14:54:00,ZSAM/2021-05-15 08:13:00,ZSAM/2021-05-15 12:00:00,ZSAM/2021-05-15 17:48:00,ZUCK/2021-05-15 14:55:00,ZSOF/2021-05-15 11:50:00,ZHCC/2021-05-15 09:30:55,ZSHC/2021-05-15 12:36:00,ZJHK/2021-05-15 09:33:00,KLAX/2021-05-15 15:59:43,ZSAM/2021-05-15 20:29:00,ZSAM/2021-05-15 18:56:00,ZUGY/2021-05-15 11:30:00,ZSAM/2021-05-15 12:22:00,ZLXY/2021-05-15 13:56:00,ZSAM/2021-05-15 18:36:00,ZSAM/2021-05-15 06:55:00,ZBYN/2021-05-15 14:16:00,ZGGG/2021-05-15 14:07:00,ZYHB/2021-05-15 07:51:00,ZSNB/2021-05-15 08:38:00,ZLXY/2021-05-16 02:14:00,ZSAM/2021-05-15 13:03:00,ZSAM/2021-05-16 00:09:00,ZSAM/2021-05-15 13:49:00,ZSAM/2021-05-15 19:35:00,ZSNJ/2021-05-15 10:36:00,ZSAM/2021-05-15 07:20:00,ZLXY/2021-05-16 04:47:00,ZPPP/2021-05-15 11:02:00,ZBTJ/2021-05-15 13:06:00,ZSAM/2021-05-15 09:43:00,ZGHY/2021-05-15 21:47:00,YSSY/2021-05-16 07:13:37,ZSOF/2021-05-15 10:46:00,ZGHA/2021-05-15 08:16:00,ZUCK/2021-05-15 20:17:00,ZJHK/2021-05-15 14:34:00,ZSAM/2021-05-15 10:22:00,ZSAM/2021-05-15 11:44:00,EGLL/2021-05-15 22:16:30,ZSAM/2021-05-15 15:31:00,ZSAM/2021-05-15 11:21:00,ZSAM/2021-05-15 23:13:00,ZSAM/2021-05-16 07:05:00,ZLIC/2021-05-15 15:47:00,ZSAM/2021-05-15 17:52:00,ZGZH/2021-05-15 22:49:38,ZSAM/2021-05-15 08:26:00,ZUGY/2021-05-15 19:43:00,ZSAM/2021-05-15 07:04:00,ZUCK/2021-05-15 09:17:00,ZSAM/2021-05-15 06:49:00,ZSHC/2021-05-15 23:10:00,ZHLY/2021-05-15 16:36:00,ZSAM/2021-05-15 12:32:00,ZSSS/2021-05-15 12:40:00,ZSSS/2021-05-15 22:20:00,ZBAD/2021-05-15 14:36:00,ZSAM/2021-05-15 13:25:00,ZSAM/2021-05-15 07:47:00,ZBAD/2021-05-15 18:26:00,ZSAM/2021-05-15 16:51:00,ZBAD/2021-05-15 19:48:00,ZSAM/2021-05-15 17:50:00,ZSAM/2021-05-15 10:28:00,ZSAM/2021-05-16 02:38:00,ZSSS/2021-05-15 19:56:00,ZSSS/2021-05-15 14:38:00,ZSAM/2021-05-15 21:59:00,ZSAM/2021-05-15 07:41:00,ZSAM/2021-05-15 14:36:00,ZSJN/2021-05-15 13:20:00,ZSAM/2021-05-15 13:14:00,ZGZH/2021-05-15 22:01:31,ZSAM/2021-05-15 11:16:00,ZSZS/2021-05-15 09:10:00,ZSAM/2021-05-15 18:25:00,ZSAM/2021-05-15 18:50:00,ZGSD/2021-05-15 18:30:00,ZUUU/2021-05-15 23:35:00,ZHCC/2021-05-16 00:31:00,ZSAM/2021-05-15 07:42:00,ZSAM/2021-05-15 20:55:00,ZUCK/2021-05-15 19:11:00,ZSAM/2021-05-15 15:57:00,ZHLY/2021-05-15 22:46:00,ZSYN/2021-05-15 09:42:00,ZSAM/2021-05-15 07:00:00,ZSJN/2021-05-15 14:20:00,ZSAM/2021-05-15 19:08:00,ZSAM/2021-05-15 16:57:00,ZSAM/2021-05-15 06:42:00,ZSAM/2021-05-15 21:01:00,ZSAM/2021-05-15 23:28:00,ZLXY/2021-05-15 09:17:00,ZUCK/2021-05-16 00:38:00,ZGHA/2021-05-15 09:06:00,ZGGG/2021-05-15 07:56:00,EGLL/2021-05-16 00:24:25,ZSYN/2021-05-15 18:21:00,ZSAM/2021-05-16 04:08:00,ZSAM/2021-05-15 07:53:00,ZUGY/2021-05-15 21:26:00,ZSAM/2021-05-16 01:15:00,ZGKL/2021-05-15 09:51:17,ZUGY/2021-05-15 09:42:00,ZSAM/2021-05-15 23:50:00,ZBYN/2021-05-15 21:25:00,ZSAM/2021-05-15 12:39:00,ZSAM/2021-05-16 01:12:00,ZSQD/2021-05-15 18:52:00,ZBAD/2021-05-15 09:21:00,ZSAM/2021-05-15 18:59:00,ZSAM/2021-05-15 09:23:00,ZSAM/2021-05-15 16:38:00,ZSAM/2021-05-16 05:59:00,ZUCK/2021-05-15 16:13:00,ZSAM/2021-05-15 08:18:00,ZSAM/2021-05-15 22:11:00,ZSAM/2021-05-16 11:32:00,ZGBH/2021-05-15 10:54:00,ZYHB/2021-05-15 22:40:00,ZUWX/2021-05-15 16:41:00,ZHCC/2021-05-15 20:07:00,ZSAM/2021-05-16 04:15:00,ZJHK/2021-05-15 11:05:00,ZSSS/2021-05-15 08:42:00,ZSAM/2021-05-15 20:17:00,ZSNJ/2021-05-15 09:09:00,ZSAM/2021-05-15 14:06:00,ZSAM/2021-05-15 07:23:00,ZSHC/2021-05-15 22:35:00,ZSAM/2021-05-15 16:27:00,ZSNB/2021-05-15 17:50:00,ZSAM/2021-05-15 13:17:00,ZSAM/2021-05-15 07:35:00,ZSHC/2021-05-15 17:18:00,ZSAM/2021-05-15 16:11:00,ZSAM/2021-05-15 15:39:00,ZSAM/2021-05-15 08:51:00,ZSAM/2021-05-15 16:05:00,ZSAM/2021-05-15 18:32:00,ZSAM/2021-05-15 19:15:00,ZSLG/2021-05-16 02:27:00,ZBHH/2021-05-15 12:49:00,ZSAM/2021-05-15 09:17:00,ZSZS/2021-05-15 07:59:00,ZGSD/2021-05-15 17:32:00,ZSAM/2021-05-15 11:18:00,ZSAM/2021-05-15 16:22:00,ZBHH/2021-05-15 10:22:00,ZUWX/2021-05-15 22:18:00,ZSAM/2021-05-15 21:22:00,ZBTJ/2021-05-15 11:50:00,ZGHA/2021-05-16 01:29:00,ZSAM/2021-05-15 12:57:00,ZSAM/2021-05-15 08:23:00,ZSQD/2021-05-16 02:01:00,ZBAD/2021-05-15 11:45:00', '[^,]+', 1, LEVEL ) str 
FROM
    DUAL CONNECT BY REGEXP_SUBSTR( 'ZBTJ/2021-05-15 22:52:00,ZSAM/2021-05-15 23:19:00,ZSAM/2021-05-15 16:02:00,ZGHA/2021-05-15 18:37:00,ZGGG/2021-05-15 09:58:00,EHAM/2021-05-15 19:21:16,ZSAM/2021-05-15 17:18:00,ZJHK/2021-05-15 16:15:00,ZSAM/2021-05-15 06:51:00,ZSHC/2021-05-15 09:37:00,ZHCC/2021-05-15 16:43:26,ZSAM/2021-05-15 09:02:00,ZSAM/2021-05-15 06:57:00,ZSLG/2021-05-15 14:22:00,ZGHY/2021-05-15 20:26:00,ZLIC/2021-05-15 11:23:00,ZSAM/2021-05-15 07:27:00,ZSSH/2021-05-15 17:37:00,ZGBH/2021-05-15 09:51:00,KLAX/2021-05-16 12:27:19,ZSAM/2021-05-15 22:32:00,ZSAM/2021-05-15 12:17:00,ZSAM/2021-05-15 20:23:00,ZSAM/2021-05-15 12:30:00,ZSAM/2021-05-15 10:59:00,ZSAM/2021-05-15 18:06:00,ZSAM/2021-05-15 23:42:00,ZSAM/2021-05-16 05:56:00,ZSAM/2021-05-15 22:20:00,WSSS/2021-05-15 13:18:43,ZSAM/2021-05-16 03:12:00,ZSAM/2021-05-15 23:36:00,WSSS/2021-05-15 15:31:07,ZBAD/2021-05-15 17:35:00,ZSAM/2021-05-15 08:38:00,ZBYN/2021-05-15 11:14:00,ZUUU/2021-05-15 14:53:00,ZSAM/2021-05-15 20:10:00,ZUUU/2021-05-15 07:01:00,ZGHA/2021-05-15 14:54:00,ZSAM/2021-05-15 08:13:00,ZSAM/2021-05-15 12:00:00,ZSAM/2021-05-15 17:48:00,ZUCK/2021-05-15 14:55:00,ZSOF/2021-05-15 11:50:00,ZHCC/2021-05-15 09:30:55,ZSHC/2021-05-15 12:36:00,ZJHK/2021-05-15 09:33:00,KLAX/2021-05-15 15:59:43,ZSAM/2021-05-15 20:29:00,ZSAM/2021-05-15 18:56:00,ZUGY/2021-05-15 11:30:00,ZSAM/2021-05-15 12:22:00,ZLXY/2021-05-15 13:56:00,ZSAM/2021-05-15 18:36:00,ZSAM/2021-05-15 06:55:00,ZBYN/2021-05-15 14:16:00,ZGGG/2021-05-15 14:07:00,ZYHB/2021-05-15 07:51:00,ZSNB/2021-05-15 08:38:00,ZLXY/2021-05-16 02:14:00,ZSAM/2021-05-15 13:03:00,ZSAM/2021-05-16 00:09:00,ZSAM/2021-05-15 13:49:00,ZSAM/2021-05-15 19:35:00,ZSNJ/2021-05-15 10:36:00,ZSAM/2021-05-15 07:20:00,ZLXY/2021-05-16 04:47:00,ZPPP/2021-05-15 11:02:00,ZBTJ/2021-05-15 13:06:00,ZSAM/2021-05-15 09:43:00,ZGHY/2021-05-15 21:47:00,YSSY/2021-05-16 07:13:37,ZSOF/2021-05-15 10:46:00,ZGHA/2021-05-15 08:16:00,ZUCK/2021-05-15 20:17:00,ZJHK/2021-05-15 14:34:00,ZSAM/2021-05-15 10:22:00,ZSAM/2021-05-15 11:44:00,EGLL/2021-05-15 22:16:30,ZSAM/2021-05-15 15:31:00,ZSAM/2021-05-15 11:21:00,ZSAM/2021-05-15 23:13:00,ZSAM/2021-05-16 07:05:00,ZLIC/2021-05-15 15:47:00,ZSAM/2021-05-15 17:52:00,ZGZH/2021-05-15 22:49:38,ZSAM/2021-05-15 08:26:00,ZUGY/2021-05-15 19:43:00,ZSAM/2021-05-15 07:04:00,ZUCK/2021-05-15 09:17:00,ZSAM/2021-05-15 06:49:00,ZSHC/2021-05-15 23:10:00,ZHLY/2021-05-15 16:36:00,ZSAM/2021-05-15 12:32:00,ZSSS/2021-05-15 12:40:00,ZSSS/2021-05-15 22:20:00,ZBAD/2021-05-15 14:36:00,ZSAM/2021-05-15 13:25:00,ZSAM/2021-05-15 07:47:00,ZBAD/2021-05-15 18:26:00,ZSAM/2021-05-15 16:51:00,ZBAD/2021-05-15 19:48:00,ZSAM/2021-05-15 17:50:00,ZSAM/2021-05-15 10:28:00,ZSAM/2021-05-16 02:38:00,ZSSS/2021-05-15 19:56:00,ZSSS/2021-05-15 14:38:00,ZSAM/2021-05-15 21:59:00,ZSAM/2021-05-15 07:41:00,ZSAM/2021-05-15 14:36:00,ZSJN/2021-05-15 13:20:00,ZSAM/2021-05-15 13:14:00,ZGZH/2021-05-15 22:01:31,ZSAM/2021-05-15 11:16:00,ZSZS/2021-05-15 09:10:00,ZSAM/2021-05-15 18:25:00,ZSAM/2021-05-15 18:50:00,ZGSD/2021-05-15 18:30:00,ZUUU/2021-05-15 23:35:00,ZHCC/2021-05-16 00:31:00,ZSAM/2021-05-15 07:42:00,ZSAM/2021-05-15 20:55:00,ZUCK/2021-05-15 19:11:00,ZSAM/2021-05-15 15:57:00,ZHLY/2021-05-15 22:46:00,ZSYN/2021-05-15 09:42:00,ZSAM/2021-05-15 07:00:00,ZSJN/2021-05-15 14:20:00,ZSAM/2021-05-15 19:08:00,ZSAM/2021-05-15 16:57:00,ZSAM/2021-05-15 06:42:00,ZSAM/2021-05-15 21:01:00,ZSAM/2021-05-15 23:28:00,ZLXY/2021-05-15 09:17:00,ZUCK/2021-05-16 00:38:00,ZGHA/2021-05-15 09:06:00,ZGGG/2021-05-15 07:56:00,EGLL/2021-05-16 00:24:25,ZSYN/2021-05-15 18:21:00,ZSAM/2021-05-16 04:08:00,ZSAM/2021-05-15 07:53:00,ZUGY/2021-05-15 21:26:00,ZSAM/2021-05-16 01:15:00,ZGKL/2021-05-15 09:51:17,ZUGY/2021-05-15 09:42:00,ZSAM/2021-05-15 23:50:00,ZBYN/2021-05-15 21:25:00,ZSAM/2021-05-15 12:39:00,ZSAM/2021-05-16 01:12:00,ZSQD/2021-05-15 18:52:00,ZBAD/2021-05-15 09:21:00,ZSAM/2021-05-15 18:59:00,ZSAM/2021-05-15 09:23:00,ZSAM/2021-05-15 16:38:00,ZSAM/2021-05-16 05:59:00,ZUCK/2021-05-15 16:13:00,ZSAM/2021-05-15 08:18:00,ZSAM/2021-05-15 22:11:00,ZSAM/2021-05-16 11:32:00,ZGBH/2021-05-15 10:54:00,ZYHB/2021-05-15 22:40:00,ZUWX/2021-05-15 16:41:00,ZHCC/2021-05-15 20:07:00,ZSAM/2021-05-16 04:15:00,ZJHK/2021-05-15 11:05:00,ZSSS/2021-05-15 08:42:00,ZSAM/2021-05-15 20:17:00,ZSNJ/2021-05-15 09:09:00,ZSAM/2021-05-15 14:06:00,ZSAM/2021-05-15 07:23:00,ZSHC/2021-05-15 22:35:00,ZSAM/2021-05-15 16:27:00,ZSNB/2021-05-15 17:50:00,ZSAM/2021-05-15 13:17:00,ZSAM/2021-05-15 07:35:00,ZSHC/2021-05-15 17:18:00,ZSAM/2021-05-15 16:11:00,ZSAM/2021-05-15 15:39:00,ZSAM/2021-05-15 08:51:00,ZSAM/2021-05-15 16:05:00,ZSAM/2021-05-15 18:32:00,ZSAM/2021-05-15 19:15:00,ZSLG/2021-05-16 02:27:00,ZBHH/2021-05-15 12:49:00,ZSAM/2021-05-15 09:17:00,ZSZS/2021-05-15 07:59:00,ZGSD/2021-05-15 17:32:00,ZSAM/2021-05-15 11:18:00,ZSAM/2021-05-15 16:22:00,ZBHH/2021-05-15 10:22:00,ZUWX/2021-05-15 22:18:00,ZSAM/2021-05-15 21:22:00,ZBTJ/2021-05-15 11:50:00,ZGHA/2021-05-16 01:29:00,ZSAM/2021-05-15 12:57:00,ZSAM/2021-05-15 08:23:00,ZSQD/2021-05-16 02:01:00,ZBAD/2021-05-15 11:45:00', '[^,]+', 1, LEVEL ) IS NOT NULL)

目标：
我自己也查了网上的一些方案。大部分使用变量声明，declare ... begin ... end。
但是无法返回查询结构。

我最后的目的是要返回用户输入字符串分割后的结果。求解，怎么解决字符串文字过长问题？

Answer 1

没用过Oracle,不过一般这样的方式,在什么环境都不推荐的,代码层面解决不行吗.直接正则表达式在代码层转换,并且可以使用union来进行结果集拼接不就行了吗