Oracle VIEW - More than 4000 bytes in Column

Question

Iam using this part of an SQL Satement to fetch Information from an N:N Relationship.

The Goal is to have an view with an column like: "STRING1,STRING2,STRING3". This works fine but i have sometimes more than 4000 Bytes in the Column.

 (SELECT
     (RTRIM(XMLAGG(xmlelement(X, TABLE1.STRING||',') order by TABLE1.STRING).extract('//text()'),','))
    FROM
        STRING_HAS_TABLE1
    JOIN TABLE1 STRING_HAS_TABLE1.STRING_ID = TABLE1.ID
    WHERE
      STRING_HAS_TABLE1.USER_ID = X.ID) AS STRINGS,

Oracle throws "Buffer overflow". I think the problem is the columntype inside the view: VARCHAR2(4000).

ERROR: ORA 19011 - Character string buffer to small

Any ideas to handle this without changing the whole application logic?

what exact error code do you get? – Kirill Leontev May 14 '13 at 10:05 — Kirill Leontev, May 14 '13 at 10:05
ERROR: ORA 19011 - Character string buffer to small – opHASnoNAME May 14 '13 at 11:22 — opHASnoNAME, May 14 '13 at 11:22

score 2 · Accepted Answer · answered May 14 '13 at 11:49

This is a problem converting implicitly between data types. You can get around it by treating it as a CLOB before trimming, by adding a getClobVal() call:

SELECT RTRIM(XMLAGG(xmlelement(X, TABLE1.STRING||',')
    order by TABLE1.STRING).extract('//text()').getClobVal(),',')
FROM ...

The RTRIM documentation shows the types it accepts, and since XMLTYPE isn't listed that means it has to be doing an implicit conversion, apparently to VARCHAR2. (The same applies to the other TRIM functions).

But it does accept CLOB, so doing an explicit conversion to CLOB means RTRIM doesn't do an implicit conversion to a type that's too small.

Rather belatedly found what I think is probably where I learnt this trick; [this old question](http://stackoverflow.com/q/13299843/266304). — Alex Poole, May 14 '13 at 11:58

Oracle VIEW - More than 4000 bytes in Column

1 Answers1

Linked