Result of String concatenation is too long

angel7170
angel7170 used Ask the Experts™
on
Hello,

When I run below query, I get error: ORA-01489: result of string concatenation is too long ORA-02063: preceding line from DBRD5

There is a problem with the column "VT.VT_TEXT" because this is the new column I tried to add to an existing query but doesn't work.

I tried converting it using "TO_CLOB" but it says ORA-22992: cannot use LOB locators selected from remote tables

Can someone please assist?
Thank you

SELECT AM.AM_SER_NUM,
       AM.AM_STAT,
       NVL(TRIM(AM.AM_LOC), -1) AM_LOC,
       NVL(TRIM(AM.AM_LO_ASGN), -1) AM_LO_ASGN,
       NVL(AM.AM_DT_FIL, to_date('1/1/0001', 'mm/dd/yyyy')) AM_DT_FIL,
       NVL(AM.AM_DT_ABAN,to_date('1/1/0001', 'mm/dd/yyyy')) AM_DT_ABAN,
       NVL(AM.AM_DT_REG, to_date('1/1/0001', 'mm/dd/yyyy')) AM_DT_REG,
      NVL(AM.AM_DT_PUB, to_date('1/1/0001', 'mm/dd/yyyy')) AM_DT_PUB,
       NVL(AM.AM_STAT_DT,to_date('1/1/0001', 'mm/dd/yyyy')) AM_STAT_DT,
       AM.AM_EXMR_NUM,
       AM.AM_PARA_LGL_NUM,
       AM.AM_LIE_NUM,
       NVL(AM.AM_1_ACTN_CT_DT, to_date('1/1/0001', 'mm/dd/yyyy')) AM_1_ACTN_CT_DT,
       NVL(( decode(am_flg_use_cur,1,'USE','')|| 
                   decode(am_flg_44d_cur,1,'44D','')||
                   decode(am_flg_44e_cur,1,'44E','')||
                   decode(am_flg_itu_cur,1,'ITU','')||
                   decode(am_flg_66a_cur,1,'66A','')||
                   decode(am_flg_no_bas_cur,1,'NOBAS','')), 'NA') CUR_FILING_BASIS_CD,
      NVL(( decode(am_flg_use_fil,1,'USE','')|| 
                   decode(am_flg_44d_fil,1,'44D','')||
                   decode(am_flg_44e_fil,1,'44E','')||
                   decode(am_flg_itu_fil,1,'ITU','')||
                   decode(am_flg_66a_fil,1,'66A','')||
                   decode(am_flg_no_bas_fil,1,'NOBAS','')), 'NA')  FIL_FILING_BASIS_CD,
       NVL(AM.AM_IN_LOC_DT, to_date('1/1/0001', 'mm/dd/yyyy')) AM_IN_LOC_DT,
        NVL(SUBSTR(TRIM(AM.AM_STE_CD_REG), 1,2), -1) AM_STE_CD_REG,
        NVL(SUBSTR(TRIM(AM.AM_STE_CD_APPL), 1,2), -1) AM_STE_CD_APPL,
        NVL(OG.OG_CATG, -1) OG_CATG,
        NVL(OG.OG_STAT, -1) OG_STAT,
        NVL(OG.OG_DT_ISS,to_date('1/1/0001', 'mm/dd/yyyy')) OG_DT_ISS,
        NVL(OG.OG_DT_ACTN, to_date('1/1/0001', 'mm/dd/yyyy')) OG_DT_ACTN,
        NVL(IU.IU_DT_USE_STMT, to_date('1/1/0001', 'mm/dd/yyyy')) IU_DT_USE_STMT,
        NVL(IU.IU_DT_NOA, to_date('1/1/0001', 'mm/dd/yyyy')) IU_DT_NOA,
        NVL(RI.RI_INTL_REG_DT, to_date('1/1/0001', 'mm/dd/yyyy')) RI_INTL_REG_DT,
        NVL(RI.RI_NOTIF_DT, to_date('1/1/0001', 'mm/dd/yyyy')) RI_NOTIF_DT,
        AM.AM_CLS_CT_ACTV       CLASS_QT,
        AM.AM_TOT_CASE_ACT      TOTAL_ACTION_QT,
        1                       OCCURRENCE_QT,
      VT.VT_TEXT
                  ---  CASE WHEN SUBSTR ( VT.VT_TEXT_TYPE, 1, 2 ) = 'AT' THEN VT.VT_TEXT ELSE '' END as ATTORNEY_LIST_TX

--select count(*)  
      FROM  AM@dbrd5 AM,
    (    select og_ser_num, max(OG_CATG) OG_CATG , max(OG_STAT) OG_STAT, max(OG_DT_ISS) OG_DT_ISS, max(OG_DT_ACTN) OG_DT_ACTN 
               from OG@dbrd5  
                group by og_ser_num
            )   OG  
       , ( select IU_SER_NUM,IU_DT_USE_STMT, IU_DT_NOA from IU@dbrd5)  IU
      , ( select RI_SER_NUM,RI_INTL_REG_DT, RI_NOTIF_DT FROM RI@dbrd5)  RI   
      , (  Select VT_sub.VT_SER_NUM, MAX(VT_sub.VT_TEXT) VT_TEXT
            from ( SELECT VT_SER_NUM, VT_TEXT_TYPE
            , LISTAGG ( VT_TEXT ) WITHIN GROUP (ORDER BY Vt_Ent_Num) OVER (PARTITION BY Vt_Ser_Num, VT_TEXT_TYPE) VT_TEXT
              FROM VT@DBRD5 VT
              WHERE REGEXP_LIKE(SUBSTR ( Vt.VT_TEXT_TYPE, 3, 2 ), '^[[:digit:]]+$')
                 and ( VT_TEXT_TYPE LIKE 'AT%')                
                     ) VT_sub
                 GROUP BY VT_sub.VT_SER_NUM
                     ) VT
WHERE AM_SER_NUM = OG_SER_NUM     (+)
  AND AM_SER_NUM = RI_SER_NUM     (+)
  AND AM_SER_NUM = IU_SER_NUM     (+)
  AND AM_SER_NUM = VT_SER_NUM     (+)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Priya SudharsanProgrammer Analyst

Commented:
I think the issue is not with the column you have mentioned.

Was the query working before adding the new column?

Author

Commented:
Yes, it was working before I added the column and its related Join table. I think ListAgg is trying to return more than the maximum size of Varchar(2) and I am not sure how to resolve it.


  Select VT_sub.VT_SER_NUM, MAX(VT_sub.VT_TEXT) VT_TEXT
            from ( SELECT VT_SER_NUM, VT_TEXT_TYPE
            , LISTAGG ( VT_TEXT ) WITHIN GROUP (ORDER BY Vt_Ent_Num) OVER (PARTITION BY Vt_Ser_Num, VT_TEXT_TYPE) VT_TEXT
              FROM VT@DBRD5 VT
              WHERE REGEXP_LIKE(SUBSTR ( Vt.VT_TEXT_TYPE, 3, 2 ), '^[[:digit:]]+$')
                 and ( VT_TEXT_TYPE LIKE 'AT%')                
                     ) VT_sub
                 GROUP BY VT_sub.VT_SER_NUM
                     ) VT
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
LISTAGG and manual string concatenation have a 4000 character limit.

You'll need to write your own stragg that deals with clob's or use the XML trick.

Here's an example of the XML trick.  It will work unless VT_TEXT has a row that has exactly 4000 characters in it.
drop table tab1 purge;
create table tab1(col1 char(1), col2 varchar2(4000));

insert into tab1 values('a',null);
insert into tab1 values('b',null);

declare
	mychar varchar2(4000);
begin
	for i in 1..3999 loop
		mychar := mychar || 'c';
	end loop;
	update tab1 set col2=mychar;
end;
/

commit;



SELECT 
        RTRIM(
            EXTRACT(XMLAGG(XMLELEMENT("s", col2 || ',')), '/s/text()').getclobval(),
            ','
        )
FROM tab1
/

Open in new window

Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hello slightwv,

I am newbie to XMLAGG. I tried to convert LISTAGG to XMLAGG but got stuck. Can you please assist?
Thank you

SELECT VT_SER_NUM
         ,RTRIM(
            EXTRACT(XMLAGG(XMLELEMENT("s", VT_TEXT || ',')), '/s/text()' ).getclobval(), ','  ) VT_TEXT
                     ---, LISTAGG ( VT_TEXT ) WITHIN GROUP (ORDER BY Vt_Ent_Num) OVER (PARTITION BY Vt_Ser_Num, VT_TEXT_TYPE) VT_TEXT
              FROM VT@DBRD5 VT
              WHERE REGEXP_LIKE(SUBSTR ( Vt.VT_TEXT_TYPE, 3, 2 ), '^[[:digit:]]+$')
                 and ( VT_TEXT_TYPE LIKE 'AT%')  
              GROUP BY VT_SER_NUM
                   

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
How big do you expect the concatenated string to be?

Try pulling a few rows from the remote database to a local table and get the query working just to make sure it is creating the correct results.

Author

Commented:
I expect the concatenation to be more than 4000 size.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>> expect the concatenation to be more than 4000 size.

That doesn't help.  It will be greater than 4000 or you wouldn't have the above error.

I'm asking if you expect 1 Meg or 4 Gig as a single result.

Author

Commented:
I am just researching it. sorry about that!

I just took a simple query to return how many rows are returned for a single serial number and what I found was the max for the attached serial number returned 105 but 103 rows are empty. It is not null or either blank record.  I think the data is bad where I have to exclude these rows to get the accuracy. I think it is blank spaces. I probably should use TRIM but should I use in the select statement or at the where clause?
screenshot.PNG
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Is VT_TEXT defined as CHAR or VARCHAR2?

If CHAR then it will be a lot of spaces.  If VARCHAR2 then there will not be unless someone actually inserted a lot of spaces.

If you think that is the issue, I would probably add it to my where clause:
and trim(VT_TEXT) is not null

Author

Commented:
It is varchar2. I used trim(VT_TEXT) is not null. Thanks for that.

I now see another serial number which returned 104 rows with actual data. Please see attached. I think this is whether LISTAGG fails to run and I am not sure how to get this working on as you said for XMLAGG.
screenshot.PNG
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Please provide an extract of sample data and expected results.

Please post them as actual text and not screen shots.

I'll take what you provide and create a working test case.

Author

Commented:
Please see attached is the sample data. Expected results is to concatenate all the text in "VT_TEXT" column into one column and output the results (for example  aaaaa.....zzzzz). The sample I provided is for one serial number. So, my query needs to be grouped by Serial number (VT_SER_NUM) with an order by VT_ENT_NUM.
Sample.xls
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Here is my test case (the select is at the bottom):
drop table tab1 purge;
create table tab1(VT_ENT_NUM number, VT_SER_NUM number, VT_TEXT_TYPE varchar2(10), VT_TEXT varchar2(4000));

--my two test rows
insert into tab1 values(1,123,'AT1111','Hello ');
insert into tab1 values(1,123,'AT1111','World');



insert into tab1 values(1,77551538,'AT0000','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
insert into tab1 values(2,77551538,'AT0000','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
insert into tab1 values(3,77551538,'AT0000','cccccccccccccccccccccccccccccccccccccccc');
insert into tab1 values(4,77551538,'AT0000','dddddddddddddddddddddddddddddddddddddddd');
insert into tab1 values(5,77551538,'AT0000','eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee');
insert into tab1 values(6,77551538,'AT0000','ffffffffffffffffffffffffffffffffffffffff');
insert into tab1 values(7,77551538,'AT0000','gggggggggggggggggggggggggggggggggggggggg');
insert into tab1 values(8,77551538,'AT0000','hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh');
insert into tab1 values(9,77551538,'AT0000','hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh');
insert into tab1 values(10,77551538,'AT0000','iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii');
insert into tab1 values(11,77551538,'AT0000','jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj');
insert into tab1 values(12,77551538,'AT0000','kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
insert into tab1 values(13,77551538,'AT0000','llllllllllllllllllllllllllllllllllllllll');
insert into tab1 values(14,77551538,'AT0000','mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm');
insert into tab1 values(15,77551538,'AT0000','nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn');
insert into tab1 values(16,77551538,'AT0000','ooooooooooooooooooooooooooooooooooooooo');
insert into tab1 values(17,77551538,'AT0000','pppppppppppppppppppppppppppppppppppppppp');
insert into tab1 values(18,77551538,'AT0000','hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh');
insert into tab1 values(19,77551538,'AT0000','iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii');
insert into tab1 values(20,77551538,'AT0000','jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj');
insert into tab1 values(21,77551538,'AT0000','kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
insert into tab1 values(22,77551538,'AT0000','llllllllllllllllllllllllllllllllllllllll');
insert into tab1 values(23,77551538,'AT0000','mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm');
insert into tab1 values(24,77551538,'AT0000','nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn');
insert into tab1 values(25,77551538,'AT0000','ooooooooooooooooooooooooooooooooooooooo');
insert into tab1 values(26,77551538,'AT0000','pppppppppppppppppppppppppppppppppppppppp');
insert into tab1 values(27,77551538,'AT0000','hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh');
insert into tab1 values(28,77551538,'AT0000','iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii');
insert into tab1 values(29,77551538,'AT0000','jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj');
insert into tab1 values(30,77551538,'AT0000','kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
insert into tab1 values(31,77551538,'AT0000','llllllllllllllllllllllllllllllllllllllll');
insert into tab1 values(32,77551538,'AT0000','mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm');
insert into tab1 values(33,77551538,'AT0000','nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn');
insert into tab1 values(34,77551538,'AT0000','ooooooooooooooooooooooooooooooooooooooo');
insert into tab1 values(35,77551538,'AT0000','pppppppppppppppppppppppppppppppppppppppp');
insert into tab1 values(36,77551538,'AT0000','hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh');
insert into tab1 values(37,77551538,'AT0000','iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii');
insert into tab1 values(38,77551538,'AT0000','jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj');
insert into tab1 values(39,77551538,'AT0000','kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
insert into tab1 values(40,77551538,'AT0000','llllllllllllllllllllllllllllllllllllllll');
insert into tab1 values(41,77551538,'AT0000','mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm');
insert into tab1 values(42,77551538,'AT0000','nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn');
insert into tab1 values(43,77551538,'AT0000','ooooooooooooooooooooooooooooooooooooooo');
insert into tab1 values(44,77551538,'AT0000','pppppppppppppppppppppppppppppppppppppppp');
insert into tab1 values(45,77551538,'AT0000','hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh');
insert into tab1 values(46,77551538,'AT0000','iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii');
insert into tab1 values(47,77551538,'AT0000','jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj');
insert into tab1 values(48,77551538,'AT0000','kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
insert into tab1 values(49,77551538,'AT0000','llllllllllllllllllllllllllllllllllllllll');
insert into tab1 values(50,77551538,'AT0000','mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm');
insert into tab1 values(51,77551538,'AT0000','nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn');
insert into tab1 values(52,77551538,'AT0000','ooooooooooooooooooooooooooooooooooooooo');
insert into tab1 values(53,77551538,'AT0000','pppppppppppppppppppppppppppppppppppppppp');
insert into tab1 values(54,77551538,'AT0000','hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh');
insert into tab1 values(55,77551538,'AT0000','iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii');
insert into tab1 values(56,77551538,'AT0000','jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj');
insert into tab1 values(57,77551538,'AT0000','kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
insert into tab1 values(58,77551538,'AT0000','llllllllllllllllllllllllllllllllllllllll');
insert into tab1 values(59,77551538,'AT0000','mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm');
insert into tab1 values(60,77551538,'AT0000','nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn');
insert into tab1 values(61,77551538,'AT0000','ooooooooooooooooooooooooooooooooooooooo');
insert into tab1 values(62,77551538,'AT0000','pppppppppppppppppppppppppppppppppppppppp');
insert into tab1 values(63,77551538,'AT0000','hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh');
insert into tab1 values(64,77551538,'AT0000','iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii');
insert into tab1 values(65,77551538,'AT0000','jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj');
insert into tab1 values(66,77551538,'AT0000','kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
insert into tab1 values(67,77551538,'AT0000','llllllllllllllllllllllllllllllllllllllll');
insert into tab1 values(68,77551538,'AT0000','mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm');
insert into tab1 values(69,77551538,'AT0000','nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn');
insert into tab1 values(70,77551538,'AT0000','ooooooooooooooooooooooooooooooooooooooo');
insert into tab1 values(71,77551538,'AT0000','pppppppppppppppppppppppppppppppppppppppp');
insert into tab1 values(72,77551538,'AT0000','hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh');
insert into tab1 values(73,77551538,'AT0000','iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii');
insert into tab1 values(74,77551538,'AT0000','jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj');
insert into tab1 values(75,77551538,'AT0000','kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
insert into tab1 values(76,77551538,'AT0000','llllllllllllllllllllllllllllllllllllllll');
insert into tab1 values(77,77551538,'AT0000','mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm');
insert into tab1 values(78,77551538,'AT0000','nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn');
insert into tab1 values(79,77551538,'AT0000','ooooooooooooooooooooooooooooooooooooooo');
insert into tab1 values(80,77551538,'AT0000','pppppppppppppppppppppppppppppppppppppppp');
insert into tab1 values(81,77551538,'AT0000','hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh');
insert into tab1 values(82,77551538,'AT0000','iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii');
insert into tab1 values(83,77551538,'AT0000','jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj');
insert into tab1 values(84,77551538,'AT0000','kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
insert into tab1 values(85,77551538,'AT0000','llllllllllllllllllllllllllllllllllllllll');
insert into tab1 values(86,77551538,'AT0000','mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm');
insert into tab1 values(87,77551538,'AT0000','nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn');
insert into tab1 values(88,77551538,'AT0000','ooooooooooooooooooooooooooooooooooooooo');
insert into tab1 values(89,77551538,'AT0000','pppppppppppppppppppppppppppppppppppppppp');
insert into tab1 values(90,77551538,'AT0000','hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh');
insert into tab1 values(91,77551538,'AT0000','iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii');
insert into tab1 values(92,77551538,'AT0000','jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj');
insert into tab1 values(93,77551538,'AT0000','kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
insert into tab1 values(94,77551538,'AT0000','llllllllllllllllllllllllllllllllllllllll');
insert into tab1 values(95,77551538,'AT0000','mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm');
insert into tab1 values(96,77551538,'AT0000','nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn');
insert into tab1 values(97,77551538,'AT0000','ooooooooooooooooooooooooooooooooooooooo');
insert into tab1 values(98,77551538,'AT0000','pppppppppppppppppppppppppppppppppppppppp');
insert into tab1 values(99,77551538,'AT0000','mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm');
insert into tab1 values(100,77551538,'AT0000','nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn');
insert into tab1 values(101,77551538,'AT0000','ooooooooooooooooooooooooooooooooooooooo');
insert into tab1 values(102,77551538,'AT0000','ooooooooooooooooooooooooooooooooooooooo');
insert into tab1 values(103,77551538,'AT0000','pppppppppppppppppppppppppppppppppppppppp');
insert into tab1 values(104,77551538,'AT0000','zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz');
commit;


SELECT  vt_ser_num, 
EXTRACT(XMLAGG(XMLELEMENT("s", vt_text) order by vt_ent_num), '/s/text()').getclobval()
FROM tab1
group by vt_ser_num
/

Open in new window



The output:
       123
Hello World

  77551538
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccc
ddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeffffffffffffffffffffffffffffffffffffffff
gggggggggggggggggggggggggggggggggggggggghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiijjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
llllllllllllllllllllllllllllllllllllllllmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnno
oooooooooooooooooooooooooooooooooooooopppppppppppppppppppppppppppppppppppppppphhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhii
iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiijjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkll
llllllllllllllllllllllllllllllllllllllmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnooo
oooooooooooooooooooooooooooooooooooopppppppppppppppppppppppppppppppppppppppphhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhiiii
iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiijjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkllll
llllllllllllllllllllllllllllllllllllmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnooooo
oooooooooooooooooooooooooooooooooopppppppppppppppppppppppppppppppppppppppphhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhiiiiii
iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiijjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkllllll
llllllllllllllllllllllllllllllllllmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnooooooo
oooooooooooooooooooooooooooooooopppppppppppppppppppppppppppppppppppppppphhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhiiiiiiii
iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiijjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkllllllll
llllllllllllllllllllllllllllllllmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnooooooooo
oooooooooooooooooooooooooooooopppppppppppppppppppppppppppppppppppppppphhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhiiiiiiiiii
iiiiiiiiiiiiiiiiiiiiiiiiiiiiiijjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkllllllllll
llllllllllllllllllllllllllllllmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnooooooooooo
oooooooooooooooooooooooooooopppppppppppppppppppppppppppppppppppppppphhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhiiiiiiiiiiii
iiiiiiiiiiiiiiiiiiiiiiiiiiiijjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkllllllllllll
llllllllllllllllllllllllllllmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnooooooooooooo
oooooooooooooooooooooooooopppppppppppppppppppppppppppppppppppppppphhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhiiiiiiiiiiiiii
iiiiiiiiiiiiiiiiiiiiiiiiiijjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkllllllllllllll
llllllllllllllllllllllllllmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnooooooooooooooo
oooooooooooooooooooooooopppppppppppppppppppppppppppppppppppppppphhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhiiiiiiiiiiiiiiii
iiiiiiiiiiiiiiiiiiiiiiiijjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkllllllllllllllll
llllllllllllllllllllllllmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnooooooooooooooooo
oooooooooooooooooooooopppppppppppppppppppppppppppppppppppppppphhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhiiiiiiiiiiiiiiiiii
iiiiiiiiiiiiiiiiiiiiiijjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkllllllllllllllllll
llllllllllllllllllllllmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnooooooooooooooooooo
ooooooooooooooooooooppppppppppppppppppppppppppppppppppppppppmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmnnnnnnnnnnnnnnnnnnnn
nnnnnnnnnnnnnnnnnnnooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooppppppppppppppppppppppp
pppppppppppppppppzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

Open in new window

Author

Commented:
I ran your query. Attached is the screenshot of the output. It looks like it did not concatenate after 2nd character from 3rd row.
screenshot.PNG

Author

Commented:
I re-ran it again. Now it is showing (HUGECLOB) for both serial numbers. Not sure what this means.

Author

Commented:
I am not getting the same results as you do. What could I be missing?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>It looks like it did not concatenate after 2nd character from 3rd row.
>>Now it is showing (HUGECLOB)

Likely the tool you are using.

Most tools only show a portion of a CLOB.  You need to tell it to display more.  I don't use a GUI so cannot help with the individual settings.

Check the help for the tool.

In sqlplus it is:
set long 1000000

Author

Commented:
Oh okay. I am using Toad 11.0
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Never used Toad.

Even a quick Google doesn't help much.  I remember a question here on Experts-Exchange a long time ago that you couldn't see the lob in the table view.  You had to select the row/column and somehow it would show the entire LOB.

Author

Commented:
In Toad, under View>Toad options>data grid> data> I checked the option to "Preview CLOB and LONG data and it seemed to work. Thank you very much for your help.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
No problem.  Glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial