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
– Alex Poole, Commented Jan 25, 2018 at 9: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
– user2671057, Commented Jan 25, 2018 at 9: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
– Alex Poole, Commented Jan 25, 2018 at 9:46

Alex Poole · Accepted Answer · 2018-01-25 10:27:25Z

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.

Collectives™ on Stack Overflow

Pass an array to bind variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related