Pass an array to bind variables

Question

I have a table that contains queries, for example:

select text from queries;

    TEXT
1   select item from items where item_no between :low_item_no and :high_item_no and description <> :irellevant

The queries already contains the place holders for the bind variables.

The values themselves exists in variables table:

select * from vars;

    ID  NAME               VALUE
1   1   low_item_no        100
2   2   high_item_no       300
3   3   irellevant_desc    old

I have a package that takes the query and execute it with

execute immediate statement

but how do I bind those variables?

I don't know how much variables I have in such query, it's not static.

I wish to have a way to do something like that:

Execute immedaite my_query_str using v_array_of_vars;

Until now I don't know of a way to do something like that, only with list of variables for example:

Execute immedaite my_query_str using v_1, v_2, v_3;

Thanks!

How would you populate the array if you don't know how many (or which) values it needs from that table? Are the bind values also being passed in; or the names; or are they looked up somewhere else (e.g. another column in `queries`)? — Alex Poole, Jan 25 '18 at 09:27
Thanks for your comment. there is a query_id column in both tables (PK in queries and FK in vars). That's how I know which var belongs to each query. — user2671057, Jan 25 '18 at 09:41
So you are only passed a query ID, and you have to look up the query text and the relevant variables in the procedure? In your example the third bind variable name doesn't match the `vars.name`. Will they match really - always? (If not... are the bind names always in ID order int he queries; and only appear once?) — Alex Poole, Jan 25 '18 at 09:46

Alex Poole · Answer 1 · 2018-01-25T10:27:25.093

I don't think you can do this with execute immediate as too much is unknown at compile time, so you'll have to use the dbms_sql package instead.

Here's a quick demo that gets the query and variables based on a common query ID. This assumes that the values in vars.name actually match the bind variable names in queries.text, and I haven't included any checks or error handling for that or other potential issues, or dealt with multiple select-list items or data types - just the basics:

declare
  my_query_str queries.text%type;
  my_cursor pls_integer;
  my_result pls_integer;
  my_col_descs dbms_sql.desc_tab2;
  my_num_cols pls_integer;
  my_item items.item%type;

begin
  select text into my_query_str from queries where query_id = 42;
  dbms_output.put_line(my_query_str);

  -- open cursor
  my_cursor := dbms_sql.open_cursor;
  -- parse this query
  dbms_sql.parse(my_cursor, my_query_str, dbms_sql.native);
  -- bind all variables by name; assumes bind variables match vars.name
  for r in (select name, value from vars where query_id = 42) loop
    dbms_output.put_line('Binding ' || r.name || ' || with <' || r.value ||'>');
    dbms_sql.bind_variable(my_cursor, r.name, r.value);
  end loop;

  my_result := dbms_sql.execute(my_cursor);
  dbms_output.put_line('execute got: ' || my_result);

  dbms_sql.describe_columns2(my_cursor, my_num_cols, my_col_descs);

  dbms_sql.define_column(my_cursor, 1, my_item, 30); -- whatever size matches 'item'

  -- fetch and do something with the results
  while true loop
    my_result := dbms_sql.fetch_rows(my_cursor);
    if my_result <= 0 then
      exit;
    end if;

    dbms_sql.column_value(my_cursor, 1, my_item);
    dbms_output.put_line('Got item: ' || my_item);
  end loop;

  dbms_sql.close_cursor(my_cursor);
end;
/

You don't seem to really need an array; but if you wanted to you could create and populate an associative array as name/value pairs and then use that fir the binds.

This is just a starting point; you may have to deal with an unknown number and/or types of columns being returned, though if that's the case processing them meaningfully will be a challenge. Perhaps you need to return the result of the query as a ref cursor, which is even simpler; demo using the SQL*Plus variable and print commands:

var rc refcursor;

declare
  my_query_str queries.text%type;
  my_cursor pls_integer;
  my_result pls_integer;

begin
  select text into my_query_str from queries where query_id = 42;
  dbms_output.put_line(my_query_str);

  -- open cursor
  my_cursor := dbms_sql.open_cursor;
  -- parse this query
  dbms_sql.parse(my_cursor, my_query_str, dbms_sql.native);
  -- bind all variables by name; assumes bind variables match vars.name
  for r in (select name, value from vars where query_id = 42) loop
    dbms_output.put_line('Binding ' || r.name || ' || with <' || r.value ||'>');
    dbms_sql.bind_variable(my_cursor, r.name, r.value);
  end loop;

  my_result := dbms_sql.execute(my_cursor);
  dbms_output.put_line('execute got: ' || my_result);

  :rc := dbms_sql.to_refcursor(my_cursor);
end;
/

print rc

Notice you don't close the cursor inside the PL/SQL block in this scenario.

You could also convert to a ref cursor and then fetch from that within your procedure - there's a bulk-collect example in the docs - but again you'd need to know the number and types of the select-list items to do that.

Pass an array to bind variables

1 Answers1