Oracle Database Discussions

Hi

I tried to migrate data from DB2 to Oracle.

There is some datatype timestamp issue.

In DB2, my table description as below,

------------------------------------------------
-- DDL Statements for table "DCLSUSER1"."SYS_ZIP_CODE"
------------------------------------------------

CREATE TABLE "DCLSUSER1"."SYS_ZIP_CODE" (
"STATE_CODE" INTEGER NOT NULL ,
"ZIP_CODE" VARCHAR(5) NOT NULL ,
"PO_COUNTY_NO" INTEGER NOT NULL ,
"AREA" VARCHAR(40) ,
"CITY" VARCHAR(30) ,
"GMT_OFFSET" DECIMAL(5,0) ,
"TIME_ZONE" INTEGER ,
"CREATED_BY" VARCHAR(25) NOT NULL ,
"CREATED_ON" TIMESTAMP NOT NULL ,
"UPDATED_BY" VARCHAR(25) ,
"UPDATED_ON" TIMESTAMP ,
"PK_ID_SYS_ZIP_CODE" INTEGER NOT NULL )
;

Also, in the exported file, the value of the column "CREATED_ON' as below,
"2005-04-13-11.13.57.972000" (for exampl)

Therefore, if I want to create such table in Oracle, how can I give the corresponding timestamp datatype for "CREATED_ON" and "UPDATED_ON"?

I ever tried to just directly use "CREATED_ON TIMESTAMP NOT NULL" and "UPDATED_ON TIMESTAMP" respectively in Oracle.

However, when I used sql*Loader to migrate data from DB2 to Oracle,
the log file shows,

Record 1: Rejected - Error on table SYS_ZIP_CODE, column CREATED_ON.
ORA-01843: not a valid month

Then, I checked the description for the table I created in Oracle. It shows the column "CREATED_ON TIMESTAMP(6)," The Oracle automatically added (6) after TIMESTAMP!!

So, in this case, how can I set that timestamp datatype for the column "CREATED_ON" and "UPDATED_ON" then it will run sqlloader smoothly?