ORA-01861 when exporting from Oracle 8.1.6.0

From: Oracle DBA - Oweson Flynn <OracleDBA_at_flysaa.com>
Date: Thu, 8 Jun 2000 16:04:35 +0200
Message-Id: <10522.108216@fatcity.com>

Hi All,

Trying to test migration from 7.3.4.5.0 to 8.1.6.0 ....

Environment:
Solaris 2.7
Oracle 8.1.6

When I try an import a table into my 8.1.6 instance, with a date field with an implicit date conversion, I get an 'ORA-01861: Literal does not match format string' error. However, the import works fine on 7.3.4.5. I have tries setting my NLS_DATE_FORMAT in an environment variable, but it makes no difference.

Anyway I can get this import to work??

Here are the scripts to recreate the situation:

Create the table (on Oracle 7 or Oracle - makes no difference), and put some data in:

DROP TABLE Oweson;

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';

CREATE TABLE Oweson
( Id NUMBER(3),

STart_Date DATE DEFAULT '1970-01-01' );

INSERT INTO Oweson VALUES ( 1, '1990-07-14' );

INSERT INTO Oweson( ID) VALUES ( 2 );

INSERT INTO Oweson VALUES ( 3, '200-06-08' );

COMMIT;

2) Export the table

exp userid=oef/oef_at_oracle8 file=oef.dmp log=oef-exp.log tables=oweson


----------------------------------------------------------------------------

-----------------------------------------------------


and here is the output


----------------------------------------------------------------------------

-----------------------------------------------------

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option
JServer Release 8.1.6.0.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                         OWESON          3 rows exported

Export terminated successfully without warnings.

3) Import the table into Oracle 7 (remember to drop it, if it already exists)
Works without having to set the NLS_DATE_FORMAT environment variable, but to be consistent ...

export NLS_DATE_FORMAT='YYYY-MM-DD'

imp userid=oef/oef_at_oracle7 file=oef.dmp log=oef-imp.log tables=oweson


----------------------------------------------------------------------------

-----------------------------------------------------


and the output


----------------------------------------------------------------------------

-----------------------------------------------------

Connected to: Oracle7 Server Release 7.3.4.5.0 - Production With the distributed and parallel query options PL/SQL Release 2.3.4.5.0 - Production

Export file created by EXPORT:V07.03.04 via conventional path . importing OEF's objects into OEF

. . importing table                       "OWESON"          3 rows imported

Import terminated successfully without warnings.

4) And the import into Oracle 8.1.6
(remember to drop it, if it already exists)

export NLS_DATE_FORMAT='YYYY-MM-DD'

imp userid=oef/oef_at_gensing file=oef.dmp log=oef-imp.log tables=oweson

and the output

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option
JServer Release 8.1.6.0.0 - Production

Export file created by EXPORT:V08.01.06 via conventional path import done in US7ASCII character set and US7ASCII NCHAR character set . importing OEF's objects into OEF
IMP-00017: following statement failed with ORACLE error 1861: " ALTER TABLE "OWESON" MODIFY ("START_DATE" DEFAULT '1970-01-01'" ")"
IMP-00003: ORACLE error 1861 encountered ORA-01861: literal does not match format string Import terminated successfully with warnings.

Regards
Oweson
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Oweson Flynn
Senior Oracle DBA
Email: OracleDBA_at_flysaa.com

Tel:    978-9826
Cell:   082-600-7-006
Fax:    978-1568


##########################################################

The original message has been scanned for known viruses with F-Secure Anti-Virus for Microsoft Exchange and it has been found clean. Received on Thu Jun 08 2000 - 09:04:35 CDT