Oracle ORA-01704: the string longer than 4000 chars

Question

I have a subquery as follows:

SELECT
    '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' 
FROM
    dual

I exec the SQL Segment on PL/SQL,it throw exception. >ORA-01704: the string literal is too long.

I want to get the subquery as next query condition,how can I do?

the complete SQL segmen as follows:

with params as
 (
  
  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('YSSY/2021-05-16 07:13:37,ZSAM/2021-05-15 18:06:00,ZHCC/2021-05-15 20:07:00,ZBAD/2021-05-15 14:36:00,ZSOF/2021-05-15 10:46:00,ZSAM/2021-05-16 05:56:00,ZHCC/2021-05-16 00:31:00,WSSS/2021-05-15 13:18:43,ZSAM/2021-05-15 12:22:00,ZSAM/2021-05-15 20:17:00,EHAM/2021-05-15 19:21:16,ZSAM/2021-05-15 10:22:00,ZSAM/2021-05-15 14:06:00,ZSAM/2021-05-16 01:12:00,ZHLY/2021-05-15 22:46:00,ZSAM/2021-05-15 23:36:00,ZBAD/2021-05-15 18:26:00,WSSS/2021-05-15 15:31:07,ZBAD/2021-05-15 17:35:00,EGLL/2021-05-15 22:16:30,ZBAD/2021-05-15 19:48:00,ZBAD/2021-05-15 09:21:00,ZSAM/2021-05-15 13:17:00,ZSAM/2021-05-16 02:38:00,ZUUU/2021-05-15 14:53:00,ZSAM/2021-05-15 09:02:00,ZSAM/2021-05-16 05:59:00,ZSAM/2021-05-15 16:05:00,ZSAM/2021-05-15 07:04:00,ZSAM/2021-05-15 19:15:00,KLAX/2021-05-16 12:27:19,ZSOF/2021-05-15 11:50:00,EGLL/2021-05-16 00:24:25,KLAX/2021-05-15 15:59:43,ZSAM/2021-05-15 22:32:00,ZSAM/2021-05-15 22:11:00,ZSAM/2021-05-15 12:17:00,ZSAM/2021-05-16 11:32:00,ZBAD/2021-05-15 11:45:00,ZSAM/2021-05-15 09:43:00',
                                '[^,]+',
                                1,
                                LEVEL) str
             FROM DUAL
           CONNECT BY REGEXP_SUBSTR('YSSY/2021-05-16 07:13:37,ZSAM/2021-05-15 18:06:00,ZHCC/2021-05-15 20:07:00,ZBAD/2021-05-15 14:36:00,ZSOF/2021-05-15 10:46:00,ZSAM/2021-05-16 05:56:00,ZHCC/2021-05-16 00:31:00,WSSS/2021-05-15 13:18:43,ZSAM/2021-05-15 12:22:00,ZSAM/2021-05-15 20:17:00,EHAM/2021-05-15 19:21:16,ZSAM/2021-05-15 10:22:00,ZSAM/2021-05-15 14:06:00,ZSAM/2021-05-16 01:12:00,ZHLY/2021-05-15 22:46:00,ZSAM/2021-05-15 23:36:00,ZBAD/2021-05-15 18:26:00,WSSS/2021-05-15 15:31:07,ZBAD/2021-05-15 17:35:00,EGLL/2021-05-15 22:16:30,ZBAD/2021-05-15 19:48:00,ZBAD/2021-05-15 09:21:00,ZSAM/2021-05-15 13:17:00,ZSAM/2021-05-16 02:38:00,ZUUU/2021-05-15 14:53:00,ZSAM/2021-05-15 09:02:00,ZSAM/2021-05-16 05:59:00,ZSAM/2021-05-15 16:05:00,ZSAM/2021-05-15 07:04:00,ZSAM/2021-05-15 19:15:00,KLAX/2021-05-16 12:27:19,ZSOF/2021-05-15 11:50:00,EGLL/2021-05-16 00:24:25,KLAX/2021-05-15 15:59:43,ZSAM/2021-05-15 22:32:00,ZSAM/2021-05-15 22:11:00,ZSAM/2021-05-15 12:17:00,ZSAM/2021-05-16 11:32:00,ZBAD/2021-05-15 11:45:00,ZSAM/2021-05-15 09:43:00',
                                    '[^,]+',
                                    1,
                                    LEVEL) IS NOT NULL))
SELECT params.flightDate,
       t.datatime,
       t.airport4code,
       trunc(t.fcsttime) AS fcstday,
       t.fcsttime,
       t.warningtype,
       t.warningvalue,
       t.warningnote,
       to_number(trunc(t.fcsttime) - trunc(t.datatime)) AS dayNums
  FROM tb_weather_forecast t, params
 where params.airportCode = t.airport4code

Roberto Hernandez · Answer 1 · 2021-08-31T09:19:52.647

The main problem here is limit buffer inside a line in sqlplus, which is not going to allow you to work with it as you are intending to.

For more information, check this article about limits in sqlplus:

Oracle Sqlplus Limits

Having said that, one possible approach is to split the string before enters in sqlplus ( you can use python or bash for that, is not difficult ), but as I don't know how this data is inserted in the query -you did not explain it-, I can't give you clues here.

Once the data is split, let's say by the comma, you can work with it as you want to.

The clob variable contains the whole string, as I split it on lines using CRLF using the comma as separator.
Then I split the records by the comma using regexp_substr
But as I used CRLF to split the records, I have to remove it.

Demo

SQL> select version from v$instance ;

VERSION
-----------------
19.0.0.0.0

SQL> select sysdate from dual ;

SYSDATE
---------
31-AUG-21

SQL> host ls -l t.sql
-rw-r--r-- 1 ftpcpl dms 5786 Aug 31 09:37 t.sql

SQL> host cat t.sql
set serveroutput on size unlimited
declare
vstring clob := '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';
vcounter pls_integer;
vrecord varchar2(100);
begin
 vcounter := regexp_count( vstring , ',') + 1;
 for var in 1..vcounter
 loop
        if var=1
        then
                vrecord := regexp_substr( vstring, '[^,]+', 1 , 1 );
                vrecord := replace( replace( vrecord , CHR(10) ) , CHR(13) );
        dbms_output.put_line(vrecord);
    elsif var > 1 and var <= vcounter
    then
                vrecord := regexp_substr( vstring, '[^,]+', 1 , var );
                vrecord := replace( replace( vrecord , CHR(10) ) , CHR(13) );
        dbms_output.put_line(vrecord);
    end if;
 end loop;
end;
/


SQL> @t.sql
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

PL/SQL procedure successfully completed.

SQL>

The OP does not mention SQL/Plus in their question nor in the tags. — MT0, Aug 31 '21 at 08:26
I don't have access to an SQL/Plus client connected to an Oracle 11g instance to test this but I would be surprised if it worked; it does not work outside of SQL/Plus [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=36698e22d0b3b4e9b809d256d1d15ecd) when there are more than 4000 bytes of characters in a literal string. — MT0, Aug 31 '21 at 08:31
@MTO, he did mention that in another question he post with the same problem. But you are right, he doesn't in this one — Roberto Hernandez, Aug 31 '21 at 08:44
@MTO, it works in sqlplus ( I am using Oracle 19c ), and It does because the string is separated by `CRLF` as I explained in my answer, and declared as a clob variable inside a PLSQL anonymous block. And it works as well in 11g too. — Roberto Hernandez, Aug 31 '21 at 09:16
@RobertoHernandez the result data as temp data ,then combine another table to query. — 月夜归醉, Aug 31 '21 at 09:37
@Roberto Hernandez I describe the question more detail, the whole sql I have given. I hope you can give me some advise — 月夜归醉, Aug 31 '21 at 09:48

Shajitha Begum K · Answer 2 · 2021-08-31T06:39:38.180

0

If you try to put more than 4000 bytes of characters into it then it will fail as the literal (denoted by the surrounding '' characters) will be larger than its maximum size.

Divide it into multiple chunks and use select SQL command:

SELECT (to_clob('') || to_clob('')) from dual;

edited Aug 31 '21 at 06:39

answered Aug 31 '21 at 06:11

Shajitha Begum K

57
10

the dual is not my business table, it is already in oracle table – 月夜归醉 Aug 31 '21 at 06:19
The String literal parameter is dynamic given from user, not fixed – 月夜归醉 Aug 31 '21 at 07:15
1

@月夜归醉 That does not change the fact that `VARCHAR2` has an upper limit of 4000 bytes of characters and you cannot have a string literal with more than that limit. – MT0 Aug 31 '21 at 09:10
@Roberto Hernandez The result data as temp data ,then combine another table to query. I have describe the question more detail, the whole sql I have given. I hope you can give me some advise – 月夜归醉 Aug 31 '21 at 10:02
@月夜归醉 , sorry mate, your question has been closed – Roberto Hernandez Aug 31 '21 at 10:05
@RobertoHernandez Thank you for answer my question,but I want to the data result set to combine another query, and I only have read privilege on db. I have been seeing the similiar question in the net,but most of them didn't refer as query data result combine another query – 月夜归醉 Sep 01 '21 at 00:16

Oracle ORA-01704: the string longer than 4000 chars

2 Answers2