Oracle 11g – Aggregate NVARCHAR2 Column and Obtain Result in NVARCHAR2

oracleoracle-11g

I need to create a view with an aggregate column of nvarchar2(4000) in Oracle 11g.

What I have:

Table1 with every column of type nvarchar2(4000):

Col_Name   txt_value
X                   a
X                   b
X                   c
Y                   a
Y                   c

The result should be a view like this with both columns of type nvarchar2(4000):

Col_Name   txt_value
X                   a;b;c
Y                   a;c

I have tried with:

CREATE VIEW dict (col_Name, txt_value) AS 
SELECT col_Name, LISTAGG (txt_value,';') WITHIN GROUP (ORDER BY txt_value)
FROM table1
GROUP BY col_Name

The only problem is that LISTAGG() is converting the result in a column of type varchar2(4000) bit I need nvarchar2.

Best Answer

As the documentation describes:

The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2.

So unless you pass the column as RAW, the result will be VARCHAR2, and that is not good enough, because that means data loss during conversion.

LISTAGG works with VARCHAR2 or RAW, so to simply put it, you need to convert everything to RAW, then convert the result back to NVARCHAR2.

Below is an example:

SQL> select parameter, value from nls_database_parameters where parameter like '%CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               EE8ISO8859P2
NLS_NCHAR_CHARACTERSET         AL16UTF16

My default characterset does not support the € sign.

SQL> create table t1 (id number, c1 varchar2(20), c2 nvarchar2(20));

Table created.

SQL> insert into t1 values (1, 'A', 'A');

1 row created.

SQL> insert into t1 values (2,  NCHR(8364),  NCHR(8364));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID C1 C2
---------- -- --
         1 A  A
         2 ?  €

As you can see, i get a ? instead of it using VARCHAR2.

If I simply try to use LISTAGG on the NVARCHAR2 column, data will be lost during conversion:

select
  listagg(c2, ',') within group (order by id) as list1
from t1;

LIST1
----------
 A, Ź

If I try to cast the result, still not good enough, as data was lost during the conversion to VARCHAR2:

select
  cast(listagg(c2, ',') within group (order by id) as nvarchar2(20)) as list2
from t1;

LIST2
----------
 A, Ź

The remaining option is, to convert to RAW:

select
  utl_raw.cast_to_nvarchar2(listagg(utl_raw.cast_to_raw(c2), utl_raw.cast_to_raw(',')) within group (order by id)) as list3
from t1;

LIST3
----------
AⰠ

Converting a VARCHAR2 delimiter will mess up the data, I need to specify the delimiter also in NVARCHAR2, so instead of ',', I have to use N',':

select
  utl_raw.cast_to_nvarchar2(listagg(utl_raw.cast_to_raw(c2), utl_raw.cast_to_raw(N',')) within group (order by id)) as list4
from t1;

LIST4
----------
A,€
Related Question