Is there a length limit (like the infamous 4000 byte) for the resulting concatenation with
'a' || 'b' || ...
and
concat(concat(concat ('a', 'b'), 'c'), ...)
thanks
Asked
Active
Viewed 3,447 times
0
-
1Possible duplicate of [LISTAGG function: "result of string concatenation is too long"](https://stackoverflow.com/questions/13795220/listagg-function-result-of-string-concatenation-is-too-long) – Patrick Bacon Feb 14 '18 at 20:16
-
People have answered this question though in the context of the listagg function performing the concatenation. – Patrick Bacon Feb 14 '18 at 20:18
-
@PatrickBacon - the answer "in the context of LISTAGG" is not applicable to concatenation in general. Oracle can concatenate CLOBs, but LISTAGG cannot handle CLOBs. – Feb 14 '18 at 20:24
-
@PatrickBacon i am aware of the listagg limit. This question is not related to that. – Prefijo Sustantivo Feb 15 '18 at 15:00
-
I read the question to narrowly (varchar2 scenario only). – Patrick Bacon Feb 15 '18 at 15:16
-
@PatrickBacon yeah, that happens. :) – Prefijo Sustantivo Feb 15 '18 at 16:12
1 Answers
2
Google is your friend - use it. The art is always in choosing the proper phrase to search on; maybe "Oracle concatenation operator" would work. You should see what the Oracle documentation says about your question. In short:
If you concatenate many single-character strings like you are, then yes, you will run into the 4000 byte limit for VARCHAR2.
If you want to exceed it, you must enclose at least one of the single-character strings within TO_CLOB() (or cast it to CLOB in some other way). Then there will be no limit to the number of bytes (NOTE: as you said, it is not CHARACTERS but BYTES - some people forget that) but, of course, the result will be a CLOB. There are many things you can do with VARCHAR2 that you can't do with CLOB (you can't join on CLOB values, you can't group or order by a CLOB expression, etc.)