Home » Open Source » Programming Interfaces » Python App =>Causes of: ORA-22065 OCI-22065: (Oracle 10g CentOS 5)
Python App =>Causes of: ORA-22065 OCI-22065: [message #513425] Mon, 27 June 2011 07:52 Go to next message
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 #513427 is a reply to message #513425] Mon, 27 June 2011 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OCI-22065: number to text translation for the given format causes overflow
 *Cause:  Rounding done due to the given string format causes overflow.
 *Action: Change the format such that overflow does not occur.

We can't say more with what you posted.

Regards
Michel
Re: Python App =>Causes of: ORA-22065 OCI-22065: [message #513606 is a reply to message #513427] Tue, 28 June 2011 06:51 Go to previous messageGo to next message
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 #513608 is a reply to message #513606] Tue, 28 June 2011 06:53 Go to previous messageGo to next message
Vackar
Messages: 81
Registered: October 2007
Member
* Actually just realised that the post is in the Open Database Interfaces forum, ignore my request for it to be moved.

The following question still stands however.
Re: Python App =>Causes of: ORA-22065 OCI-22065: [message #513618 is a reply to message #513608] Tue, 28 June 2011 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste what you did to get the error.

Regards
Michel
Re: Python App =>Causes of: ORA-22065 OCI-22065: [message #513623 is a reply to message #513618] Tue, 28 June 2011 07:20 Go to previous messageGo to next message
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 #513626 is a reply to message #513623] Tue, 28 June 2011 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the content of:
var->environment->numberToStringFormatBuffer.ptr
&var->data[pos]
when the error occurs?

Regards
Michel
Re: Python App =>Causes of: ORA-22065 OCI-22065: [message #513630 is a reply to message #513626] Tue, 28 June 2011 07:28 Go to previous messageGo to next message
Vackar
Messages: 81
Registered: October 2007
Member
Hi Michel,

I'm not entirely sure.

I'm running a binary build of the driver.

Are you aware of any method I can use to check this value from the binary build?

Thanks,
Vackar
Re: Python App =>Causes of: ORA-22065 OCI-22065: [message #513642 is a reply to message #513630] Tue, 28 June 2011 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm not entirely sure.

But you said "there are no infinity values", infinite is far less in Oracle than in SQL Server.

Quote:
Are you aware of any method I can use to check this value from the binary build?

If you can't modify the code, I see no other option than debugger.
Maybe you can get the value of second point directly from SQL Server.

Regards
Michel
Re: Python App =>Causes of: ORA-22065 OCI-22065: [message #513653 is a reply to message #513642] Tue, 28 June 2011 09:12 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: Python App =>Causes of: ORA-22065 OCI-22065: [message #513658 is a reply to message #513657] Tue, 28 June 2011 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many thanks for your feedback (and sorry about MS SQL I mixed up another question from someone retrieving data from a SQL Server DB through a db link).

Regards
Michel
Re: Python App =>Causes of: ORA-22065 OCI-22065: [message #513661 is a reply to message #513658] Tue, 28 June 2011 10:17 Go to previous message
Vackar
Messages: 81
Registered: October 2007
Member
No problem Michel,
thanks for your help.

Vackar
Previous Topic: Cannot create JDBC Driver of class 'oracle.jdbc.driver.OracleDriver'
Next Topic: Execute Firebird procedure from Oracle
Goto Forum:
  


Current Time: Sun Dec 21 18:03:09 CST 2014

Total time taken to generate the page: 0.05396 seconds