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.

As others have stated you can't do this with types declared in the package. It's not clear why you're doing this rather than using a real table to hold the exchange rates, or what you want to do with the selected data. Is this in a cursor? Are you really looking for data for a specific currency from your select? Are you manipulating an item value based on an exchange rate? More context might help inspire alternative approaches. — Alex Poole, Mar 02 '11 at 11:48

score 23 · Answer 1 · answered Mar 02 '11 at 09:49

23

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

answered Mar 02 '11 at 09:49

Marcin Wroblewski

3,491
18
27

is there a way to use table type in functions? – sabertooth1990 Apr 04 '17 at 17:25
yes. you can use table type inside a function exactly the same way as in the anonymous PL/SQL block from this answer – Marcin Wroblewski Apr 05 '17 at 07:46
actually you can use almost all described objects in package specifications/body. The only thing - you cannot create local type as objects. Only as records is allowed for packages – t v Aug 20 '19 at 13:14

Tony Andrews · Answer 2 · 2014-09-01T09:51:41.527

17

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.

edited Sep 01 '14 at 09:51

answered Mar 02 '11 at 09:36

Tony Andrews

129,880
21
220
259

If I am not using all columns of an exsisting table, do I need to declare a type anyways? – zygimantus Nov 21 '16 at 12:07
@zygimantus Sorry but I don't understand your question, can you explain more? – Tony Andrews Nov 21 '16 at 12:12
For example can I declare this kind of type `TYPE my_type IS TABLE OF my_table%ROWTYPE` instead using of your descibed custom columns? – zygimantus Nov 21 '16 at 12:17
1

In PL/SQL yes you can - and can select from a variable of that type also if using 12C. – Tony Andrews Nov 21 '16 at 12:38
Ok. Thanks for info. – zygimantus Nov 21 '16 at 13:09

Alex Poole · Answer 3 · 2011-03-02T17:54:43.007

You can't do it in a single query inside the package - you can't mix the SQL and PL/SQL types, and would need to define the types in the SQL layer as Tony, Marcin and Thio have said.

If you really want this done locally, and you can index the table type by VARCHAR instead of BINARY_INTEGER, you can do something like this:

-- dummy ITEM table as we don't know what the real ones looks like
create table item(
    item_num number,
    currency varchar2(9)
)
/   

insert into item values(1,'GBP');
insert into item values(2,'AUD');
insert into item values(3,'GBP');
insert into item values(4,'AUD');
insert into item values(5,'CDN');

create package so_5165580 as
    type exch_row is record(
        exch_rt_eur number,
        exch_rt_usd number);
    type exch_tbl is table of exch_row index by varchar2(9);
    exch_rt exch_tbl;
    procedure show_items;
end so_5165580;
/

create package body so_5165580 as
    procedure populate_rates is
        rate exch_row;
    begin
        rate.exch_rt_eur := 0.614394;
        rate.exch_rt_usd := 0.8494;
        exch_rt('GBP') := rate;
        rate.exch_rt_eur := 0.9817;
        rate.exch_rt_usd := 1.3572;
        exch_rt('AUD') := rate;
    end;

    procedure show_items is
        cursor c0 is
            select i.*
            from item i;
    begin
        for r0 in c0 loop
            if exch_rt.exists(r0.currency) then
                dbms_output.put_line('Item ' || r0.item_num
                    || ' Currency ' || r0.currency
                    || ' EUR ' || exch_rt(r0.currency).exch_rt_eur
                    || ' USD ' || exch_rt(r0.currency).exch_rt_usd);
            else
                dbms_output.put_line('Item ' || r0.item_num
                    || ' Currency ' || r0.currency
                    || ' ** no rates defined **');
            end if;
        end loop;
    end;
begin
    populate_rates;
end so_5165580;
/

So inside your loop, wherever you would have expected to use r0.exch_rt_eur you instead use exch_rt(r0.currency).exch_rt_eur, and the same for USD. Testing from an anonymous block:

begin
    so_5165580.show_items;
end;
/

Item 1 Currency GBP EUR .614394 USD .8494
Item 2 Currency AUD EUR .9817 USD 1.3572
Item 3 Currency GBP EUR .614394 USD .8494
Item 4 Currency AUD EUR .9817 USD 1.3572
Item 5 Currency CDN ** no rates defined **

Based on the answer Stef posted, this doesn't need to be in a package at all; the same results could be achieved with an insert statement. Assuming EXCH holds exchange rates of other currencies against the Euro, including USD with currency_key=1:

insert into detail_items
with rt as (select c.currency_cd as currency_cd,
        e.exch_rt as exch_rt_eur,
        (e.exch_rt / usd.exch_rt) as exch_rt_usd
    from exch e,
        currency c,
        (select exch_rt from exch where currency_key = 1) usd
    where c.currency_key = e.currency_key)
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
join rt on i.doc_currency = rt.currency_cd;

With items valued at 19.99 GBP and 25.00 AUD, you get detail_items:

DOC DOC_CURRENCY NET_VALUE         NET_VALUE_IN_USD  NET_VALUE_IN_EURO
--- ------------ ----------------- ----------------- -----------------
1   GBP          19.99             32.53611          23.53426
2   AUD          25                25.46041          18.41621

If you want the currency stuff to be more re-usable you could create a view:

create view rt as
select c.currency_cd as currency_cd,
    e.exch_rt as exch_rt_eur,
    (e.exch_rt / usd.exch_rt) as exch_rt_usd
from exch e,
    currency c,
    (select exch_rt from exch where currency_key = 1) usd
where c.currency_key = e.currency_key;

And then insert using values from that:

insert into detail_items
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
join rt on i.doc_currency = rt.currency_cd;

Thanks for the detailed information, please see my updated --notes-- in the question. — Stef Heyenrath, Mar 02 '11 at 14:49
@Stef: I think this is the closest equivalent, but doesn't work exactly the same way. Still not sure why you wouldn't use a regular table for this, but you could also look at a global temporary table if you want different values for different sessions. I think a pipelined function would have the same issues with the type. — Alex Poole, Mar 02 '11 at 15:06
There are some links on [this earlier question](http://stackoverflow.com/questions/670461/does-oracle-have-an-equivalent-of-sql-servers-table-variables) but doesn't really address what you're trying to do. — Alex Poole, Mar 02 '11 at 15:21

score 1 · Answer 4 · answered Mar 02 '11 at 15:44

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.

I'm not sure how this is a solution when it doesn't compile: you have an extra comma after `AS net_value_in_euro`, and `batchsize` isn't defined. And `doit` isn't declared in the package header so it can't be run. I think this should have been an edit to the question rather than an answer. But why are you doing this in PL/SQL at all? This could be done in a single `update` statement. — Alex Poole, Mar 02 '11 at 17:08
Updated my answer with an `insert` that creates the same `detail_items` records as the `doit` procedure. — Alex Poole, Mar 02 '11 at 17:49
@Alex, it doesn't compile, because it's only a hint how to create it. Maybe that's confusing. The reasons for me are: 1) No view: I don't want to 'pollute' the database with a those small helper views. 2) I want to use PL/SQL because of logging the batch errors and because of the big volume of data that has to be processed. — Stef Heyenrath, Mar 03 '11 at 06:54
OK, if you're wedded to PL/SQL then this will work. You could still use the select part from my insert version (from `with rt as`) as your cursor and avoid the PL/SQL table and function, which seem to be adding unnecessary complexity. But I'm straying further form the original question. — Alex Poole, Mar 03 '11 at 07:33

score 0 · Answer 5 · answered Aug 20 '19 at 12:57

In package specs you can do all you mentioned but not sure about INDEX BY BINARY_INTEGER;

In package body:

initialize the table in declarations:

exch_rt exch_tbl := exch_tbl();

in order to add record to the local collection, in begin - end block you can do:

exch_rt.extend;
                                one_row.exch_rt_usd := 2;
                                one_row.exch_rt_eur := 1;
                                one_row.currency_cd := 'dollar';
                                exch_rt(1) := one_row; -- 1 - number of row in the table - you can put a variable which will be incremented inside a loop

in order to get data from this table , inside package body you can use:

select exch_rt_usd, exch_rt_eur, currency_cd from table(exch_rt)

enjoy!

P.S. sorry for a late answer :D

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

5 Answers5

Linked