Improved Listagg on Overflooooow: 12 Things Developers Will Love About Oracle Database 12c Release 2 Part 4

We’ve seen how longer object names can cause problems with variable declarations. But this can also bring more subtle issues. For example, the following query returns a comma-separated list of indexes for each table in your schema:

select table_name, 
       listagg( index_name, ',' ) 
         within group ( order by index_name ) inds 
from   user_indexes 
group  by table_name;

This is all very well and good. But there’s a potential problem with it. Listagg() returns a varchar2. This is limited to 4,000 bytes (32,767 if you’re using extended data types).

So in 12.1 and 11.2, you needed 130 or more indexes on a table before you start running into issues. And if you have that many indexes on one table, you’ve got bigger problems than hitting this limit!

But this changes in 12.2. With longer names, you could hit this limit at just over 30 indexes on a table. While still a large number, this is plausible. Particularly in reporting databases and data warehouses. And you can be sure that someone, somewhere will start creating “self-documenting” indexes like:

create index 
  reducing_the_monthly_invoice_run_     
  from_four_hours_to_three_minutes_ 
  PROJ12345_make_everything_faster_ 
  csaxon_thanks_everyone_yeah_baby on ...

Create too many of these and your listagg query will throw frustrating ORA-01489 errors.

To get around this is tricky. So in 12.2 we’ve added an overflow clause. To use it, place “on overflow truncate” after the separator:

select table_name, 
       listagg( index_name, ',' on overflow truncate ) 
         within group ( order by index_name ) inds 
from   user_indexes 
group  by table_name;

With this in place, instead of an exception your output will now look something like:

...lots_and_lots_and_lots,of_indexes,...(42)

The “…” at the end indicates that the output is larger than Oracle can return. The number in brackets how many characters Oracle trimmed from the results. So not only can you see there is more data, you get an indication of how much there is.

The full syntax of this is:

listagg ( 
  things, ',' [ on overflow (truncate|error) ] 
  [ text ] [ (with|without) count ] 
) within group (order by cols)

Now you can explicitly say whether you want error or truncation semantics. There’s a good chance you’ve already written code to handle the ORA-1489 errors. So to keep the behaviour of your code the same, the default remains error.

The text and count clauses control what appears at the end of the string. If you want to replace “…” with “more”, “extra” or a “click for more” hyperlink, just provide your new string!

select table_name, 
       listagg( index_name, ',' on overflow truncate 
         '<a href="http://www.showfulldetails.com">click here</a>' 
       ) within group ( order by index_name ) inds 
from   user_indexes 
group  by table_name;

You can also remove the number of trimmed characters by specifying “without count”.

Full article originally published at blogs.oracle.com on November 10, 2016.