troubleshooting Question

Result of String concatenation is too long

Avatar of angel7170
angel7170Flag for United States of America asked on
Oracle Database
21 Comments1 Solution1046 ViewsLast Modified:
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     (+)
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 21 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 21 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros