G
GT
Hello,
We had run into intermittent "ORA-01858: a non-numeric
character was found where a numeric was expected" and "ORA-
06502 PL/SQL: numeric or value error string" error on few
of our store prodcedures. We are using .Net Framework 1.1
with MS Oracle Provider and Oracle 9i (9.2.0.1.0).
The same data and same store procedure always works on
developer's desktop. Hand executing those store procedures
with the same data works fine. So we know it's not the
data problem or incorrect store procedure problem.
But the exact same thing would work fine on an integration
server, and then suddenly throws the oracle exception
intermittently. And it might work a couple of hours latter
or after the SP was recompiled. Dumping the
OracleParameters in the OracleParameter array before
calling the SP reveals nothing wrong with the input
parameters.
The Oracle exception does not indicate which parameter is
the offender. Since it's itermittent and we have a large
parameter list, one-by-one elimination would be difficult.
We do not specify the OracleType for our OracleParameters
because OracleProvider documentation says:
1. "The DbType, OracleType, and Size properties of a
parameter can be inferred by setting Value. Therefore, you
are not required to specify them."
2. "Setting the Precision or Scale properties has no
effect."
So my questions are:
1. How can we pinpoint the offending parameter? DB tracing?
2. Is the omission to sepcify OracleType a problem?
3. If this is a bug in the OracleClient, any fix?
4. Will Oracle ODP solve this problem?
Thanks,
GT
We had run into intermittent "ORA-01858: a non-numeric
character was found where a numeric was expected" and "ORA-
06502 PL/SQL: numeric or value error string" error on few
of our store prodcedures. We are using .Net Framework 1.1
with MS Oracle Provider and Oracle 9i (9.2.0.1.0).
The same data and same store procedure always works on
developer's desktop. Hand executing those store procedures
with the same data works fine. So we know it's not the
data problem or incorrect store procedure problem.
But the exact same thing would work fine on an integration
server, and then suddenly throws the oracle exception
intermittently. And it might work a couple of hours latter
or after the SP was recompiled. Dumping the
OracleParameters in the OracleParameter array before
calling the SP reveals nothing wrong with the input
parameters.
The Oracle exception does not indicate which parameter is
the offender. Since it's itermittent and we have a large
parameter list, one-by-one elimination would be difficult.
We do not specify the OracleType for our OracleParameters
because OracleProvider documentation says:
1. "The DbType, OracleType, and Size properties of a
parameter can be inferred by setting Value. Therefore, you
are not required to specify them."
2. "Setting the Precision or Scale properties has no
effect."
So my questions are:
1. How can we pinpoint the offending parameter? DB tracing?
2. Is the omission to sepcify OracleType a problem?
3. If this is a bug in the OracleClient, any fix?
4. Will Oracle ODP solve this problem?
Thanks,
GT