SQL & PL/SQL

Dear all,

I need to get multiple code values and put it into a variable which later need to pass into the where clause of an sql. But i am not getting any results even i pass those values in the variable of an where clause: below is my Procedure:

declare

TYPE crMain_record is RECORD (
     v_code             dummy.v_code%type,
     n_no               dummy.n_no%type,
	 v_no				dummy1.v_no%type,

     v_trans            dummy2.v_trans%type,
     v_rem              dummy2.v_rem%type,
     t_amount           dummy2.t_amount%type,
     c_amount           dummy2.c_amount%type,
     trans_date         dummy2.trans_date%type,
     user_1             dummy2.user_1%type,
     applicable         dummy2.applicable%type,

     vou_no             dummy3.vou_no%type,
     n_amount           dummy3.n_amount%type,
      d_voer            dummy3.d_voer%type
		 );


i  crMain_record;
TYPE crMain_cursor IS REF CURSOR;
cr_Main    crMain_cursor;
--------------------------
   --variables

   lb_first_record          boolean:= true;
   ln_count                 number:=0;

 lv_character varchar2(32767):= '('||''''||'COMMIS'||''''||' , '||''''||'AGY BUILDING BENS'||''''||')';
-- lv_character varchar2(32767):= ''''||'COMMIS'||''''||','||''''||'AGY BUILDING BENS'||'''';
BEGIN
open cr_main for

select distinct c.v_code,
       b.n_no,
       b.v_no,
       b.trans,
       b.v_rem,
       decode(v_cd_flag, 'D', b.amount) t_amount,
       decode(v_cdflag, 'C', b.amount) c_amount,
       b.trans_date,
       b.user1,
       d.applicable,
       a.vou_no,
       a.n_amount,
       a.d_voer
from   dummy3 a, dummy2 b, dummy c,dummy4 d
where  a.n_seq (+) = b.n_seq
and    a.n_no (+)             = b.n_no
and    b.n_no                 = c.n_no
and upper(b.v_code)= upper(d.v_code)
and c.v_code ='A0004'
--and    decode(:fn_code,'ALL','ALL',c.v_code) = :fn_code

Here is the where clause

and upper(d.v_code) in lv_character

Here is the variable "lv_character" where i pass the variable values..
And lv_character values are commung from a setup table

--and    decode(:fn__code,'ALL','ALL', b.v_code) = :fn_code
--and    trunc(b.d_date) between :fn_date_from and :fn_date_to
and    trunc(b.d_date) between '25/07/2011' and '30/07/2011';

	 --MAIN LOOP
   LOOP
--     message(lv_character);
       FETCH cr_main INTO i;
       EXIT WHEN cr_main%NOTFOUND;

			 IF lb_first_record = true then
			    lb_first_record := false;

          	 END IF;

       --------------------------------------------------
       ln_count := ln_count + 1;
			 dbms_output.put_line(
			                  i.v_agent_code||
   		        			    ','||'"'||i.n_no||'"'||
   		        			    ','||'"'||i.v_no||'"'||
   		        			    ','||'"'||i.trans||'"'||
   		        			    ','||'"'||i.v_rem||'"'||
   									    ','||to_char(i.t_amount,'999999999.99') ||
   									    ','||to_char(i.c_amount,'999999999.99') ||
  		        			    ','||to_char(i.trans_date,'dd-mon-rrrr') ||
  		        			    ','||'"'||i.user1||'"'||           
   		        			    ','||'"'||i.applicable||'"'||
   		        			    ','||'"'||i.vou_no||'"'||
   									    ','||to_char(i.n_amount,'999999999.99') ||
   		        			    ','||to_char(i.d_voer,'dd-mon-rrrr') ||
   									    ',');
   END LOOP;
   CLOSE cr_main;

END;

"lv_character" is going to hold the multiple code values which i need to pass into whare clause of the above SQL:
the totlal number of these mulitipe codes can be more then 50..
And lv_character values are commung from a setup table

lv_character varchar2(32767):= '('||''''||'COMMIS'||''''||' , '||''''||'AGY BUILDING BENS'||''''||')';

--And lv_character values are commung from a setup table.

where "lv_character" holdes multipe code values...
And lv_character values are commung from a setup table

and upper(d.v_code)in lv_character

if the is any other way to do this plz reply...

Thanks for Help...

Edited by: user3029023 on Sep 18, 2012 9:50 AM

Edited by: user3029023 on Sep 18, 2012 9:52 AM

Edited by: user3029023 on Sep 18, 2012 11:27 AM

Edited by: user3029023 on Sep 18, 2012 11:29 AM