January 10, 2012

Example of dynamic SQL

This is a Example of dynamic SQL. You can wrtie following code to check how dynamic sql works.
 column1 := 'custno';  
 column2 := 'custname';  
 t_table1 := 'cust_test';  
 t_table2 := 'cust';  
 testval1 cust.custno%type;  
 t_valeu2 cust.custname%type;  
 t_resultestval1 cust.custno%type;  
 t_resultestval2 cust.custname%type;  
 execsqlcodetest := 'select '||column1||','||column2||' from '||table2||'   
 where ('||column1||','||column2||') in (select '||column1||','||column2||'   
 from '||table1|| ' where '||column1||' like %:b1%   
 and '||column2||' like %:b2%)';  
 execute immediate execsqlcodetest using testval1, testval2 into   
 t_resultestval1, t_resultestval2;