OraFAQ Forum: SQL & PL/SQL » Conversion into Clob from Varchar2

Home » SQL & PL/SQL » SQL & PL/SQL » Conversion into Clob from Varchar2 (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator