Python App =>Causes of: ORA-22065 OCI-22065: [message #513425] |
Mon, 27 June 2011 07:52 |
Vackar
Messages: 81 Registered: October 2007
|
Member |
|
|
Hi,
I have an app which is throwing an error:
OCI-22065: number to text translation for the given format causes overflow
From the reading I've done this can be caused by trying to load positive/negative infinity from a binary_float in python.
However looking at the rows that the query returns, there are no infinity values.
Has anyone else encountered this error, or does anyone know what the other causes of this error are?
Thanks,
Vackar
|
|
|
|
Re: Python App =>Causes of: ORA-22065 OCI-22065: [message #513606 is a reply to message #513427] |
Tue, 28 June 2011 06:51 |
Vackar
Messages: 81 Registered: October 2007
|
Member |
|
|
Ok, this may be more appropriate to ask in the client tools forum, is there anyway this can be moved?
Anyway, I've been doing a lot of googleing and it looks like the app frameworks is setting the cursor.numbersAsStrings flag on the cursor to True.
My unsderstanding is that this will result in cx_Oracle calling OCINumberToText().
So my question is: does anyone know what may be causing the call to OCINumberToText() to result in OCI-22065?
I've really been struggling with this issue.
Any help would be greatly appreaciated.
Thanks,
Vackar
|
|
|
|
|
Re: Python App =>Causes of: ORA-22065 OCI-22065: [message #513623 is a reply to message #513618] |
Tue, 28 June 2011 07:20 |
Vackar
Messages: 81 Registered: October 2007
|
Member |
|
|
Here is the block in the cx_Oracle driver that appears to do the call:
if (var->type == &vt_NumberAsString || var->type == &vt_LongInteger) {
stringLength = sizeof(stringValue);
status = OCINumberToText(var->environment->errorHandle,
&var->data[pos],
(text*) var->environment->numberToStringFormatBuffer.ptr,
var->environment->numberToStringFormatBuffer.size, NULL, 0,
&stringLength, (unsigned char*) stringValue);
if (Environment_CheckForError(var->environment, status,
"NumberVar_GetValue(): as string") < 0)
return NULL;
stringObj = cxString_FromEncodedString(stringValue, stringLength,
var->environment->encoding);
if (!stringObj)
return NULL;
if (var->type == &vt_NumberAsString)
return stringObj;
followed by
return OracleNumberToPythonFloat(var->environment, &var->data[pos]);
and the source for that is:
//-----------------------------------------------------------------------------
// OracleNumberToPythonFloat()
// Return a Python date object given an Oracle date.
//-----------------------------------------------------------------------------
static PyObject *OracleNumberToPythonFloat(
udt_Environment *environment, // environment
OCINumber* value) // value to convert
{
double doubleValue;
sword status;
status = OCINumberToReal(environment->errorHandle,
value, sizeof(double), (dvoid*) &doubleValue);
if (Environment_CheckForError(environment, status,
"OracleNumberToPythonFloat()") < 0)
return NULL;
return PyFloat_FromDouble(doubleValue);
}
[Updated on: Tue, 28 June 2011 07:22] Report message to a moderator
|
|
|
|
|
|
Re: Python App =>Causes of: ORA-22065 OCI-22065: [message #513653 is a reply to message #513642] |
Tue, 28 June 2011 09:12 |
Vackar
Messages: 81 Registered: October 2007
|
Member |
|
|
Sorry Michel,
I think I've confused you.
When I said "there are no infinity values" I meant when running the query outside of python (without cx_Oracle)
The values returned are actually null.
These null values seem to cause something strange to happen when the sql is being executed and the results parsed via cx_Oracle.
However when the values are not null the reusults are parsed fine.
Also i'm not using sql server, just Oracle.
Any other ideas?
Thanks
Vackar
|
|
|
Re: Python App =>Causes of: ORA-22065 OCI-22065: [message #513657 is a reply to message #513623] |
Tue, 28 June 2011 10:04 |
Vackar
Messages: 81 Registered: October 2007
|
Member |
|
|
OK,
I finally figured out what was going wrong.
If I run the query from a java client I get Null as a result.
If I run the query from python (default) I get 0.0 as a result
If I run the query from python (numbersAsStrings flag set to 1) I get the OCI-22065 error.
However, if I fire up enterprise manager and run the query from there, I get -~ (negative infinity)
-Bingo-
It turned out that the script that I used to bulk load data with wasn't doind proper checks.
It inserted 'NaN' into the field and this then magically this became -~
Hopefully this will be useful to someone in the future.
Thanks,
Vackar
[Updated on: Tue, 28 June 2011 10:05] Report message to a moderator
|
|
|
|
|