Функция PL/SQL, возвращающая неверный результат

Ниже приведена функция PL/SQL, которая возвращает неверный результат в SQL Navigator и SQL Developer, но возвращает правильный ответ в SQL Plus.

У нас есть скрипт, который выполняет его и возвращает неправильный ответ, поэтому пытаемся это исправить. Кто-нибудь может увидеть какие-либо проблемы с этим? Он отлично работает для большинства людей, но у меня есть несколько человек, которые заходят и возвращают ноль / ничего в SQL Navigator и Developer. Он не заполняет l_end_date для них, и, следовательно, кредиты не заполняются.

Хорошо работает тогда в SQL Plus по некоторым причинам.

    create or replace function mis_get_mem_lcr_credits(p_mem_no in number) RETURN  number is
    --
       v_lcr_credit   number;
       l_mem_no       number;
       l_start_date   date;
       l_end_date     date;
       l_dob          date;
       l_18th_date    date;
    --
       cursor c1 is
       select mem_no, ind_birth_dt
       from cd_individual
       where mem_no = l_mem_no
       and pkg_mem_utils.get_member_age(mem_no,ind_birth_dt) >= 18
       and nvl(ind_student_flag,'N') = 'N'
       order by mem_no, ind_birth_dt;
    --
       cursor c2 is
       select distinct m_effdt,
              m_termdt
       from cd$v_member_contracts9 cd1,
            cd_member_product_link cd2
       where cd1.mem_no = l_mem_no
       and cd1.policy_no = cd2.policy_no
       and cd1.m_effdt = cd2.mem_product_eff_dt --.2
       and (l_18th_date between cd1.m_effdt and cd1.m_termdt OR cd1.m_effdt > l_18th_date)--.3 18 at time of contract effective date
       and nvl(cd1.lapsed_to_start,'N') = 'N'
       and cd2.product_id not in (14,41,31) -- Exclude No Cover, DentalProtect and HealthProtect
       and cd2.product_id NOT IN (select distinct product_id
                                  from cd_product_options
                                  where nvl(allowed_for_lcr,'Y') = 'N')
       order by cd1.m_effdt ASC;
    --
    begin
    --
       l_mem_no       := p_mem_no;
       v_lcr_credit   := 0;
       l_dob          := null;
    --
       for crec in c1 loop            
       --
          l_dob := crec.ind_birth_dt;
       --
        --  l_18th_date := substr(to_char(l_dob,'DD/MM/YYYY'),0,6)||(substr(to_char(l_dob,'DD/MM/YYYY'),7,4)+18);

           if to_char(l_dob) like '29-02%' then
                    l_18th_date :=    add_months(to_date(l_dob+1),216 );
            else
                    l_18th_date :=   add_months(to_date(l_dob), 216);
            end if;

       --       

          for crec2 in c2 loop
          --



             if crec2.m_termdt > sysdate then
             --
                l_end_date := sysdate;            
             --         
             else
             --
                l_end_date := crec2.m_termdt;
             --
             end if;
          --
             if v_lcr_credit = 0 then --earliest contract
             --
                if l_18th_date between crec2.m_effdt and crec2.m_termdt then
                --
                   v_lcr_credit := v_lcr_credit + months_between(l_end_date,l_18th_date);
                --
                else
                --
                   v_lcr_credit := v_lcr_credit + months_between(l_end_date,crec2.m_effdt);
                --
                end if;
             --
             else
             --
                v_lcr_credit := v_lcr_credit + months_between(l_end_date,crec2.m_effdt);
             --
             end if;
          --
          end loop;
       --
       end loop;
    --
       return round(nvl(v_lcr_credit,0));

    --
    end mis_get_mem_lcr_credits;
    /
    show errors

    spool off

    exit
Источник

2 ответа

Решение

Никогда, никогда не используйте to_date() на DATE значение.

to_date() преобразует varchar к date,

Если вы называете это с DATE значение даты преобразуется в varchar, который затем преобразуется обратно в дату, с которой оно должно было начаться, и дважды подвергается неявному преобразованию типов данных в этом процессе.

Переменная l_dob определяется как DATE так что вы должны изменить

add_months(to_date(l_dob+1),216 );
...
add_months(to_date(l_dob), 216);

в

add_months(l_dob+1,216);
...
add_months(l_dob, 216);
Источник

Может быть из-за разных значений

NLS_TERRITORY, NLS_DATE_FORMAT и т.д. в разных средах. Поэтому я бы предложил явно установить эти значения в вашем скрипте. например что-то вроде EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY=''AMERICA''';

Некоторые ссылки:

NLS_DATE_FORMAT

NLS_TERRITORY

Источник
Другие вопросы по тегам