8 Replies Latest reply: Jul 26, 2011 2:15 AM by Mkirtley-Oracle RSS

    The DATE type data can't be migrated with SQL Developer from SQL Server2008

    873590
      Hi,
      When migrate the table SPSSCMOR__CUSTOMPROPERTYDAY data from a SQL Server 2008 to Oracle 11, always failed. Could you please tell me how to deal with this kind of error when only migrate data with SQL Developer migration wizard? Thanks so much.
      SQL Developer version:3.0.04

      The error log is:
      Record 1: Rejected - Error on table CDS41P2.SPSSCMOR__CUSTOMPROPERTYDAT, column XVALUE.
      ORA-01843: not a valid month

      Record 2: Rejected - Error on table CDS41P2.SPSSCMOR__CUSTOMPROPERTYDAT, column XVALUE.
      ORA-01843: not a valid month

      Record 3: Rejected - Error on table CDS41P2.SPSSCMOR__CUSTOMPROPERTYDAT, column XVALUE.
      ORA-01843: not a valid month

      The data is as below:
      091ED7D267D38155000001310342278E86E8<EOFD>0<EOFD>2010-10-16<EORD>091ED7D267D38155000001310342278E86F1<EOFD>0<EOFD>2010-10-16<EORD>091ED7D267D38155000001310342278E8701<EOFD>0<EOFD>2010-10-16<EORD>


      In SQL Server 2008,
      The table SPSSCMOR__CUSTOMPROPERTYDAY's DDL is:
      USE [SCDS41P2]
      GO

      /****** Object: Table [dbo].[SPSSCMOR__CUSTOMPROPERTYDAT] Script Date: 07/22/2011 01:42:50 ******/
      SET ANSI_NULLS ON
      GO

      SET QUOTED_IDENTIFIER ON
      GO

      SET ANSI_PADDING ON
      GO

      CREATE TABLE [dbo].[SPSSCMOR__CUSTOMPROPERTYDAT](
           [objid] [binary](18) NOT NULL,
           [objver] [int] NOT NULL,
           [XVALUE] [date] NULL,
      PRIMARY KEY CLUSTERED
      (
           [objid] ASC,
           [objver] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY]

      GO

      SET ANSI_PADDING OFF
      GO


      In Oracle 11, the same table SPSSCMOR__CUSTOMPROPERTYDAY's DDL is:
      --------------------------------------------------------
      -- DDL for Table SPSSCMOR__CUSTOMPROPERTYDAT
      --------------------------------------------------------

      CREATE TABLE "CDS41P2"."SPSSCMOR__CUSTOMPROPERTYDAT"
      (     "OBJID" RAW(18),
           "OBJVER" NUMBER(38,0),
           "XVALUE" DATE
      ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "USERS" ;
      --------------------------------------------------------
      -- DDL for Index SYS_C009871
      --------------------------------------------------------

      CREATE UNIQUE INDEX "CDS41P2"."SYS_C009871" ON "CDS41P2"."SPSSCMOR__CUSTOMPROPERTYDAT" ("OBJID", "OBJVER")
      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "USERS" ;
      --------------------------------------------------------
      -- Constraints for Table SPSSCMOR__CUSTOMPROPERTYDAT
      --------------------------------------------------------

      ALTER TABLE "CDS41P2"."SPSSCMOR__CUSTOMPROPERTYDAT" MODIFY ("OBJID" NOT NULL ENABLE);

      ALTER TABLE "CDS41P2"."SPSSCMOR__CUSTOMPROPERTYDAT" MODIFY ("OBJVER" NOT NULL ENABLE);

      ALTER TABLE "CDS41P2"."SPSSCMOR__CUSTOMPROPERTYDAT" ADD PRIMARY KEY ("OBJID", "OBJVER")
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "USERS" ENABLE;

      Edited by: 870587 on Jul 22, 2011 2:06 AM