any help on ORA-01489: result of string concatenation is too long

[Jack]

The problem I have occurs when the SQL that I build is too great in
length. Here is my template SQL STATEMENT.

SELECT INSTANCE_NAME, GENESIS_PORT,
LTRIM(SYS_CONNECT_BY_PATH(API_NAME || ':' || TO_CHAR
(AVERAGE_TIME,'90.9999') || 's','; '), ';') AS AVERAGE_TIME_API
FROM ( SELECT INSTANCE_NAME, GENESIS_PORT, AVERAGE_TIME, API_NAME,
ROW_NUMBER() OVER
(PARTITION BY INSTANCE_NAME ORDER BY AVERAGE_TIME DESC) IM,
COUNT(*) OVER
(PARTITION BY INSTANCE_NAME) CNT
FROM GEN_API_PERFORMANCE
WHERE INSTANCE_NAME IS NOT NULL
AND STATISTICS_DATE>SYSDATE-2/24 AND
HOSTNAME='10.228.51.41'
AND API_NAME NOT IN
('_getMethods','genesisClearServerPerformance')
)
WHERE LEVEL = CNT
START WITH IM = 1
CONNECT BY PRIOR INSTANCE_NAME = INSTANCE_NAME AND PRIOR IM = IM - 1



When I try to increase this varchar2 past 4000, I get the:
ORA-01489: result of string concatenation is too long. Any solution
for this error?

[Joel]

does this work?   ...,LTRIM(SYS_CONNECT_BY_PATH(to_clob(API_NAME) || ......

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_sql_semantics.htm#sthref811

[Jack]

to_clob(......) . I tried and its not working... Any else?

[ddf]

On Mar 12, 1:06 am, Jack <muhammaddzulkarn...@gmail.com> wrote:
> to_clob(......) . I tried and its not working... Any else?

Perform this task within an anonymous PL/SQL block where you can
declare a varchar2 of length 32767.


David Fitzjarrell

[Michael Moore]

I would try using to_clob on each part of the string concat operation.. i.e. to_clob(':')||to_clob(myvar).

There might be an implicit cast operation if any part of the string is not clob.
Not really sure.

Mike