Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle error ORA-01861

Re: Oracle error ORA-01861

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 23 Sep 1999 13:31:18 -0400
Message-ID: <pmHqNz+1fGVDXfIXeVY6BjhK+Vne@4ax.com>


On Thu, 23 Sep 1999 12:00:23 GMT, Oliver Schoenwaelder <schoeni_at_de.infogrames.com> wrote:

>Hi,
>
>I've got a problem with this insert statement:
>
>insert into impkunde
>(impkdn_mandant,impkdn_satznummer,impkdn_grust,impkdn_yversionn,impkdn_n
>um,impkdn_such,impkdn_stand,impkdn_erfass,impkdn_datum,impkdn_wechsel,im
>pkdn_land,impkdn_spr,impkdn_rab,impkdn_gart,impkdn_stverf,impkdn_vzweig,
>impkdn_grentf,impkdn_vertret,impkdn_limit,impkdn_name,impkdn_zeich,impkd
>n_kenn,impkdn_bem,impkdn_ans,impkdn_str,impkdn_aort,impkdn_lakenn,impkdn
>_plz,impkdn_nort,impkdn_ans2,impkdn_str2,impkdn_aort2,impkdn_lakenn2,imp
>kdn_plz2,impkdn_nort2,impkdn_tele,impkdn_yemail,impkdn_fax,impkdn_kontak
>t,impkdn_bank,impkdn_blz,impkdn_konum,impkdn_post,impkdn_poblz,impkdn_po
>num,impkdn_frbez,impkdn_ysonst,impkdn_ustid,impkdn_grkenn,impkdn_vart,im
>pkdn_vart2,impkdn_vart3,impkdn_ablade,impkdn_kvz,impkdn_labkv,impkdn_yze
>nreg,impkdn_yvis,impkdn_yzretour,impkdn_ylkont,impkdn_ylsperr,impkdn_ypr
>i,impkdn_ymretour,impkdn_ynralt,impkdn_yzrab,impkdn_yretour,impkdn_yezdr
>uck,impkdn_ydigrad,impkdn_ykatalog,impkdn_ykugrp,impkdn_ypfach,impkdn_yg
>typ,impkdn_ywww,impkdn_ysofortre,impkdn_ybsitz,impkdn_ygrund,impkdn_yfil
>iale,impkdn_yklass,impkdn_yspid)
>VALUES
>('LAG','LAG.0.1.5028',1,'0001oo4F','520','QUELLE','19990716','19970531',
>'19990921',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,N
>ULL,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,NULL,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.

That error happens when you are trying to convert a string to a date. It looks like you are inserting into the date columns impkdn_stand, impkdn_erfass, and impkdn_datum values of '19990716', '19970531' and '19990921'. Well that is not the default Oracle date format. What you need to do is to a to_date on the inputted values for the format that you are inserting.

eg.

  insert into impkunde (

    impkdn_stand,
    impkdn_erfass,
    impkdn_datum )

  values (
    to_date( '19990716', 'YYYYMMDD' ),
    to_date( '05311999', 'MMDDYYYY' ),
    to_date( '1968-DEC-03 17:33:12', 'YYYY-MON-DD HH24:MI:SS' ) )



hope this help

chris.

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

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 23 1999 - 12:31:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US