COLLECT and DISTINCT

In regular SQL, you can use the DISTINCT qualifier with the COLLECT function.
Here it removes the duplicates for PETER and DAVID.

SQL> select cast(collect(first_name) as  sys.dbms_debug_vc2coll)
  2  from HR.employees
  3  where job_id = 'SA_REP'
  4  and manager_id in (147,145);

CAST(COLLECT(FIRST_NAME)ASSYS.DBMS_DEBUG_VC2COLL)
--------------------------------------------------------------------------------
DBMS_DEBUG_VC2COLL('Peter', 'David', 'Peter', 'Christopher', 'Nanette', 'Oliver'
, 'Clara', 'Danielle', 'Mattea', 'David', 'Sundar', 'Amit')


SQL>
SQL> select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)
  2  from HR.employees
  3  where job_id = 'SA_REP'
  4  and manager_id in (147,145);

CAST(COLLECT(DISTINCTFIRST_NAME)ASSYS.DBMS_DEBUG_VC2COLL)
--------------------------------------------------------------------------------
DBMS_DEBUG_VC2COLL('Amit', 'Christopher', 'Clara', 'Danielle', 'David', 'Mattea'
, 'Nanette', 'Oliver', 'Peter', 'Sundar')

However, if you get into PL/SQL, things are different. Without the DISTINCT, it works fine.


SQL> declare
  2    v_tab sys.dbms_debug_vc2coll;
  3  begin
  4    select cast(collect(first_name) as  sys.dbms_debug_vc2coll)
  5    into v_tab
  6    from HR.employees
  7    where job_id = 'SA_REP'
  8    and manager_id in (147,145);
  9    dbms_output.put_line(v_tab.count);
 10  end;
 11  /
12

With the DISTINCT an error is reported.


SQL> declare
  2    v_tab sys.dbms_debug_vc2coll;
  3  begin
  4    select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)
  5    into v_tab
  6    from HR.employees
  7    where job_id = 'SA_REP'
  8    and manager_id in (147,145);
  9  end;
 10  /
  select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)
              *
ERROR at line 4:
ORA-06550: line 4, column 15:
PL/SQL: ORA-30482: DISTINCT option not allowed for this function
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

That's the case in 11.2.0.2. I'm not sure if there's a patch for it, or whether it will be fixed in 12c. [And if they want to get that out in the twelfth month of 2012, they don't have long left.]

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.

Toastmasters - Testing public speaking away from 'production'

Every Wednesday lunch (pretty much) there's a Toastmasters meeting at the company where I'm working. After having been there a year, I finally got around to turning up a couple of weeks ago. Hey, they also have live music on Wednesday lunch. It's a busy place.

Yury, from Pythian and the Sydney Oracle Meetup, has mentioned Toastmasters. Given my audience is mostly developers, let me sum it up my way: Toastmasters is a test environment for public speaking.

As with a test environment, you're expected to have a few "does not meet requirements" incidents. That's why it is there. You can experience those, and learn from them, and repeat with improvements. And none of that damages "production". It is a dedicated, safe environment for rehearsals.

I can't speak for all groups, but in the couple of meetings I have attended, as a guest, I've seen positive and constructive criticism. As well as learning to speak, others are evaluating...which means listening. The talks are only a few minutes, so I don't bore people with techy details. There's no sign of Powerpoint either, so it isn't quite a conference rehearsal. 

Again, like testing, this means you are using 'test' data, rather than volume testing. You can focus on the fundamentals of communicating rather than on content. 

I plan to sign up fully in the near future, so there might be more coming in this blog, or it might come through in my Google Plus posts (apparently I am in the circles of over a thousand people, but I'm none the wiser on how many actually read anything I post).