This question is more or less the same as this
In the package header :
Declared the following row type:
TYPE exch_row IS RECORD(
currency_cd VARCHAR2(9),
exch_rt_eur NUMBER,
exch_rt_usd NUMBER);
And this table type:
TYPE exch_tbl IS TABLE OF exch_row INDEX BY BINARY_INTEGER;
Added a variable:
exch_rt exch_tbl;
In the package body:
Fill this table variable with some data.
In a procedure in the package body:
I want to use the following statement:
CURSOR c0 IS
SELECT i.*, rt.exch_rt_eur, rt.exch_rt_usd
FROM item i, exch_rt rt
WHERE i.currency = rt.exchange_cd
How to do this in Oracle ?
Notes
Actually I'm looking for the 'Table Variable' solution in MSSQL:
DECLARE @exch_tbl TABLE
(
currency_cd VARCHAR(9),
exch_rt_eur NUMBER,
exch_rt_usd NUMBER)
)
And use this Table Variable inside my StoredProcedure.
In SQL you may only use table type which is defined at schema level (not at package or procedure level), and index-by table (associative array) cannot be defined at schema level. So - you have to define nested table like this
create type exch_row as object (
currency_cd VARCHAR2(9),
exch_rt_eur NUMBER,
exch_rt_usd NUMBER);
create type exch_tbl as table of exch_row;
And then you can use it in SQL with TABLE operator, for example:
declare
l_row exch_row;
exch_rt exch_tbl;
begin
l_row := exch_row('PLN', 100, 100);
exch_rt := exch_tbl(l_row);
for r in (select i.*
from item i, TABLE(exch_rt) rt
where i.currency = rt.currency_cd) loop
-- your code here
end loop;
end;
/