When using
LISTAGG recently in view definition I had the problem that the column content
based on LISTAGG was not shown in ArcMap / ArcCatalog.
The LISTAGG
command usage was like that:
…
cast(listagg(nachname
|| '; ' || vorname || ' / ') within group(order by nachname) as nvarchar2(500))
as eigentuemer_name,
…
The columns
(nachname, vorname) I concatenated were of data type “nvarchar2”. My initial
view definition did not specify the data type for the column – I added it later
but it did not solve my problem.
In SQL
Developer the content of the column was shown right from the start. To check
how other applications behave I connected to the view using Excel and tried to
import the data into a sheet. When I did that I got an ORA-01401 inserted value too large
for column message. After bit of
research I found the following discussion on SO.
It was
mentioned that there might be an issue with the length of the right hand side of the concatenation operator when usung NVARCHAR. I changed the view
definition to VARCHAR2 and also increased the size. Afterwards I could import
the data into Excel – but in ArcMap the column was still empty.
The second hint on the page –
applying to_char conversion - finally solved the issue:
...
cast(listagg(to_char(nachname)
|| '; ' || to_char(vorname) , ' / ') within group(order by nachname) as
varchar2(4000)) as eigentuemer_name,
It’s a bit
strange – other columns in the view based on NVARCHAR2 are shown in ArcMap.
Only in connection with LISTAGG the problem occurs.
ArcMap 10.5.1,
Oracle 11.2.0.4.0
