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

Home -> Community -> Usenet -> c.d.o.server -> Loader Problems

Loader Problems

From: Dirk Lorek <dilore_at_pobox.com>
Date: Thu, 30 Dec 1999 09:15:32 GMT
Message-ID: <386b0d92.762423687@NEWS.SUPERNEWS.CO.UK>


I have problems loading a table containing a date column. The table definition is:

col1 number(4) not null
col2 varchar2(80) null
col3 date not null default '1900-01-01'

The nls_date_format parameter in the parameter (ora.ini) file is YYYY-MM-DD. A common insert via sqlplus works fine when only inserting col1 and col2. col3 is assigned the default date. Inserting col3 with the value '1900-01-01' works fine too.

When _loading_ only col1 and col2 (i e ommitting col3), I get the following error:

    Record xx: Rejected - Error on table MYTABLE     ORA-01861: literal does not match format string

When I allow nulls in col3 I get the same result. I have verified that

this has to do with col3, loading into a another table containing only col1 and col2 works perfectly.

Why do I not get the default date in these cases?

When loading col3 I tried several variants in the loader-control file:

   col3 CONSTANT "1900-01-01 00:00:00"                  ORA-01861
   col3 CONSTANT "1900-01-01"                           ORA-01861  
   col3 CONSTANT '1900-01-01 00:00:00'                  ORA-01861
   col3 CONSTANT '1900-01-01                            ORA-01861  
   col3 CONSTANT "TO_DATE('1900-01-01', 'YYYY-MM-DD')"
          ORA-01858: a non-numeric character was found where a numeric
                     was expected

The only thing that works is    

   col3 sysdate

Suggestions anyone?

Dirk Received on Thu Dec 30 1999 - 03:15:32 CST

Original text of this message

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