0

I have a table in an Oracle 11g database that's similar to the following:

CREATE TABLE BUDGET_DATA (
  BUDGET_MONTH varchar2(30)
  , BUDGET_CATEGORY varchar2(30)
  , BUDGET_AMOUNT varchar2(30)
)

All the values in BUDGET_AMOUNT are numbers. When I do select SUM, the values come out as I would have expected if BUDGET_AMOUNT was actually stored as a numeric.

SELECT BUDGET_MONTH, SUM(BUDGET_AMOUNT)
FROM BUDGET_DATA
GROUP BY BUDGET_MONTH

I'm curious, does Oracle perform a native conversion to number if you put a VARCHAR2 in an aggregate function? I would have expected that I'd have to do the following to get the data to sum correctly:

SELECT BUDGET_MONTH, SUM(TO_NUMBER(BUDGET_AMOUNT))
FROM BUDGET_DATA
GROUP BY BUDGET_MONTH

Can someone point me to some documentation about this behavior? I haven't been able to find anything to describe it.

  • 1
    Never store numbers in a varchar column. Just don't – a_horse_with_no_name Dec 13 '17 at 22:42
  • Unfortunately, I didn't create/don't have control over the table. I just have to use it. Coming from a SQL Server background where trying to do the above would error out, I found it really surprising it worked. – Evan M. Dec 14 '17 at 15:07
2

Implicit Data Conversion

Oracle Database automatically converts a value from one data type to another when such a conversion makes sense.

You can find more on the above URL.

After you read it, forget everything written there, and use explicit conversion. Relying on implicit conversion is bad practice.

| improve this answer | |
  • 1
    I agree with Balaz as far as he went, but he stopped short .. after reading the reference, then forgetting it, one should avoid the problem in the first place by using proper data types. BUDGET_AMOUNT should have been declared as a NUMBER. Failure to do so is a Design Fail (tm) of the first order. – EdStevens Dec 13 '17 at 21:57
  • Not disagreeing. Unfortunately I don't have design control. I just needed to query the data, and was really surprised when it worked (I had assumed when I first wrote the query that it was a number). Marked your answer as correct, as it's obviously using implicit conversion. However, I didn't find anything in that article that talks about it doing it specifically with aggregates. – Evan M. Dec 14 '17 at 15:08

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.