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:
Very useful sample.Tnx
you are a life saver!
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
I haven't done this in Hibernate. I don't have this environment set up at present.
Post a Comment