Conversion into Clob from Varchar2 [message #665274] |
Mon, 28 August 2017 08:29 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I want to form the string by concatenating some of the columns in the tables which contains so many records. After concatenation of all possible columns of a table, VARCHAR2 data type is not supporting as the length of concatenated string becomes more that 4000.
Following is example query which supports the up to 4000 length .
WITH DATA AS
(SELECT level l,
TO_CHAR(to_date(level,'j'),'jsp') g
FROM dual
CONNECT BY level <[COLOR=#ff0000]203[/COLOR]
)
SELECT rtrim(LISTAGG( l
||'-'
||g
||',' ) WITHIN GROUP (
ORDER BY l),',') clob_list
FROM DATA ;
If I increase that 203 numbers its giving the following error message
ORA-01489: RESULT OF string concatenation IS too long
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation RESULT IS more than the maximum SIZE.
*Action: Make sure that the RESULT IS less than the maximum SIZE.
I tried with the help of XMLAGG ,but there also we have limit up to 4000 character only .
Do I need to implement the user defined function to return the CLOB data type by appending all required values .
Or
Is there any alternate by using SIMPLE SQL Query ?
Please help me to resolve the issue
Thanks
|
|
|
|
|
|
|