Thanks for all help at this issue. I'll post here my solution:
Package Header
CREATE OR REPLACE PACKAGE X IS
TYPE exch_row IS RECORD(
currency_cd VARCHAR2(9),
exch_rt_eur NUMBER,
exch_rt_usd NUMBER);
TYPE exch_tbl IS TABLE OF X.exch_row;
FUNCTION GetExchangeRate RETURN X.exch_tbl PIPELINED;
END X;
Package Body
CREATE OR REPLACE PACKAGE BODY X IS
FUNCTION GetExchangeRate RETURN X.exch_tbl
PIPELINED AS
exch_rt_usd NUMBER := 1.0; --todo
rw exch_row;
BEGIN
FOR rw IN (SELECT c.currency_cd AS currency_cd, e.exch_rt AS exch_rt_eur, (e.exch_rt / exch_rt_usd) AS exch_rt_usd
FROM exch e, currency c
WHERE c.currency_key = e.currency_key
) LOOP
PIPE ROW(rw);
END LOOP;
END;
PROCEDURE DoIt IS
BEGIN
DECLARE
CURSOR c0 IS
SELECT i.DOC,
i.doc_currency,
i.net_value,
i.net_value / rt.exch_rt_usd AS net_value_in_usd,
i.net_value / rt.exch_rt_eur AS net_value_in_euro,
FROM item i, (SELECT * FROM TABLE(X.GetExchangeRate())) rt
WHERE i.doc_currency = rt.currency_cd;
TYPE c0_type IS TABLE OF c0%ROWTYPE;
items c0_type;
BEGIN
OPEN c0;
LOOP
FETCH c0 BULK COLLECT
INTO items LIMIT batchsize;
EXIT WHEN items.COUNT = 0;
FORALL i IN items.FIRST .. items.LAST SAVE EXCEPTIONS
INSERT INTO detail_items VALUES items (i);
END LOOP;
CLOSE c0;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
END;
END X;
Please review.