SQL Developer, COLLECT and CAST

This one is a quick hint for SQL Developer and the COLLECT function.

In case you're not familiar with it, COLLECT is an aggregate function that was introduced in 10gR2 and simply gathers all the items up into a VARRAY/TABLE style collection type. If you use it in PL/SQL programs, you can handle the collection programmatically.

But sometimes it can be handy to see the elements in a quick ad-hoc query.

In SQL*Plus, you'll get an output that is ugly, but usable:


SQL> l
  1  select country_id, collect(city)
  2  from hr.locations
  3* group by country_id
SQL> /

CO
--
COLLECT(CITY)
--------------------------------------------------------------------------------
AU
SYSTPv4AgebXuT9qJVHqM2Gc/MQ==('Sydney')

BR
SYSTPv4AgebXuT9qJVHqM2Gc/MQ==('Sao Paulo')

CA
SYSTPv4AgebXuT9qJVHqM2Gc/MQ==('Toronto', 'Whitehorse')


In SQL Developer, the same query will return you an error:

ORA-00932: inconsistent datatypes: expected NUMBER got -
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

As described in the documentation for COLLECT, you should apply a CAST. If you don't already have a convenient TYPE that is a TABLE OF VARCHAR2, and can't create one, there's a built-in one you can use, SYS.DBMS_DEBUG_VC2COLL. Unusually for a SYS object, you do need to specify the schema name as there's no public synonym.


select country_id, cast(collect(city) as sys.dbms_debug_vc2coll) cities
from hr.locations
group by country_id

I'm mostly using 11gR2 now, which has the more powerful LISTAGG, but if you still haven't upgraded all your databases, ad-hoc COLLECTs can be useful. And in programs, a collection is easier to parse than a string.