I don't understand why implicit conversion isn't working for me for the median function in Oracle. (Obviously I have plenty of ways to work around it with proper explicit conversions, but I'm wondering why it's happening at all. Am I missing something obvious or is this a bug? It just seems so unlikely to be a bug.)
I have a table with a varchar2(255 byte) column. It's nullable, but contains no nulls. All of the strings in this field are numeric.
I can do this:
select avg(this_field) from this_table
and that's fine.
I can do this:
select median(to_number(this_field)) from this_table
and that's fine too.
But I can't do this:
select median(this_field) from this_table
because I get the error "ORA-30495: The argument should be of numeric or date/datetime type."
The Oracle documentation on MEDIAN says that implicit conversion is done:
This function takes as arguments any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.
This is the exact same wording as the Oracle documentation on AVG.
And yet:
select median(to_char('1')) from dual;
ORA-30495: The argument should be of numeric or date/datetime type.
select avg(to_char('1')) from dual;
1
select median(to_number(to_char('1'))) from dual;
1
So...what's going on?
That seems to be a documentation bug (which dates back to when it was first introduced in 10gR1). I imagine whoever wrote that copied and pasted from another function like avg, and didn't take out the bits that didn't apply for median.
Some functions, like trunc() and round(), allow either data type to be passed and also allow implicit conversion of strings to numbers - but not to dates, in my testing anyway. But those tend to be listed twice in the documentation with the different argument types, and are single-row functions; off-hand I can't think of any aggregates that behave like that.
avg() can arguably do implicit conversion because everything passed in has to be convertible to a number. They could have made median() only try to implicitly convert to number like trunc(), but perhaps there was too much overhead, or some implementation reason why it was difficult, or they planned to try to convert to dates as well; or maybe it was just overlooked.
The wording of that section would still have been wrong; the first paragraph states it takes a numeric or datetime value, so for the second to only refer to numeric would be incorrect anyway. It looks like the first sentence of that second paragraph shouldn't be there, and the rest should apply to datetimes as well, at least in terms of the return type.
There's a feedback button at the bottom of the left-hand navigation panel in the docs. You can report it there, and then see if the documentation is ever updated.
Alternatively, you could raise a service request to get the behaviour changed to match the docs - though that paragraph still needs some work...