Ordering by list of strings in Oracle SQL without LISTAGG

Question

I'm working with two entities: Item and Attribute, which look something like the following:

Item
----
itemId

Attribute
---------
attributeId
name

An Item has Attributes, as specified in an association table:

ItemAttribute
--------------
itemId
attributeId

When this data gets to the client, it will be displayed with a row per Item, and each row will have a list of Attributes by name. For example:

Item  Attributes
----  ----------
1     A, B, C
2     A, C
3     A, B

The user will have the option to sort on the Attributes column, so we need the ability to sort the data as follows:

Item  Attributes
----  ----------
3     A, B
1     A, B, C
2     A, C

At present, we're getting one row of data per ItemAttribute row. Basically:

  SELECT Item.itemId,
         Attribute.name
    FROM Item
    JOIN ItemAttribute
      ON ItemAttribute.itemId = Item.itemId
    JOIN Attribute
      ON Attribute.attributeId = ItemAttribute.attributeId
ORDER BY Item.itemId;

Which produces a result like:

itemId  name
------  ----
1       A
1       B
1       C
2       A
2       C
3       A
3       B

The actual ORDER BY clause is based on user input. It's usually a single column, so the ordering is simple, and the app-side loop that processes the result set combines the Attribute names into a comma-separated list for presentation on the client. But when the user asks to sort on that list, it'd be nice to have Oracle sort the results so that -- using the example above -- we'd get:

itemId  name
------  ----
3       A
3       B
1       A
1       B
1       C
2       A
2       C

Oracle's LISTAGG function can be used to generate the attribute lists prior to sorting; however Attribute.name can be a very long string, and it is possible that the combined list is greater than 4000 characters, which would cause the query to fail.

Is there a clean, efficient way to sort the data in this manner using Oracle SQL (11gR2)?

Answer 1

There are really two questions here:

1) How to aggregate more than 4000 characters of data

Is it even sensible to aggregate so much data and display it in a single column?

Anyway you will need some sort of large structure to display more than 4000 characters, like a CLOB for example. You could write your own aggregation method following the general guideline described in one of Tom Kyte's thread (obviously you would need to modify it so that the final output is a CLOB).

I will demonstrate a simpler method with a nested table and a custom function (works on 10g):

SQL> CREATE TYPE tab_varchar2 AS TABLE OF VARCHAR2(4000);
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION concat_array(p tab_varchar2) RETURN CLOB IS
  2     l_result CLOB;
  3  BEGIN
  4     FOR cc IN (SELECT column_value FROM TABLE(p) ORDER BY column_value) LOOP
  5        l_result := l_result ||' '|| cc.column_value;
  6     END LOOP;
  7     return l_result;
  8  END;
  9  /

Function created.

SQL> SELECT item,
  2         concat_array(CAST (collect(attribute) AS tab_varchar2)) attributes
  3    FROM data
  4   GROUP BY item;

ITEM ATTRIBUTES
1    a b c
2    a c
3    a b

2) How to sort large data

Unfotunately you can't sort by an arbitrarily large column in Oracle: there are known limitations relative to the type and the length of the sort key.

• Trying to sort with a clob will result in an ORA-00932: inconsistent datatypes: expected - got CLOB.
• Trying to sort with a key larger than the database block size (if you decide to split your large data into many VARCHAR2 for example) will yield an ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I suggest you sort by the first 4000 bytes of the attributes column:

SQL> SELECT * FROM (
  2     SELECT item,
  3            concat_array(CAST (collect(attribute) AS tab_varchar2)) attributes
  4       FROM data
  5      GROUP BY item
  6  ) order by dbms_lob.substr(attributes, 4000, 1);

ITEM ATTRIBUTES
3    a b
1    a b c
2    a c