2

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)) ;
3
  • Welcome to the DBA.SE community. In order for the community to be able to provide you with an adequate answer, you would have to supply us with more details. E.g. if you could provide us with the DDL for the table plants, then that would help us immensely. Click on edit and add additional details to your question. Currently your question might be closed as Needs details or clarity - This question should include more details and clarify the problem..
    – John K. N.
    Commented Feb 7, 2023 at 12:34
  • 1
    PLEASE don't create Application tables into ANY of the Oracle-supplied Schemas! Create your own Schemas (each within their own Tablespace, ideally) and create whatever you want inside those. None of us have any idea what Our Friends in Redwood City are going to do with their next upgrade and if they decide they want a table called PLANTS in their SYSTEM schema, then yours will be trashed.
    – Phill W.
    Commented Feb 7, 2023 at 13:47
  • Why do you enclose numeric values with quotes? That is bad practice. And you should never use TO_DATE() on a value which is already a DATE (in your case SYSDATE) Commented Feb 8, 2023 at 14:30

1 Answer 1

2

I assume that you have a column typed as NUMBER(precision, scale) in the table plants where precision is the maximum total number of digits before and after the decimal point and scale is the number of digits after the decimal point. Look for such a column. A value you are inserting exceeds these limits.

Also, you are inserting most of the values as string enclosed in single quotes. Insert values into number columns without quotes. Even if it works in Oracle, it doesn't in other databases and it makes it difficult to detect numeric columns in the insert statement.

Now that you have added the table declaration, I see that you are inserting '200681' into id_tax_code which is typed as NUMBER(5,0) but the value has 6 digits (if I matched the columns right).

I also wonder why GUID columns are typed as VARCHAR2(200 BYTE). They have a length of 36 characters.

And field names like FIELD48 are terrible.

I also suggest inserting line breaks e.g., after every five columns and every five values in the insert statement. This makes it much easier to match columns and values:

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'
)

See also:

  • Data Types
  • Nulls. You should insert NULL instead of '' for empty columns. Optionally, you could drop the column name from the columns list as well as the corresponding value from the values list. Columns that are not inserted explicitly will default to the column default value or NULL if no default value is specified.
3
  • For completeness would you like tomention DP1 as well. Commented Feb 7, 2023 at 13:02
  • '0' is inserted into dp1 which is typed as NUMBER(3,0). So, this should work (except that the apostrophes bother me). Commented Feb 7, 2023 at 13:09
  • My mistake, I miscounted the fields. Commented Feb 7, 2023 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.