Re: custom aggregate function in oracle 10g

From: <vitalisman_at_gmail.com>
Date: Wed, 19 Mar 2008 06:37:22 -0700 (PDT)
Message-ID: <cbfec7ec-961e-4a43-bff4-7073290db7ec@2g2000hsn.googlegroups.com>


On Mar 19, 1:12 pm, Jürg Schaufelberger
<juerg.schaufelber..._at_tele2.ch> wrote:
> 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

Could that be that you have defined the CHAINSTRING function with the PARALLEL_ENABLE clause? Received on Wed Mar 19 2008 - 08:37:22 CDT

Original text of this message