欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

ORA-22922: 不存在的 LOB 值 问题如何解决?

程序员文章站 2022-07-28 21:09:10
这些天一直在做自定义报表管理,被这个问题给烦住了。 replace(to_char(wmsys.wm_concat(distinct to_char(ws.userdefined...

这些天一直在做自定义报表管理,被这个问题给烦住了。

replace(to_char(wmsys.wm_concat(distinct to_char(ws.userdefined5))),',',',')as fld_edi_description,

oracle应该是10.2.0.5以上的版本,wm_concat返回的是clob类型

这个WM_CONCAT导致的,这是oracle undocumented的一个列转行的函数,这个函数会把varchar的字段合为lob,但是奇怪的是即使用cast函数强转还是用substr和to_char结合 都解决不了这个问题。 

解决办法。

写了一个列转行的函数:

create or replace TYPE zh_concat_im
AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR VARCHAR2(32767),
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
               P1 IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
                                 RETURNVALUE OUT VARCHAR2,
                                 FLAGS IN NUMBER)
                     RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
                    SCTX2 IN  zh_concat_im) RETURN NUMBER
);
/
create or replace TYPE BODY zh_concat_im
IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)
  RETURN NUMBER
  IS
  BEGIN
    SCTX := zh_concat_im(NULL) ;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
          P1 IN VARCHAR2)
  RETURN NUMBER
  IS
  BEGIN
    IF(CURR_STR IS NOT NULL) THEN
      CURR_STR := CURR_STR || ':' || P1;
    ELSE
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
                                 RETURNVALUE OUT VARCHAR2,
                                 FLAGS IN NUMBER)
    RETURN NUMBER
  IS
  BEGIN
    RETURNVALUE := CURR_STR ;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
                                   SCTX2 IN zh_concat_im)
  RETURN NUMBER
  IS
  BEGIN
    IF(SCTX2.CURR_STR IS NOT NULL) THEN
      SELF.CURR_STR := SELF.CURR_STR || ':' || SCTX2.CURR_STR ;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
END;
/

函数:

create or replace FUNCTION zh_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING zh_concat_im ;
/

效率不如WM_CONCAT但是还是解决了这个问题

select 
        TO_CHAR(SYSDATE,'yyyymmdd') as fld_TM_manifest_id,
        1 as fld_TM_cartons ,
        nvl(ps.wmps_grossweight,0)as fld_TM_cbm ,
        nvl(ps.wmps_cubic,0)as fld_TM_weight ,
        'AIR'as  fld_TM_container_type,
        '' as fld_TM_container_no,
        'AUD' as fld_TM_seal_no,
        ''as fld_TM_vessel_name,
        TO_CHAR(SYSDATE+1,'DD/MM/YYYY') as fld_TM_edt,
        ps.wmps_traceID as fld_TMD_connote_no,
        p.userdefined2 as fld_TMD_order_no,  
        1 as fld_TMD_cartons,
        nvl(ps.wmps_cubic, 0) as fld_TMD_cbm,
        nvl(ps.wmps_grossweight, 0) as fld_TMD_weight,
        to_char(sysdate,'Month dd yyyy hh:miAM','NLS_DATE_LANGUAGE = American')||'_BWB_Manifest' as fld_TO_filename,
        TO_CHAR(SYSDATE+3,'dd/mm/yyyy') as fld_TO_import_date,
        o.wmor_soreference2 as fld_TO_order_no,
        1 as fld_TO_carrier,
        o.wmor_consigneename as fld_TO_surname,
        NVL(o.wmor_c_address1, ' ') as fld_TO_address_1,
        NVL(o.wmor_c_address2, ' ') as fld_TO_address_2,
        o.wmor_c_country as fld_TO_country_code,
        o.wmor_c_city as fld_TO_suburb,
        NVL(o.wmor_c_province, ' ') as fld_TO_state,
        o.wmor_c_zip as fld_TO_postcode,
        NVL(o.wmor_c_tel1, ' ') as fld_TO_telephone,
        'AeParcelPac' as fld_TO_delivery_instr,
        NVL(o.wmor_c_address3, ' ') as fld_TO_address_3,
        NVL(ps.wmps_grossweight, 0) as fld_TO_weight,
        1 as fld_TOL_line_no,
        '' as fld_TOL_product_no,
        '' as fld_TOL_item_no,   
        1 as fld_TOL_quantity,  
        TO_CHAR(SYSDATE+5,'DD/MM/YY') as fld_TOL_promised_date,   
        sum(NVL(os.userdefined2,0)* os.wmos_qtypicked) as fld_TOL_price,
        sum(NVL(os.userdefined2,0)* os.wmos_qtypicked)   as fld_TOL_total_amount, 
       -- substr(to_char(wmsys.wm_concat(distinct to_char(ws.userdefined5))) ,1,2000)as fld_edi_description,
       --select to_char(ZH_Concat(userdefined5)) from cd_wh_sku group by cdsk_sku_code;
      to_char(ZH_Concat(distinct to_char(ws.userdefined5))) as fld_edi_description,
        'Jeanswest' as mame,
        '11th Floor, Unit C, 2 Tins Centre'as addr1,
        'Hung Cheung Road 3' as addr2,
        'Tuen Mun' as city,
        '*' as state,
        'NA' as postcode,
        'HK'as country
from wm_out_packing_summary ps,wm_out_packing p,wm_out_order o,wm_out_order_sku os,cd_wh_sku ws
where 
     ps.wmps_traceid=p.wmop_traceid
and  p.wmop_orderno=o.wmor_order_no
and  p.wmop_sku_code=os.wmos_sku_code
and  os.wmos_order_no=o.wmor_order_no
and  ws.cdsk_sku_code=os.wmos_sku_code
group by
ps.wmps_traceID,o.wmor_soreference2,o.wmor_consigneename,o.wmor_c_country,o.wmor_c_city,o.wmor_c_zip,ps.wmps_grossweight,
ps.wmps_cubic,ps.wmps_cubic,ps.wmps_grossweight,o.wmor_c_address1,o.wmor_c_address2,o.wmor_c_province,o.wmor_c_tel1,o.wmor_c_address3,