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.