Im trying to create a cursor fetch_table_info that uses the LISTAGG in its select statement.
create or replace PROCEDURE testcheck is
cursor fetch_table_info is
select LISTAGG(B.COLUMN_NAME,'||'',''||')
WITHIN GROUP (ORDER BY B.COLUMN_POSITION)
from USER_IND_COLUMNS B
where B.TABLE_NAME=B.INDEX_NAME and b.TABLE_NAME='TEST_TAB';
begin
dbms_output.put_line(SQLERRM);
end;
/
The select statement executes fine individually, but compiling the procedure is giving the below error
Found 'LISTAGG', Only CUME_DIST, DENSE_RANK, PERCENT_RANK, RANK, PERCENTILE_CONT and PERCENTILE_DISC are valid with WITHIN GROUP
Thanks a lot in advance!
It looks like you're using an older version of Toad that doesn't recognise the listagg function introduced in Oracle 11gR2.
Description
The 11gR2 new analytic function LISTAGG is not recognised by syntax checker....
The code above runs ok but if we try to format it, the following error message appears:
ERROR line 4, col 10, ending_line 4, ending_col 16, Found listagg, Only CUME_DIST, DENSE_RANK, PERCENT_RANK, RANK, PERCENTILE_CONT and PERCENTILE_DISC are valid with WITHIN GROUP
Cause
The parser in version 10.1 does not support this 11gR2 functionResolution
Toad for Oracle 11.0 and above support this. You can download the latest version of Toad using http://bit.ly/noHL20
... which amusingly points to an old Quest URL that just redirects to the Dell support landing page. Looks like you can get the current version here.