Result of String concatenation is too long

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

angel7170Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Priya SudharsanProgrammer AnalystCommented:
I think the issue is not with the column you have mentioned.

Was the query working before adding the new column?
0
angel7170Author 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
0
slightwv (䄆 Netminder) 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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

angel7170Author 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

0
slightwv (䄆 Netminder) 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.
0
angel7170Author Commented:
I expect the concatenation to be more than 4000 size.
0
slightwv (䄆 Netminder) 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.
0
angel7170Author 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
0
slightwv (䄆 Netminder) 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
0
angel7170Author 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
0
slightwv (䄆 Netminder) 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.
0
angel7170Author 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
0
slightwv (䄆 Netminder) 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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
angel7170Author 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
0
angel7170Author Commented:
I re-ran it again. Now it is showing (HUGECLOB) for both serial numbers. Not sure what this means.
0
angel7170Author Commented:
I am not getting the same results as you do. What could I be missing?
0
slightwv (䄆 Netminder) 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
0
angel7170Author Commented:
Oh okay. I am using Toad 11.0
0
slightwv (䄆 Netminder) 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.
0
angel7170Author 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.
0
slightwv (䄆 Netminder) Commented:
No problem.  Glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.