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

User · 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

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[oracle] How to use a table type in a SELECT FROM statement?

Examples related to oracle

Examples related to select

Examples related to row