Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Loader Problems
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