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

Question

I have a procedure in which I'm using Dynamic SQL(variable actual_query) and I'm generating this query based on the input parameter i_tables which is a concatenation of the name of some tables. It has one of these forms:

1. All tables `test_table1,test_table2.

2. 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 and I want to use it in my procedure.The problem is that I do not know how exactly I should change my procedure to use them specially in this situation when number of variables are not known. When you know the exact number of variables, you write execute immediate actual_query using var1,var2 and you know you will exactly have 2 variables.

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;


-- debug results
dbms_output.put_line(actual_query); 

-- execute    
open o_result for actual_query;
end;

After reading DBMS_SQL manual , I tried to edit my code and I did this:

create or replace procedure z_bind_variable_test(i_tables varchar2,
                                             i_cid    number,
                                             o_result out sys_refcursor)       is
actual_query varchar2(1000) := '';
c            pls_integer;
begin
-- this is the base query
actual_query := 'select * 
              from z_test_a t1 
               inner join z_test_b t2  
            on t1.c_num = t2.c_num ';

-- check input parameter " i_tables "
if i_tables like '%test_table1%' then
  actual_query := actual_query || ' ' || 'inner join test_table1 t3 ' ||
                'on t3.C_NUM = t1.C_NUM ' || 'and t3.cid = :c_id';
end if;

if i_tables like '%test_table2%' then
  actual_query := actual_query || ' ' || 'inner join test_table2 t4 ' ||
                'on t4.C_NUM = t1.C_NUM ' || 'and t4.cid = :c_id ';
end if;

-- get cursor
c := dbms_sql.open_cursor();

-- parse the SQL
dbms_sql.parse(c, actual_query, DBMS_SQL.NATIVE);

-- using bind variables
dbms_sql.bind_variable(c, ':c_id', i_cid);

-- execute
o_result := dbms_sql.execute(c);

-- close the cursor
dbms_sql.close_cursor(c);
end;

But it just won't work! I want to learn how to use bind variables correctly but don't know whether I'm in the right path.

Update : The error I get is PLS-00382:Expression is of wrong type. And the problem is for this part o_result := dbms_sql.execute(c);

Answer 1

Use DBMS_SQL.

The fine manual has plenty of examples.

Sample

TBD

Answer 2

split the variable and fetch each element. That is, use a cursor