get error DPI-1044: value cannot be represented as an Oracle number when using very large number

[saule1508]

See https://www.oracle.com/corporate/security-practices/assurance/vulnerability/reporting.html for how to report security issues.

For other issues:

1. Is it an error or a hang or a crash?

It is an error

2. What error(s) you are seeing?

cx_Oracle.DatabaseError: DPI-1044: value cannot be represented as an Oracle number

3. Include a runnable Python script that shows the problem.
   Include all SQL needed to create the database schema.

create table test(
    id number default to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'), 
    txt varchar2(20) );

insert into test(txt) values('test');
commit;

import cx_Oracle

oracle_con = cx_Oracle.connect("...", "...", "<...DSN description..>", encoding="UTF-8")
cursor = oracle_con.cursor() 
cursor.execute("select id from test where txt = 'test'") 
res=cursor.fetchone()
cursor.execute("select * from test where id = :id",id=res[0])
# this works: cursor.execute("select * from test where id = " + str(res[0]))
cursor.close()
oracle_con.close()

4. Show the output of:

import sys
import platform

print("platform.platform:", platform.platform())
print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
print("platform.python_version:", platform.python_version())

platform.platform: Linux-3.10.0-1127.el7.x86_64-x86_64-with-redhat-7.8-Maipo
sys.maxsize > 2**32: True
platform.python_version: 3.6.8

And:

import cx_Oracle
print("cx_Oracle.version:", cx_Oracle.version)
print("cx_Oracle.clientversion:", cx_Oracle.clientversion())

cx_Oracle.version: 7.3.0
cx_Oracle.clientversion: (19, 6, 0, 0, 0)

5. What is your Oracle Database version?

12.1.0.2

[anthony-tuininga]

I am able to replicate the issue with some values of sys_guid() -- namely ones that exceed 38 digits. Interestingly, constraints may only go from 1 to 38 digits, but unconstrained values can exceed that!

[anthony-tuininga]

The patch ended up being quite simple. If you are able to build cx_Oracle for yourself you can test and let me know if itt works for you as well. I used these scripts for my own tests:

The SQL:

create table issue_459 (
    num     number
);

insert into issue_459
values (225268684584131642404515845017960494737);

insert into issue_459
values (8225268684584131642404515845017960494737);

insert into issue_459
values (-8225268684584131642404515845017960494737);

commit;

and the Python script:

import cx_Oracle

conn = cx_Oracle.connect("user/password@dsn")
cursor = conn.cursor()

cursor.execute("select * from issue_459")
vals = [i for i, in cursor]

for val in vals:
    print(val, "with length", len(str(val)))

for val in vals:
    cursor.execute("select * from issue_459 where num = :val", val=val)
    for fetched_val, in cursor:
        print(fetched_val)

[saule1508]

Thank you very much, this was fast (so much faster than metalink !!). I don't know how to compile it and I work in a very secure (hardened) environment so I cannot test (but the patch is obvious).
For the record, this column using a guid is generated by the support tool orachk, when it pushes the orachk zip file in a table.

Pierre

[cjbj]

Thanks for your comments.

For the record, compilation steps are in https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html#install-using-github

[anthony-tuininga]

This is included in cx_Oracle 8.0.1 which was just released.