[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Result of String concatenation is too long

Posted on 2014-08-25
21
Medium Priority
?
639 Views
Last Modified: 2014-08-25
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

0
Comment
Question by:angel7170
  • 11
  • 9
21 Comments
 
LVL 2

Expert Comment

by:Priya Sudharsan
ID: 40283326
I think the issue is not with the column you have mentioned.

Was the query working before adding the new column?
0
 

Author Comment

by:angel7170
ID: 40283339
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40283362
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:angel7170
ID: 40283530
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40283536
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
 

Author Comment

by:angel7170
ID: 40283548
I expect the concatenation to be more than 4000 size.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40283553
>> 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
 

Author Comment

by:angel7170
ID: 40283627
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40283667
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
 

Author Comment

by:angel7170
ID: 40283724
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40283733
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
 

Author Comment

by:angel7170
ID: 40283770
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40283813
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
 

Author Comment

by:angel7170
ID: 40283852
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
 

Author Comment

by:angel7170
ID: 40283861
I re-ran it again. Now it is showing (HUGECLOB) for both serial numbers. Not sure what this means.
0
 

Author Comment

by:angel7170
ID: 40283865
I am not getting the same results as you do. What could I be missing?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40283868
>>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
 

Author Comment

by:angel7170
ID: 40283875
Oh okay. I am using Toad 11.0
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40283896
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
 

Author Comment

by:angel7170
ID: 40283910
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40283916
No problem.  Glad to help.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question