How to solve error "ORA:01-006 :bind variable does not exist" when working with bind variables?

Question

I have a table with below structure and data :

create table TEST_TABLE
(
  item_number NUMBER,
  item_name   VARCHAR2(50),
  item_col    VARCHAR2(50),
  item_pol    VARCHAR2(50)
)

Sample data:

item_number |  item_name|  item_col |  item_pol
------------------------------------------------
     1      |   blue    |     b     |   c
     2      |   red     |     d     |   a
     3      |   black   |     e     |   a
     4      |   yellow  |     d     |   b

This is my sample procedure with in which I'm trying to use bind variables .

create or replace procedure test_bind_variable(res         out sys_refcursor,
                                               item_number varchar2,
                                               item_name   varchar2,
                                               item_col    varchar2,
                                               item_pol    varchar2) is

  qry varchar2(8000);

begin

  qry := 'select * from test_table where 1=1 ';

  if (item_number is not null) then
    qry := qry || ' and item_number = :1  ';
  end if;

  if (item_name is not null) then
    qry := qry || ' and item_name = :2 ';
  end if;

  if (item_col is not null) then
    qry := qry || ' and item_col= :3 ';
  end if;

  if (item_pol is not null) then
    qry := qry || ' and item_pol = :4 ';
  end if;

  dbms_output.put_line(qry);

  open res for  qry
    using item_number, item_name, item_col, item_pol;

end;

The problem is that when all Input parameters have a values , the procedure works properly , without any error , but when only one or two parameters have value , I receive this error : ORA-01006: bind variable does not exist,. How can I solve this problem? Some parameters might have value and some may not .

Thanks in advance

Answer 1

You can completely avoid dynamic SQL (you also should never name PL/SQL variables the same as table columns):

create or replace procedure test_bind_variable(res         out sys_refcursor,
                                               p_item_number varchar2,
                                               p_item_name   varchar2,
                                               p_item_col    varchar2,
                                               p_item_pol    varchar2) is

  qry varchar2(8000);

begin

  open res for select * from test_table where
    (p_item_number is null or p_item_number = item_number) and
    (p_item_name is null or p_item_name = item_name) and
    (p_item_col is null or p_item_col = item_col) and
    (p_item_pol is null or p_item_pol = item_pol)

end;

PS. Make sure you take the operator precedence into account, unlike me.

Answer 2

You can't dynamically send bind variables to the dynamic query. Instead, you'll have to code in the bind variables in the same order no matter what, you can use a trick so that they're ignored though.

create or replace procedure test_bind_variable(res         out sys_refcursor,
                                               item_number varchar2,
                                               item_name   varchar2,
                                               item_col    varchar2,
                                               item_pol    varchar2) is

  qry varchar2(8000);

begin

  qry := 'select * from test_table where 1=1 ';

  if (item_number is not null) then
    qry := qry || ' and item_number = :1  ';
  else
    qry := qry || ' and (1=1 or :1 is null) ';
  end if;

  if (item_name is not null) then
    qry := qry || ' and item_name = :2 ';
  else
    qry := qry || ' and (1=1 or :2 is null) ';
  end if;

  if (item_col is not null) then
    qry := qry || ' and item_col= :3 ';
  else
    qry := qry || ' and (1=1 or :3 is null) ';
  end if;

  if (item_pol is not null) then
    qry := qry || ' and item_pol = :4 ';
  else
    qry := qry || ' and (1=1 or :4 is null) ';
  end if;

  dbms_output.put_line(qry);

  open res for  qry
    using item_number, item_name, item_col, item_pol;

end;

This works because

(1=1 or :1 is null)

Will always return true (1=1), and it still allows you to pass in a bind variable in the correct place. Short circuit evaluation will be triggered so that the optimizer can safely ignore this when working out the best plan.

Answer 3

You shouldn't be able to call that procedure without supplying all the parameters as you have no defaults.