Cześć,
Stworzyłem poniższa procedurę, pojawił się problem z liczbą danych które generuje.
Insert odpalany bez procedury zwraca ponad 15000 rekordów. Kiedy odpalę procedurę, liczba generowanych danych to tylko 120 rekordów. Parametry nadaje takie same, jak puszczam procedure oraz odpalam insert "ręcznie". Czym to może być spowodowane?
create or replace procedure calc_p (p_startdate NUMBER, p_end_date NUMBER, p_id NUMBER) is
BEGIN
DBMS_OUTPUT.PUT_LINE('Start: '||SUBSTR(localtimestamp,1,17));
delete from rep_pd where b_id = p_id and rep_date between p_startdate and p_end_date;
COMMIT;
insert into rep_pd
with cont(rep_date, b_id, con_number, okres_zap) as (
select /*+ materialize*/ rep_date, b_id, con_number, nvl(maturity_date - origin_date, 0) AS okres_zap
from con@sg_al where description not in ('OVER', 'Overn' )
and b_id = p_id
AND rep_date BETWEEN p_startdate AND p_end_date
)
SELECT mr.suffix1, mr.data_danych, ma.rep_date, b_id, mr.k_symbol, konto_nazwa,
CASE
WHEN mr.k_nazwa LIKE '%EXT%' THEN '04'
WHEN mr.k_symbol LIKE '3731%' then '12'
ELSE mr.typ_k_symbol END AS cl_t,
ma.okres_zap, mr.rezydent_symbol, mr.w_symbol as cu,
mr.sal_ma_pl as outstanding, get_pd_account(k_symbol) AS depo
substr(TO_NUMBER(TO_CHAR(data_danych, 'yyyymmdd')),7,2) as dzien
from abc.a_mr mr
left join cont ma
on ma.b_id = p_id
and to_date(ma.rep_date,'YY/MM/DD') = mr.data_danych
and ma.con_number = mr.suffix1
where sal_ma_pl <> 0
and get_pd_account(k_symbol) is not null
and nvl(typ_k_symbol,'20') not in ('01','02','02A')
and mr.data_danych BETWEEN TO_DATE(p_startdate,'YY/MM/DD') AND TO_DATE(p_end_date,'YY/MM/DD');
commit;
DBMS_OUTPUT.PUT_LINE('Koniec: '||SUBSTR(localtimestamp,1,17));
end calc_p;