sql - Oracle Database 10g: collect, cast for string aggregation -

I use collect and cast string aggregation try'm, Oracle database is similar to listagg 11 (also, unfortunately, I'm query is not capable wm_concat database). I also only use this database for reading.

My current query is as follows:

  select plan.EMPLID, plan.STRM period, CAST (collect (scheme. ACAD_PLAN) as sys.dbms_debug_vc2coll) SYSADM.PS_OSR_PLAN_TRM plan agg_plans PlankEMPLID where = '999999999' PlankEMPLID, group plan.STRM  

this gives me the results:

  EMPLID period 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 argument is correct, but I am having trouble with the cast statement. I know that sys.dbms_debug_vc2coll is of type table varchar2 (1000) . I want to remove SYS.DBMS_DEBUG_VC2COLL (VARCHAR () from each line and only for a list of results.

There is a string to be included in the resulting collection What's the way? What I'm seeing in this overflowing flow is something like ... However, I have the facility of reading only to this database. I would like to try to make it a confidential statement.

Thanks also for the help.

edit Do: I do not oppose any solutions that use xmlagg .

< p> you can use XMLAGG () follows:

  plan.emplid, plan.strm period as, RTRIM (XMLAGG ( XmlElement (e, plan.acad_plan || ',') order by plan.acad_plan) .EXTRACT ('// text ()'), ',') agg_plans from sysadm.ps_osr_plan_trm scheme where plan.emplid = '999999999' group Plan by. Plan, Plan.strm;  

Note that the first parameter of XMLELEMENT () can be anything, it only XML tag containing th plan.acad_plan || 'Value', ' will be wrapped, and eventually it will be discarded.