ORA-00923: Use over (partition by) with Xmlagg in Oracle

Question

I have a query of the form below and I need it to partition by a certain column but when I place it like this, it gives me the error : from keyword not found where expected

Select distinct t_name,  rtrim(xmlagg(xml element(e, text, ',').extract('//text()') order by c_id).getclobval(), ',' ) over (partition by t_name) col_list from all_cls where schema ='a' and table in ('tableA' , 'tableB')

What is the issue and how do I fix it so it functions properly with xmlagg as it does have for the below query with list agg :

Select distinct t_name,  listagg(text ',' ) within group(order by c_id) over (partition by t_name) col_list from all_cls where schema ='a' and table in ('tableA' , 'tableB')

score 0 · Accepted Answer · answered Apr 27 '19 at 07:27

0

It seems that you want to select table name and columns it contains.

LISTAGG, in this case, doesn't require OVER clause because you'll have to use GROUP BY (if you want to fetch table name as well) so it'll do the partitioning job; also, GROUP BY - in turn - makes DISTINCT unnecessary.

Something like this:

SQL> select table_name,
  2    listagg(column_name, ',' ) within group(order by column_id) col_list
  3  from all_tab_cols
  4  where owner = 'SCOTT'
  5    and table_name in ('DEPT' , 'BONUS')
  6  group by table_name;

TABLE_NAME COL_LIST
---------- --------------------------------------------------
BONUS      ENAME,JOB,SAL,COMM
DEPT       DEPTNO,DNAME,LOC

SQL>

XMLAGG version would be as follows; just as above, no special partitioning is necessary as GROUP BY does it:

SQL> select table_name,
  2    rtrim(xmlagg(xmlelement(e, column_name || ',').extract('//text()')
  3      order by column_id).getclobval(), ',' ) col_list
  4  from all_tab_cols
  5  where owner = 'SCOTT'
  6    and table_name in ('DEPT' , 'BONUS')
  7  group by table_name;

TABLE_NAME COL_LIST
---------- --------------------------------------------------
BONUS      ENAME,JOB,SAL,COMM
DEPT       DEPTNO,DNAME,LOC

SQL>

answered Apr 27 '19 at 07:27

Littlefoot

131,892
15
35
57

I'll try it out and let you know. Also another question, if the concatenated column list that is being returned let's say over 300 columns is used in another select statement will it work or throw the same error. Let's say select col1,col2... col350 from.. – Highdef Apr 27 '19 at 07:32
LISTAGG can return the result if it isn't longer than 4000 characters, which means that *it depends* how long those 300 columns' names are. If the result is longer than that, you'll have to use XMLAGG which doesn't have that restriction. – Littlefoot Apr 27 '19 at 07:34
Let me rephrase it, my question is can the select query handle length of more than 4000bytes if we try to say manually write a select query with more than 300columns with each column on avg say length 20. I know about the listagg limitation, but it's the select query maximum length I'm confused about in this case after the xmlagg returns the concatenated list of columns. – Highdef Apr 27 '19 at 07:35
As 300 * 10 = 3000, I suppose it should be OK. Your best option is to simply try it and see what happens. By the way, what kind of tables are those? Data warehouse? I mean, what do you need that many columns for? Not a problem, just being curious. – Littlefoot Apr 27 '19 at 07:38
Yeah, data warehouse. And again if it's say 6000 bytes instead of 3000 would select query be able to handle it? From the sources I read, it says in Oracle 8 it is 32k max length for select statement? Anyways, I'll try it out once. – Highdef Apr 27 '19 at 07:40
4000 characters is LISTAGG's restriction, not SELECTs. 32K? Is it, actually, a PL/SQL and you're storing the result into a variable? If you hit the limit, switch to a CLOB datatype. Oracle 8? Whoa, that's an old piece of software :) – Littlefoot Apr 27 '19 at 07:42
Yeah so, we're trying to create queries per table to select columns but not all of them that's why the xmlagg use. And we're using this col_list and pass it to a parent select statement which goes like : select 'select' || col_list || 'from table name' from (the given select query in the question) thus it will produce a table with a column having select queries. And the select queries length in that column might exceed 4000bytes – Highdef Apr 27 '19 at 07:48
Aha. That looks like a dynamic SQL. OK, good luck with whatever you are doing! – Littlefoot Apr 27 '19 at 07:49

ORA-00923: Use over (partition by) with Xmlagg in Oracle

1 Answers1