How to hurt your brain with Oracle

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.

This entry was posted on Wednesday, January 28th, 2004 at 5:33 pm and is filed under General. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

3 Responses to “How to hurt your brain with Oracle”

1. On January 28, 2004 at 7:41 pm Behrang wrote:

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…

2. On January 28, 2004 at 11:07 pm JM Ibanez wrote:

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)

3. On January 29, 2004 at 10:46 am Sam Newman wrote:

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….

Have your say

Fields in bold are required. Email addresses are never published or distributed.

Some HTML code is allowed:

URIs must be fully qualified (eg: http://www.domainname.com) and all tags must be properly closed.

Line breaks and paragraphs are automatically converted.

Please keep comments relevant. Off-topic, offensive or inappropriate comments may be edited or removed.

magpiebrain