Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Rajeshwaran.

Asked: May 07, 2012 - 12:21 pm UTC

Answered by: Tom Kyte - Last updated: May 27, 2020 - 1:03 am UTC

Category: Database - Version: 11.2.0.1

Viewed 10K+ times! This question is

You Asked

Tom:

I was benchmarking stragg with listagg and results below, is that stragg is better than listagg? Please let me know your thoughts on this.

drop table t purge;

create table t as
select *
from ( select *
       from all_objects
       where rownum <= 1000  ) a, 
 (select 'A' txt from dual
 union all select 'B' txt from dual
 union all select 'C' txt from dual);

exec dbms_stats.gather_table_stats(user,'T');

column owner format a10 trunc;
column object_type format a15 trunc;
column object_name format a30 trunc;
column vals format a20 trunc;

exec runstats_pkg.rs_start;

select owner,object_type,object_name,
  listagg(txt,';') within group(order by txt) as vals
from t
group by  owner,object_type,object_name;

exec runstats_pkg.rs_middle;

select owner,object_type,object_name,
  fnc(txt) as vals
from t
group by owner,object_type,object_name;

exec runstats_pkg.rs_stop(100);

rajesh@ORA11GR2> exec runstats_pkg.rs_stop(100);
Run1 ran in 644 hsecs
Run2 ran in 229 hsecs
run 1 ran in 281.22% of the time

Name                                  Run1        Run2        Diff
LATCH.checkpoint queue latch           113           9        -104
STAT...redo size                     4,288       4,180        -108
STAT...session logical reads           108         236         128
STAT...consistent gets                  50         200         150
STAT...consistent gets - exami           0         150         150
STAT...consistent gets from ca          50         200         150
LATCH.SQL memory manager worka         206           5        -201
LATCH.cache buffers chains             816         484        -332
STAT...Elapsed Time                    646         234        -412
STAT...session cursor cache hi           2       1,001         999
STAT...workarea executions - o           4       1,004       1,000
STAT...execute count                     5       1,005       1,000
STAT...sorts (memory)                    1       1,001       1,000
STAT...recursive calls                   4       1,006       1,002
STAT...opened cursors cumulati           5       1,008       1,003
STAT...calls to get snapshot s           3       1,051       1,048
STAT...sorts (rows)                  6,000       9,000       3,000
LATCH.JS slv state obj latch        15,968           1     -15,967
STAT...session uga memory max      123,452      65,512     -57,940
STAT...session pga memory max      196,608     327,680     131,072
STAT...session pga memory          196,608     327,680     131,072

Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
      18,521       1,490     -17,031  1,243.02%

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.25
rajesh@ORA11GR2>


create or replace type vcarray is table of varchar2(400);

create or replace type stragg as object
(
 l_total vcarray,
 
 static function odciaggregateinitialize(ctx in out stragg) return number,
 
 member function odciaggregateiterate(self in out stragg,
  val varchar2) return number,
 
 member function odciaggregatemerge(self in out stragg,
  ctx2 stragg) return number,
 
 member function odciaggregateterminate(self in stragg,
   returnvalue out  varchar2,
   flags in number) return number
);
/

create or replace type body stragg is
 static function odciaggregateinitialize(ctx in out stragg) 
 return number
 is
 begin
  ctx := stragg( vcarray() );
  return ODCIConst.Success;
 end;
 
 member function odciaggregateiterate(self in out stragg,
 val varchar2) return number
 is  
 begin
  if val is not null then
   self.l_total.extend;
   self.l_total(self.l_total.count) := val;
  end if;
  return ODCIConst.Success;
 end;
 
 member function odciaggregatemerge(self in out stragg,
 ctx2 stragg) return number
 as
 begin
  self.l_total := ctx2.l_total;
  return odciconst.success;
 end;
 
 member function odciaggregateterminate(self in stragg,
   returnvalue out  varchar2,
   flags in number) return number as 
 begin
  for x in (select column_value from table(self.l_total) order by 1)
  loop
   returnvalue := returnvalue ||';'||x.column_value;
  end loop;
  returnvalue := trim(';' from returnvalue);
  return odciconst.success;
 end;
end;
/

create or replace function fnc(x varchar2)
return varchar2
aggregate using stragg;
/

and we said...

Is that repeatable for you?

I get radically different results (I think the latch difference was caused by some other session or background process - it doesn't latch like that normally)

this was 11.2.0.3:

ops$tkyte%ORA11GR2> create table t as
  2  select *
  3  from ( select *
  4         from all_objects
  5         where rownum <= 1000  ) a,
  6      (select 'A' txt from dual
  7      union all select 'B' txt from dual
  8      union all select 'C' txt from dual);

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> column owner format a10 trunc;
ops$tkyte%ORA11GR2> column object_type format a15 trunc;
ops$tkyte%ORA11GR2> column object_name format a30 trunc;
ops$tkyte%ORA11GR2> column vals format a20 trunc;
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set termout off
ops$tkyte%ORA11GR2> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set termout off
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec runstats_pkg.rs_stop(100);
Run1 ran in 3 cpu hsecs
Run2 ran in 37 cpu hsecs
run 1 ran in 8.11% of the time

Name                                  Run1        Run2        Diff
STAT...redo size                     4,532       4,724         192
LATCH.row cache objects                 79         364         285
STAT...session cursor cache hi           2       1,001         999
STAT...execute count                     5       1,005       1,000
STAT...sorts (memory)                    1       1,001       1,000
STAT...workarea executions - o           4       1,004       1,000
LATCH.simulator hash latch               6       1,006       1,000
STAT...opened cursors cumulati           5       1,005       1,000
STAT...recursive calls                   4       1,006       1,002
STAT...table fetch by rowid              0       2,118       2,118
STAT...rows fetched via callba           0       2,118       2,118
STAT...index fetch by key                0       2,118       2,118
STAT...sorts (rows)                  6,000       9,000       3,000
STAT...calls to get snapshot s           2       3,120       3,118
STAT...buffer is not pinned co           0       4,236       4,236
STAT...consistent gets - exami           8       6,363       6,355
STAT...consistent gets from ca          58       6,414       6,356
STAT...consistent gets                  58       6,414       6,356
STAT...session logical reads            89       6,450       6,361
LATCH.cache buffers chains             248       6,623       6,375
STAT...session uga memory          -65,456           0      65,456
STAT...session pga memory                0     393,216     393,216
STAT...logical read bytes from     729,088  52,838,400  52,109,312

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
694       8,380       7,686      8.28%

PL/SQL procedure successfully completed.



I wrapped the queries with a set termout off/on so we don't time sqlplus painting the screen.

and you rated our response

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Retested it again - Now looks different

May 08, 2012 - 1:21 am UTC

Reviewer: Rajeshwaran Jeyabal

Is that repeatable for you?


rajesh@ORA11GR2> @d:\script.sql;
rajesh@ORA11GR2> column owner format a10 trunc;
rajesh@ORA11GR2> column object_type format a15 trunc;
rajesh@ORA11GR2> column object_name format a30 trunc;
rajesh@ORA11GR2> column vals format a20 trunc;
rajesh@ORA11GR2> 
rajesh@ORA11GR2> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@ORA11GR2> 
rajesh@ORA11GR2> set termout off;
rajesh@ORA11GR2> select owner,object_type,object_name,
  2    listagg(txt,';') within group(order by txt) as vals
  3  from t
  4  group by  owner,object_type,object_name;

1000 rows selected.

Elapsed: 00:00:00.07
rajesh@ORA11GR2> set termout on;
rajesh@ORA11GR2> 
rajesh@ORA11GR2> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2> 
rajesh@ORA11GR2> set termout off;
rajesh@ORA11GR2> select owner,object_type,object_name,
  2    fnc(txt) as vals
  3  from t
  4  group by owner,object_type,object_name;

1000 rows selected.

Elapsed: 00:00:00.17
rajesh@ORA11GR2> set termout on;
rajesh@ORA11GR2> 
rajesh@ORA11GR2> exec runstats_pkg.rs_stop(100);
Run1 ran in 8 hsecs
Run2 ran in 52 hsecs
run 1 ran in 15.38% of the time
 
Name                                  Run1        Run2        Diff
STAT...session cursor cache hi           2       1,001         999
STAT...sorts (memory)                    1       1,001       1,000
STAT...execute count                     5       1,005       1,000
STAT...workarea executions - o           4       1,004       1,000
STAT...opened cursors cumulati           5       1,007       1,002
STAT...recursive calls                   3       1,005       1,002
STAT...calls to get snapshot s           3       1,007       1,004
STAT...sorts (rows)                  6,000       9,000       3,000
STAT...session uga memory max      123,452      65,512     -57,940
STAT...session pga memory max      131,072      65,536     -65,536
 
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
         580         594          14     97.64%

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.62
rajesh@ORA11GR2> 
rajesh@ORA11GR2> 
rajesh@ORA11GR2> spool off;

May 08, 2012 - 8:17 am UTC

Reviewer: A reader

@Rajeshwaran -

What are trying to say / get out of your question?
Tom Kyte

Followup  

May 08, 2012 - 11:24 am UTC

he is saying "no, it doesn't reproduce - listagg is much faster - there was something else going on in my database at that point in time that was happening when I ran listagg but not when I ran stragg"


runstats is only 'reliable' in a single user situation as it looks at database wide metrics, not just session metrics.

February 05, 2013 - 2:13 am UTC

Reviewer: Sangeetha P from Qatar

Hi Tom,

I have a query like this.
Please ignore the table structure.

SELECT LOOP_DATE,
SHIFT,
stragg( DISTINCT lic_list) lic_list,
SUM(DEMAND_VAL) DEMAND_VAL
FROM
(SELECT SHIFT,
LOOP_DATE,
DEMAND_VAL,
'('
||AC_TYPE
||','
||CNT
||')' lic_list
FROM
(SELECT LOOP_DATE,
DEMAND_VAL,
AC_TYPE,
SHIFT,
NVL( (LENGTH(lic_list) - NVL(LENGTH(REPLACE(lic_list,AC_TYPE,'')),0))/ LENGTH(AC_TYPE),0) cnt
FROM
(SELECT LOOP_DATE,
DEMAND_VAL,
AC_TYPE,
SHIFT,
(SELECT WM_CONCAT(CREW_LIST) CREW_LIST
FROM baseshift_tab BS
WHERE BS.SHIFT_DATE=DEMAND_TAB.LOOP_DATE
AND BS.WORKPOOL =DEMAND_TAB.SHIFT
) lic_list
FROM
(SELECT DEMAND_TAB.LOOP_DATE,
DEMAND_TAB.DEMAND_VAL,
(SELECT MST_AC_TYP.AC_TYP
FROM MST_AC_TYP
WHERE MST_AC_TYP.MAT_ID =DEMAND_TAB.ROW_NUM
AND MST_AC_TYP.isACTIVE ='Y'
) AC_TYPE,
DEMAND_TAB.SHIFT
FROM DEMAND_TAB
) DEMAND_TAB
)
)
)
group by LOOP_DATE,
SHIFT

This query is executing fine in my SQL editor. If I place the same inside a procedure it shows me this error:Error(153,3): PL/SQL: ORA-30482: DISTINCT option not allowed for this function

-Inside procedure I will bulk collect this result into a nested table.
-MST_AC_TYP is a physical table.

-baseshift_tab and demand_tab are actually nested tables for my procedure. But for trying it in the SQL editor I made it as a physical table. In procedure I tried with these tables as Physical as well as nested. In both the cases it is showing this error.

Output :-

lic_list column value with distinct (required result) -> (320,10)|(330,9)
lic_list column value without distinct->(330,9)|(320,10)|(330,9)|(330,9)|(330,9)
Basically I am trying to avoid the duplicates from lic_list column.

Is this a bug or am I doing something wrong?

Thanks in advance.

Regards
Sangeetha
Tom Kyte

Followup  

February 06, 2013 - 8:07 am UTC

plsql has an open, in progress bug - it doesn't recognize distinct inside of an analytic.

work around is to use native dynamic sql for now. that "hides" the distinct from plsql


February 10, 2013 - 4:39 am UTC

Reviewer: Sangeetha P from Qatar

Ok Thanks Tom. I just used a function to remove duplicates.Hope that is fine.

Function Code:

WITH data AS
(SELECT token,
rownum rn
FROM
( select distinct TRIM( SUBSTR (TXT, INSTR (TXT, '|', 1, level ) + 1 , INSTR (TXT, '|', 1, level+1) - INSTR (TXT, '|', 1, level) -1 ) ) as TOKEN
FROM
( SELECT '|' || ltrim(rtrim(p_input,'|'),'|') || '|' txt FROM dual
)
CONNECT BY level <= LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1
)
)
SELECT LTRIM( MAX( SYS_CONNECT_BY_PATH(TOKEN,'|') ), '|' )
|| '|' STR
INTO v_output_str
FROM data
START WITH RN = 1
connect by prior RN = RN-1 ;
RETURN rtrim( v_output_str, '|' );

Thanks and Regards
Sangeetha

In SQL Developer , result of stragg is showing box characters

July 23, 2013 - 7:45 am UTC

Reviewer: Sangeetha from Qatar

Hi Tom,

Hope you are doing fine.
I have one more question on stragg/WM_CONCAT:

When I tried to execute the following query, (Stragg/WM_CONCAT on a hierarchical query) in SQL Developer it is showing box characters. But the same query works fine in SQL PLUS.I am using SQL Developer Version 3.2.20.09

select PRIVILEGEID,stragg(menu_Path) from (
select PATH_TAB.PRIVILEGEID, PATH_TAB.menu_Path from
(SELECT MENUNAME,
PRIVILEGEID,
SYS_CONNECT_BY_PATH(MENUNAME, '/') menu_Path
from AMPPS_COMN_MENUITEMS_T
start with PARENTID =0
connect by prior MENUITEMID = PARENTID
) PATH_TAB
where PATH_TAB.PRIVILEGEID<>0) group by PRIVILEGEID;

Will there be any issues if I use this in a procedure and give as a refcursor output to .NET code?

Thanks and Regards
Sangeetha


Tom Kyte

Followup  

July 31, 2013 - 6:36 pm UTC

use DUMP() on it to see what characters you see in a) sqlplus, b) sqldev

are your charactersets the same???

if you do not use stragg (just forget about wm_concat ok, no documentation) - does the raw data show boxes or not?

August 01, 2013 - 10:15 am UTC

Reviewer: Sangeetha from Qatar

Hi Tom,

Thank you very much for the reply. My Menu_Path column data is like this (there is no box):

/Administration
/Planning
/PLANNING/AIRCRAFT SCHEDULING
/Planning/Aircraft Scheduling/Dynamic Planner etc

After doing stragg(), I am expecting it like /Administration|/Planning|/PLANNING/AIRCRAFT SCHEDULING etc.

I tried dump, it is showing numbers :
Typ=1 Len=90: 0,47,0,80,0,108,0,97,0,110,0,110,0,105,0,110,0,103,0,47,0,65,0,105,0,114,0,99,0,114,0,97,0,102,0,116,0,32,0,83,0,99,0,104,0,101,0,100,0,117,0,108,0,105,0,110,0,103,0,47,0,68,0,121,0,110,0,97,0,109,0,105,0,99,0,32,0,80,0,108,0,97,0,110,0,110,0,101,0,114.

Is there anything I am missing here.
Query used:
select PRIVILEGEID, dump((stragg(menu_Path) )) from (
select PATH_TAB.PRIVILEGEID, PATH_TAB.menu_Path from
(SELECT MENUNAME,
PRIVILEGEID,
SYS_CONNECT_BY_PATH(MENUNAME, '/') menu_Path
from AMPPS_COMN_MENUITEMS_T
start with PARENTID =0
connect by prior MENUITEMID = PARENTID
) PATH_TAB
where PATH_TAB.PRIVILEGEID<>0) group by PRIVILEGEID;

Thanks and Regards
Sangeetha




Tom Kyte

Followup  

August 02, 2013 - 7:09 pm UTC

and when you compare a dump output from an environment that "works" to one that does not - are they the same or different?

and answer the other question I asked.


and what is your database characterset

listagg question

September 27, 2013 - 7:42 am UTC

Reviewer: Sokrates

sokrates@11.2 > create table t ( i int );

Table created.

sokrates@11.2 > insert into t values( 2 );

1 row created.

sokrates@11.2 > insert into t values( 1 );

1 row created.

sokrates@11.2 > select
  2     listagg(i, ', ') within group(order by i) oi,
  3     listagg(i, ', ') within group(order by null) onull,
  4     listagg(i, ', ') within group(order by 1) o1
  5  from t;

OI
----------------------------------------------------------------
ONULL
----------------------------------------------------------------
O1
----------------------------------------------------------------
1, 2
1, 2
1, 2


sql has to return oi = '1, 2'.

Is sql free to return onull = '2, 1' as well ?
Is sql free to return o1 = '2, 1' as well ?

list-un-agg function?...

April 04, 2014 - 5:21 pm UTC

Reviewer: James from Portland, OR

Hello Tom.

Just wondering if you knew of any plans to offer a built-in analytic to unpack an aggregated column so that we'd see one row per packed value (with other duplicated attributes, of course due to the "ungrouping").

Does something like that already exist? we've poked around in the docs, but may have missed it. We're on 11.2.

Thanks and have a great weekend.
Tom Kyte

Followup  

April 16, 2014 - 5:53 pm UTC

there are no analytics that increase the size of a result set, they preserve the cardinality of the result set...

that said, this can be accomplished using table unnesting:

ops$tkyte%ORA11GR2> create table t
  2  as
  3  select deptno, listagg( ename, ',' ) within group (order by ename) as enames
  4    from scott.emp
  5   group by deptno
  6  /

Table created.

ops$tkyte%ORA11GR2> select * from t;

    DEPTNO ENAMES
---------- --------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCO
           TT,SMITH

        30 ALLEN,BLAKE,JAMES,MA
           RTIN,TURNER,WARD


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select deptno, column_value
  2    from (select deptno, ','||enames||',' enames from t),
  3          table( cast( multiset(
  4              select trim( substr (enames,
  5                        instr (enames, ',', 1, level  ) + 1,
  6                        instr (enames, ',', 1, level+1)
  7                           - instr (enames, ',', 1, level) -1 ) ) as token
  8                from dual
  9             connect by level <= length(enames)-length(replace(enames,',',''))-1 )
 10     as sys.odciVarchar2List ) )
 11  /

    DEPTNO COLUMN_VALUE
---------- ------------
        10 CLARK
        10 KING
        10 MILLER
        20 ADAMS
        20 FORD
        20 JONES
        20 SCOTT
        20 SMITH
        30 ALLEN
        30 BLAKE
        30 JAMES
        30 MARTIN
        30 TURNER
        30 WARD

14 rows selected.

can be done with model clause too

April 17, 2014 - 8:40 pm UTC

Reviewer: Igor from ME

SQL> select deptno, ename
  2  from t
  3  model
  4  partition by (deptno)
  5  dimension by (0 rn)
  6  measures (enames, enames ename, enames enames_left)
  7  rules iterate(100) until ename[iteration_number] = enames[iteration_number]
  8  (
  9  enames[iteration_number] = NVL(enames_left[CV()-1], enames[CV()]),
 10  ename[iteration_number] = NVL(substr(enames[CV()], 1, instr(enames[CV()],',')-1), enames[CV()]),
 11  enames_left[iteration_number] = substr(enames[CV()], instr(enames[CV()],',')+1)
 12  )
 13  order by 1,2
 14  ;

    DEPTNO ENAME
---------- -------
        10 CLARK
        10 KING
        10 MILLER
        20 ADAMS
        20 FORD
        20 JONES
        20 SCOTT
        20 SMITH
        30 ALLEN
        30 BLAKE
        30 JAMES
        30 MARTIN
        30 TURNER
        30 WARD

14 rows selected.

Tom Kyte

Followup  

April 17, 2014 - 10:51 pm UTC

or recursion

ops$tkyte%ORA11GR2> with data( deptno, enames, cnt, idx )
  2  as
  3  (select deptno, ','||enames||',', length(enames)-length(replace(enames,',',''))+1 cnt, 1 idx from t
  4    union all
  5   select deptno, enames, cnt, idx+1 from data where idx < cnt )
  6  select deptno, trim( substr (enames,
  7                               instr (enames, ',', 1, idx  ) + 1,
  8                               instr (enames, ',', 1, idx+1)
  9                                        - instr (enames, ',', 1, idx) -1 ) ) ename
 10    from data
 11   order by deptno, idx
 12  /

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 ADAMS
        20 FORD
        20 JONES
        20 SCOTT
        20 SMITH
        30 ALLEN
        30 BLAKE
        30 JAMES
        30 MARTIN
        30 TURNER
        30 WARD

14 rows selected.



there are many many many ways to write this, yes.

listagg

January 30, 2015 - 7:59 pm UTC

Reviewer: Sri from TX

Just as sql%rowcount tells the no of rows affected by a DML statement, is there a way to tell how many items were grouped in the delimiter concatenated output?

I am getting this error

May 08, 2015 - 4:29 pm UTC

Reviewer: Rahul Pardeshi from Detroit, MI

ORA-06502: PL/SQL: numeric or value error: character

string buffer too small
ORA-06512: at

"APPS.CCL_STRING_AGG_TYPE", line 38

its a very big query with many unions

if I break the query I don't get the error

CCL_STRING_AGG_TYPE is identical to STRING_AGG_TYPE

my type coding

May 08, 2015 - 5:05 pm UTC

Reviewer: Rahul Pardeshi from Detroit, MI

/* Formatted on 5/8/2015 12:59:43 PM (QP5 v5.252.13127.32847) */
DROP TYPE APPS.CCL_STRING_AGG_TYPE;

CREATE OR REPLACE TYPE APPS.ccl_STRING_AGG_TYPE
AS OBJECT
(
v_result VARCHAR2 (32767),
--v_result VARCHAR2 (4000),

--v_result NVARCHAR2
STATIC FUNCTION odciaggregateinitialize (ctx IN OUT ccl_STRING_AGG_TYPE)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate (self IN OUT ccl_STRING_AGG_TYPE,
p_string IN VARCHAR2)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge (self IN OUT ccl_STRING_AGG_TYPE,
ctx2 IN ccl_STRING_AGG_TYPE)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate (self IN ccl_STRING_AGG_TYPE,
returnvalue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER
);
/

DROP TYPE BODY APPS.CCL_STRING_AGG_TYPE;

CREATE OR REPLACE TYPE BODY APPS.ccl_STRING_AGG_TYPE
IS
STATIC FUNCTION odciaggregateinitialize (ctx IN OUT ccl_STRING_AGG_TYPE)
RETURN NUMBER
IS
BEGIN
ctx := ccl_STRING_AGG_TYPE (NULL); -- initialize the concatenation to NULL
RETURN odciconst.success;
END odciaggregateinitialize;

MEMBER FUNCTION odciaggregateiterate (self IN OUT ccl_STRING_AGG_TYPE,
p_string IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
self.v_result := self.v_result || ',' || p_string; -- Append a delimiter and new value
RETURN odciconst.success;
END odciaggregateiterate;

MEMBER FUNCTION odciaggregatemerge (self IN OUT ccl_STRING_AGG_TYPE,
ctx2 IN ccl_STRING_AGG_TYPE)
RETURN NUMBER
IS
BEGIN
-- If merging, simply concatenate them together
-- Since each string will either be NULL or delimiter prefixed, no need to re-delimit
self.v_result := self.v_result || ctx2.v_result;
RETURN odciconst.success;
END odciaggregatemerge;

MEMBER FUNCTION odciaggregateterminate (self IN ccl_STRING_AGG_TYPE,
returnvalue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER
IS
BEGIN
-- Since we prefix the string initially with a comma, remove the extra here before returning
returnvalue := LTRIM (self.v_result, ',');
RETURN odciconst.success;
END odciaggregateterminate;
END;
/

Listagg on 12.2

January 24, 2017 - 8:01 am UTC

Reviewer: Rajeshwaran, Jeyabal

Team,

Was going through this article from Datawarehousing blog.

https://blogs.oracle.com/datawarehousing/entry/dealing_with_very_very_long

Don't have access to 12.2 on cloud, so went into livesql.oracle.com to play with 12.2.0.1

So here is my test case, from livesql link.

https://livesql.oracle.com/apex/livesql/file/content_EG8FLP6M9EBU7IYHHXV3GGVFY.html

Could you help me to understand, why does this statement returns this errors?

select listagg(object_name,';' on overflow truncate) within group(order by object_name) final_list
from all_objects


ORA-06502: PL/SQL: numeric or value error: character string buffer too small 


looked into the doc, to understand what is ORA-06502 - but don't find that listed there.

http://docs.oracle.com/database/122/ERRMG/ORA-00000.htm#ERRMG-GUID-27437B7F-F0C3-4F1F-9C6E-6780706FB0F6

Connor McDonald

Followup  

January 24, 2017 - 9:40 am UTC

Sounds like a livesql bug. I've passed it onto the livesql team.

ORA 600 on Stragg

May 25, 2020 - 2:38 pm UTC

Reviewer: Rajeshwaran, Jeyabal

Sorry to reach AskTom for ORA-0600 error.

But we got this error from 12.2 and 18.0 - and working with support for the past 4 days, but no luck yet.

Questions:

1) So kindly let us know if you have come across any such situation like this?
2) patching 18.0 to 18.10 will solve this? ( we are on Gen1 Exadata cloud@customer and 19c is not available yet for Gen1 Exadata cloud@customer - so 19c is not possible for us)
3) any workaround this problem available? ( without parallel in-place it works, the application table got around 750M rows, so without parallel it runs for more than 3+hours and progressed only 30% of the data sets, so we need parallel ddl here )
demo@PDB1> create table t ( x number,y number, z varchar2(1000) );

Table created.

demo@PDB1> insert into t (x,y,z)
  2  select trunc(rownum/10) x, mod(rownum,10) y, rpad('*',1000,'*') z
  3  from all_objects;

68615 rows created.

demo@PDB1> commit;

Commit complete.

demo@PDB1> explain plan for
  2  create table t
  3  nologging
  4  parallel 4
  5  as
  6  select x,stragg( vcobj(y,z) ) x1
  7  from t
  8  group by x ;
explain plan for
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qerpxInitialize2], [0x7FFDC078C178], [], [], [], [], [], [], [], [], [], []

create or replace type vcobj is object( nid number, ntxt clob );
/

create or replace type vcarray is table of vcobj
/

create or replace type stragg_agg_type as object
(
l_total vcarray,

static function odciaggregateinitialize(ctx in out stragg_agg_type) return number,

member function odciaggregateiterate(self in out stragg_agg_type,
  val vcobj) return number,

member function odciaggregatemerge(self in out stragg_agg_type,
  ctx2 stragg_agg_type) return number,

member function odciaggregateterminate(self in stragg_agg_type,
   returnvalue out  clob,
   flags in number) return number
);
/

create or replace type body stragg_agg_type is
       static function odciaggregateinitialize(ctx in out stragg_agg_type) 
        return number  is
       begin
                ctx := stragg_agg_type( vcarray() );
                return ODCIConst.Success;
       end;
       
       member function odciaggregateiterate(self in out stragg_agg_type,
              val vcobj) return number is  
        begin
                     if val.nid is not null then
                           self.l_total.extend;
                           self.l_total(self.l_total.count) := val;
                     end if;
                     return ODCIConst.Success;
       end;
       
       member function odciaggregatemerge(self in out stragg_agg_type,
       ctx2 stragg_agg_type) 
        return number  as
       begin
              for i in 1..ctx2.l_total.count
              loop
                     l_total.extend;
                     l_total( l_total.count ) := ctx2.l_total(i);
              end loop;
              return odciconst.success;
       end;
       
       member function odciaggregateterminate(self in stragg_agg_type,
          returnvalue out  clob,
          flags in number) 
        return number as 
        begin
              for x in (select nid,ntxt from table(self.l_total) order by 1)
              loop
                     returnvalue := returnvalue ||x.ntxt;
              end loop;
                     returnvalue := trim(';' from returnvalue);
                     return odciconst.success;
       end;
end;
/

create or replace function stragg(x vcobj)
return clob
parallel_enable 
aggregate using stragg_agg_type;
/

Connor McDonald

Followup  

May 26, 2020 - 5:10 am UTC

Is it good enough for each element to be capped at vc2(4000) ? and just have a clob as result ?


SQL>
SQL> create table t ( x number,y number, z varchar2(1000) );

Table created.

SQL>
SQL> insert into t (x,y,z)
  2     select trunc(rownum/10) x, mod(rownum,10) y, rpad('*',1000,'*') z
  3     from dba_objects;

72698 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> drop type stragg_agg_type;

Type dropped.

SQL>
SQL> drop type vcarray;

Type dropped.

SQL>
SQL> create or replace type vcobj is object( nid number, ntxt varchar2(4000) );  <<=====
  2  /

Type created.

SQL>
SQL> create or replace type vcarray is table of vcobj
  2  /

Type created.

SQL>
SQL> create or replace type stragg_agg_type as object
  2  (
  3  l_total vcarray,
  4
  5  static function odciaggregateinitialize(ctx in out stragg_agg_type) return number,
  6
  7  member function odciaggregateiterate(self in out stragg_agg_type,
  8    val vcobj) return number,
  9
 10  member function odciaggregatemerge(self in out stragg_agg_type,
 11    ctx2 stragg_agg_type) return number,
 12
 13  member function odciaggregateterminate(self in stragg_agg_type,
 14     returnvalue out  clob,
 15     flags in number) return number
 16  );
 17  /

Type created.

SQL>
SQL> create or replace type body stragg_agg_type is
  2         static function odciaggregateinitialize(ctx in out stragg_agg_type)
  3          return number  is
  4         begin
  5                  ctx := stragg_agg_type( vcarray() );
  6                  return ODCIConst.Success;
  7         end;
  8
  9         member function odciaggregateiterate(self in out stragg_agg_type,
 10                val vcobj) return number is
 11          begin
 12                       if val.nid is not null then
 13                             self.l_total.extend;
 14                             self.l_total(self.l_total.count) := val;
 15                       end if;
 16                       return ODCIConst.Success;
 17         end;
 18
 19         member function odciaggregatemerge(self in out stragg_agg_type,
 20         ctx2 stragg_agg_type)
 21          return number  as
 22         begin
 23                for i in 1..ctx2.l_total.count
 24                loop
 25                       l_total.extend;
 26                       l_total( l_total.count ) := ctx2.l_total(i);
 27                end loop;
 28                return odciconst.success;
 29         end;
 30
 31         member function odciaggregateterminate(self in stragg_agg_type,
 32            returnvalue out  clob,
 33            flags in number)
 34          return number as
 35          begin
 36            --dbms_lob.createtemporary(returnvalue,true);
 37                for x in (select nid,ntxt from table(self.l_total) order by 1)
 38                loop
 39                       returnvalue := returnvalue ||x.ntxt;
 40                       --dbms_lob.append(returnvalue,x.ntxt);
 41                end loop;
 42                       returnvalue := trim(';' from returnvalue);
 43                       return odciconst.success;
 44         end;
 45  end;
 46  /

Type body created.

SQL>
SQL> create or replace function stragg(x vcobj)
  2  return clob
  3  parallel_enable
  4  aggregate using stragg_agg_type;
  5  /

Function created.

SQL>
SQL>
SQL> explain plan for
  2      create table t1
  3      nologging
  4      parallel 4
  5      as
  6      select x,stragg( vcobj(y,z) ) x1
  7      from t
  8      group by x ;

Explained.

SQL>
SQL> create table t1
  2      nologging
  3      parallel 4
  4      as
  5      select x,stragg( vcobj(y,z) ) x1
  6      from t
  7      group by x ;

Table created.

SQL>
SQL>


ORA 600 on Stragg

May 26, 2020 - 5:44 am UTC

Reviewer: Rajeshwaran, Jeyabal

Thanks, thats works for us.

But how did you identified that this particular ORA-600 was from Clob attribute of the object instance?

what mechanism/tool/Trace event was used to identity this?
Connor McDonald

Followup  

May 27, 2020 - 1:03 am UTC

process of elimination....The biggest of hitting an oracle bug is when you mix lots of different technologies, because its so hard to test for those boundary conditions. So you had:

- object types
- clobs
- user defined aggregate
- table functions
- concatentation operator on clobs
- parallel plsql
- parallel ddl

Removing parallel also works but I assume that would be a problem in terms of your performance needs.

So then I tried piecemeal removing/replacing things....eg

- dbms_lob for append
- remove table function (we lost ordering but just to see if that was the issue)
- etc etc


More to Explore

DBMS_STATS

More on PL/SQL routine DBMS_STATS here