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)
Conversion into Clob from Varchar2 [message #665274] Mon, 28 August 2017 08:29 Go to next message
saipradyumn
Messages: 310
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
Re: Conversion into Clob from Varchar2 [message #665275 is a reply to message #665274] Mon, 28 August 2017 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 25747
Registered: January 2009
Location: SoCal
Senior Member
SQL strings are limited to 4000.
If string greater than 4000 is required, then CLOB must be used.
Re: Conversion into Clob from Varchar2 [message #665291 is a reply to message #665275] Tue, 29 August 2017 01:08 Go to previous messageGo to next message
saipradyumn
Messages: 310
Registered: October 2011
Location: Hyderabad
Senior Member
That's what my problem BlackSwan,

How to convert to CLOB with appending all the required values into single variable?


Re: Conversion into Clob from Varchar2 [message #665305 is a reply to message #665291] Tue, 29 August 2017 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 25747
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Mon, 28 August 2017 23:08
That's what my problem BlackSwan,

How to convert to CLOB with appending all the required values into single variable?


http://lmgtfy.com/?q=oracle+clob+append
Re: Conversion into Clob from Varchar2 [message #665324 is a reply to message #665305] Wed, 30 August 2017 03:12 Go to previous message
saipradyumn
Messages: 310
Registered: October 2011
Location: Hyderabad
Senior Member
Thank u very much
Previous Topic: REGEXP_INSTR
Next Topic: Need help on query
Goto Forum:
  


Current Time: Thu Dec 14 08:02:07 CST 2017

Total time taken to generate the page: 0.06309 seconds