Oracle SELECT with an array using an IN

SELECT with an array with 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;

Powered by Qumana

Advertisement