magpiebrain

Sam Newman's site, a Consultant at ThoughtWorks

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.

Advertisements

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

  1. Behrang

    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…

    Reply
  2. JM Ibanez

    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)

    Reply
  3. Sam Newman

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

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Basic HTML is allowed. Your email address will not be published.

Subscribe to this comment feed via RSS

%d bloggers like this: