Oracle SELECT with an array using an IN

Oracle SELECT with an array using an IN

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

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