Oracle a non-numeric character was found where a numeric was expected

Question

I have problem with some thing input multiple date through prompt my query something like

select something
from
something
where
to_date (bus.TDATE) not in (:didb1)

I want to input like '12-Jun-2016','11-Jun-2016'

i am doing php oracle, My code following:

select bus.*,con.* from
BusinessPayment bus,
ConsumerPayment con
where bus.TDATE=con.TDATE
and to_date (bus.TDATE) not in (:didbv)
');
$bscode1='11-June-2016','10-June-2016','09-June-2016';
oci_bind_by_name($stid, ':didbv', $bscode1);
oci_execute($stid);

Answer 1

You can't use a single bind variable to represent a list of values in an in() clause. Bind variables don't contain multiple values; the value you bind is seen as a single string value. So you're effectively doing:

where
to_date (bus.TDATE) not in (q'['11-June-2016','10-June-2016','09-June-2016']')

If TDATE is already a date type - which it hopefully is, and the joins suggest it might be - then you should not be passing that through to_date() - you are doing an implicit conversion from date to string, then a semi-explicit conversion back to a date, both using your session's NLS_DATE_FORMAT. That is either doing unnecessary work, or losing resolution, depending on your format model and whether the dates have times. If the intention is to ignore the time part of the value then there are better ways to do that.

Anyway, since that is a date now whatever type it was before, the right had side of that filter will also be converted to a date, so you're doing:

where
to_date (bus.TDATE) not in (to_date(q'['11-June-2016','10-June-2016','09-June-2016']'))

... and it's that which is throwing the exception, whatever your NLS settings are. Even if you passed a single value it would error because of the embedded single quotes. You can replicate that with:

select to_date(q'['11-June-2016','10-June-2016','09-June-2016']') from dual;

which also gets ORA-01858: a non-numeric character was found where a numeric was expected.

Ideally you would pass the individual date strings as elements of an array, but you can also work around this with an XML hack. You can pass a single string containing comma-delimited values to XMLTable(), and it will split it in to rows:

select * from xmltable(q'['11-June-2016','10-June-2016','09-June-2016']');

COLUMN_VALUE                                                                   
--------------------------------------------------------------------------------
11-June-2016
10-June-2016
09-June-2016

Then you can convert each of those values to a date:

select to_date(column_value, 'DD-Month-YYYY')
from xmltable(q'['11-June-2016','10-June-2016','09-June-2016']')

and use that in a subquery or a join:

select * from dual
where trunc(sysdate) not in (select to_date(column_value, 'DD-Month-YYYY')
  from xmltable(q'['11-June-2016','10-June-2016','09-June-2016']'));

And that works with a string bind variable, so your code would be:

select bus.*,con.*
from BusinessPayment bus
join ConsumerPayment con
on bus.TDATE=con.TDATE
where bus.TDATE not in (select to_date(column_value, 'DD-Month-YYYY')
      from xmltable(:didbv));

I've modified this to use ANSI join syntax, which isn't relevant but there's rarely a reason to use the ancient syntax you have. If TDATE does include the time you'll need to modify it a bit - truncating is simplest but might affect performance.

Or you could join:

select bus.*,con.*
from xmltable(:didbv) x
join BusinessPayment bus
on bus.TDATE >= to_date(x.column_value, 'DD-Month-YYYY')
and bus.TDATE < to_date(x.column_value, 'DD-Month-YYYY') + 1
join ConsumerPayment con
on bus.TDATE=con.TDATE

In this version TDATE can have a time, and it matches any time on the days specified in the bind variable.

It would be better to supply the dates in a format that doesn't have the month name in it, as that could give you problems when run in different sessions or locales. If you know the PHP side will always be English you could force that to be recognised:

on bus.TDATE >= to_date(x.column_value, 'DD-Month-YYYY', #NLS_DATE_LANGUAGE=ENGLISH')

but if you can change the PHP format to use month numbers, and change the Oracle format model to match, that would be a bit simpler.