FROM keyword not found where expected, text selection Oracle SQL

Question

I want to produce a table which contains substrings and IDs of another table, where the substrings occur. See question Concatenate values.

create table table_expressions
(
A clob
);

insert all
  into table_expressions (a) values ('atveroeosipsum')
  into table_expressions (a) values ('test') 
  into table_expressions (a) values ('stetclitakasd') 
  into table_expressions (a) values ('noseatakimata') 
  into table_expressions (a) values ('loremipsumdolor') 
  into table_expressions (a) values ('consetetursadipscingelitr') 
select * from dual;

create table a_x
(
A clob,
B clob
);

insert all
  into a_x (a, b) values('atveroeosipsumloremipsumdolor', 1)
  into a_x (a, b) values('stetclitakasdtest', 2) 
  into a_x (a, b) values('noseatakimataatveroeosipsum', 3)
  into a_x (a, b) values('loremipsumdolor', 4)
  into a_x (a, b) values('consetetursadipscingelitr', 5)
select * from dual;

create table a_y 
  as 
  with 
  input_strings ( a ) as ( 
select column_value from table_expressions 
  ) 
select t2.a, listagg(t1.b, ',') within group (order by t1.b) 
 as ids from a_x t1 join input_strings t2 on t1.a like '%' || t2.a || '%' 
 group by t2.a 

The table_expressions contains the desired substrings My real data produce quite a lot IDs to be concatenated. After running the code with the real data the error 01489. 00000 - "result of string concatenation is too long" *Cause: String concatenation result is more than the maximum size. *Action: Make sure that the result is less than the maximum size. occurs.

How could I rewrite the code in order to format the resulting column IDs in CLOB format? I looked at question Listagg function but I didn't understand the code in the posted answer.

This code:

create table a_y
as
with
 input_strings ( a ) as (
   select a
   from table_expressions                    
 )
select   t2.a, RTRIM(XMLAGG(XMLELEMENT(E,t1.a,',').EXTRACT('//text()') ORDER BY t1.a).GetClobVal(),',') AS LIST as ids
from     a_x t1 
     join input_strings t2 on t1.a like '%' || t2.a || '%'
group by t2.a
;

produces error FROM keyword not found where expected. I would like to produce a table a_y, which should look like that and columns A ans IDs should be in clob format:

A                            | IDs                                    
-----------------------------|-------
atveroeosipsum               | 1,3
test                         | 2
stetclitakasd                | 2
noseatakimata                | 3
loremipsumdolor              | 1,4
consetetursadipscingelitr    | 5

How can I fix this?

Answer 1

The immediate error is caused by having two aliases given to the result of the concatenation: You have AS LIST as ids. You can't give two aliases to the result of a calculation. If you want the newly created table to have a column LIST then delete as ids, and vice versa.

Then you will run into another error: you are attempting to ORDER BY t1.a in the aggregation. That won't work; you can't order by a CLOB in the XML aggregation. Do you really care in what order the aggregation happens? If you don't, change to ORDER BY NULL. If you do care, you have a problem, since in Oracle an order_by_clause simply cannot order by a CLOB expression. You will have to create a separate column for ordering by using other methods.

In the solution overall, there is no need for the WITH clause. Wherever you refer to "input_strings" in the query (other than the WITH clause), simply write "table_expressions".

EDIT

Here is how this could be made to work. First I will show the CREATE TABLE statements. I will assume that table_expressions has a CLOB column of search strings, and that there are NO DUPLICATES in this column. Even so, the table also needs a separate primary key, of a data type that is not LOB or other long, not-standard-type. I use NUMBER for this.

Then I aggregate by this primary key column. Alas, I can't select the search string at the same time. I could SELECT MAX(t2.a) but that doesn't work with CLOB values either! Instead, I need a further join to match the primary key to the search string. (Sorry, the query will take that much longer because of this...)

In the aggregation, I sort by the first 4000 characters of the string value from column a. This is not as good as sorting by the entire input string, but it is still better than ordering by NULL.

create table a_x ( a, b ) as
  select to_clob('atveroeosipsumloremipsumdolor'), 1 from dual union all
  select to_clob('stetclitakasdtest')            , 2 from dual union all
  select to_clob('noseatakimataatveroeosipsum')  , 3 from dual union all
  select to_clob('loremipsumdolor')              , 4 from dual union all
  select to_clob('consetetursadipscingelitr')    , 5 from dual
;

create table table_expressions ( a, pk ) as 
 select to_clob('atveroeosipsum') , 10 from dual union all 
 select to_clob('test') , 11 from dual union all 
 select to_clob('stetclitakasd') , 12 from dual union all 
 select to_clob('noseatakimata') , 13 from dual union all 
 select to_clob('loremipsumdolor') , 14 from dual union all 
 select to_clob('consetetursadipscingelitr'), 15 from dual 
 ;

create table a_y as
select te.a, s.ids
from   table_expressions te 
       join
       (select   t2.pk, RTRIM(XMLAGG(XMLELEMENT(E,t1.a,',').EXTRACT('//text()') 
                     ORDER BY cast(t1.a as varchar2(4000))).GetClobVal(),',') as ids
        from     a_x t1 
         join table_expressions t2 
          on t1.a like '%' || t2.a || '%'
         group by t2.pk
       ) s
on te.pk = s.pk
;

Now let's check what we got:

select * from a_y;

A                          IDS
-------------------------  ---------------------------------------------------------
atveroeosipsum             atveroeosipsumloremipsumdolor,noseatakimataatveroeosipsum
test                       stetclitakasdtest
stetclitakasd              stetclitakasdtest
noseatakimata              noseatakimataatveroeosipsum
loremipsumdolor            atveroeosipsumloremipsumdolor,loremipsumdolor
consetetursadipscingelitr  consetetursadipscingelitr

EDIT #2

If you need to concatenate the id's from table a_x (column b), not the CLOBs themselves, then replace t1.a with t1.b (and, in the ORDER BY clause of XMLAGG, you don't need any cast, just order by t1.b).

drop table a_y purge;

create table a_y as
select te.a, s.ids
from   table_expressions te 
       join
       (select   t2.pk, RTRIM(XMLAGG(XMLELEMENT(E,t1.b,',').EXTRACT('//text()') 
                     ORDER BY t1.b).GetClobVal(),',') as ids
        from     a_x t1 
         join table_expressions t2 
          on t1.a like '%' || t2.a || '%'
         group by t2.pk
       ) s
on te.pk = s.pk
;

select * from a_y;

A                          IDS
-------------------------  ---
atveroeosipsum             1,3
test                       2
stetclitakasd              2
noseatakimata              3
loremipsumdolor            1,4
consetetursadipscingelitr  5