Re: Oracle error ORA-01861

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 22 Sep 1999 15:27:09 +0100
Message-ID: <938011231.7906.0.nnrp-10.9e984b29@news.demon.co.uk>

You seem to have a few Date columns where you are inserting values like:

YYYYMMDD
19990716

The default translation from strings to dates is

dd-mon-yy
16-jul-99

Unless you change the global date format string, or rewrite the insert with a to_date() call, this would cause the error you have quoted. e.g.

to_date('19990716','yyyymmdd')

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Oliver Schoenwaelder wrote in message
<37E8D88A.CA245E48_at_de.infogrames.com>... Hi,

I've got a problem with this insert statement:

insert into impkunde
(impkdn_mandant,impkdn_satznummer,impkdn_grust,impkdn_yversionn,impkdn_num,i
mpkdn_such,impkdn_stand,impkdn_erfass,impkdn_datum,impkdn_wechsel,impkdn_lan

d,impkdn_spr,impkdn_rab,impkdn_gart,impkdn_stverf,impkdn_vzweig,impkdn_grent
f,impkdn_vertret,impkdn_limit,impkdn_name,impkdn_zeich,impkdn_kenn,impkdn_be
m,impkdn_ans,impkdn_str,impkdn_aort,impkdn_lakenn,impkdn_plz,impkdn_nort,imp

kdn_ans2,impkdn_str2,impkdn_aort2,impkdn_lakenn2,impkdn_plz2,impkdn_nort2,im pkdn_tele,impkdn_yemail,impkdn_fax,impkdn_kontakt,impkdn_bank,impkdn_blz,imp kdn_konum,impkdn_post,impkdn_poblz,impkdn_ponum,impkdn_frbez,impkdn_ysonst,i mpkdn_ustid,impkdn_grkenn,impkdn_vart,impkdn_vart2,impkdn_vart3,impkdn_ablad e,impkdn_kvz,impkdn_labkv,impkdn_yzenreg,impkdn_yvis,impkdn_yzretour,impkdn_ ylkont,impkdn_ylsperr,impkdn_ypri,impkdn_ymretour,impkdn_ynralt,impkdn_yzrab ,impkdn_yretour,impkdn_yezdruck,impkdn_ydigrad,impkdn_ykatalog,impkdn_ykugrp ,impkdn_ypfach,impkdn_ygtyp,impkdn_ywww,impkdn_ysofortre,impkdn_ybsitz,impkd n_ygrund,impkdn_yfiliale,impkdn_yklass,impkdn_yspid) VALUES
('LAG','LAG.0.1.5028',1,'0001oo4F','520','QUELLE','19990716','19970531','199
90921',0.00,'DM','D',NULL,'LAG.12.3.2395','LAG.12.3.2398','LAG.12.3.2393',0, 'LAG.0.1.98',950000.00,'fürth
nürnberger straße 91-95','O. Schoeni',NULL,NULL,'QUELLE G. SCHICKEDANZ;ABTEILUNG V/400','NÜRNBERGER STRAßE 91-95',NULL,'D','90762','FÜRTH',NULL,NULL,NULL,'D',NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'UPS frei',NULL,NULL,NULL,0,0,'LAG.0.1.5028','LAG.23.0.5154',0,NULL,NULL,0,0.00,N ULL,0.00,0,0,0,0,'LAG.12.3.901',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'C',NULL) Oracle returns "ORA-01861: literal does not match format string". I read the error explanation but don't know what the error is.

The table definition is as follows:

create table impkunde (

impkdn_satznummer varchar2 (28) CONSTRAINT PK_IMPKDN_SATZNUMMER primary key ,

   impkdn_mandant       varchar2 (3)  ,
   impkdn_grust number(38)   ,
   impkdn_yversionn     varchar2 (8)  ,
   impkdn_num   varchar2 (8)  ,
   impkdn_such  varchar2 (8)  ,
   impkdn_stand date  ,
   impkdn_erfass        date  ,
   impkdn_datum date  ,
   impkdn_wechsel       number(15)   ,
   impkdn_verd  varchar2 (28)  ,
   impkdn_land  varchar2 (4)  ,
   impkdn_spr   varchar2 (1)  ,
   impkdn_rab   varchar2 (3)  ,
   impkdn_rechnung      varchar2 (28)  ,
   impkdn_gewaehr       varchar2 (28)  ,
   impkdn_lbed  varchar2 (28)  ,
   impkdn_pbed  varchar2 (28)  ,
   impkdn_zbed  varchar2 (28)  ,
   impkdn_gart  varchar2 (28)  ,
   impkdn_stverf        varchar2 (28)  ,
   impkdn_vzweig        varchar2 (28)  ,
   impkdn_grentf        number(38)   ,
   impkdn_vertret       varchar2 (28)  ,
   impkdn_betreuer      varchar2 (28)  ,
   impkdn_haendl        varchar2 (28)  ,
   impkdn_limit number(15)   ,
   impkdn_as    varchar2 (28)  ,
   impkdn_name  varchar2 (45)  ,
   impkdn_zeich varchar2 (15)  ,
   impkdn_kenn  varchar2 (300)  ,
   impkdn_bem   varchar2 (300)  ,
   impkdn_ans   varchar2 (100)  ,
   impkdn_str   varchar2 (34)  ,
   impkdn_aort  varchar2 (34)  ,
   impkdn_lakenn        varchar2 (3)  ,
   impkdn_plz   varchar2 (10)  ,
   impkdn_nort  varchar2 (34)  ,
   impkdn_ans2  varchar2 (100)  ,
   impkdn_str2  varchar2 (34)  ,
   impkdn_aort2 varchar2 (34)  ,
   impkdn_lakenn2       varchar2 (3)  ,
   impkdn_plz2  varchar2 (10)  ,
   impkdn_nort2 varchar2 (34)  ,
   impkdn_tele  varchar2 (70)  ,
   impkdn_yemail        varchar2 (70)  ,
   impkdn_fax   varchar2 (18)  ,
   impkdn_kontakt       varchar2 (20)  ,
   impkdn_bank  varchar2 (40)  ,
   impkdn_blz   varchar2 (10)  ,
   impkdn_konum varchar2 (13)  ,
   impkdn_post  varchar2 (39)  ,
   impkdn_poblz varchar2 (10)  ,
   impkdn_ponum varchar2 (13)  ,
   impkdn_frbez varchar2 (14)  ,
   impkdn_ysonst        varchar2 (16)  ,
   impkdn_ustid varchar2 (16)  ,
   impkdn_grkenn        varchar2 (6)  ,
   impkdn_vart  varchar2 (64)  ,
   impkdn_vart2 varchar2 (64)  ,
   impkdn_vart3 varchar2 (64)  ,
   impkdn_ablade        varchar2 (64)  ,
   impkdn_kvz   number(38)   ,
   impkdn_labkv number(38)   ,
   impkdn_yzenreg       varchar2 (28)  ,
   impkdn_yvis  varchar2 (28)  ,
   impkdn_yzretour      number(38)   ,
   impkdn_ylkont        date  ,
   impkdn_ylsperr       date  ,
   impkdn_ypri  number(38)   ,
   impkdn_ymretour      number(15)   ,
   impkdn_ynralt        varchar2 (8)  ,
   impkdn_yzrab number(15)   ,
   impkdn_yretour       number(1) CONSTRAINT CHECK_IMPKDN_YRETOUR CHECK


(impkdn_yretour between 0 and 1)   ,


   impkdn_yezdruck      number(1) CONSTRAINT CHECK_IMPKDN_YEZDRUCK CHECK


(impkdn_yezdruck between 0 and 1)   ,


   impkdn_ydigrad       number(1) CONSTRAINT CHECK_IMPKDN_YDIGRAD CHECK


(impkdn_ydigrad between 0 and 1)   ,


   impkdn_ykatalog      number(1) CONSTRAINT CHECK_IMPKDN_YKATALOG CHECK


(impkdn_ykatalog between 0 and 1)   ,


   impkdn_ykugrp        varchar2 (28)  ,
   impkdn_yhhaus        varchar2 (28)  ,
   impkdn_ypfach        varchar2 (10)  ,
   impkdn_ygtyp varchar2 (10)  ,
   impkdn_ywww  varchar2 (30)  ,
   impkdn_ysofortre     varchar2 (1)  ,
   impkdn_ybsitz        varchar2 (20)  ,
   impkdn_ygrund        varchar2 (65)  ,
   impkdn_yfiliale      varchar2 (10)  ,
   impkdn_yklass        varchar2 (1)  ,

impkdn_yspid varchar2 (8)
)

Can anyone help?

Best regards,

O. Schoenwaelder Received on Wed Sep 22 1999 - 09:27:09 CDT