ORA-06502字符转数字错误 PL/SQL管道表函数转换带逗号数值失败

问题根源 #

报错是因为to_number函数的转换逻辑依赖当前会话的NLS_NUMERIC_CHARACTERS参数：

• 你在SQL Developer中执行转换成功，是因为当前会话的NLS参数设置了逗号作为小数分隔符
• 函数实际运行时的会话（应用调用、定时任务触发等场景）的NLS参数默认小数点为.、千分符为,，会把4159,57中的逗号识别为千分符，不符合数字格式规则，因此抛出转换错误

修复方案 #

1. 所有涉及p_iznos_s_pdv转数字的逻辑，都显式指定转换格式和NLS参数，不要依赖会话默认配置，避免不同环境参数差异导致的报错
2. 你当前代码还存在隐式转换的风险：v_zadnja_rata赋值语句中直接用varchar2类型的p_iznos_s_pdv做数值计算，也会触发隐式转换，建议提前把入参一次性转为数值类型变量后统一使用

修复后核心代码示例 #

FUNCTION kreiraj_reprogram_rate (p_sifra_kupca             VARCHAR2,
                                     p_id_kartice_kupca        NUMBER,
                                     p_iznos_s_pdv             VARCHAR2,
                                     p_broj_rata               NUMBER,
                                     p_frekvencija_dospijeca   NUMBER,
                                     p_datum_dospijeca         VARCHAR2,
                                     p_datum_dokumenta         VARCHAR2)
        RETURN reprogram_rate_table
        PIPELINED
    IS
        v_data              reprogram_rate_rec;
        v_user              VARCHAR2 (200) := mc__secure_context.get_context_username ();
        v_iznos_s_pdv_num   NUMBER; -- 新增变量存储转换后的金额数值
        v_iznos_rate        NUMBER;
        v_broj_rate         NUMBER;
        v_zadnja_rata       NUMBER;
        v_datum_dokumenta   DATE;
        v_datum_dospijeca   DATE;
    BEGIN
        -- 一次性显式转换金额，指定逗号为小数分隔符
        v_iznos_s_pdv_num := TO_NUMBER(p_iznos_s_pdv, '9999999999D99', 'NLS_NUMERIC_CHARACTERS='', ''');
        write_log_table ('TEST','p_iznos_s_pdv: '|| v_iznos_s_pdv_num);
        
        v_datum_dospijeca := TO_DATE (p_datum_dospijeca, 'dd.mm.yyyy');
        v_datum_dokumenta := TO_DATE (p_datum_dokumenta, 'dd.mm.yyyy');

        v_iznos_rate := ROUND (v_iznos_s_pdv_num / p_broj_rata, 2);
        -- 替换原来的p_iznos_s_pdv为转换后的数值变量，避免隐式转换
        v_zadnja_rata :=
            v_iznos_s_pdv_num - ROUND ((p_broj_rata - 1) * v_iznos_rate, 2);
        v_broj_rate := 1;

        -- 后续循环逻辑保持不变即可
        FOR r IN 1 .. p_broj_rata
        LOOP
            IF v_broj_rate = p_broj_rata
            THEN
                v_data.osnovica := ROUND ((v_zadnja_rata / 1.25), 2);
                v_data.pdv := ROUND (v_zadnja_rata * 0.2, 2);
                v_data.ukupno := v_zadnja_rata;
            ELSE
                v_data.osnovica := ROUND ((v_iznos_rate / 1.25), 2);
                v_data.pdv := ROUND (v_iznos_rate * 0.2, 2);
                v_data.ukupno := ROUND (v_iznos_rate, 2);
            END IF;

            v_data.sifra_kupca := p_sifra_kupca;
            v_data.kartica_kupca := p_id_kartice_kupca;
            v_data.broj_rate := v_broj_rate;
            v_data.datum_dokumenta := v_datum_dokumenta;
            v_data.datum_dospijeca := v_datum_dospijeca;

            PIPE ROW (v_data);

            v_broj_rate := v_broj_rate + 1;

            v_datum_dospijeca :=
                CASE
                    WHEN p_frekvencija_dospijeca = 1
                    THEN
                        ADD_MONTHS (v_datum_dospijeca, 1)
                    WHEN p_frekvencija_dospijeca = 3
                    THEN
                        ADD_MONTHS (v_datum_dospijeca, 3)
                    WHEN p_frekvencija_dospijeca = 6
                    THEN
                        ADD_MONTHS (v_datum_dospijeca, 6)
                    WHEN p_frekvencija_dospijeca = 12
                    THEN
                        ADD_MONTHS (v_datum_dospijeca, 12)
                END;
        END LOOP;
    END kreiraj_reprogram_rate;

内容的提问来源于stack exchange，提问作者GodDamnLawyer