2

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

4

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.

7
  • 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 ? – Pantea May 15 at 13:28
  • 1
    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". – mustaccio May 15 at 14:08
  • 2
    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. – Andrew Sayer May 15 at 14:51
  • 1
    @AndrewSayer citation needed – mustaccio May 15 at 19:15
  • 1
    @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. – Andrew Sayer May 15 at 20:56
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.

3
  • 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)" – Pantea May 15 at 12:37
  • 1
    I've edited in an explanation, let me know if it's still not clear – Andrew Sayer May 15 at 13:28
  • 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 – Pantea May 15 at 13:58
-1

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

1
  • 3
    You can still call it with null as the argument values – Andrew Sayer May 15 at 12:26

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.