Frederick Tang Weblog

This is strictly on an issue on a Oracle9i database. It has been fixed in Oracle10g.

Just would like to write a brief note about a test case we found with using sys_connect_by_path to concatenate values on a column from multiple rows into one string, on an Oracle9i database.

This technique has been discussed on a few websites:
http://www.williamrobertson.net/documents/one-row.html
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
http://www.oracle.com/technology/oramag/code/tips2006/101606.html

As we might all know, VARCHAR2 in Oracle 9i has a limit of 4000 bytes (note, not characters). This limits applies to string concatenation, and will (or should) result in ORA-01489: result of string concatenation is too long if the result of concatenation yields a string greater than 4000 bytes in length. Eddie has demonstrated some examples here: http://awads.net/wp/2005/12/06/more-on-generating-strings/

I discovered a difference in behaviour when concatenating strings using sys_connect_by_path on Oracle9i, between a Multibyte character set database and a Single-byte character set database, where I would get an ORA-01489 error at just over the 2000 bytes mark on a Multibyte database. For example, UTF8 is Multibyte and WE8ISO8859P1 is a Single-byte character set. (http://download.oracle.com/docs/cd/B10501_01/server.920/a96529/appa.htm#956722)

There are a couple of ways to find out which character set the database is built on (requires dba privilege or select_catalog_role granted):

select value$ from props$ where name='NLS_CHARACTERSET';
select * from database_properties where property_name='NLS_CHARACTERSET';

We construct a simple test case, a table with an ID column, and a VARCHAR2 column. I am going to insert a fixed string of 10 characters into the VARCHAR2 column, then apply the string concatenation technique using sys_connect_by_path. I am not using any special characters, so each character should just be 1 byte. It is possible to get size of a varchar2 string using vsize().

create table test_concat_sys
(
   id     number,
   str    varchar2(4000)
);

insert into test_concat_sys
SELECT 1 as id, '1111111111' RNDMSTR
FROM all_objects
WHERE rownum <= 200;

commit;

On a UTF8 character set database, this will work, and the last row will be 2002 bytes (182×10+182). That’s 182 rows of 10 bytes, plus 1 byte for each ‘/’ in the concatenation delimiter.

SELECT VSIZE(SYS_CONNECT_BY_PATH(str, '/')) as BYTES
FROM
(
   select A.*, row_number() over (partition by id order by id) row#
   from test_concat_sys A
   where rownum <= 182
)
START WITH row#=1
CONNECT BY PRIOR id=id and prior row# = row# -1;

This will result in an ORA-01489 error:

SELECT VSIZE(SYS_CONNECT_BY_PATH(str, '/')) as BYTES
FROM
(
   select A.*, row_number() over (partition by id order by id) row#
   from test_concat_sys A
   where rownum <= 183
)
START WITH row#=1
CONNECT BY PRIOR id=id and prior row# = row# -1;

The behavior is working as expected on a Oracle 9i Single-byte database or an Oracle10g (or later) release, and we can set rownum upto 363 rows (363×10+363 = 3993), one more row and it will return ORA-01489 as expected since we will reach the limit (364×10+364 = 4004).

I have logged a SR for this, and the reply from Oracle was that they “officially accepted this is the right candidate for bug.”, but “9i is in sustaining support mode and development will not provide any fix for this. Also this issue is fixed in 10g so they would not accept it.”

All the more reason to upgrade to 10g if not already done so. This bug is rather odd to hit because we wouldn’t normally expect to concatenate a string upto 2000 characters and beyond. But in our case, the business obviously did.

There are other ways to concatenate strings from multiple rows, they are also covered in the websites linked at the beginning of this article.