python bulk insert: getting Error ORA-01438-CodePudding

Have table with NUMBER, while perform .executemany ( INS, [ data ] ) getting Error ORA-01438. Same if I inserting single record. I loop the record and found the one that fail cx_Oracle. it's type is: NUMBER(19,4)

Suspected value: 1000000000000000.0

Any idea ?..

CodePudding user response：

It is about scale and precision.

Columm you declared is NUMBER (19, 4) which means that it accepts numeric values up to 19 digits in length, out of which 4 are reserved for decimals, which also means that there are 19 - 4 = 15 digits available for the whole number.

Value you tried to put in there is

1000000000000000.0
12345678901234567890
         10    ^    20
               |
             16 digits of the whole number

As 16 digits is larger that max allowed 15, your code failed.

This is what you have:

SQL> create table test (val number (19, 4));

Table created.

SQL> insert into test values (1000000000000000.0);
insert into test values (1000000000000000.0)
                         *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

If the column was declared as (20, 4), it would be OK as you'd then have 16 digits for the whole number:

SQL> drop table test;

Table dropped.

SQL> create table test (val number (20, 4));

Table created.

SQL> insert into test values (1000000000000000.0);

1 row created.

SQL>

Alternatively, declare it just as NUMBER and it'll let you insert maximum allowed scale and precision, with no constraints.

CodePudding user response：

Littlefoot explained your insert issue. I will explain your fetch issue! If you use the default conversion to float, you are limited to 15-16 digits of precision -- and so rounding takes place upon fetch. In order to preserve the precision you need to use the decimal.Decimal type. That can done with a type handler or (in python-oracledb) with a simple switch. The following samples demonstrate this:

cx_Oracle

import cx_Oracle as oracledb

def output_type_handler(cursor, name, default_type, size, precision, scale):
    if default_type == oracledb.NUMBER:
        return cursor.var(decimal.Decimal, arraysize=cursor.arraysize)

conn = oracledb.connect(user=USER, password=PASSWORD, dsn=DSN)
conn.outputtypehandler = output_type_handler
cursor = conn.cursor()
cursor.execute("select number_val from my_table")

python-oracledb

import oracledb

oracledb.defaults.fetch_decimals = True

conn = oracledb.connect(user=USER, password=PASSWORD, dsn=DSN)
cursor = conn.cursor()
cursor.execute("select number_val from my_table")