Re: ORA-01861 when exporting from Oracle 8.1.6.0

Oweson,

This is definitely a bug. I reproduced it on 8.0 and 8.1. For some reason import is not picking up the NLS_DATE_FORMAT at the env level. When I set NLS_DATE_FORMAT in the init.ora and bounced the db it imported just fine.

HTH,

• Anita
  • Oracle DBA - Oweson Flynn <OracleDBA_at_flysaa.com> wrote:
    > 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:
    >
    > 1) 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.
    >
    ##########################################################
    > --
    > Author: Oracle DBA - Oweson Flynn
    > INET: OracleDBA_at_flysaa.com
    >
    > Fat City Network Services -- (858) 538-5051 FAX:
    > (858) 538-5051
    > San Diego, California -- Public Internet
    > access / Mailing Lists
    >
    

    ---

    
    > To REMOVE yourself from this mailing list, send an
    > E-Mail message
    > to: ListGuru_at_fatcity.com (note EXACT spelling of
    > 'ListGuru') and in
    > the message BODY, include a line containing: UNSUB
    > ORACLE-L
    > (or the name of mailing list you want to be removed
    > from). You may