RE: ORA-01861 when importing into Oracle 8.1.6.0

Hi Patrick,

( As a matter of interest - which would be more efficient - the implicit date conversion, or the explicit date conversion? Any thoughts on this? I would have thought the implicit method would first fail (as it trying to use a string), and then Oracle would try to do the date conversion of the string to a date, which would succeed; whereas the explicit conversion would immediately do the conversion, with no 'retries' )

I thought of that, but the table was created a LONG time ago with the implicit date conversion as the default value. I agree with you 100%, but it doesn't help with my migration from 7.3.4 to 8.1.6., where the table has already been exported in the old way - I may have to tell them to re-do the export after changing the default value.

Also, even if I pre-create the table on the destination instance with the explicit date conversion, and then try to do the import (with ignore=y), I get exactly the same error!

I'm going to try setting the NLS_DATE_FORMAT parameter in the inis<sid>.ora file (and bounce the instance), and try again.

Any other ideas?

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

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

-----Original Message-----
From: Elliott, Patrick [mailto:Patrick.Elliott_at_bestbuy.com] Sent: Thursday, June 08, 2000 9:48 PM
To: Multiple recipients of list ORACLE-L Subject: RE: ORA-01861 when exporting from Oracle 8.1.6.0

I don't think the environment variable is working. One way to fix this is to implicitely specify the to_date command in your default i.e. to_date('1970-01-01', 'YYYY-MM-DD').
Issue this statement before the export

alter table Oweson
modify start_date default to_date('1970-01-01', 'YYYY-MM-DD');

The other way to fix this is to make the nls_date_format parameter match what it was in the old instance in the init.ora.

> -----Original Message-----
> From: Oracle DBA - Oweson Flynn [SMTP:OracleDBA_at_flysaa.com]
> Sent: Thursday, June 08, 2000 10:15 AM
> To: Multiple recipients of list ORACLE-L
> Subject: ORA-01861 when exporting from Oracle 8.1.6.0
>
> 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
> also send the HELP command for other information (like subscribing).

-- 
Author: Elliott, Patrick
  INET: Patrick.Elliott_at_bestbuy.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
also send the HELP command for other information (like subscribing).
##########################################################