Re: workaround for odciaggregate bug?
Date: Mon, 26 Jan 2009 06:27:42 -0600
Message-ID: <7b8774110901260427l5af0fdeoba6e69a37dc3642d_at_mail.gmail.com>
Have fun with that ODCI package. =) I filed an SR on 18-Jun-2008 on that "unsorting" issue, and it has been sitting on Bug 7194959 ever since. I told my developers to try doing it programmatically, because BDE is obviously not considering this a high priority. =)
On Fri, Jan 23, 2009 at 3:57 PM, Stauffer, Robert G < rstauffer_at_decommunications.com> wrote:
> All,
>
> 9.2.0.7 and 10.1.0.4 on AIX 5.2
>
> I'm trying to use ODCIAggregate and Tom Kyte's stringAgg function to
> concatenate a number in multiple rows into a string. Below is the
> output of the select statements I ran. Create scripts for the table,
> type, and function are below the output. I'm expecting the returned
> string to be '010203' but it returns '010032'. Unfortunately, it looks
> like there's a bug (7194959 to be fixed in 11.2) in ODCIAggregate that
> "unsorts" the sorted data when it creates the concatenated string. I've
> looked around (asktom, metalink, etc.) and can't seem to find a
> work-around for this method. Has anyone come up with one?
>
> SQL> select acctnbr, snapshotdate, prodcount from testtable;
>
> ACCTNBR SNAPSHOTDATE PRODCOUNT
> ---------- ------------------- ----------
> 0123456789 01/22/2009 15:15:29 3
> 0123456789 01/21/2009 15:15:40 0
> 0123456789 01/20/2009 15:15:45 2
> 0123456789 01/19/2009 15:15:48 0
> 0123456789 01/18/2009 15:16:06 1
> 0123456789 01/17/2009 15:16:11 0
>
> 6 rows selected.
>
> SQL> select acctnbr, snapshotdate, prodcount from testtable order by
> acctnbr, snapshotdate;
>
> ACCTNBR SNAPSHOTDATE PRODCOUNT
> ---------- ------------------- ----------
> 0123456789 01/17/2009 15:16:11 0
> 0123456789 01/18/2009 15:16:06 1
> 0123456789 01/19/2009 15:15:48 0
> 0123456789 01/20/2009 15:15:45 2
> 0123456789 01/21/2009 15:15:40 0
> 0123456789 01/22/2009 15:15:29 3
>
> 6 rows selected.
>
> SQL> col countstring form a30
> SQL> select acctnbr, stringAgg(prodcount) countstring
> 2 from (select acctnbr, snapshotdate, prodcount from testtable order
> by acctnbr, snapshotdate)
> 3 group by acctnbr;
>
> ACCTNBR COUNTSTRING
> ---------- ------------------------------
> 0123456789 010032
>
> SQL>
>
> ------------------------------------------------------------
>
> CREATE TABLE TESTTABLE (
> SNAPSHOTDATE DATE NOT NULL,
> ACCTNBR VARCHAR2(10) NOT NULL,
> PRODCOUNT INTEGER NOT NULL
> )
> /
> Insert into TESTTABLE (SNAPSHOTDATE, ACCTNBR, PRODCOUNT) Values
> (TO_DATE('01/22/2009 15:15:29','MM/DD/YYYY HH24:MI:SS'),'0123456789',3);
> Insert into TESTTABLE (SNAPSHOTDATE, ACCTNBR, PRODCOUNT) Values
> (TO_DATE('01/21/2009 15:15:40','MM/DD/YYYY HH24:MI:SS'),'0123456789',0);
> Insert into TESTTABLE (SNAPSHOTDATE, ACCTNBR, PRODCOUNT) Values
> (TO_DATE('01/20/2009 15:15:45','MM/DD/YYYY HH24:MI:SS'),'0123456789',2);
> Insert into TESTTABLE (SNAPSHOTDATE, ACCTNBR, PRODCOUNT) Values
> (TO_DATE('01/19/2009 15:15:48','MM/DD/YYYY HH24:MI:SS'),'0123456789',0);
> Insert into TESTTABLE (SNAPSHOTDATE, ACCTNBR, PRODCOUNT) Values
> (TO_DATE('01/18/2009 15:16:06','MM/DD/YYYY HH24:MI:SS'),'0123456789',1);
> Insert into TESTTABLE (SNAPSHOTDATE, ACCTNBR, PRODCOUNT) Values
> (TO_DATE('01/17/2009 15:16:11','MM/DD/YYYY HH24:MI:SS'),'0123456789',0);
> create or replace type StringAggType as object (theString
> varchar2(4000),
> static function ODCIAggregateInitialize (sctx IN OUT StringAggType
> ) return number,
> member function ODCIAggregateIterate (self IN OUT StringAggType,
> value IN varchar2 ) return number,
> member function ODCIAggregateTerminate (self IN StringAggType,
> returnValue OUT varchar2, flags IN number) return number,
> member function ODCIAggregateMerge (self IN OUT StringAggType,
> ctx2 IN StringAggType ) return number
> );
> /
> create or replace type body StringAggType is
> static function ODCIAggregateInitialize(sctx IN OUT StringAggType)
> return number is
> begin
> dbms_output.put_line('initializing ...');
> sctx := StringAggType( null );
> return ODCIConst.Success;
> end;
> member function ODCIAggregateIterate(self IN OUT StringAggType, value
> IN varchar2) return number is
> begin
> self.theString := theString || value;
> dbms_output.put_line('iteration result '||self.theString||' ...');
> return ODCIConst.Success;
> end;
> member function ODCIAggregateTerminate(self IN StringAggType,
> returnValue OUT varchar2, flags IN number) return number is
> begin
> returnValue := self.theString;
> dbms_output.put_line('terminating '||returnValue||' ...');
> return ODCIConst.Success;
> end;
> member function ODCIAggregateMerge(self IN OUT StringAggType, ctx2 IN
> StringAggType) return number is
> begin
> dbms_output.put_line('merging ...');
> self.theString := self.theString || ctx2.theString;
> return ODCIConst.Success;
> end;
> end;
> /
> CREATE or replace FUNCTION stringAgg(input varchar2 )
> RETURN varchar2
> PARALLEL_ENABLE AGGREGATE USING StringAggType;
> /
> select acctnbr, snapshotdate, prodcount from testtable;
> select acctnbr, snapshotdate, prodcount from testtable order by acctnbr,
> snapshotdate;
> col countstring form a30
> select acctnbr, stringAgg(prodcount) countstring
> from (select acctnbr, snapshotdate, prodcount from testtable order by
> acctnbr, snapshotdate)
> group by acctnbr;
>
>
> Bob Stauffer
> DBA
> D&E Communications
> Ephrata, PA
> rstauffer_at_decommunications.com
> 717-738-8737
>
> **DISCLAIMER
> This e-mail message and any files transmitted with it are intended for the
> use of the individual or entity to which they are addressed and may contain
> information that is privileged, proprietary and confidential. If you are not
> the intended recipient, you may not use, copy or disclose to anyone the
> message or any information contained in the message. If you have received
> this communication in error, please notify the sender and delete this e-mail
> message. The contents do not represent the opinion of D&E except to the
> extent that it relates to their official business.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- Charles Schultz -- http://www.freelists.org/webpage/oracle-lReceived on Mon Jan 26 2009 - 06:27:42 CST