Thursday, September 21, 2006

Oracle SELECT with an array using an IN

declare

   -- CREATE OR REPLACE TYPE PROD_TYPES.TYPE_NUMBER_ARRAY AS TABLE OF NUMBER
  nt_src      prod_types.type_number_array := PROD_TYPES.TYPE_NUMBER_ARRAY();    -- Constructor     
  nt_dest     prod_types.type_number_array := PROD_TYPES.TYPE_NUMBER_ARRAY();    -- Constructor     

begin

  -- Test data (no order)
  select
    product_version_id
  bulk collect
    into
      nt_src
  from product_version v
  where v.product_version_id between 138120 and 140000;

  -- select * from table(sys.dbms_debug_vc2coll(1,2,'a')) order by 1 desc
 
  -- Data ordered
  select
    product_version_id
  bulk collect
    into 
      nt_dest
  from
    product_version
  where
    product_version.product_version_id in (select column_value from table(cast(nt_src AS prod_types.type_number_array)))
  order by
    product_version.expiration_date2 desc,
    product_version.base_price desc,
    product_version.expiration_date2 desc;

  for i in nt_dest.first .. nt_dest.last loop
    dbms_output.put_line(to_char(nt_dest(i)));
  end loop;

  exception
    when others then
      dbms_output.put_line('sqlerrm : '||sqlerrm);
end;

4 comments:

Ziya ┼×enol said...

Very useful sample.Tnx

Luca said...

you are a life saver!

ranjithredy said...

give me a sample code IN HIBERNATE to select multiple records based on ids like....select *from table where in(,,,,)..

send it to my mail id : abbareddy8ranjith@gmail.com

Jason Vogel said...

I haven't done this in Hibernate. I don't have this environment set up at present.