我的号码错误无效

Question

SET SERVEROUTPUT ON    
DECLARE
    lv_comp_msr VARCHAR2(20000);
 BEGIN
     WITH msr AS
        (SELECT REGEXP_SUBSTR ('02,03,04,09,12', '[^,]+',1,LEVEL) AS msr_id
           FROM DUAL
          CONNECT BY REGEXP_SUBSTR ('02,03,04,09,12', '[^,]+',1,LEVEL) IS NOT NULL    ) 
     SELECT listagg (measure_id, ',') WITHIN GROUP (    ORDER BY measure_id) AS MEASURE_ID 
     INTO lv_comp_msr
    FROM  
          (SELECT measure_id FROM irp_measures_def   ) 
    WHERE measure_id IN  (SELECT listagg (msr_id, ',') WITHIN GROUP (ORDER BY msr_id) msr_id
                          FROM msr   ) 
      --and COMP_MSR_FLAG is null
   ;   
   DBMS_OUTPUT.put_line('lv_comp_msr=' || lv_comp_msr); 
END;

Answer 1

listagg()生成一个字符串。在WHERE子句中，您将它与measure_id进行比较，我将猜测它是数字。您正在比较一个数字和一个字符串，因此Oracle执行隐式数据类型转换。但当然，聚合逗号分隔的字符串不能转换为数字，因此出现了ORA-01722错误。

这一错误很容易避免。您有一个WITH子句，它生成一个数字表:为什么首先要应用一个listagg()？您只需使用子查询保理的输出：

 WHERE measure_id IN  (SELECT msr_idFROM msr   )