"ORA-00923: FROM keyword not found where expected" just on Oracle 10g not in 11g

Question

I am using the dynamic SQL from:

https://stackoverflow.com/a/36333897/5021320

to create a table and some partitions based on time of the day.

On a Oracle 11g DB is working OK, but on a Oracle 10g I am getting the following error:

ORA-00923: FROM keyword not found where expected

Trying to run the following query alene, also is giving me the error:

select listagg('PARTITION TABLE_NAME_' || to_char(sysdate + (level-1)/24, 'yyyymmddHH24') ||
' VALUES LESS THAN (TIMESTAMP''' || to_char(sysdate + (level)/24, 'yyyy-mm-dd HH24') || ':00:00''), ')
within group (order by level)
from dual
connect by level <= 24; 

Why is this happening?

Answer 1

You can try something like this.

   SELECT WMSYS.WM_CONCAT('PARTITION TABLE_NAME_'
  || TO_CHAR(sysdate + (level-1)/24, 'yyyymmddHH24')
  || ' VALUES LESS THAN (TIMESTAMP'''
  || TO_CHAR(sysdate + (level)/24, 'yyyy-mm-dd HH24')
  || ':00:00'') ') OUTPUT
FROM DUAL
  CONNECT BY level <= 24

    -----------------------------OUTPUT----------------------------------------
 OUTPUT
PARTITION TABLE_NAME_2016041907 VALUES LESS THAN (TIMESTAMP'2016-04-19 08:00:00') ,PARTITION TABLE_NAME_2016041908 VALUES LESS THAN (TIMESTAMP'2016-04-19 09:00:00') ,PARTITION TABLE_NAME_2016041909 VALUES LESS THAN (TIMESTAMP'2016-04-19 10:00:00') ,PARTITION TABLE_NAME_2016041910 VALUES LESS THAN (TIMESTAMP'2016-04-19 11:00:00') ,PARTITION TABLE_NAME_2016041911 VALUES LESS THAN (TIMESTAMP'2016-04-19 12:00:00') ,PARTITION TABLE_NAME_2016041912 VALUES LESS THAN (TIMESTAMP'2016-04-19 13:00:00') ,PARTITION TABLE_NAME_2016041913 VALUES LESS THAN (TIMESTAMP'2016-04-19 14:00:00') ,PARTITION TABLE_NAME_2016041914 VALUES LESS THAN (TIMESTAMP'2016-04-19 15:00:00') ,PARTITION TABLE_NAME_2016041915 VALUES LESS THAN (TIMESTAMP'2016-04-19 16:00:00') ,PARTITION TABLE_NAME_2016041916 VALUES LESS THAN (TIMESTAMP'2016-04-19 17:00:00') ,PARTITION TABLE_NAME_2016041917 VALUES LESS THAN (TIMESTAMP'2016-04-19 18:00:00') ,PARTITION TABLE_NAME_2016041918 VALUES LESS THAN (TIMESTAMP'2016-04-19 19:00:00') ,PARTITION TABLE_NAME_2016041919 VALUES LESS THAN (TIMESTAMP'2016-04-19 20:00:00') ,PARTITION TABLE_NAME_2016041920 VALUES LESS THAN (TIMESTAMP'2016-04-19 21:00:00') ,PARTITION TABLE_NAME_2016041921 VALUES LESS THAN (TIMESTAMP'2016-04-19 22:00:00') ,PARTITION TABLE_NAME_2016041922 VALUES LESS THAN (TIMESTAMP'2016-04-19 23:00:00') ,PARTITION TABLE_NAME_2016041923 VALUES LESS THAN (TIMESTAMP'2016-04-20 00:00:00') ,PARTITION TABLE_NAME_2016042000 VALUES LESS THAN (TIMESTAMP'2016-04-20 01:00:00') ,PARTITION TABLE_NAME_2016042001 VALUES LESS THAN (TIMESTAMP'2016-04-20 02:00:00') ,PARTITION TABLE_NAME_2016042002 VALUES LESS THAN (TIMESTAMP'2016-04-20 03:00:00') ,PARTITION TABLE_NAME_2016042003 VALUES LESS THAN (TIMESTAMP'2016-04-20 04:00:00') ,PARTITION TABLE_NAME_2016042004 VALUES LESS THAN (TIMESTAMP'2016-04-20 05:00:00') ,PARTITION TABLE_NAME_2016042005 VALUES LESS THAN (TIMESTAMP'2016-04-20 06:00:00') ,PARTITION TABLE_NAME_2016042006 VALUES LESS THAN (TIMESTAMP'2016-04-20 07:00:00') -----------------------------------------------------------------------------