A Programmer's Dream

.NET Data Parameters

Posted by Stephen Wrighton on 06 Jun 2007

so, we're working on this application, and its backend is an Oracle database, as I've been harping on for a week or two now. Anyways, we're utilizing packages and parameter lists because, well, it's Oracle, and the folks that designed the thing want every single data access to be via a stored procedure.

I can live with it.

Well, I've been playing with things, and discovered a slight flaw in the .NET to Oracle relationship. If I pass an empty string or a string without any characters to it, I get an error back from Oracle.

So, basically, if I have a text field and I enter a just a space, and then trim the value from that text field as I set it as the value of the data parameter I will get a hard crash.

Same as if the user leaves a text field blank.

This is the exception being reported by .NET:
Parameter 'teacher': No size set for variable length data type: String.
Why it would not accept the empty string, or even assume a NULL value I'm not certain. So I tried to provide default values, and apparently the parameter is overriding the default value as I get the same error.

Anyways, what I'm having to do is add a space to the values from the text field and then trim that value before I use it in the stored procedure.

And it gets better.

I can't reassign a value to the parameters, because they're all IN parameters by default. I guess I could change them to IN/OUT but that's a lot of hassle. So I'm having to do the trims every time I use the variable in the stored procedure.

This is just so much easier in SQL Server...

Tweet me @kidananubix if you like this post.