A Programmer's Dream

I feel like an idiot thanks to Oracle Stored Procedure parameters

Posted by Stephen Wrighton on 05 Jun 2007

Yeah, Oracle's Stored Procedures parameter lists made me feel like an idiot.

Consider, I have a person table, and one of the fields is an email address, named "Email." Well, not thinking perfectly well, I labeled the parameter that is being passed in "email" as well.

Oracle was perfectly happy with that.

Anyways, this Stored Procedure is designed to handle Insert/Updates for this Person table. Well with my parameter list the INSERT happens just dandy. Without a problem. But my UPDATE refused to put the new value into the column.

Ultimately, I realized that the stored procedure was ignoring my parameter on the update in order to have the table update the column with the value that is currently in there. Why, oh why wouldn't Oracle say "Hey, stupid, I don't know which value you want me to use, you've given me to variables named the same thing!" But no, unless I was paying close attention (and by the time I did my own testing, I missed it, and it only showed up in peer testing) I would have missed the fact that that single field of the Course wasn't updating.

Ah, just another reason why I love SQL Server over Oracle.

Tweet me @kidananubix if you like this post.