custom aggregate function in oracle 10g

From: Jürg Schaufelberger <juerg.schaufelberger_at_tele2.ch>
Date: Wed, 19 Mar 2008 05:12:07 -0700 (PDT)
Message-ID: <5ec209bd-62fc-4f49-98a1-3c96d566953a@59g2000hsb.googlegroups.com>

Hello
I need a aggregate function for concatenate a list of strings. I created a custom aggregate function, here only the body of the aggregate function:

MEMBER FUNCTION ODCIAggregateIterate
  ( self IN OUT StringChain,
    val IN VARCHAR2
  ) RETURN NUMBER IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Iterate ' || TO_CHAR(val));     IF val IS NULL THEN

        /* Will never happen */
        DBMS_OUTPUT.PUT_LINE('Null on iterate');
    ELSE
      IF self.runningLength = 0 THEN
        self.runningString := val;
        self.runningLength := LENGTH(val);
      ELSE
        self.runningString := self.runningString || ',' || val;
        self.runningLength := self.runningLength + LENGTH(val) + 1;
      END IF;

    END IF;
    RETURN ODCIConst.Success;
  END; This function works, but if I want to have the items of the string chain in the same order as in the list, I realize it does not work. Here the example

SQL> select v.fid_ls_liegenschaf, to_number(f.nummer) nummer from av_vs_plan_lieg v, av_pe_plan f
where v.deleted = 0 and v.fid_pe_plan = f.fid and fid_ls_liegenschaf = 140
order by 2

FID_LS_LIEGENSCHAF NUMMER

------------------ ------
140                53
140                54
140                55
140                56
140                57
140                71
140                72

7 Zeilen ausgewählt.

SQL> select fid_ls_liegenschaf, chainstring(h.nummer) from (
select v.fid_ls_liegenschaf, to_number(f.nummer) nummer from av_vs_plan_lieg v, av_pe_plan f
where v.deleted = 0 and v.fid_pe_plan = f.fid order by 2) h
where fid_ls_liegenschaf = 140
group by fid_ls_liegenschaf

FID_LS_LIEGENSCHAF CHAINSTRING(H.NUMMER)

------------------ ---------------------
140                53,54,55,57,72,71,56

1 Zeilen ausgewählt.

Have anybody an idea what's the raeson in this case? Thanks for any help

Juerg Received on Wed Mar 19 2008 - 07:12:07 CDT