How to use a table type in a SELECT FROM statement?

Question

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.

Answer 1

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;
/

Answer 2

Prior to Oracle 12C you cannot select from PL/SQL-defined tables, only from tables based on SQL types like this:

CREATE OR REPLACE TYPE exch_row AS OBJECT(
currency_cd VARCHAR2(9),
exch_rt_eur NUMBER,
exch_rt_usd NUMBER);


CREATE OR REPLACE TYPE exch_tbl AS TABLE OF exch_row;

In Oracle 12C it is now possible to select from PL/SQL tables that are defined in a package spec.