1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Conversion into clob from varchar2

Discussion in 'SQL PL/SQL' started by Revathi Thirunagari, Aug 28, 2017.

  1. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    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 .

    Code (SQL):
    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
    Code (SQL):
    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
    Revathi Thirunagari
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    372
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which release of Oracle are you using?
     
  3. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad

    Hi David
    Thanks for interest to solve our issue .

    We are using the following version :

    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    "CORE 11.2.0.4.0 Production"
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production

    Thanks
    Revathi Thirunagari
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    769
    Likes Received:
    148
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    For example:
    Code (Text):


    SELECT
      SUBSTR(XMLAGG(XMLELEMENT(g,','||g)).extract('//text()').getclobval(),2)
    FROM
    (
      SELECT
         LEVEL ID, TO_CHAR(to_date(level,'j'),'jsp')  g
      FROM dual
        CONNECT BY level <=203
        );

     
     
  5. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    T
    Hi krasnoslobodtsev_si

    Thank you very much .Its satisfying my requirement .

    Thanks
    Revathi Thirunagari