ORA-22922: does not exist how to solve the LOB Value Problem ?, Ora-22922lob

ORA-22922: does not exist how to solve the LOB Value Problem ?, Ora-22922lob

I have been doing custom report management these days, and this problem is annoying.

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

Oracle should be version 10.2.0.5 or later, and wm_concat will return the clob type

This WM_CONCAT is a column-to-row function of oracle unmarshented. This function combines varchar fields into lob, however, it is strange that the combination of substr and to_char cannot solve this problem by using the cast function for forced conversion.

Solution.

A column-to-row function is written:

create or replace TYPE zh_concat_imAUTHID 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_imIS  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;/

Function:

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

It is not as efficient as WM_CONCAT, but it still solves this problem.

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,        'Hong Kong' 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 wswhere      ps.wmps_traceid=p.wmop_traceidand  p.wmop_orderno=o.wmor_order_noand  p.wmop_sku_code=os.wmos_sku_codeand  os.wmos_order_no=o.wmor_order_noand  ws.cdsk_sku_code=os.wmos_sku_codegroup byps.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,