Michael
Mon Feb 04 16:24:18 CST 2008
On Feb 4, 12:08 pm, greg <thef...@nospam.nospam> wrote:
> Does this also apply to Oracle, and if so, how can I specify it? Right now I
> am doing the following:
In Oracle you can not define stored procedures without a datatype. In
ADO.NET and Oracle providers you cannot avoid specifying a parameter
type enumeration when adding a parameter. If there is an overload to
add a parameter without specifying a parameter type enumeration, you
can rest assured that the documentation for that provider and method
says there's a default enumeration used, probably VarChar/Varchar2
(depending on your provider).
Although you should strive to add your parameters with the appropriate
ADO.NET parameter type, you can achieve non-deterministic successful
results with the wrong parameter type because implicit datatype
conversions may occur.
For example, if your stored procedure PROCEDURE whatever (NUMBER_PARAM
IN NUMBER)... was defined and you used and you added a parameter
aCommand.Parameters("NUMBER_PARAM", aNumberVariable)
What I'm fairly confident would happen is that under the ADO.NET
covers, the parameter type enumeration would be VarChar (or VarChar2
if using ODP.NET) and the second parameter would be cast as a string
object and passed along to the RDMBS as string. The stored procedure
would then implicitly cast it back to a number and probably execute
without a problem assuming its value was within the database
datatype's domain of acceptable range. Almost every .NET type can be
cast to a string in some way and the RDBMS can very possibly succeed
with implicitly converting a string to most of it's data types.
Nonetheless this implicitness is not optimally performing nor
deterministic in its nature.
So maybe your friend was saying that it was best to be explicit in
your practices - whereas you could avoid the double implicit (once
in .NET and once in the RDBMS) datatype casting I've tried to
illuminate happening above.
Hope that helps,
Michael O
http://blog.crisatunity.com