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

Advertisements
This entry was posted in Oracle. Bookmark the permalink.

3 Responses to Oracle SELECT with an array using an IN

  1. Toni says:

    Gracias Jason, me ha servido de mucho.

  2. Idetrorce says:

    very interesting, but I don’t agree with you
    Idetrorce

  3. Thanks for information.
    many interesting things
    Celpjefscylc

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s