Ora 01438 значение больше чем позволяет заданная для этого столбца точность

Database Error Messages

When inserting or updating records, a numeric value was entered that exceeded the precision defined for the column. For example, for a column defined to be NUMBER(3,2), the precision or the maximum number of significant decimal digits is 3. The scale or the number of digits from the decimal point to the least significant digit is 2. In this case, a value of 123.89 will cause the error because the precision allows a maximum of 3 digits and 2 of them must be to the right of the decimal point. A value of 3.89 will not cause the error.

Action

Enter a value that complies with the precision defined for the numeric column. You can also use the MODIFY option with the ALTER TABLE command to redefine the precision.

Previous Releases

Release 21c, 19c

value larger than specified precision allowed for this column

Cause

When inserting or updating records, a numeric value was entered that exceeded the precision defined for the column.

Action

Enter a value that complies with the numeric column’s precision, or use the MODIFY option with the ALTER TABLE command to expand the precision.

ORA-01438: value larger than specified precision allowed for this column tips

Cause: When inserting or updating records, a numeric value was entered that exceeded the precision defined for the column.

Action: Enter a value that complies with the numeric column»s precision, or use the MODIFY option with the ALTER TABLE command to expand the precision .

Because ORA-01438 often consists of an erred entry in your SQL, it is often the case that syntax needs to be evaluated. For example, in the Oracle Technology Network’s forums, a user consistently received ORA-01437 when he is running his procedure. His replier pointed out that the values entered may exceed the column definition, or he was entering the CHAR value in the NUMBER field. From that information, ORA-01438 was successfully resolved.

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum .

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail:

and include the URL for the page.

Burleson Consulting

The Oracle of Database Support

Copyright © 1996 — 2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

How to find the error column for «ORA-01438: value larger than specified precision allowed for this column»

I get this error ORA-01438: value larger than specified precision allowed for this column , but I don’t know what column. How can I find the column with the error?

INSERT INTO plants(created_at,updated_at,published,title,id_old,simplified_name,code,dp1,dh1,dh2,dh4,field9,migrate,lives_in_ro,is_endemic,is_international,is_national,priority,id_synonim,id_group,genus,id_tax_code,guid,vernacular_name,is_sensitive,id_red_list_codes,id_owner,insert_date,update_date,is_in_zoo,is4a,is5a,is5b,is4b,is_cites,is_bdv,is_ber,is_bon,is_new_record,is_for_derogation,id_inserter,berna1,bonn1,anexa_bonn,anexa_berna,berna2,berna3,bonn2,a17code,field47,field48,field49,taxup,incerta,spontan,cultivat,invaziva,al,turbarie,lr,l_rosie,lista_rosie,taxon,increngatura,clasa,ordin,familia,gen,specia,autor,subspecia,autor_ssp,varietatea,autor_var,forma,autor_for,ngexc,ngcateg,biblio,transferat,submited_checked ) VALUES(TO_DATE(sysdate, 'yyyy/mm/dd hh24:mi:ss'), TO_DATE(sysdate, 'yyyy-mm-dd hh24:mi:ss'), 0, 'Euclasta perisalis', '99012', 'perisalis', '', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '306200', '1', 'Euclasta', '200681', '8615DFE9-C28A-4240-B3A2-79D97ECCD41C', '', '0', '', '','00:00,0', '', '0', '0','0', '0','0', '0', '0', '0', '0', '0', '0', 'D8446A30-8B8B-4059-AE3F-86AF0ABA39B8', '0', '0', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '0', '', '', '', 'Arthropoda', 'Insecta', 'Lepidoptera', 'Crambidae', 'Euclasta', 'varii', '', '', '', '', '', '', '', '', '', '', '', '0') 

And here is the DLL

 DDL for Table PLANTS -------------------------------------------------------- CREATE TABLE "SYSTEM"."PLANTS" ( "ID" NUMBER(19,0), "DELETED_AT" TIMESTAMP (6), "CREATED_AT" TIMESTAMP (6), "UPDATED_AT" TIMESTAMP (6), "PUBLISHED" CHAR(1 BYTE) DEFAULT '0', "TITLE" VARCHAR2(200 BYTE), "DESCRIPTION" CLOB, "ID_OLD" NUMBER(10,0), "SIMPLIFIED_NAME" VARCHAR2(200 BYTE), "CODE" VARCHAR2(100 BYTE), "DP1" NUMBER(3,0), "DH1" NUMBER(3,0), "DH2" NUMBER(3,0), "DH4" NUMBER(3,0), "FIELD9" NUMBER(3,0), "MIGRATE" NUMBER(3,0), "LIVES_IN_RO" NUMBER(3,0), "IS_ENDEMIC" NUMBER(3,0), "IS_INTERNATIONAL" NUMBER(3,0), "IS_NATIONAL" NUMBER(3,0), "PRIORITY" NUMBER(3,0), "ID_SYNONIM" NUMBER(10,0), "ID_GROUP" NUMBER(3,0), "GENUS" VARCHAR2(100 BYTE), "ID_TAX_CODE" NUMBER(5,0), "GUID" VARCHAR2(200 BYTE), "VERNACULAR_NAME" VARCHAR2(200 BYTE), "IS_SENSITIVE" NUMBER(3,0), "ID_RED_LIST_CODES" VARCHAR2(200 BYTE), "ID_OWNER" VARCHAR2(200 BYTE), "INSERT_DATE" VARCHAR2(200 BYTE), "UPDATE_DATE" VARCHAR2(200 BYTE), "IS_IN_ZOO" NUMBER(3,0), "IS4A" NUMBER(3,0), "IS5A" NUMBER(3,0), "IS5B" NUMBER(3,0), "IS4B" NUMBER(3,0), "IS_CITES" NUMBER(3,0), "IS_BDV" NUMBER(3,0), "IS_BER" NUMBER(3,0), "IS_BON" NUMBER(3,0), "IS_NEW_RECORD" NUMBER(3,0), "IS_FOR_DEROGATION" NUMBER(3,0), "ID_INSERTER" VARCHAR2(200 BYTE), "BERNA1" NUMBER(3,0), "BONN1" NUMBER(3,0), "ANEXA_BONN" VARCHAR2(200 BYTE), "ANEXA_BERNA" VARCHAR2(200 BYTE), "BERNA2" NUMBER(3,0), "BERNA3" NUMBER(3,0), "BONN2" NUMBER(3,0), "A17CODE" VARCHAR2(200 BYTE), "FIELD47" VARCHAR2(200 BYTE), "FIELD48" VARCHAR2(200 BYTE), "FIELD49" VARCHAR2(200 BYTE), "TAXUP" VARCHAR2(100 BYTE), "INCERTA" VARCHAR2(100 BYTE), "SPONTAN" VARCHAR2(100 BYTE), "CULTIVAT" VARCHAR2(100 BYTE), "INVAZIVA" VARCHAR2(100 BYTE), "AL" VARCHAR2(100 BYTE), "TURBARIE" VARCHAR2(100 BYTE), "LR" VARCHAR2(100 BYTE), "L_ROSIE" VARCHAR2(100 BYTE), "LISTA_ROSIE" VARCHAR2(100 BYTE), "TAXON" VARCHAR2(200 BYTE), "INCRENGATURA" VARCHAR2(200 BYTE), "CLASA" VARCHAR2(200 BYTE), "ORDIN" VARCHAR2(200 BYTE), "FAMILIA" VARCHAR2(200 BYTE), "GEN" VARCHAR2(200 BYTE), "SPECIA" VARCHAR2(200 BYTE), "AUTOR" VARCHAR2(200 BYTE), "SUBSPECIA" VARCHAR2(200 BYTE), "AUTOR_SSP" VARCHAR2(200 BYTE), "VARIETATEA" VARCHAR2(200 BYTE), "AUTOR_VAR" VARCHAR2(200 BYTE), "FORMA" VARCHAR2(200 BYTE), "AUTOR_FOR" VARCHAR2(200 BYTE), "NGEXC" VARCHAR2(100 BYTE), "NGCATEG" VARCHAR2(100 BYTE), "BIBLIO" VARCHAR2(100 BYTE), "TRANSFERAT" NUMBER(3,0), "SUBMITED_CHECKED" NUMBER(3,0) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM" LOB ("DESCRIPTION") STORE AS BASICFILE ( TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ; 

ORA-01438: value larger than specified precision allows for this column

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse suscipit sapien ac sapien malesuada fringilla. Fusce venenatis, mauris id sagittis dapibus, mauris velit sollicitudin ante, a pulvinar leo orci vel erat. Nam mattis erat augue, at luctus ex dignissim et. Donec suscipit, dui at efficitur tristique, nulla nisi ornare lorem, vel dictum lectus eros nec felis.

Error Message

Using cov2sde command may generate the following error: SDE Error(-51) Underlying DBMS error Extended error code(1438): ORA-01438: value larger than specified precision allows for this column.

Cause

Attribute values of the coverage in one or more NUMBER fields are too large for the new fields created in the Oracle table.

Cov2sde first creates an Oracle table to receive the data. This new table’s NUMBER fields are defined from the output/display column and decimals fields specified for the coverage’s INFO table.

To illustrate, assume we have a single parcel coverage with an AREA field whose output/display width is 3, and its decimal column is set to 1 decimal place. When this AREA field is added to the new Oracle table it will be defined as NUMBER(3,1). The 3 indicates the maximum number of digits Oracle will store in this number field. The 1 means that one of those three digits is reserved for a position to the right of the decimal point. Thus, if the area value in the coverage is 12.3, it would be a legitimate number, but an area value of 123.4 would not be¹.
¹Koch, G., and K. Loney, «Creating, Dropping, and Altering Tables and Views,» Oracle: The Complete Reference, Electronic Edition (1997):383.

Solution or Workaround

Changing the output/display column width in the INFO table to a width larger than the attribute value before loading the coverage into the Oracle table eliminates the error, and should allow the data to load.

In the previous example, changing the column width from 3 to 4 or higher will correct the problem.
Find offending INFO column(s) and increase output/display width size using ArcCatalog™

or INFO

.

Note: ArcINFO™ Workstation refers to the column as Output width.
ArcInfo™ Desktop refers to the column as Display width.

• Legacy Products