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);

• miracle173: and can you tell us what problems do you have and what do you want to achieve?
• Pantea: @miracle173.I've changed my code to make it simpler and I've also added some notes to make my point clearer. I want to be able to write neat and professional codes and one of the ways is to ask good questions . Thanks for your concern.
• miracle173: you still didn't describe your problem. In a comment you mention that you " have problem in this part of the code : o_result := dbms_sql.execute(c);". But you didn't tell us what kind of problem you encounter. Did you get an error message? If so, what error message?
• Pantea: @miracle173 . I updated my question and added the error code . hope this will help :)

Answer 1

Use DBMS_SQL.

The fine manual has plenty of examples.

Sample

TBD

• Michael Kutz: To Be Determined (TBD) is a place holder for actual code example because I havent had my morning coffee. In the meantime, I leave it as an exercise for the student to read all about DBMS_SQL in the fine manual.
• Pantea: I've changed my code after reading the manuals and related question . The problem still remains . I was wondering if you could take a look at my new code .
• Michael Kutz: Did you spit out the query before you parsed it? What problem/error code?
• Pantea: I've changed my code and I used dbms_sql as you said . but I'm not sure whether I'm in the correct path! I have problem in this part of the code : o_result := dbms_sql.execute(c)
• René Nyffenegger: While it is true that the fine manual has plenty of examples, there is none (at least none that I was able to find) that addresses the problem of the OP. In addition, there seems to be no documented functionality in dbms_sql that allows to determine the number of bind variables in an arbitrary SQL statement.

Answer 2

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

• Pantea: Thanks but could you please give an example? Cause could not follow.