ELECT query gives ORA-01489 after 11g upgrade

[84]Hi Mirza, This does not match with my issue : I...... Cedric
[85]Dear, Yes we are aware that your not not upgrad...... Mirza
[86]Sure... I should maybe ask my question another way...... Cedric

Subject: SELECT query gives ORA-01489 after 11g upgrade
Os info: Linux CentOs 6
Oracle info: 11.2.0.4
Error info: ORA-01489
Message: Hi everybody,
We have recently migrated our datawarehouse database from 10.2.0.5 to
11.2.0.4.
One the side effects we had is the following.
The SQL statement below was working fine and fast on 10g but fires an
ORA-01489 (RESULT OF STRING CONCATENATION IS TOO LONG) on 11g.
SELECT Table__14.LIBEBR
FROM ( SELECT NOLQBR,
MAX (EXERBR) KEEP (DENSE_RANK LAST ORDER BY EXERBR, NOLQBR, NU_
LIG) AS EXERBR,
MAX (SEQTBR) KEEP (DENSE_RANK LAST ORDER BY EXERBR, NOLQBR, NU_
LIG) AS SEQTBR,
MAX (LIBEBR) KEEP (DENSE_RANK LAST ORDER BY EXERBR, NOLQBR, NU_
LIG) AS LIBEBR
FROM ( SELECT EXERBR,
NOLQBR,
SEQTBR,
NOLGBR,
NU_LIG,
--SUBSTR (SYS_CONNECT_BY_PATH (TRIM (LIBEBR), '|'),
2) AS LIBEBR
to_clob (substr(SYS_CONNECT_BY_PATH (TRIM (LIBEBR),
'|'),2)) AS LIBEBR
FROM (SELECT EXERBR,
NOLQBR,
SEQTBR,
NOLGBR,
LIBEBR,
ROW_NUMBER ()
OVER (PARTITION BY EXERBR, NOLQBR
ORDER BY EXERBR, NOLQBR)
AS nu_lig
FROM brd) H
START WITH NU_LIG = 1
CONNECT BY PRIOR H.EXERBR = H.EXERBR
AND PRIOR H.NOLQBR = H.NOLQBR
--AND prior H.SEQTBR = H.SEQTBR
AND PRIOR H.NU_LIG + 1 = H.NU_LIG)
GROUP BY NOLQBR) Table__14

For sure I can replace this line...
SUBSTR (SYS_CONNECT_BY_PATH (TRIM (LIBEBR), '|'), 2) AS LIBEBR

... by this one...
to_clob (substr(SYS_CONNECT_BY_PATH (TRIM (LIBEBR), '|'),2)) AS LIBEBR

... to fix the 4000 character limitation of VARCHAR2.
But in that case an other problem is triggered by this line...
MAX (LIBEBR) KEEP (DENSE_RANK LAST ORDER BY EXERBR, NOLQBR, NU_LIG) AS LIBEBR

... since LIBEBR is supposed to be anything but a CLOB.
Have you any ideas ?
Thank you very much.
Cedric


Subject: Re: SELECT query gives ORA-01489 after 11g upgrade
Message: Dear,
Kindly check below MOS you will get some idea
During upgrade DBUA fails with error "ORA-01489: result of string
concatenation is too long" (Doc ID 1961929.1)
and check below link
%3A%2F%2Fcommunity.oracle.com%2Fthread%2F3157775&_afrLoop=3827453581162
55&resultTitle=Query+does+not+work+in+11.2.0.3+release+but+works+correc
tly+in+11.2.0.1+release.+In+what+could+be+the+reason%3F&commId=3157775&
displayIndex=1&_afrWindowMode=0&_adf.ctrl-state=9gu4biekw_238


Subject: Re: SELECT query gives ORA-01489 after 11g upgrade
Message: Hi Mirza,
This does not match with my issue : I am not being upgrading my
database. Database upgrade is over and now we have side effects on some
of the end user's queries, such as the one I wrote in my previous
message.
Regards,
Cedric


Subject: Re: SELECT query gives ORA-01489 after 11g upgrade
Message: Dear,
Yes we are aware that your not not upgrading, we have provided you mos
notes and links to get some idea.


Subject: Re: SELECT query gives ORA-01489 after 11g upgrade
Message: Sure... I should maybe ask my question another way.
I have already found on MOS (Bug 20234397) that the problem was the
4000 bytes limitation for VARCHAR2 on 11g release, and that the
workaround was to use CLOB instead.
This is why I convert the field LIBEBR from VARCHAR2 to CLOB in my
query, using...
to_clob (substr(SYS_CONNECT_BY_PATH (TRIM (LIBEBR), '|'),2)) AS LIBEBR

... instead of...
SUBSTR (SYS_CONNECT_BY_PATH (TRIM (LIBEBR), '|'), 2) AS LIBEBR

And this works... but this also has a consequence on the top clause of
the query...
MAX (LIBEBR) KEEP (DENSE_RANK LAST ORDER BY EXERBR, NOLQBR, NU_LIG) AS LIBEBR

... where the field contained in MAX(...) cannot be a CLOB.
So my problem is to find a way to re-write this top clause to make it
accept a CLOB field.
Regards,
Cedric


Subject: Re: SELECT query gives ORA-01489 after 11g upgrade
Message: Hi Cédric,
So your problem is that MAX is not working on CLOB objects. Ok. So
change your object ;-)
You could use DBMS_LOB.SUBSTR(LIBEBR,2000,1) for example ?
The problem is also that we don't have any idea of the size your
variables may reach. If you can, cast your CLOB column.
Regards,
Joel