Monday, December 22, 2008

Where is the bug?

So, if I have a table that has a column defined as NUMBER(10) and try to insert the value of 'ABCDEFG' in it, I get an "ORA-01858: a non-numeric character was found where a numeric was expected" error. Fair enough, I expect that the database does type checking and won't allow an invalid number in the field.

In addition, if I try to insert an 11 digit number, I get an "ORA-01438: value larger than specified precision allowed for this column" error.

So far so good.

If an invalid number, such as "-0" or "17~:49" got into the database via JDBC, would it be a client problem, a server problem, or both?


MattBall said...

Both plus a "training issue"

Gary Myers said...

I don't get it. JDBC still issues SQL and the SQL engine will still catch the invalid number. Any set of bytes that are actually stored will be managed as a number.
The issue isn't as clear cut for dates as individual bytes have additional constraints (repesenting months, hours etc) and there are ways (mostly through OCI) to get a series of seven bytes into a data field which doesn't represent a valid date.
It can cause 'odd' effects.
create table test (v date);
insert into test
select reverse(sysdate) from dual;
select * from test;

In this case the 'bug' is me using an undocumented function (reverse).

Ofir said...

it could be a bug in the JDBC driver (that translate the string to a valid number) or in the database (that accepts an invalid string) or even a feature of java (who knows).
If I were you, I would trace the database session to see the exact SQL statement sent by the JDBC layer and continue based on the results...

Anonymous said...

It will be a client problem, because it's the client which is trying to insert wrong values. However the client can't succeed because the server will not allow such values.


Martin Berger said...

I'm not sure about the question, but it's a application problem.
The RDBMS will reject the value it cannot cast implicite, so someone else have to do it. At the end, it's the applications problem.
But I still doesn't get the base of the question.