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.
Best Answer
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
And then you can use it in SQL with TABLE operator, for example: