How to use ‘Bind Variables’ in a ‘Dynamic Query ‘ when the exact number of variables are not known


I have a procedure in which I’m using Dynamic SQL. The input parameteri_tables is a string which is a concatenation of the name of some tables . It might be one these :

1)All tables test_table1,test_table2,test_table3.

2)Only two tables , for instance test_table2,test_table3 .

3)Nothing .So NULL will pass to the procedure.

I’ve read about Bind variable and the significant role it has in preventing injection and improving performance. I want to use bind variable in my procedure but there is one things I don’t know how to handle :

As you can see we do not know the exact number of variables.It might be one , two , three or nothing. Depending on The input parameteri_tables.

   create or replace procedure bind_variable_test(i_tables varchar2,                                                   i_cid    number,                                                   o_result out sys_refcursor) is     actual_query varchar2(1000) := '';    begin         -- this is the base query     actual_query := 'select *                 from z_test_a t1                   inner join z_test_b t2                 on t1.id = t2.id';      -- check input parameter " i_tables "       if i_tables like '%test_table1%' then              actual_query := actual_query || '  inner join test_table1 t3 on t3.id = t1.id and                              t3.cid = ' || i_cid;      end if;       if i_tables like '%test_table2%' then             actual_query := actual_query || '  inner join test_table2 t4 on t4.id = t1.id and                              t4.cid = ' || i_cid;      end if;       if i_tables like '%test_table3%' then             actual_query := actual_query || '  inner join test_table3 t5 on t5.id = t1.id and                    t5.cid = ' || i_cid;      end if;      open o_result for actual_query;     end;