Wednesday, June 11, 2008

Stragg

Tom Kyte popularized the STRAGG function, a sometimes critical piece missing from Oracle's arsenal of cool commands, has been bandied about quite a bit:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:21961626004

We were recently tasked with using a STRAGG-like function but with dynamic delimiters. Tom Kyte's standard STRAGG does not handle that. Enter Adrian Billington, who took it one step further (look for "10g goes one further"). I really liked his example, and it caused me to research the COLLECTION functionality a little deeper. A little - I do not claim to have mastered it by any means.

Unfortunately for us, we seem to be hitting bug 4381035 (not exactly sure about that, but the best hit I can find). This is under 10.2.0.2.

BANDEV_SQL > CREATE OR REPLACE TYPE oracle.stragg_type AS TABLE OF VARCHAR2(4000);
/
2
Type created.

BANDEV_SQL > CREATE OR REPLACE FUNCTION oracle.stragg (
nt_in IN stragg_type,
delimiter_in IN VARCHAR2 DEFAULT ','
) RETURN varchar2 IS

v_idx PLS_INTEGER;
v_str varchar2(32000);
v_dlm VARCHAR2(10);

BEGIN

v_idx := nt_in.FIRST;
WHILE v_idx IS NOT NULL LOOP
v_str := v_str || v_dlm || nt_in(v_idx);
v_dlm := delimiter_in;
v_idx := nt_in.NEXT(v_idx);
END LOOP;

RETURN v_str;

END stragg;
/
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
Function created.

BANDEV_SQL > drop table test;

Table dropped.

BANDEV_SQL > create table test (a char(2), b char(1));

Table created.

BANDEV_SQL > insert into test values ('aa','1');

1 row created.

BANDEV_SQL > insert into test values ('aa','2');

1 row created.

BANDEV_SQL > insert into test values ('aa','3');

1 row created.

BANDEV_SQL > select a, oracle.stragg(CAST(COLLECT(b) as ORACLE.STRAGG_TYPE)) b from test group by a;
select a, oracle.stragg(CAST(COLLECT(b) as ORACLE.STRAGG_TYPE)) b from test group by a
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -




This was a bummer. After a bunch of reading and experimenting, I finally hit upon something that seems to be working:

BANDEV_SQL > select a, oracle.stragg(cast(multiset(select b from test) as oracle.STRAGG_TYPE),'|') b from test group by a;

A
--
B
--------------------------------------------------------------------------------------------------------------------------------------------
aa
1|2|3



I am not exactly sure why MULTISET is working better than COLLECT in this case; it seems to me that both should be producing nested tables.

5 comments:

Chris said...

Hi!
You can also create an aggregate function that accumulates strings in a collection.
The code is rather long for this tiny comment widget, so I wrote a post on my blog.

SnippetyJoe said...

Hmm, perhaps the error is due to a bug with your particular version of Oracle? I've used an almost identical approach in the past with Oracle XE and it worked fine (see SQL Snippets: Rows to String - Object Method).

--
Joe Fuda
http://www.sqlsnippets.com/

Charles Schultz said...

I had Oracle Support file a bug: 7194959. It is a very repeatable test case using UncleChris's ODCI examples. The problem is that ODCI is sorting based on an internal algorithm, not on the order in which data is passed in. In effect, one has absolutely no control on how the data is sorted.

pushpraj said...

data type on your table and type (varray) are different) they have to be same). That’s why collection is not working

Charles Schultz said...

Can you be more specific? First off, this is not my function, I am merely using it. Second, I do not use varray.