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

by

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

More Related Contents:

  1. How to get records randomly from the oracle database?
  2. Oracle date difference to get number of years
  3. ORA-01830: date format picture ends before converting entire input string / Select sum where date query
  4. ANYONE CAN TELL ME THAT WHY THIS CODE IS GIVING ME AN ERROR OF TABLE OT CLUSTER KEY WORD IS MISSING? [closed]
  5. How do I escape a reserved word in Oracle?
  6. How to put more than 1000 values into an Oracle IN clause [duplicate]
  7. ORA-30926: unable to get a stable set of rows in the source tables
  8. ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
  9. Fetch Oracle table type from stored procedure using JDBC
  10. TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
  11. “Safe” TO_NUMBER()
  12. When or Why to use a “SET DEFINE OFF” in Oracle Database
  13. error: ORA-65096: invalid common user or role name in oracle
  14. Oracle insert if not exists statement
  15. Error : ORA-01704: string literal too long
  16. How can I select from list of values in Oracle
  17. Oracle client ORA-12541: TNS:no listener [closed]
  18. Reason why oracle is case sensitive?
  19. Faster alternative in Oracle to SELECT COUNT(*) FROM sometable
  20. SQL*Plus how to accept text variable from prompt?
  21. How to find difference b/w TIMESTAMP format values in Oracle?
  22. SQL Fiddle Output Error
  23. oracle PL/SQL how to calculate range ip for IPv6 cidr
  24. Trying to export a Oracle via PL/SQL gives a date of 0000-00-00
  25. Oracle: how to add minutes to a timestamp?
  26. How to drop multiple interval partitions based on date?
  27. How to list active / open connections in Oracle?
  28. How to generate a version 4 (random) UUID on Oracle?
  29. Find out the history of SQL queries
  30. Getting Error – ORA-01858: a non-numeric character was found where a numeric was expected

Leave a Comment