Search code examples
oracle-databaseobiee

Max size allowed with EVALUATE_AGGR in OBIEE

I have a field I'm bringing in to OBIEE, in the RPD, and creating a comma-delimited list using their EVALUATE_AGGR function. I'm using Oracle BI Admin Tool v11.1.1.7.1.

Is there a limit to the list size that gets created? Do I have to limit it? This is an example of the function I'm using:

EVALUATE_AGGR('LISTAGG(%1) WITHIN GROUP (ORDER BY %1)' AS CHAR,"DW".""."DW"."FACT COURS"."COURS_LIST")
Solution

  • So apparently since I'm aggregating a varchar, the max size is the same for any varchar in Oracle, 4000. I was able to limit it in the RPD by adding a VARCHAR size, in this case 200.

    EVALUATE_AGGR('LISTAGG(%1) WITHIN GROUP (ORDER BY %1)' AS VARCHAR(200),"DW".""."DW"."FACT COURS"."COURS_LIST")

    I decided to just move the logic into the ETL (PLSQL), and creating the aggregate there, and using SQL to limit it myself to no more than 4000.