Oracle - Can A Bind Variable Be Put In In() Clause If The Variable Can Be Empty

Question

In Oracle, can a bind variable be put in IN() clause if it can be empty? Asking because in the SQL if Oracle see IN() without any data in it, it shows error

ORA-00936: missing expression.

Edit: Failed to mention that no PL/SQL can be used...

Edit2: Also failed to mention that the variable is either in format 1,2,3 or empty string. The empty string cannot be replaced with NULL.

var can only be 1,2,3 for example or nothing (not null) select * from table where code in (nvl(,null)) would result in error — Yasen
– Yasen, Commented Sep 13, 2013 at 6:57
Define 'nothing' - why isn't that null? Empty string ('') and null are the same thing in Oracle. If you have a bind varible in the query then it won't disappear. Maybe you should show your code so we can see what you really mean, and any errors you've got so far. — Alex Poole
– Alex Poole, Commented Sep 13, 2013 at 7:07
There's a PHP code which replaces the variable with either 1,2,3 or empty string which is not surrounded by quotes (neither null, nor empty string surrounded by quotes ''). This makes it impossible I guess. Thanks for the efforts anyway and sorry for not explaining it good enough. — Yasen
– Yasen, Commented Sep 13, 2013 at 7:34

Rene · Accepted Answer · 2013-09-12 13:53:11Z

1

Seems to be working:

declare

   l_statement varchar2(32767);

begin
   l_statement := 'select * from user_tables where table_name in (:a)';

   execute immediate l_statement
      using '';

end;

answered Sep 12, 2013 at 13:53

Rene

10.6k5 gold badges37 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Barmar Over a year ago

That's not an empty list, it's a list containing an empty string.

Rene Over a year ago

Empty string and null are the same thing in Oracle

Koszczi · Accepted Answer · 2013-09-12 13:51:04Z

1

You can do it, it will be syntactically correct. I hope you are aware that NULL is not equal to anything even to NULL (so NULL=NULL is not TRUE but NULL), it can cause unexpected results in your query, so consider using NVL function if it doesn't affect performance

answered Sep 12, 2013 at 13:51

Koszczi

613 bronze badges

Collectives™ on Stack Overflow

Oracle - Can A Bind Variable Be Put In In() Clause If The Variable Can Be Empty

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related