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.

• Pantea: Thanks for your brilliant answer , I have a question in here , when we do not provide input parameter "p_item_number" , the original query is this?? --> "select * from test_table where p_item_number is null or p_item_number = null " ??, I mean what is the exact query when we do not provide the input parameters ?
• mustaccio: You can not call a procedure with fewer parameters than defined, not if you did not specify defaults anyway. So "not provided" here means "is null".
• Andrew Sayer: Note that this will cause difficulty for the optimizer as it will have to produce a plan which is legal in all circumstances (a full table scan is the only possibility). Dynamic SQL is recommended for these sorts of requirements.
• mustaccio: @AndrewSayer citation needed
• Andrew Sayer: @mustaccio Demo here dbfiddle.uk/… As you can see, even hinting it to use an index will not help. I'll admit there is a possibility for an or expansion transformation, but you're not going to get it with 4 different ORs without doing it yourself.

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.

• Pantea: That was great ,thanks , could you explain a little bit more about what really happens when we do not supply other input parameters? I mean what happens in this part exactly "and (1=1 or :1 is null)"
• Andrew Sayer: I've edited in an explanation, let me know if it's still not clear
• Pantea: Thanks , now your answer is completely clear , I'm now a bit confused with about the second answer , I think I have forgot some basic concepts about computer science and some courses we had in our universities , I don't know :D

Answer 3

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

• Andrew Sayer: You can still call it with null as the argument values