List aggregation (LISTAGG) in 11g

As you may already found that LISTAGG function is new in Oracle DB 11g. This function performs a string aggregation (concatenation) natively without implementing user-defined objects (most popular STRAGG) and other function that loops through data.

Here is the format of the function:

LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

Here is the simple example :

SQL> select first_name from employees where department_id =20;

FIRST_NAME
——————–
Michael
Pat

SQL> select listagg(first_name,’;’) within group (order by null) as fnames
2  from employees
3  where department_id = 20;

FNAMES
——————————————————————————–
Michael;Pat

Lets say there are multiple employees whose first name is same. But I would like to concatenate those names uniquely. That means I don’t want to list duplicate names, just concatenate distinct first names.

Here is example of department which contains employees of same first name.

SQL> r
1  select  listagg(first_name,’;’) within group (order by first_name) as fnames
2  from employees
3* where department_id = 80
FNAMES
——————————————————————————–
Alberto;Allan;Alyssa;Amit;Charles;Christopher;Clara;Danielle;David;David;Eleni;E
lizabeth;Ellen;Gerald;Harrison;Jack;Janette;John;Jonathon;Karen;Lindsey;Lisa;Lou
ise;Mattea;Nanette;Oliver;Patrick;Peter;Peter;Sarath;Sundar;Sundita;Tayler;Willi
am

As you see in result there are two David s and two Peter s. How can I select only distinct data.

LISTAGG function doesn’t let you use the distinct key. See below what happens if I try to do this.

SQL> r
1  select  listagg(DISTINCT first_name,’;’) within group (order by first_name) as fnames
2  from employees
3* where department_id = 80
select  listagg(DISTINCT first_name,’;’) within group (order by first_name) as fnames
*
ERROR at line 1:
ORA-30482: DISTINCT option not allowed for this function

But this can be resolved using sub query as below.

SQL> r
1  select listagg(first_name,’;’) within group (order by first_name) as fnames
2  from
3  (
4  select distinct department_id, first_name
5  from employees
6* where department_id = 80)
FNAMES
——————————————————————————–
Alberto;Allan;Alyssa;Amit;Charles;Christopher;Clara;Danielle;David;Eleni;Elizabe
th;Ellen;Gerald;Harrison;Jack;Janette;John;Jonathon;Karen;Lindsey;Lisa;Louise;Ma
ttea;Nanette;Oliver;Patrick;Peter;Sarath;Sundar;Sundita;Tayler;William

Now Results display distinct names as highlighted.

Read Full Post »

Export issue on 11g, can’t export tables with zero rows.

Traditional Oracle export/import had bug exporting tables with not allocated segments in 11g 11.2.0.1

Exporting a schema with tables those have zero rows gets error EXP-00011.

This is because 11g introduced “segmented_created” column in DBA_TABLES. if you see value “NO” for this, that means no segments were allocated. In this case, Export can’t export table in version 11.2.0.1. This has been fixed in 11.2.0.2.

For this version of oracle, either use Datapump or allocate an extent using following syntax.

select owner, table_name,segment_created from dba_tables where sgement_created = ‘NO’

alter table ; allocate extent.

Note that allocating extent takes up little more space in database. It allocates according to tablespace’s definition.

Read Full Post »