This is rapidly becoming a log of my stupid mistakes, but I thought I’d post another beauty from the Extremely Tired And Lacking In Coffee school of programming. A stored procedure was returning some very strange results – namely it was returning the same record no matter what was passed in. My procedure looked like this:
CREATE OR REPLACE FUNCTION get_some_info(customerId NUMBER)
RETURN mercury.GenericCursor AS
res mercury.GenericCursor;
BEGIN
OPEN res FOR
SELECT someinfo
FROM customer
WHERE
customerid = customerId and
...;
RETURN res;
END;
Anyway, no matter the customerId passed in, back came the same record. Eventually my more SQL-minded colleague pointed out that Oracle is case insensitive at matching the parameter/column name and returned me the first record. A quick rename of the parameter to customer_id and all was well. It would of been nice if Oracle had thrown an error or at least a warning when I gave it the procedure – even so, not one of my finer moments.
magpiebrain 
3 Responses to “How to hurt your brain with Oracle”
Hi
The same also happens in Java:
class X {
int a;
public void method(int a) {
a = a;
}
}
The above class compiles with no problems but does not do what it was meant to do. The
a = a
has to change to
this.a = a
to work correctly. The compiler does not give any errors but some IDEs such as Eclipse underline that statement saying it does not have any effect on the variable a…
I usually avoid such issues by prefixing parameter variables with ‘p_’; that way, I know which variable I’m referring to. (And I don’t have to worry about case insensitivity either)
Yeah – I use a prefix as well having been bitten by that particular bug a couple of times in my early Java days. Checkstyle and PMD should also pick it up as a usless statement too. The annoying thing with the Oracle bug was that I completely forgot that SQL is case insensitive….