Thursday, August 20, 2009

Dynamic IN clause

Hi ALl,
This is one good ways of writing dynamic in clause..

if you have a select stament where you are uing in clause to restrict the data.if you are not sure on the inclause statment
what you will do..
what i used to do is make a dynamica sql build inclause on run time and then execute it ..But there is another smart way of building it dynamically..

Example1:

cursor c_cust_info is
select * from hz_cust_accounts where customer_class_code in ('CEMP','CEMP20','INDIV')

for making the inclause to by dynamic


cusror c_cust_info(c_in_statement varchar2)
select * from hz_cust_accounts where instr(c_in_statement,customer_class_code)>0

and open the cusor with the in clause you require..like

for rec in c_cust_info('CEMP,CEMP20') loop

end loop;