1

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

4
  • 1
    and can you tell us what problems do you have and what do you want to achieve? – miracle173 Jul 28 '20 at 17:01
  • @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. – Pantea Jul 29 '20 at 9:09
  • 1
    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? – miracle173 Jul 29 '20 at 21:41
  • @miracle173 . I updated my question and added the error code . hope this will help :) – Pantea Aug 1 '20 at 7:32
1

Use DBMS_SQL.

The fine manual has plenty of examples.

Sample

TBD

5
  • 1
    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. – Michael Kutz Jul 27 '20 at 9:48
  • 1
    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 . – Pantea Jul 27 '20 at 11:32
  • 1
    Did you spit out the query before you parsed it? What problem/error code? – Michael Kutz Jul 27 '20 at 13:01
  • 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); – Pantea Jul 28 '20 at 6:31
  • 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. – René Nyffenegger Jan 20 at 10:15
1

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

1
  • Thanks but could you please give an example? Cause could not follow. – Pantea Jul 27 '20 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.