This discussion is archived
8 Replies Latest reply: Jul 26, 2011 12:15 AM by mkirtley-Oracle RSS

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

873590 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points