1

I'm trying to use collect and cast for string aggregation, similar to listagg in Oracle Database 11 (also, unfortunately, wm_concat is not enabled on the database I'm querying). I also have read-only access to this database.

My current query is as follows

    SELECT plan.EMPLID,
        plan.STRM TERM,
        CAST(COLLECT(plan.ACAD_PLAN) AS sys.dbms_debug_vc2coll) agg_plans
    FROM SYSADM.PS_OSR_PLAN_TRM plan
    WHERE plan.EMPLID = '999999999'
    GROUP BY plan.EMPLID, plan.STRM

This gives me this result:

EMPLID      TERM    AGG_PLANS
999999999   1152    SYS.DBMS_DEBUG_VC2COLL(VARCHAR(ECON-MN, TXTLCLO-MN))
999999999   1154    SYS.DBMS_DEBUG_VC2COLL(VARCHAR(ACCTING-BS, ECON-MN))

I believe my logic is correct, but I'm having with trouble with the cast statement. I know that sys.dbms_debug_vc2coll is of type table of varchar2(1000). I would like to remove SYS.DBMS_DEBUG_VC2COLL(VARCHAR( from each row and simply list the results.

Is there a way to join together the resulting collection into a string? This stack overflow post has something similar to what I'm looking for... however, again, I have readonly access to this database. I'd also like to try to keep it one select statement.

Thanks for any help.

EDIT: I am not opposed to a solution that uses xmlagg.

Community
  • 1
  • 1
Kurt Mueller
  • 3,173
  • 2
  • 29
  • 50
  • 1
    Have you tried using `XMLAGG()`? – David Faber Mar 17 '15 at 13:59
  • Yea, I wasn't getting the same results however. Do you have any links/examples you can point me toward? – Kurt Mueller Mar 17 '15 at 14:00
  • 1
    See [here for a list of string aggregation techniques](http://oracle-base.com/articles/misc/string-aggregation-techniques.php). You can use `COLLECT()` but you need to write a function to convert the tabled results to a string. – David Faber Mar 17 '15 at 14:04
  • 1
    And see this question for an answer using `XMLAGG()`: http://stackoverflow.com/questions/20190598/aggregate-string-connection-in-oracle-10g – David Faber Mar 17 '15 at 14:06

1 Answers1

1

You could use XMLAGG() as follows:

SELECT plan.emplid, plan.strm AS term
     , RTRIM( XMLAGG( XMLELEMENT(e, plan.acad_plan || ',') ORDER BY plan.acad_plan ).EXTRACT('//text()'), ',' ) AS agg_plans
  FROM sysadm.ps_osr_plan_trm plan
 WHERE plan.emplid = '999999999'
 GROUP BY plan.emplid, plan.strm;

Note that the first parameter to XMLELEMENT() can be anything, it is simply the XML tag in which the value of plan.acad_plan || ',' will be wrapped, and ultimately it will be discarded.

David Faber
  • 12,277
  • 2
  • 29
  • 40